DAT312 Managing and Deploying your SQL Server schemas with Visual Studio Team Edition for Database Professionals

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

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

4. Overview of Team System for Database Professionals

5. Visual Studio Team System New! Visual Studio Team Suite SoftwareArchitects SoftwareDevelopers DatabaseProfessionals SoftwareTesters VisualStudioTeamExplorer VisualStudioIndustryPartners 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 MSF Process and Guidance 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

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

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

8. Database Development Life CycleThe cycle of life for database developers Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

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

10. Project ModelThe 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

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

12. DATABASE PROJECTS

13. 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

14. Schema Projects • Support three areas: • Data Generation Plans • Schema Objects • Scripts • Data Generation will be covered fully in talk DAT320

15. 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

16. 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

17. 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

18. Scripts • The Scripts folder contains two children folder by default: • PreDeployment • PostDeployment • 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

19. Import Database Schema • Also sometimes referred to as reverse engineer • Imports an existing database schema into a database project • For CTP3 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

20. 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 FullText Indexing enabled • Off for CTP3 • 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…

21. SOURCE CONTROL INTEGRATION

22. Source Control Integration • Full Integration with Visual Studio SCC Interfaces • Support out of the box for Team Foundation Server, Visual SourceSafe • 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

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

24. Database Development Life CycleThe cycle of life for database developers Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

25. Schema Compare

26. Schema Compare • What is Schema Compare? • Allows quick comparisons of: • project -> database • database -> 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

27. 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

28. Building and Deploying Database Projects

29. 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 • Deploy via SQL query tool • Deploy via MSBuild task • FUTURE: SQLCMD command support

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

31. Database Development Life CycleThe cycle of life for database developers Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

32. 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

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

34. Resources

35. Visual Studio Team System Breakout Sessions • DEV304 - Delving into Visual Studio 2005 Team Edition for Software Architects • 6/12/2006 10:45AM-12:00PM 160ABC • DEV307 - Delving into Visual Studio 2005 Team Edition for Software Developers • 6/12/2006 1:30PM-2:45PM Grand Ballroom A • DEV311 - Delving into Visual Studio 2005 Team Edition for Software Testers • 6/12/2006 5:00PM-6:15PM 104 ABC • DEV217 - Introducing Visual Studio 2005 Team Edition for Database Professionals • 6/13/2006 10:15AM-11:30AM 104 ABC • DAT312 - Managing and Deploying Your SQL Server Schemas with Visual Studio Team Edition for Database Professionals • 6/13/2006 1:00PM-2:15PM 156 ABC • DAT320 - Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals • 6/14/2006 8:30AM-9:45AM 160 ABC • DEV327 - 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:15AM-11:30AM 160 ABC • DEV429 - Visual Studio 2005 Team Foundation Server (Part 2): Developing Custom Process Templates, Work Item Types and Policies • 6/14/2006 2:00PM-3:15PM 160 ABC • DEV233 - Visual Studio 2005 Team Foundation Server: Using Metrics to Manage and Troubleshoot Your Projects • 6/14/2006 5:30PM-6:45PM 153 ABC • DEV237 - Visual Studio 2005 Team Foundation Server: Step-by-Step Migration and Adoption Planning • 6/15/2006 9:45AM-11:00AM 104 ABC • DEV439- Visual Studio 2005 Team System and Microsoft Solution Framework: Implementing an Agile or CMMI Process • 6/15/2006 1:00PM-2:15PM 156 ABC • DAT433 Under the Hood of Visual Studio Team Edition for Database Professionals 6/15/2006 • 1:00PM-2:15PM 160 ABC

36. Visual Studio Team System HOL / TCL Hands on Labs DEV005 Streamlining your Development Process with Visual Studio 2005 Team System DEV008 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:30AM-9:45AM Teamlook and TeamSpec in ContextModeling that Supports Visual Studio 2005 DEV TLC Theatre 6/14/2006 5:30PM-6:45PM Modeling that Supports Visual Studio 2005 DEV TLC Theatre 6/14/2006 5:30PM-6:45PM Visual Studio Team Edition for Database Professionals: Overview DEV TLC Theatre 6/15/2006 9:45AM-11:00AM Code Generation and Model Execution from DSL Tools DEV TLC Theatre 6/15/2006 2:45PM-4:00PM

37. Fill out a session evaluation on CommNet for a chance to Win an XBOX 360!

38. © 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows, 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.