Transcript Introduction to Visual Studio Team System for Database

(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



Source control for DBs


Generate scripts/apply updates
Data generation


Ability to store different versions as changesets
Schema and data compare


“One version of the truth”
Offline sandbox for multiple developers
Generate meaningful test data automatically
DB Unit tests

Same test infrastructure as rest of Team System (uses SQL)
WHERE DOES DATA DUDE FIT IN?
MSF Process and Guidance
Visual Studio Team Suite
Visual
Studio
Team
Explorer
Software
Architects
Application
Modeling
Infrastructure and
Deployment
Modeling
Software
Developers
Code Analysis
Performance
Tuning
Software
Testers
Performance
Testing
Manual Testing
Test Case
Security Analysis Management
New!
Database
Professionals
Database
Change
Management
Database Testing
Database Build &
Deployment
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
Visual
Studio
Industry
Partners
HOW IS IT ALL CONNECTED?
 Import
database schema to populate
project from existing database
 Changes to schema traditionally have
immediate affect
Production
 With off-line project nothing changesDatabase
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)
Test
Database
WHAT DOES A DEVELOP SCENARIO LOOK LIKE?
Manage
Develop
Deploy
Creates New DB
Project
Writes Tests
Reviews Changes
Writes DB Code
Refactors
Compares Updates to
Production
Runs Tests
Builds Deploy Package
Checks In
Deploys to Production
Reverse Engineers DB
to Project
Creates Data
Generation Plan
DBA
DB DEVELOPER
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
 Team
objects like logins, custom error messages
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:






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!


Project -> database; database -> database
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