Visual Studio Team Edition for Database Professionals

1. Visual Studio Team Editionfor Database Professionals Mario Szpuszta Software Architect Developer & Platform Group Microsoft Österreich GmbH. marioszp@microsoft.com http://blogs.msdn.com/mszCool Microsoft Confidential

2. MSDN Briefings – Organisation • Monthly technical briefings • Currently released technology • Your current needs • Invitation / Registration / Feedback • http://blogs.msdn.com/msdnat • http://blogs.msdn.com/talk • Well, what I am doing here?

3. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

7. Static Code Analysis

8. Unit Testing

9. The Development Engine IT Governance Operational Excellence • Deployment • Impact analysis • Updates and Maintenance • App Health Monitoring • Security • Automated failover and recovery plans • Workflow customization • Business process re-engineering • Demand generation • Business Value Capture • Outsourcing • Resource planning Test Arch PM Dev IT Solution Lifecycle Value through “Better Together” integration across the Solution Lifecycle

10. Process and Architecture Guidance Code Profiler Change Management Work Item Tracking Reporting Project Site Integration Services Project Management Dynamic Code Analyzer Build Server Unit Testing Static Code Analyzer Team Foundation Client Code Coverage Deployment Modeling Visio and UML Modeling Application Modeling Test Case Management Logical Infra. Modeling Load Testing Class Modeling VS Pro Manual Testing Visual Studio Industry Partners Visual Studio Team System Visual StudioTeam Architect Visual StudioTeam Developer Visual StudioTeam Test Visual StudioTeam Foundation

12. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

13. Product Overview (1) • Database Project System • Schema and Script Versioning • SCC Integration • T-SQL Editor with Query Execution • Database Schema Management • Build & Deploy • Schema Compare • Data Compare

14. Product Overview (2) • Database Unit Testing • (Test) Data Generator • Stored Procedure Unit Testing • Schema Refactoring • Version 1: Change names, only • Future versions: indexes, constraints, relations • Team Foundation Server Integration • Work Item Tracking • Process Integration

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

16. Production Database Management Studio Schema Conceptual Overview • Difficult to Manage Change to the schema • Production Database is 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

17. Production Database Management Studio Schema Conceptual Overview • Schema Change now managed in VSTS and TFS • Production Database is now “One version of the truth” only for 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 Changes can be rolled out in a scheduled, managed way Scripts allow administrators to mange change updates “One Version of the Truth” for Schema • Offline • Under Source Control Schema Changes

18. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

19. Project ModelThe center of gravity 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 (*)

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. Production Database Test Database Offline Model • Import database schema to populate project from existing database • Changes to schema traditionally have immediate affect • With off-line project nothing changes until you deploy the change Create table AUCTION ( id int not null, title varchar(25) not null, startDate DateTime not null, length in not null)

22. Reverse Engineering a Schema DEMO

23. Shredding in to SQL Fragments • Loading, importing or reverse engineering • Shreds the schema definition • Smallest possible DDL fragments • Example: • Table • CREATE TABLE [dbo].[Territories]([TerritoryID] [nvarchar] (20) NOT NULL,[TerritoryDescription] [nchar] (50) NOT NULL,[RegionID] [int] NOT NULL) ON [PRIMARY] • Primary Key • ALTER TABLE [dbo].[Territories] ADD CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED ([TerritoryID]) ON [PRIMARY] • FK • ALTER TABLE [dbo].[Territories] ADDCONSTRAINT [FK_Territories_Region] FOREIGN KEY ([RegionID]) REFERENCES [dbo].[Region] ([RegionID])

24. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

25. Managed Change • Changes are local • Comparison between databases • Test database • Production database • Elements under source control • Any SCCI compliant version system • Template driven • Version specific SQL 2000 or SQL 2005

26. Working With the Project • Make changes • Add new elements • Modify existing elements • Delete Items • Compare databases • Build update script • Deploy new or incremental update • Visual Studio • MSBuild action

27. Build/Deploy Standard VS build task Configurations New vs. Update 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 RTM: SQLCMD command support

28. Changing the database DEMO

29. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

30. What you need for Testing? Updated schema Test drivers (unit tests) Tons of data (realistic)

31. Data GenerationDesign Time • Data generator component • Strategy for generation • Distribution for generator • Range of values • Relation between values • Settings for generator & distribution • Number of rows • Row-count ratios between tables

32. Data GenerationDesign Time – Default Behavior • Per column generator • Matching data type • Aware of CHECK constraints • Special attributes • Foreign Keys  Foreign Key generator • Uniqueness  PK, UC, indexes • Default distribution • Uniform distribution when not unique

33. Data Generation – Value Generators • Simple generators for each data type • Strings (char, varchar, nvarchar…) • Numbers (smallint, int, bigint, float…) • Binary (varbinary, image…) • Date and Time • UUID and Bit • Complex generators • Foreign Key • Regular Expression • Data Bound

34. Data GenerationDistributions @ Work

35. Data GenerationDesign Time • Understand domain constraints • Check constraints (min/max) • Table cardinality • Enforce table ratios • Column value distribution

36. Generate Test-Data DEMO

37. Database Unit TestingDesign Time • Automatically generate unit tests: • Stored Procedures, Functions, Triggers • Test Validation (assertions) • T-SQL  RAISERROR • Client Assertions • None Empty ResultSet • Row Count • Execution Time, … • Pre & Post Test Scripts

38. Database Unit Testing Test Execution • Automatic Deployment Integration • Automatically deploy database project prior to running tests • Data Generation Integration • Automatically generate data based on generation plan prior to running tests • Execution & Validation connections • Validation connection can be higher privileged account

39. Create a Unit Test DEMO

40. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

41. Database Schema RefactoringWhat is refactoring? • “A database refactoring is a small change to your database schema which improves its design without changing its semantics.” • Agile Database Development, Scott Ambler

42. Database Schema Refactoring Rename Refactoring… • Rename any SQL 2000/2005 schema object • Updates all references in… • Schema Objects • Data Generation Plans • Scripts • Database Unit Tests • Preview changes prior to commit • Global undo to reverse all changes

43. Database Schema Refactoring Refactoring Safety Net • Unit Testing • Generate tests after refactoring • Version Control • Store all previous versions before refactoring • Schema Compare • Analyze the exact differences between the project and live database to understand the impact of the update

44. Refactoring an Object DEMO

45. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

46. Command Line Building • Using devenv.exe • Visual Studio shell in command line mode • Using MSBuild.exe • Important note: • In CTP3 the project needs to be opened inside Visual Studio!

47. Project Properties • SET options • Only override when different • Collations • Only override when different • Difference between New and Update

48. Building Using MSBuild • Build – New database script • msbuild NorthwindOnline.dbproj /t:build • msbuild NorthwindOnline.dbproj /t:build /p:Configuration="New Deployment" • Build – Update for defined target server • msbuild NorthwindOnline.dbproj /t:build /p:Configuration="Update Deployment" /p:TargetConnectionString="Data Source=(local)\sql80;Integrated Security=True;Pooling=False;" /p:TargetDatabase="NorthwindOnlineTestRun"

49. Deploying Using MSBuild • Deploy – New database • msbuild NorthwindOnline.dbproj /t:deploy /p:Configuration="New Deployment" • Deploy – Update Database • msbuild NorthwindOnline.dbproj /t:deploy /p:Configuration="Update Deployment" /p:TargetConnectionString="Data Source=(local)\sql80;Integrated Security=True;Pooling=False;"

50. Misc. Actions Using MSBuild • All (Build + Deploy) • msbuild NorthwindOnline.dbproj /t:all • Clean • msbuild NorthwindOnline.dbproj /t:clean • msbuild NorthwindOnline.dbproj /t:clean /p:Configuration="New Deployment" • msbuild NorthwindOnline.dbproj /t:clean /p:Configuration="Update Deployment"