Due to the fact the keynote ran over 15 min. we will start at 10:45. Sorry!

2. DAT317 Due to the fact the keynote ran over 15 min. we will start at 10:45.Sorry!

3. DAT317 Database Schema Versioning How to Use Microsoft Visual Studio Team Edition for Database Professionals and Team Foundation Server to Version and Deploy Your Databases Gert E.R. Drapers Software Architect Microsoft Corp.

4. Agenda • The Database Development Lifecycle • Working in a Team Environment • How to Establish your Project • The Project System • Understanding the Schema • The Schema Object Container • Build and Deploy

5. Incorporate the Database Professional into the software lifecycle and provide them with a foundation for change management and process integration. • Change Management • Project Based Development • Project Model that represents schema as objects providing a “personal sandbox” for offline development that lives within a Visual Studio Solution • Team Collaborationwith Work Item and Process Integration with Team Foundation Server • Automated Change Support • Rename Refactoring with the ability to preview pending changes prior to execution • Comparison Tools (Schema & Data Compare) allow comparisons& synchronization of schema and data with design/test/production databases • Source/Version Control of all database objects with the ability to reverse engineer a database to bring it under Source Control • Database Unit Testing • Leverages theTest Project Infrastructure • GenerateMeaningfulData Valuesthrough the ability to import information such as Row Counts and histograms from a real database • Data Generator provides Repetitive Dataset Generation for tests based on saved settings • Build / Deployment • MSBuild Integrationfor Database Deployments/Builds based on Projects • Either Create a new Database at the target location or Update an Existing Schema

6. Production Database Management Studio Schema Conceptual Overview • Difficult to manage changes to the schema • Production database is “the” one version of the truth for Data and Schema • DBA doesn’t have access to changes until he/she has deploy or reject choice • Changes often made to production database and not rolled back into test Tuning Monitoring Schema Changes “One Version of the Truth”for Data and Schema

7. Production Database Management Studio Conceptual Overview • Schema Change now managed in VSTS and TFS • Production Database is now “the” version of the truth” for just the Data • DBA doesn’t have access to changes until he/she has deploy or reject choice • “One Version of the truth for Schema” is Under Source Control Tuning Monitoring “One Version of the Truth” for Data “One Version of the Truth”for Schema Changes can be rolled out in a scheduled, managed way Scripts allow administrators to mange change updates • Offline • Under Source Control Schema Schema Changes

8. Database Development Life Cycle Collection of .SQL file containing T-SQL DDL fragments Import database schema SQLServerDatabase Database Project Template Database Project Create New Project SQL Script Reverse engineer existing .SQL script files

9. Offline Schema Model • Parsing  SqlCodeDom (based on Abstract Syntax Trees) • Interpretation  Schema Model • Symbol list • Object References (hard and soft dependencies)

10. Database Development Life Cycle Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

11. DeploySQLScript SQLServerDatabase Database Development Life Cycle Import database schema SQLServerDatabase Build project Database Project Template Database Project Create New Project Deploy project SQL Script Reverse engineer existing .SQL script files

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

13. demo Establish the Project Environment Gert E.R. Drapers Software Architect Microsoft Corp.

14. SCM Production Database Staging Database Establish the project environment Check in to Source Control DBDev Trust Boundary DBA Database Project Import schema

15. Sync • Check-out • Edit/Refactor • Test • Check-in • Work is being drivenand tracked viawork items Sandbox Sandbox Sandbox Sandbox SCM Production Database Staging Database Isolated Iterative Development DBDev Trust Boundary DBA

16. Daily Build Output Can also beused in a “Continuous”build environment SCM Production Database Staging Database Test Database Build Cycle Get Latest Daily Build Test DBDev Trust Boundary DBA

17. SCM Production Database Staging Database SQL Deploy Script Deploy the project environment Sync from Label DBDev Trust Boundary DBA Deploy Database Project Build Verify Refine deploy script

18. Benefits of this Approach • Managed, project oriented evolution of database schema • Application and database schema can now be managed together • Work in “isolation”, deploying only when changes verified through empirical means • Leverage VSTS work item tracking and process guidance increases team collaboration and unity

19. demo Version the Project Gert E.R. Drapers Software Architect Microsoft Corp.

20. Offline Model • Project model • Schema Objects representation • Collection of T-SQL DDL fragments • Objects are Parsed and Interpreted at: • Project Load Time • Object Change (save) • Source Control Sync (external change)

21. Understanding your Schema • Build-up understanding of the DDL Fragments in Stages • Phase-1 Parsing • Retrieve the object identifier and object type • Phase-1 Interpretation • Retrieve additional type specifics like schemabinding • Phase-1 SQL Server Compile Validation • Perform compile time validation against (local) SQL Server, design database with is associated with the project • Phase-2 Parsing • Build a full AST (Abstract Syntax Tree, aka the parse tree) for the DDL fragment • Phase-2 Interpretation • Retrieve the remaining type specific detail from the AST • All stages contribute to building and maintaining the schema context • Object symbol list • Object dependency graph (tracking)

22. Understanding Schema Objects Schema Manager P1 Parsing P1 Interpre-tation Design DB Success Success Success Failed Failed Failed Success Error List Add to Schema Context Schema Manager P2 Parsing P2 Interpre-tation Update Schema Context Success Success Failed Failed Failed Warning List

23. File Naming & Extension Scheme • Everything is a .SQL file • Associated with the T-SQL editor • Using a two part naming scheme to identify types • This is not required, but helps identification of types • Providing visual feedback (icon) • Associating with code-behind designers in the future • Enforcing the single object per file • By default the file name encodes the object name • Not required • Filename do not have to match the containing type name • Required since SQL Server namespace restrictions do not match the file system naming restrictions

24. demo Exploring the Project System Gert E.R. Drapers Software Architect Microsoft Corp.

25. DeploySQLScript SQLServerDatabase Build & Deploy 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

26. Target Database Build & Deploy Project State “What you want” Current State “How it is right now” Database Project Build SQL Script Deploy Difference based build script Execute Incremental Update Script

27. demo Build and Deploy the Project Gert E.R. Drapers Software Architect Microsoft Corp.

28. Command Line Building • All steps are implemented as MSBuild tasks • SqlBuildTask • SqlDeployTask • DataGeneratorTask • All project properties can be overwritten at the command line • Note: • Be aware of the “user dependent” settings in the .dbproj.user file when using TeamBuild

29. Command-line Build • Using the project settings: • msbuild NorthwindOnline.dbproj /t:build • Overwriting project settings • msbuild NorthwindOnline.dbproj /t:build /p:TargetConnectionString="Data Source=(local)\sql80;Integrated Security=True;Pooling=False;" /p:TargetDatabase="NorthwindOnlineTestDB"

30. Command-line Deployment • Using the project settings: • msbuild NorthwindOnline.dbproj /t:deploy • Overwriting project settings: • msbuild NorthwindOnline.dbproj /t:deploy /p:TargetConnectionString="Data Source=(local)\sql80;Integrated Security=True;Pooling=False;"/p:TargetDatabase="NorthWind"

31. Provisioning Multiple Servers • How can I deploy to multiple targets? • The Database Project only understand a single target server/database at the time • You can use the MSBuild tasks to provision multiple servers • Using command line or tool that calls the MSBuild infrastructure • Pseudo code:for each server+database combination in list{ SqlBuildTask SqlDeployTask}

32. demo Building from the Command Line Gert E.R. Drapers Software Architect Microsoft Corp.

33. Team Foundation Build • Build Server infrastructure • Fully integrates with Team Foundation Server • Extensible • Based on MSBuild • Fully extensible XML-based scripting • Rich Command-line support • Remote and Desktop build support • Have to install Team Build separately • Not installed as part of Team Foundation Server

34. Team Foundation Build Architecture TF Client Team Build Client Build Configuration files - MSBuild Scripts Build request and Reports Build Server Build start/stop Team Build Service Build events Source Control Team Build Build sources and scripts MSBuild Scripts and targets Build Drop site Work item Tracking Open and Update bugs Team Build logger Static Analysis & Testing Build and Test data TFS Build Events Team Build Store TF Warehouse TFS Data Tier

35. Step 1 – Create Team Build Type • Using Team Explorer • Create Team Build Type using Wizard • Creates a TFSBuild.proj script located in a TeamBuildTypes folder in version control • You will need to customize this script

36. Step 2 – Customize Team Build Type • Check-out the TFSBuild.proj file for edit • Ensure you are using the Default configuration <ConfigurationToBuild Include="Default|Any CPU"> <FlavorToBuild>Default</FlavorToBuild> <PlatformToBuild>Any CPU</PlatformToBuild> </ConfigurationToBuild> • Add an AfterDropBuild target or AfterCompile if you are doing testing <Target Name="AfterDropBuild"> <MSBuild Projects="$(SolutionRoot)\SolutionName\ProjectName\ProjectName.dbproj" Properties="Configuration=Default;OutDir=$(SolutionRoot)\..\binaries\Default\" Targets="Deploy" /> </Target> • Check-in changes

37. Step 3 – Add Support for Testing • Ensure your build references the test list <MetaDataFile Include="$(SolutionRoot)\MyProject\MyProject.vsmdi"> <TestList>MyUnitTests</TestList> </MetaDataFile> • Ensure your build copies the app.config file from the test project

38. Step 4 – Create Custom App.Config • Database unit tests need to access the app.config to locate the .dgen file • Note: Relative paths are different in Team Build than in Visual Studio! • Copy App.Config to TeamBuild.App.Config and make the path changes <DataGenerationDataGenerationFileName="..\..\..\Sources\MySolutionName\MyProjectName\Data Generation Plans\MyProjectTestData.dgen" />

39. Step 5 – Copy Custom App.Config • Add another task to your Build Type <ItemGroup> <TestProjectConfigFile Include="C:\build\MySolutionName\MyProjectName\src\MySolutionName\MyTestProjectName\TeamBuild.app.config" /> </ItemGroup> <Target Name="AfterCompile"> <Copy SourceFiles="@(TestProjectConfigFile)" DestinationFiles="@(TestProjectConfigFile->'C:\Build\MySolutionName\MyTestProjectName\Binaries\Default\MyTestProjectName.dll.config')" /> </Target>

40. Step 6 – Modify Project.dbproj File • Target server and database settings are stored in a project.dbproj.user file • This file is not version controlled by default • You will have to manually edit the .dbproj file, copying lines over from the .user file <TargetConnectionString>Data Source=TFS\SQL90;Integrated Security=True;Pooling=False </TargetConnectionString> <TargetDatabase> MyProjectName </TargetDatabase> • Check changes back in when done

41. demo Building using Team Build Gert E.R. Drapers Software Architect Microsoft Corp.

42. Summary • Managed, project oriented evolution of database schema – no more rollbacks • Application and database schema can now be managed together • Work in “isolation”, deploying only when changes verified • Leverage VSTS work item tracking and process guidance • Further Questions: • Gert: gertd@microsoft.com

43. Q&A

44. Resources • MSDN Forum: Visual Studio Team System - Database Professionals • http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725&SiteID=1 • White papers • What Microsoft Visual Studio 2005 Team Edition for Database Professionals Can Do for You • A Security Overview of Microsoft Visual Studio 2005 Team Edition for Database Professionals • Database Unit Testing with Team Edition for Database Professionals • Samples • http://www.codeplex.com/vsdbpro • Trial Edition • http://www.microsoft.com/downloads/details.aspx?familyid=7de00386-893d-4142-a778-992b69d482ad&displaylang=en • Blog • http://blog.msdn.com/gertd

45. Resources Technical Communities, Webcasts, Blogs, Chats & User Groups http://www.microsoft.com/communities/default.mspx Microsoft Learning and Certification http://www.microsoft.com/learning/default.mspx Microsoft Developer Network (MSDN) & TechNet http://microsoft.com/msdn http://microsoft.com/technet Trial Software and Virtual Labs http://www.microsoft.com/technet/downloads/trials/default.mspx

46. Call to Action Visit the TLC Area to talk to SQL Server experts Download the latest SQL Server “Katmai” CTP from: http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx SQL Server TechCenter: http://www.microsoft.com/technet/prodtechnol/sql/default.mspx SQL Server DevCenter:http://msdn.microsoft.com/sql SQL Server Best Practices:http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

47. Complete an evaluation on CommNet and enter to win!