Introduction to Visual Studio Team Edition for Database Professionals

1. (code name: Data Dude) Introduction to Visual Studio Team Edition for Database Professionals Josh Robinson joshr@aculix.com Aculix

2. Need was identified to find missing member of Visual Studio family • Eric Rudder decided missing persona should be called “Data Dude” • Project focus: Replace“PROD” database with system to hold “one version of the truth” • Development began Sept 1, 2005 • 4 Developers at start A brief History of “data dude”

3. Manage DB change through schema management • “One version of the truth” • Offline sandbox for multiple developers • Source control for DBs • Ability to store different versions as changesets • Schema and data compare • Generate scripts/apply updates • Data generation • Generate meaningful test data automatically • DB Unit tests • Same test infrastructure as rest of Team System (uses SQL) What do I get out of the box?

4. Where does Data Dude fit in? 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 Database Build &Deployment Test Case Management 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

5. Production Database Test Database How is it all Connected? • 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)

6. What does a Develop Scenario look like? Manage Develop Deploy Writes Tests Writes DB Code Refactors Runs Tests Checks In Reviews Changes Compares Updates to Production Builds Deploy Package Deploys to Production Creates New DB Project Reverse Engineers DB to Project Creates Data Generation Plan DBA DB DEVELOPER DBA

7. Core concept: off-line database development • “Schema” projects • Microsoft SQL Server 2000 • Microsoft SQL Server 2005 • Simply a series of files collected together into a single logical collection • Objects are stored as .SQL files at the most atomic level • Schema view versus file view What is a Database project?

8. Included • All SQL Server objects within a user database • Must be user-created objects • Schema folders reflect the SQL version • i.e. “Service Broker” objects only appear in SQL 2005 projects • Not Included • System objects like logins, custom error messages • Team DB “knows” about them, included in scripts What’s included in a schema project?

9. DEMO – create A project and import A database

10. Standard MSBuild task • Configurations • New versus existing builds • Project properties for build • Schema compare used for build • Pre/Post Deployment scripts • Build results in SQL script file • Deploy • SQL query tool; Deploy via MSBuild task • SQLCMD command support Build and deploy a database schema

11. DEMO – Build and deploy a database

12. Data generation is used to create a solid foundation for testing • Deterministic – always generate the same layout • Matched to your schema and very customizable • Extensible mechanism, build your own data generators • Will support histograms and distributions in final release How is Data Generation implemented?

13. DEMO – Generate test data

14. Stored Procedures • Functions • Triggers • Arbitrary SQL • Support at RTM to automatically deploy changes to test system and generate data • Deterministic data generation ensures stable test state • Can test with your application tier because of common framework What can I test?

15. DEMO – Create a database unit test

16. Bring power of refactoring to SQL • Cascading Change • Update all dependent objects in database project • Schema objects, Data generation, Unit Tests, SQL Scripts • Make an atomic change, see preview • Rename • Meet corporate standards • Better express semantic intent – clarity • Supports global undo to back out any change Database refactoring

17. DEMO – Refactoring the database

18. Allows comparisons of: • Project -> database; database -> database • Full SQL Server 2000 and 2005 support • Object level script difference between DBs • Notifies when data loss may occur • Generate script or apply changes directly • It’s smart! • understands constraints, creates temp tables to hold data, more • Compare security settings • Users, roles and permissions Schema Compare

19. DEMO – Schema Compare

20. Included in Team Suite at No Extra Cost • Purchase as an individual Edition • Same pricing as other Team System Editions • Retail Price - $5469 • Many Discounts available • http://msdn.microsoft.com/vstudio/howtobuy/default.aspx • Availability • CTP 5 Available Today (CTP 6 out soon) • http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/ • RTM By the end of 2006 Pricing, Licensing and availability

21. The End