Introduction to VSTS Database Professional

1. Introduction to VSTS Database Professional Tejasvi Kumar Developer Technology Specialist | Microsoft India tejkumar@microsoft.com

2. Agenda Introduction to VSTS Database Development using VSTS Managing Database Change Building and Deploying a Database Project Data Generation Database Unit Testing Q & A

3. Visual Studio Team System

4. Merger of Developer and Database Editions • Customers currently owning VSTS Developer Edition or Database Edition will getVSTS Developer Edition + Database Edition • VSTS Developer Edition • Unit Testing and Code Coverage • Static Analysis • Code Coverage • Code Profiling • Code Metrics

5. Database Development Using VSTS • Database Development Life-Cycle Support • Database Development Team Integration with Visual Studio Team System • Creating Database Projects • Creating a New Database Project • Importing Database Objects from an Existing Database • Importing Database Objects from a Script

6. Database Development Life-Cycle Support Team Foundation Server Process Guidance supports two new roles Team Foundation Server Integration Database Administrator Database Developer • Version control management • Work item tracking • Team build integration Integrated client setup with Visual Studio Team System No separate setup is required

7. Database Development Team Integration with VSTS Team Foundation Server Code Files Test Files Project Files Development Testing Project Management • Work item tracking • Source control • Build support • Testing Database work items and artifacts are managed in the same way as other team work items and work products Database Developers Database Administrators Database Objects Database Files

8. DeploySQLScript SQLServerDatabase Creating Database Projects Import database schema SQLServerDatabase Database Project Template Build project Database Project Create new project Deploy project SQL Script Import existing .SQL script files

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

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

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

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

13. Managing Database Changes • Refactoring Database Objects • Introducing Database Schema Versioning • Support for Source Control Providers

14. Refactoring Database Objects • Database Edition supports object rename refactoring • Rename any SQL 2000 and SQL 2005 schema objects • VSTE DB refactoring supports changing the name of references in: • Schema objects • Data generation plans • Scripts • Database unit tests • Database Edition name refactoring supports a preview of changes before committing

15. Introducing Database Schema Versioning Production Database • Schema change managed in Team System and Team Foundation Server • Production Database is only used as the baseline for data, not for schema • The database administrator does not have access to schema changes until it is time to deploy • Baseline schema is under source control • Stores versioned schema • Uses source control • Changes can be rolled out in a scheduled, managed way • Scripts enable administrators to manage change updates Source Control Database Schema Changes

16. Support for Source Control Providers • SCCI provider-based • Support for Visual Studio based source control providers: • TFS • VSS • UI support for Visual Studio through Solution Explorer • By default, Visual Studio automatically checks out all files that are used by objects • Scripts are stored as Unicode scripts by default

17. Building and Deploying a Database Project • Using MSBuild • Deploying a Build: Full Deployment • Deploying a Build: Incremental Deployment

18. Data Generation • Introducing Design Time Data Generation • Generating Data at Design Time • Executing a Data Generation Plan

21. Database Unit Testing • Database Unit Testing Overview • Developing Database Unit Tests • Validating Tests • Executing Tests • Customizing and Extending Database Unit Tests

22. Database Unit Testing Overview • Ensures that database changes do not break existing code • Generates automated tests for programmability objects • Creates a battery of tests to run with every database build • Supports design and customization of tests by using different languages: • T-SQL • VB.NET • C# • Builds on the existing unit-testing functionality in Test Edition • Integrates database tests with application unit tests

25. DEMO VSTS Database Professional

26. References • Visual Studio Team System: • http://msdn.microsoft.com/teamsystem • Microsoft Application Platform: • http://msdn.microsoft.com/applicationplatform

27. Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form • Use the Question Manager on LiveMeeting to ask your questions now!

28. tejkumar@microsoft.com