Managing the Database Lifecycle using Team Edition for

Managing the Database Lifecycle using Team Edition for Database Professionals Randy Pagels Developer Technology Specialist Microsoft Corporation

Agenda • • • What is Visual Studio Team System? Key themes of “Data Dude” Living in the “Sandbox” • Database Project Types • Data Generation • Unit Testing • Refactoring • Build & Deploy • Schema & Data Compare Process Guidance Setup Requirements Q&A

What we heard from customers “why did we build it…” • • Managing Database Change is hard… • changes to the schema throughout the development process and after deployment Managing Database State is hard… • What’s the state for the Dev, Test, or Prod environments • It’s hard to track changes to the database Managing Database Testing is hard… • It’s hard to consistently establish the state of the database • I can’t use my live production data Disconnect between development and database teams • Developers may make poor database design decisions • Needs to be more integrated

Visual Studio Team System CIO PMO Architect Tester Developer Designer Project Manager Application Support Business Analyst Operations

Visual Studio Team System CIO PMO Architect Team Edition for Database Professionals Tester • • Expand to database teams Manage Database Change Extend Team productivity and collaboration Developer Integrated quality DB Pro Designer Project Manager Application Support Business Analyst Operations

Conceptual Overview l l 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 Production Database Management Studio Tuning Monitoring Schema Changes Schema “One Version of the Truth” for Data and Schema

Conceptual Overview l l 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 Production Database “One Version of the Truth” for Schema • Offline • Under Source Control Schema Changes Management Studio 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

Visual Studio Team System MSF Process and Guidance Visual Studio Team Suite New! Software Architects Software Developers Software Testers Database Professionals Application Modeling Code Analysis Performance Testing Infrastructure and Deployment Modeling Visual Studio Team Explorer Performance Tuning Manual Testing Database Projects Data/Schema Compare Data Generation Unit Testing Rename/Refactor Build/Deploy Security Analysis Test Case Management Code Coverage Unit Testing 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 Visual Studio Industry Partners

Key Themes l Manage Database Schema Change Ø l Mitigate Risk of those changes Ø l Leverage the “Sandbox” Establish the Database Development Lifecycle ( DDLC ) Ø l Enable Version Control for Database schema Support iterative development Incorporate the DDLC into the Software Development Lifecycle ( SDLC ) Ø Holistic Application

Off-line Development l l l Production Database Import database schema to populate project Create table AUCTION from existing not null, ( id int database title varchar(25) not null, start. Date. Time traditionally have Changes to schema not null, length in not null) immediate affect With off-line project nothing changes until you deploy the change Test Database

Creating a Project Represents the off-line database You are NOT connected to a live database! Simply a series of files collected together into a single logical collection The files represent the truth of your schema Connects to SCCI providers for versioning such as Team Foundation Server The database project is a first class project along with the other VS project types ( C# / VB. NET / C++, etc. )

Creating the “Sandbox” The Project System & Import Database Schema

Test Data To create a solid foundation for testing we support data generation Deterministic – always generate the same layout Matched to your schema and very customizable Extensible mechanism, build your own generators & distributions Bottom Line: Realistic values with representative distributions

Fill the “Sandbox” Establish a Representative Staging Area

Database Unit Testing l l Unit Testing helps ensure that changes do not break existing code Unit test designer is SQL focused Ø l l l Work in the language of your choice: TSQL, VB. NET, C# Builds on existing Team Test Unit Test functionality Integrate your database tests along side your application unit tests We can test the following: Ø Generate test stubs for these types § Ø Stored Procedures, Functions, Triggers Arbitrary SQL

Playing in the “Sandbox” Database Unit Testing

Refactoring l Bring power of refactoring to SQL Ø l Update all dependent objects in database project Ø Ø l Cascading Change Schema objects, Data generation, Unit Tests, SQL Scripts Preview all changes Make an atomic change Global Undo Rename Ø Ø Meet corporate standards Better express semantic intent – clarity

Leveraging the “Sandbox” Database Refactoring

MSBuild Tasks l l Build, deploy and data generation all implemented as MSBuild tasks MSBuild enables: Ø Ø l Command line usage Programmatic access Chaining and composition of tasks Team Build integration Team. Build on a Build Server Ø It is a requirement to have VSDBPro installed on the Team Build Server

“Sandbox” the Next Stage Safely Deploying & Managing Updates

Incorporate the Database Professional into the software lifecycle and provide them with a foundation for change management and process integration. l 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 Collaboration with Work Item and Process Integration with Team Foundation Server Automated Change Support § § § Ø Database Unit Testing § § § l 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 Leverages the Test Project Infrastructure Generate “Real and Meaningful” Data Values through 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 Integration for Database Deployments/Builds based on Projects Either Create a new Database at the target location or Update an Existing Schema

Wrap Up 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: Randy Pagels: RPagels@microsoft. com

Related Content l Blogs Ø Ø l White papers Ø Ø l 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 Samples Ø l http: //blog. msdn. com/gertd http: //blog. msdn. com/camerons http: //www. codeplex. com/vsdbpro MSDN Forum

© 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

MSF Process Guidance

MSF Integration • Extends existing MSF process guidance for database development for both: • • • Adds 2 roles: • • • Database Administrator Database Developer Adds 3 new work streams: • • MSF Agile MSF CMMI Create a Database Project Implement a Database Development Task Deploy a Database Project Delivered as MSF content update through the web

Setup Requirements • Visual Studio 2005 Team Edition for Database Professionals • • Add-on to Visual Studio 2005 Team Suite Own SKU on top of Visual Studio 2005 Professional Need to have C# or VB. NET installed for Unit Testing Need a local SQL Server 2005 instance for background compile time validation