Transcript An Inside Look At an Exciting New Addition to Visual Studio Team

DAT320
Testing and Refactoring Your Database with
Visual Studio Team Edition for Database
Professionals
2
3
DAT320
Testing and Refactoring Your
Database with Visual Studio
Team Edition for Database
Professionals (Part 2)
Sachin Rekhi
Program Manager
Microsoft Corporation
Gert E.R. Drapers
Architect/Development Manager
Microsoft Corporation
4
Agenda
The Database Development Life Cycle
The Database Developer Story
Data Generation
Database Unit Testing
Schema Refactoring
The Power of Integration
Summary
5
Other Sessions
Introducing Visual Studio Team Edition for
Database Professionals
DEV217 - 6/13/2006 10:15AM - 11:30AM, room: 104 ABC
Managing and Deploying your SQL Server Schemas with
Visual Studio Team Edition for Database Professionals
DAT312 - 6/13/2006 1:00PM - 2:15PM, room: 156 ABC
Testing & Refactoring your Database with
Visual Studio Team Edition for Database Professionals
DAT320 - 6/14/2006 8:30AM - 9:45AM, room: 160 ABC
Under the Hood of
Visual Studio Team Edition for Database Professionals
DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC
6
Product Overview
Database Project System
Schema and Script Versioning (SCC Integration)
Schema Build & Deploy
Schema Compare
Data Compare
Database Unit Testing
(Test) Data Generator
Schema Refactoring
T-SQL Editor with Query Execution
Work Item and Process Integration with TFS
7
Project Model
The center of gravity
The database project represents the “truth” with
regards to schema versioning
Optionally database project can be placed under
source control
.SQL script files is the canonical format used
Changes are tracked at the “object level”
For example indexes, constraints, triggers are tracked
independent of the base table definition, in order have
the highest granularity of change tracking
8
Database Development Life Cycle
The cycle of life for database developers
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Create New Project
Database
Project
Reverse engineer existing .SQL script files (*)
(*) Not implemented in the current CTP
9
Database Development Life Cycle
The cycle of life for database developers
Edit
Refactor
Compare
Deploy
Database
Project
Build
Data
Generation
Test
Compare
10
Database Development Life Cycle
The cycle of life for database developers
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Deploy
SQL
Script
Build project
Create New Project
Database
Project
Deploy project
Reverse engineer existing .SQL script files (*)
(*) Not implemented in the current CTP
SQL
Server
Database
11
Continuing the Journey…
In part 1 we established:
A Database Project representing our schema
Placed it under version control
Create a sandbox environment using Build & Deploy
Verified if the sandbox matches the project and the
original database, using Schema Compare
Now we are ready to start making changes!
First we are going to create test data
Which will be used by the test bed we are creating
So we can validate our changes made using
Schema Refactoring
12
Data Generation
Design Time
Setting up Data Generation implies defining:
Which generator to use
Which distribution to attach to the generator
Changing setting on the generator & distribution
The numbers of rows to generate
Optionally defining the rowcount ratios between tables
By default:
Each column is bound to the generator matching the
column data type
FK columns are mapped to the Foreign Key generator
Uniqueness is inferred from PK, UC constraints and indexes
Using the Uniform distribution when not unique
13
Data Generation
Design Time
Value generators
Simple generators for each data type
Strings: ASCII and Unicode ((var)char, n(var)char, (n)text)
Numbers: tinyint, smallint, int, bigint, real, float, decimal,
numeric, money
Binary ((var)binary, image)
Date and Time
UniqueIdentifier (GUID)
Bit
Complex generators
Foreign Key, Regular Expression, Data Bound
Distributions
Uniform, Normal, Inverse Normal, Exponential, Inverse
Exponential
Can import column distributions from production database (*)
(*) Not implemented in the current CTP
14
Data Generation
Distributions @ Work
Data Generation Distributions
12000
Generate Value (0-10000)
10000
8000
Uniform
Normal
6000
InverseNormal
Exponential
InverseExponential
4000
2000
0
1
501
1001 1501 2001 2501 3001 3501 4001 4501
# of Rows
15
Data Generation
Design Time
Understand domain constraints
Check constraints (min/max)
Table cardinality
Enforce table ratios
Column value distribution
16
Data Generation
Executing a Data Generation Definition
Validation of
Security requirements
Fails when security requirements are not met!
Target schema against DGEN definitions
Fails the generation when bindings do not match!
Optionally purge tables
Required to guarantee repeatable data generation
Spin up parallel streams of INSERT statements
Based on relation ships between tables
Number of connections used is currently gated by the
schema relationships.
Configurable Error Thresholds
17
Data Generation
Customization & Extensibility
Customization of value generation
RegEx Generator
Data Bound Generator
Extensibility
Custom Generator
Custom Distribution
Discussed in detail in:
Under the Hood of Visual Studio Team Edition for
Database Professionals
DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC
18
Data Generation
Sachin Rekhi
Program Manager
Visual Studio Team System
19
Database Unit Testing
Design Time
Automatically generate unit tests stubs for:
Stored Procedures, Functions (*), Triggers (*)
Test Validation (assertions)
T-SQL (server based) Assertions
RAISERROR command
Client Side Assertions
None Empty ResultSet
Row Count
Execution Time, …
Pre & Post Test Scripts
(*) Not implemented in the current CTP
20
Database Unit Testing
Test Execution
Automatic Deployment Integration
Automatically deploy database project prior to running
tests (*)
Data Generation Integration
Automatically generate data based on generation plan
prior to running tests
Execution & Validation connections
Validation connection can be higher privileged account
(*) Not implemented in the current CTP
21
Database Unit Testing
Customization & Extensibility
Database Unit Test designer generates C# or
VB.NET code
Can customize generated code for:
Custom test validation logic
Parameterized test support
Managing transactions
Additional test setup and teardown of tests
Discussed in detail in:
Under the Hood of Visual Studio Team Edition for
Database Professionals
DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC
22
Database Unit Testing
Sachin Rekhi
Program Manager
Visual Studio Team System
23
Database Schema Refactoring
What is refactoring?
“A database refactoring is a small change to your
database schema which improves its design
without changing its semantics.”
Agile Database Development, Scott Ambler
For example:
Rename a Schema Object Name for
consistency, understandability, maintainability…
Objective: Rename ALL schema object references;
direct and indirect inside all:
Tables, views, stored procedures, user defined functions, …
24
Database Schema Refactoring
Rename Refactoring…
Rename any SQL 2000 & SQL 2005 schema
object
Updates all references in…
Schema Objects
Data Generation Plans
Scripts
Database Unit Tests
Preview changes prior to commit
Global undo to reverse all changes
25
Database Schema Refactoring
Refactoring Safety Net
Unit Testing
Develop a battery of tests to run after a refactoring to
ensure database still functions as expected
Version Control
Store all previous versions so you can always go back
to a prior baseline in source control
Schema Compare
Analyze the exact differences between the project and
live database to understand the impact of the update
26
Schema Refactoring
Sachin Rekhi
Program Manager
Visual Studio Team System
27
Integrating In To The Cycle of Life
Edit
Refactor
Compare
Deploy
Database
Project
Build
Data
Generation
Test
Compare
28
End-to-End Database Development
Sachin Rekhi
Program Manager
Visual Studio Team System
29
Summary
Handle Schema Change Management and
Deployment
Mitigate the Risks Involved with making and
deploying changes
Integrate the Database Professional in to the
Development Life Cycle
30
Resources
Break-out Sessions
Under the Hood of Visual Studio Team Edition for
Database Professionals
DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC
Hands on Labs
DEV008 Take a Tour of Visual Studio 2005 Team
System for Database Professionals
Chalk Talks
Visual Studio Team Edition for Database
Professionals: Overview
DEV TLC Theatre 6/15/2006 9:45AM-11:00AM
31
Resources…
CTP 3 Download Site
http://download.microsoft.com/download/1/a/3/1a32ea84-11a34adf-953e-7a65b9831f5a/VSDATAD1.img
Team Website
http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/de
fault.aspx
Product Forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7
25&SiteID=1
PowerToys and Samples
http://gotdotnet.com/Workspaces/Workspace.aspx?id=378460fd1254-427b-aa7d-e777a826a564
Blogs
http://blogs.msdn.com/gertd
32
Fill out a session
evaluation on
CommNet for
a chance to
Win an XBOX 360!
33
© 2006 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.