TEAM SYSTEM: IT’S BIG
Treating Databases as First-Class Citizens in
DevelopmentRob BagbyDeveloper EvangelistMicrosoft [email protected]
TEAM SYSTEM: IT’S BIG
Agenda
OverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
TEAM SYSTEM: IT’S BIG
Some Important Questions
• Where is the “truth” for my schema?– Production? –What about bug fixes?–What about version next?
• How do I version my databases?• What do I do for test data?• How do I test my database logic?• What tools do I use to differentiate
schemas, data?
TEAM SYSTEM: IT’S BIG
Challenge• Where’s the truth?
Solution• Multiple Truths,
stored offline• How do I version? • The same way I
version source code• Where do I get test data? • Test Data Generator
• How do I unit test? • The same way I test source code (with a little SQL love thrown in)
• What tools do I have to manage change?
• Schema Compare, Data Compare, Refactoring
TEAM SYSTEM: IT’S BIG
The “Truth” of my schema
• The production DB is only 1 version of the truth of your schema
• There may be bug fixes in Q/A right now, waiting deployment
• There may be development going on right now by multiple developers / teams
TEAM SYSTEM: IT’S BIG
Project Model
• The database project represents the “truth” of your schema
• The project contains the schema (*.sql files)
• Use version control to manage different versions
TEAM SYSTEM: IT’S BIG
Database Development Lifecycle
SQL Server
Database
DatabaseProject
Template
SQL Script
DatabaseProject
Import Database
Schema
Create new project
Reverse engineer
existing sql s
cripts
TEAM SYSTEM: IT’S BIG
Database Development Lifecycle
DatabaseProject
Edit
Refactor
Compare
Data Gen
Test
Compare
BuildDeploy
TEAM SYSTEM: IT’S BIG
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
class AuctionApplication( int id; void MethodA();)
class AuctionApplication( int id; void MethodA();)
Version Control Challenge
DatabaseDatabase
class AuctionApplication( int id; void MethodA(); void MethodB();)
class AuctionApplication( int id; void MethodA(); void MethodB();)
class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)
class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)
Revision History
AppApp
V 1 V 2 V 3ALTER TABLE dbo.Auction WITH CHECK ADD CONSTRAINT Au_PK PRIMARY KEY (id)
ALTER TABLE dbo.Auction WITH CHECK ADD CONSTRAINT Au_PK PRIMARY KEY (id)
ALTER TABLE dbo.Auction WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)
ALTER TABLE dbo.Auction WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)
TEAM SYSTEM: IT’S BIG
-- version 1 Add table dbo.AuctionIF OBJECT_ID (N'dbo.Auction', N'U') IS NULLBEGINCREATE TABLE dbo.Auction(
id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)END-- version 2 Add PK Au_PKIF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'Au_PK' AND type = 'PK')BEGIN
ALTER TABLE Auction WITH CHECK ADD CONSTRAINT Au_PK PRIMARY KEY (id)END-- version 3 Add UC Au_SKIF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'Au_SK' AND type = ‘UQ')BEGIN
ALTER TABLE Auction WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)END
-- version 1 Add table dbo.AuctionIF OBJECT_ID (N'dbo.Auction', N'U') IS NULLBEGINCREATE TABLE dbo.Auction(
id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)END-- version 2 Add PK Au_PKIF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'Au_PK' AND type = 'PK')BEGIN
ALTER TABLE Auction WITH CHECK ADD CONSTRAINT Au_PK PRIMARY KEY (id)END-- version 3 Add UC Au_SKIF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'Au_SK' AND type = ‘UQ')BEGIN
ALTER TABLE Auction WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)END
Manual Versioning
TEAM SYSTEM: IT’S BIG
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
LogicalDatabaseLogical
Database
Version ControlThe “Data dude” approach
class AuctionApplication( int id; void MethodA();)
class AuctionApplication( int id; void MethodA();)
class AuctionApplication( int id; void MethodA(); void MethodB();)
class AuctionApplication( int id; void MethodA(); void MethodB();)
class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)
class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)
Revision History
AppApp
V 1 V 2V 3CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL UNIQUE, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL UNIQUE, start DATETIME NULL, len INT NULL)
Source-controlled and deployed scripts do not need to match
TEAM SYSTEM: IT’S BIG
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL UNIQUE, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
IncrementalDeploymentIncrementalDeployment
LogicalDatabaseLogical
Database
DeploymentThe “Data dude” approach
Revision HistoryV 1 V 2V 3
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL UNIQUE, start DATETIME NULL, len INT NULL)
CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL UNIQUE, start DATETIME NULL, len INT NULL)
NewDeployment
NewDeployment ALTER TABLE dbo.Auction
WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)
TEAM SYSTEM: IT’S BIG
The Project Model
demo
TEAM SYSTEM: IT’S BIG
Agenda
OverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
TEAM SYSTEM: IT’S BIG
Test Data Q & A
• Why not production data?– Ahhh, it may be illegal– Doesn’t test edge cases– Doesn’t address schema changes
• What are our test data requirements?– “Random”– Deterministic– Appropriately distributed–…
TEAM SYSTEM: IT’S BIG
Test Data Q & A (continued)
• What are the differing needs for test data?– Functional Test– Load Test
• What are the versioning implications–We need differing plan(s) for differing
schemas–We need to version plans along side
schemas
TEAM SYSTEM: IT’S BIG
(Test) Data Generation in VSTS
Choose the following• The Tables• Number of rows (RowCount ratios help)• The generator for each column– Out of the box: string, RegEx, data bound,
etc.– Write your own– Set generator-specific settings– Set the seed – provides determinism
• The distribution
TEAM SYSTEM: IT’S BIG
(Test) Data Generation
demo
TEAM SYSTEM: IT’S BIG
Agenda
OverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
TEAM SYSTEM: IT’S BIG
Database Unit Testing
• Automatically generate unit test 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 and Post Test Scripts
TEAM SYSTEM: IT’S BIG
Database Unit Testing
• 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
TEAM SYSTEM: IT’S BIG
Database Unit Tests
demo
TEAM SYSTEM: IT’S BIG
Agenda
OverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
TEAM SYSTEM: IT’S BIG
What kind of change can we manage?
• Refactoring• Compare schemas• Compare data
TEAM SYSTEM: IT’S BIG
Managing Change
demo