(code name: Data Dude) INTRODUCTION TO VISUAL STUDIO TEAM EDITION FOR DATABASE PROFESSIONALS Josh Robinson [email protected]

Aculix

A BRIEF HISTORY OF “DATA DUDE”

 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

WHAT DO I GET OUT OF THE BOX?

     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)

WHERE DOES DATA DUDE FIT IN?

Visual Studio Team Explorer Software Architects Application Modeling Infrastructure and Deployment Modeling Visual Studio Team Suite Software Developers Software Testers

New!

Database Professionals Code Analysis Performance Tuning Security Analysis Performance Testing Manual Testing Test Case Management Database Change Management Database Testing Database Build & Deployment Visual Studio Industry Partners Unit Testing Code Coverage 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

HOW IS IT ALL CONNECTED?

   Import database schema to populate project from existing database title varchar(25) not null, startDate DateTime not null, length in not null) immediate affect

Production

With off-line project nothing changes until you deploy the change

Database Test Database

WHAT DOES A DEVELOP SCENARIO LOOK LIKE?

Manage Creates New DB Project Reverse Engineers DB to Project Creates Data Generation Plan DBA Develop Writes Tests Writes DB Code Refactors Runs Tests Checks In DB DEVELOPER Deploy Reviews Changes Compares Updates to Production Builds Deploy Package Deploys to Production DBA

WHAT IS A DATABASE PROJECT?

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’S INCLUDED IN A SCHEMA PROJECT?

 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

DEMO – CREATE A PROJECT AND IMPORT A DATABASE

BUILD AND DEPLOY A DATABASE SCHEMA

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

DEMO – BUILD AND DEPLOY A DATABASE

HOW IS DATA GENERATION IMPLEMENTED?

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

DEMO – GENERATE TEST DATA

WHAT CAN I TEST?

       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

DEMO – CREATE A DATABASE UNIT TEST

DATABASE REFACTORING

     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

DEMO – REFACTORING THE DATABASE

SCHEMA COMPARE

       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

DEMO – SCHEMA COMPARE

PRICING, LICENSING AND AVAILABILITY

   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/db pro/ RTM By the end of 2006

THE END