Transcript Database development with VSTS: Visual 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




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




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
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
Visual
Studio
Team
Explorer
New!
Software
Architects
Software
Developers
Software
Testers
Database
Professionals
Application
Modeling
Code Analysis
Performance
Testing
Infrastructure and
Deployment
Modeling
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

Manage Database Schema Change


Mitigate Risk of those changes


Leverage the “Sandbox”
Establish the Database Development Lifecycle
( DDLC )


Enable Version Control for Database schema
Support iterative development
Incorporate the DDLC into the Software
Development Lifecycle ( SDLC )

Holistic Application
Off-line Development



Production
Database
Import database schema to populate project
Create table AUCTION
from existing
database
( id int not
null,
title varchar(25) not null,
startDate
DateTime not
null,
Changes to
schema
traditionally
have
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


Unit Testing helps ensure that changes do not break
existing code
Unit test designer is SQL focused




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

Bring power of refactoring to SQL


Update all dependent objects in database project





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


Build, deploy and data generation all
implemented as MSBuild tasks
MSBuild enables:





Command line usage
Programmatic access
Chaining and composition of tasks
Team Build integration
TeamBuild 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.

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




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: [email protected]
Related Content

Blogs



White papers



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


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, 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