DAT 312 Managing and Deploying your SQL Server

DAT 312 Managing and Deploying your SQL Server schemas with Visual Studio Team Edition for Database Professionals Richard Waymire PM Architect Microsoft Corporation Mairead A. O’Donovan Program Manager Microsoft Corporation

Agenda Overview of Team System for Database Professionals (TSDATA) Database Projects Source Control Integration Schema Compare Build and Deploy

Overview of Team System for Database Professionals

Visual Studio Team System New! Visual Studio Team Suite MSF Process and Guidance Visual Studio Team Explorer Software Architects Software Developers Software Testers Database Professionals Application Modeling Code Analysis Performance Testing Database Change Management Infrastructure and Deployment Modeling Performance Tuning Manual Testing Database Testing Test Case Management Database Deployment Security Analysis Unit Testing Code Coverage Class Modeling Visio and UML Modeling Visual Studio Professional Edition Load Test Agent Visual Studio Team Foundation Server Change Management Reporting Integration Services Work Item Tracking Project Site Project Management Visual Studio Industry Partners

Product Overview Database Project System Schema and Script Versioning (SCC Integration) Schema Build & Deploy Schema Compare Database Unit Testing (Test) Data Generator Schema Refactoring T-SQL Editor with Query Execution Work Item and Process Integration with TFS

Database Development Life Cycle The cycle of life for database developers SQL Server Database Project Template SQL Script Import database schema Create New Project Database Project Reverse engineer existing. SQL script files (*) Not implemented in the current CTP

Database Development Life Cycle The cycle of life for database developers Edit Refactor Compare Deploy Database Project Data Generation Test Compare Build

Database Development Life Cycle The cycle of life for database developers SQL Server Database Project Template SQL Script Import database schema Deploy SQL Script Build project Create New Project Database Project Deploy project Reverse engineer existing. SQL script files (*) Not implemented in the current CTP SQL Server Database

Project Model The center of gravity The database project represents the “truth” with regards to schema versioning Optionally database project can be placed under source control. SQL script files is the canonical format used Changes are tracked at the “object level” For example indexes, constraints, triggers are tracked independent of the base table definition, in order have the highest granularity of change tracking

Other Sessions Introducing Visual Studio Team Edition for Database Professionals DEV 217 - 6/13/2006 10: 15 AM - 11: 30 AM, room: 104 ABC Managing and Deploying your SQL Server Schemas with Visual Studio Team Edition for Database Professionals DAT 312 - 6/13/2006 1: 00 PM - 2: 15 PM, room: 156 ABC Testing & Refactoring your Database with Visual Studio Team Edition for Database Professionals DAT 320 - 6/14/2006 8: 30 AM - 9: 45 AM, room: 160 ABC Under the Hood of Visual Studio Team Edition for Database Professionals DAT 433 - 6/15/2006 1: 00 PM - 2: 15 PM, room: 160 ABC

DATABASE PROJECTS

Database Projects Defined “Schema” projects SQL Server 2000 SQL Server 2005 “Script Only” projects SQL Server 2000 SQL Server 2005 Different types to support different syntax/language options A project is simply a series of files collected together into a single logical collection The files represent the truth of your schema

Schema Projects Support three areas: Data Generation Plans Schema Objects Scripts Data Generation will be covered fully in talk DAT 320

Schema Objects The “Meat and potatoes” of the project system All SQL Server objects are reflected here All objects within user databases, that is And that are user-created objects (no system objects) Schema folders reflect the product version So “Service Broker” objects only show up in the SQL Server 2005 project type Table and View are containers for child objects Indexes, Triggers, Constraints, Statistics, Full-text Goal is to be as compatible as possible with SQL Server Management Studio Object Explorer view We use the same icons, folder view when possible

Templates For each type of object you can have in the project system, we have a version-specific template to create that object Wherever possible, the template will generate valid SQL But probably not the SQL you want They give you a starting point to write your SQL statements

Error States Any given project item (file) can have three error states: Clean You just see the expected icon for the object Dirty You get a red exclamation point superimposed on the icon for the object This means the script/DDL wasn’t understood by us, or The syntax is wrong Not fully interpreted/implemented yet You get a yellow warning triangle superimposed on the icon for the object This means we’re not done yet understanding some SQL code in the object So you shouldn’t see this when we release You can’t refactor over the object in this file But, it will build/deploy correctly Errors and warnings show up in the Error List

Scripts The Scripts folder contains two children folder by default: Pre. Deployment Post. Deployment Each contains a single script (for now) that will be either pre-pended or post-pended during project build More on that later… You can also insert any folder or. SQL script file into this area

Import Database Schema Also sometimes referred to as reverse engineer Imports an existing database schema into a database project For CTP 3 it only supports importing into an empty SQL Server 2000 project We use the schema compare engine under the covers to generate the script Then submit the script one piece at a time as if you’d typed them in yourself We then turn each object into a separate file in your project

Project Properties Project properties set a variety of information about a given database project Project Version Eventually you’ll be able to “upgrade” a project from SQL Server 2000 to SQL Server 2005 Default Schema When a new object is created, what schema does it go into by default Is Full. Text Indexing enabled Off for CTP 3 Default Collation What collation should be assumed as default for all schema objects Import Existing Schema will reset this to whatever the collation was on the source database Build Properties will be discussed later…

SOURCE CONTROL INTEGRATION

Source Control Integration Full Integration with Visual Studio SCC Interfaces Support out of the box for Team Foundation Server, Visual Source. Safe However, any SCCI compliant source system should work Standard Graphical Interface If you work with VB/C# projects and source control, we’ll behave the same Same icons on project items Same top level/context menu items

The Project System, Import Database Schema, and Source Control Integration Mairead A. O’Donovan Program Manager

Database Development Life Cycle The cycle of life for database developers Edit Refactor Compare Deploy Database Project Data Generation Test Compare Build

Schema Compare

Schema Compare What is Schema Compare? Allows quick comparisons of: project -> database GUI interface and/or SQL scripts to compare diffs Full SQL Server 2000 and SQL Server 2005 support Object level script differences between databases Handles Data Motion

Schema Compare (cont) Options for restricting/changing script Restrict which object types are compared Compare while ignoring schema, filegroups, etc. Enables comparison of test to production Turn off dependency checking Generate backwards compatible scripts i. e. script a 2005 database with a 2000 script Compare security settings Users, roles, and permissions

Building and Deploying Database Projects

Build/Deploy Standard MSBuild task Configurations New vs. Existing builds Project properties for build Schema compare used for build Pre/Post Deployment scripts Build results in. SQL script file Deploy via SQL query tool Deploy via MSBuild task FUTURE: SQLCMD command support

Schema Compare, Build and Deploy Database Project Mairead A. O’Donovan Program Manager

Database Development Life Cycle The cycle of life for database developers Edit Refactor Compare Deploy Database Project Data Generation Test Compare Build

Wrap Up Database Projects will change the way developers and DBAs work and think about change management in SQL Server Go get the CTP and try it out on your databases Report feedback (good & bad) Further Questions: Richard: rwaymi@microsoft. com Mairead: maireado@microsoft. com

Other Sessions Testing & Refactoring your Database with Visual Studio Team Edition for Database Professionals DAT 320 - 6/14/2006 8: 30 AM - 9: 45 AM, room: 160 ABC Under the Hood of Visual Studio Team Edition for Database Professionals DAT 433 - 6/15/2006 1: 00 PM - 2: 15 PM, room: 160 ABC

Resources Technical Chats and Webcasts http: //www. microsoft. com/communities/chats/default. mspx http: //www. microsoft. com/usa/webcasts/default. asp Microsoft Learning and Certification http: //www. microsoft. com/learning/default. mspx MSDN & Tech. Net http: //microsoft. com/msdn http: //microsoft. com/technet Virtual Labs http: //www. microsoft. com/technet/traincert/virtuallab/rms. mspx Newsgroups http: //communities 2. microsoft. com/communities/newsgroups/en-us/default. aspx Technical Community Sites http: //www. microsoft. com/communities/default. mspx User Groups http: //www. microsoft. com/communities/usergroups/default. mspx

Visual Studio Team System Breakout Sessions DEV 304 - Delving into Visual Studio 2005 Team Edition for Software Architects 6/12/2006 10: 45 AM-12: 00 PM 160 ABC DEV 307 - Delving into Visual Studio 2005 Team Edition for Software Developers 6/12/2006 1: 30 PM-2: 45 PM Grand Ballroom A DEV 311 - Delving into Visual Studio 2005 Team Edition for Software Testers 6/12/2006 5: 00 PM-6: 15 PM 104 ABC DEV 217 - Introducing Visual Studio 2005 Team Edition for Database Professionals 6/13/2006 10: 15 AM-11: 30 AM 104 ABC DAT 312 - Managing and Deploying Your SQL Server Schemas with Visual Studio Team Edition for Database Professionals 6/13/2006 1: 00 PM-2: 15 PM 156 ABC DAT 320 - Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals 6/14/2006 8: 30 AM-9: 45 AM 160 ABC DEV 327 - Visual Studio 2005 Team Foundation Server (Part 1): Applying Version Control, Work Item Tracking and Team Build to Your Software Development Project 6/14/2006 10: 15 AM-11: 30 AM 160 ABC DEV 429 - Visual Studio 2005 Team Foundation Server (Part 2): Developing Custom Process Templates, Work Item Types and Policies 6/14/2006 2: 00 PM-3: 15 PM 160 ABC DEV 233 - Visual Studio 2005 Team Foundation Server: Using Metrics to Manage and Troubleshoot Your Projects 6/14/2006 5: 30 PM-6: 45 PM 153 ABC DEV 237 - Visual Studio 2005 Team Foundation Server: Step-by-Step Migration and Adoption Planning 6/15/2006 9: 45 AM-11: 00 AM 104 ABC DEV 439 - Visual Studio 2005 Team System and Microsoft Solution Framework: Implementing an Agile or CMMI Process 6/15/2006 1: 00 PM-2: 15 PM 156 ABC DAT 433 Under the Hood of Visual Studio Team Edition for Database Professionals 6/15/2006 1: 00 PM-2: 15 PM 160 ABC

Visual Studio Team System HOL / TCL Hands on Labs DEV 005 Streamlining your Development Process with Visual Studio 2005 Team System DEV 008 Take a Tour of Visual Studio 2005 Team System for Database Professionals Chalktalks Building Quality In: Visual Studio Team System and Continuous Integrated Testing DEV TLC Theatre 6/14/2006 8: 30 AM-9: 45 AM Teamlook and Team. Spec in Context Modeling that Supports Visual Studio 2005 DEV TLC Theatre 6/14/2006 5: 30 PM-6: 45 PM Visual Studio Team Edition for Database Professionals: Overview DEV TLC Theatre 6/15/2006 9: 45 AM-11: 00 AM Code Generation and Model Execution from DSL Tools DEV TLC Theatre 6/15/2006 2: 45 PM-4: 00 PM

Fill out a session evaluation on Comm. Net for a chance to Win an XBOX 360!

© 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.