Transcript Data Generation

Visual Studio Team Edition
for Database Professionals
Mario Szpuszta
Software Architect
Developer & Platform Group
Microsoft Österreich GmbH.
[email protected]
http://blogs.msdn.com/mszCool
Microsoft Confidential
MSDN Briefings – Organisation
Monthly technical briefings
Currently released technology
Your current needs
Invitation / Registration / Feedback
http://blogs.msdn.com/msdnat
http://blogs.msdn.com/talk
Well, what I am doing here?
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
Static Code Analysis
Unit Testing
IT Solution Lifecycle
Value through “Better Together” integration across the Solution Lifecycle
IT
Governance
• Business
process re-engineering
• Demand generation
• Business
Value Capture
• Outsourcing
• Resource planning
The Development Engine
PM
Test
Arch
Dev
Operational
Excellence
• Deployment
• Impact analysis
• Updates
and Maintenance
• App Health Monitoring
• Security
• Automated failover
and recovery plans
• Workflow customization
Visual Studio
Visual Studio
Visual Studio
Team Architect
Team Developer
Team Test
Application Modeling
Dynamic Code Analyzer
Load Testing
Logical Infra. Modeling
Static Code Analyzer
Manual Testing
Deployment Modeling
Code Profiler
Test Case Management
Unit Testing
Code Coverage
Class Modeling
Visio and UML Modeling
Team Foundation Client
VS Pro
Visual Studio
Team Foundation
Build Server
Change Management
Reporting
Integration Services
Work Item Tracking
Project Site
Project Management
Visual Studio Industry Partners
Process and Architecture Guidance
Visual Studio Team System
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
Product Overview (1)
Database Project System
Schema and Script Versioning
SCC Integration
T-SQL Editor with Query Execution
Database Schema Management
Build & Deploy
Schema Compare
Data Compare
Product Overview (2)
Database Unit Testing
(Test) Data Generator
Stored Procedure Unit Testing
Schema Refactoring
Version 1: Change names, only
Future versions: indexes, constraints, relations
Team Foundation Server Integration
Work Item Tracking
Process Integration
Database Development Life Cycle
The cycle of life for database developers
Edit
Refactor
Compare
Deploy
Database
Project
Data
Generation
Test
Compare
Build
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
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
Project Model
The center of gravity
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Create New Project
Database
Project
Reverse engineer existing .SQL script files (*)
Collection of
.SQL file
containing TSQL DDL
fragments
Offline Model
Project model
Schema Objects representation
Collection of T-SQL DDL fragments
Objects are Parsed and Interpreted at:
Project Load Time
Object Change (save)
Source Control Sync (external change)
Offline Model
Production
Database
Import database schema to populate
project from existing database
Changes to schema traditionally have
immediate affect
With off-line project nothing changes
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
Reverse Engineering a Schema
DEMO
Shredding in to SQL Fragments
Loading, importing or reverse engineering
Shreds the schema definition
Smallest possible DDL fragments
Example:
Table
CREATE TABLE [dbo].[Territories]
(
[TerritoryID] [nvarchar] (20) NOT NULL,
[TerritoryDescription] [nchar] (50) NOT NULL,
[RegionID] [int] NOT NULL
) ON [PRIMARY]
Primary Key
ALTER TABLE [dbo].[Territories] ADD CONSTRAINT [PK_Territories]
PRIMARY KEY NONCLUSTERED ([TerritoryID]) ON [PRIMARY]
FK
ALTER TABLE [dbo].[Territories] ADD
CONSTRAINT [FK_Territories_Region] FOREIGN KEY ([RegionID])
REFERENCES [dbo].[Region] ([RegionID])
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
Managed Change
Changes are local
Comparison between databases
Test database
Production database
Elements under source control
Any SCCI compliant version system
Template driven
Version specific SQL 2000 or SQL 2005
Working With the Project
Make changes
Add new elements
Modify existing elements
Delete Items
Compare databases
Build update script
Deploy new or incremental update
Visual Studio
MSBuild action
Build/Deploy
Standard VS build task
Configurations
New vs. Update builds
Project properties for build
Schema compare used for build
Pre/Post Deployment scripts
Build results in SQL script file
Deploy
Deploy via SQL query tool
Deploy via MSBuild task
RTM: SQLCMD command support
Changing the database
DEMO
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
What you need for Testing?
Updated schema
Test drivers (unit tests)
Tons of data (realistic)
Data Generation
Design Time
Data generator component
Strategy for generation
Distribution for generator
Range of values
Relation between values
Settings for generator & distribution
Number of rows
Row-count ratios between tables
Data Generation
Design Time – Default Behavior
Per column generator
Matching data type
Aware of CHECK constraints
Special attributes
Foreign Keys  Foreign Key generator
Uniqueness  PK, UC, indexes
Default distribution
Uniform distribution when not unique
Data Generation – Value Generators
Simple generators for each data type
Strings (char, varchar, nvarchar…)
Numbers (smallint, int, bigint, float…)
Binary (varbinary, image…)
Date and Time
UUID and Bit
Complex generators
Foreign Key
Regular Expression
Data Bound
Data Generation
Distributions @ Work
Data Generation Distributions
12000
Generate Value (0-10000)
10000
8000
Uniform
Normal
InverseNormal
Exponential
InverseExponential
6000
4000
2000
0
1
501
1001 1501 2001 2501 3001 3501 4001 4501
# of Rows
Data Generation
Design Time
Understand domain constraints
Check constraints (min/max)
Table cardinality
Enforce table ratios
Column value distribution
Generate Test-Data
DEMO
Database Unit Testing
Design Time
Automatically generate unit tests:
Stored Procedures, Functions, Triggers
Test Validation (assertions)
T-SQL  RAISERROR
Client Assertions
None Empty ResultSet
Row Count
Execution Time, …
Pre & Post Test Scripts
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
Create a Unit Test
DEMO
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
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
Database Schema Refactoring
Rename Refactoring…
Rename any SQL 2000/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
Database Schema Refactoring
Refactoring Safety Net
Unit Testing
Generate tests after refactoring
Version Control
Store all previous versions before refactoring
Schema Compare
Analyze the exact differences between the
project and live database to understand the
impact of the update
Refactoring an Object
DEMO
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
Command Line Building
Using devenv.exe
Visual Studio shell in command line mode
Using MSBuild.exe
Important note:
In CTP3 the project needs to be opened
inside Visual Studio!
Project Properties
SET options
Only override when different
Collations
Only override when different
Difference between New and Update
Building Using MSBuild
Build – New database script
msbuild NorthwindOnline.dbproj /t:build
msbuild NorthwindOnline.dbproj /t:build
/p:Configuration="New Deployment"
Build – Update for defined target server
msbuild NorthwindOnline.dbproj /t:build
/p:Configuration="Update Deployment"
/p:TargetConnectionString="Data
Source=(local)\sql80;Integrated
Security=True;Pooling=False;"
/p:TargetDatabase="NorthwindOnlineTestRun"
Deploying Using MSBuild
Deploy – New database
msbuild NorthwindOnline.dbproj /t:deploy
/p:Configuration="New Deployment"
Deploy – Update Database
msbuild NorthwindOnline.dbproj /t:deploy
/p:Configuration="Update Deployment"
/p:TargetConnectionString="Data
Source=(local)\sql80;Integrated
Security=True;Pooling=False;"
Misc. Actions Using MSBuild
All (Build + Deploy)
msbuild NorthwindOnline.dbproj /t:all
Clean
msbuild NorthwindOnline.dbproj /t:clean
msbuild NorthwindOnline.dbproj /t:clean
/p:Configuration="New Deployment"
msbuild NorthwindOnline.dbproj /t:clean
/p:Configuration="Update Deployment"
MSBuild Task: SqlBuild/SqlDeploy
BuildType { "New Deployment" | "Update Deployment“ }
TargetConnectionString
TargetDatabase
Build options:
DefaultCollation {"True" | "False“}
EnableFullTextIndexing {"True" | "False“}
ScriptCreateDBStatement {"True" | "False“}
GenerateDropsIfNotInProject {"True" | "False“}
SourceDatabase {"True" | "False“}
Set options:
ARITHABORT {"True" | "False“}
NUMERIC_ROUNDABORT {"True" | "False“}
ANSI_NULLS {"True" | "False“}
CONCAT_NULL_YIELDS_NULL {"True" | "False“}
ANSI_PADDING {"True" | "False“}
ANSI_WARNINGS {"True" | "False“}
QUOTED_IDENTIFIER {"True" | "False“}
Building with MSBuild
DEMO
Provisioning Multiple Servers
Deploy to multiple targets?
Database Project
Single target server/database, only
Use MSBuild tasks to provision
Command line or tool calling MSBuild
for each server+database combination in list
{
SqlBuildTask
SqlDeployTask
}
Data Generation
Customization & Extensibility
Customization of value generation
RegEx Generator
Data Bound Generator
Extensibility
Custom Generator
Custom Distribution
Data Generator Extensibility
Generators
Implement:
IDesigner
IGenerator
Base class
Generator
Attributes
GeneratorAttribute
GeneratorNameAttribute
Distributions
Implement:
IDistribution
Registration
Data Generator – Registration
Generators and Distributions have to:
%ProgramFiles%\Microsoft Visual Studio 8\DBPro\Extensions
%ProgramFiles%\Microsoft Visual Studio
8\DBPro\Microsoft.VisualStudio.TeamSystem.Data.Extensions.xml
Be strong key signed
<?xml version="1.0" encoding="us-ascii"?>
<types version="1">
<type>Microsoft.VisualStudio.TeamSystem.Data.Generators.RegexString,
Microsoft.VisualStudio.TeamSystem.Data.Generators,
Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a
</type>
<type>Microsoft.VisualStudio.TeamSystem.Data.Generators.Exponential,
Microsoft.VisualStudio.TeamSystem.Data.Generators,
Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a
</type>
</types>
Custom Data Generator
DEMO
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
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary
Summary
Team Edition for Database Professionals
Database development life-cycle
Basic functionality
Reverse-Engineer database schema
Source Control for schema
Refactor database schema
Generate test data, create unit tests
Compare schemas
Extensible infrastructure
Pricing, Licensing and Availability
Included in Team Suite at No Extra Cost
Purchase as an individual Edition
Same pricing as other Team System Editions
Availability
CTP 4 Available Today
RTM By the end of 2006
Resources…
CTP 5 Download Site
http://www.microsoft.com/downloads/details.aspx?FamilyID=40
14554e-903a-4a62-b429-2b027321c32d&DisplayLang=en
Team Website
http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/
default.aspx
Product Forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=
725&SiteID=1
PowerToys and Samples
http://gotdotnet.com/Workspaces/Workspace.aspx?id=378460fd
-1254-427b-aa7d-e777a826a564
Agenda
Team System Review
VSTS for Database Professionals
Project System
Change Management
Database Unit Testing
Database Refactoring
Advanced Topics, Extensibility
Summary