phx - session #4 treating databases as first-class citizens in development
DESCRIPTION
TRANSCRIPT
![Page 1: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/1.jpg)
TEAM SYSTEM: IT’S BIG
Treating Databases as First-Class Citizens in
DevelopmentRob BagbyDeveloper EvangelistMicrosoft [email protected]
![Page 2: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/2.jpg)
TEAM SYSTEM: IT’S BIG
AgendaOverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
![Page 3: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/3.jpg)
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?
![Page 4: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/4.jpg)
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
![Page 5: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/5.jpg)
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
![Page 6: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/6.jpg)
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
![Page 7: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/7.jpg)
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 scrip
ts
![Page 8: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/8.jpg)
TEAM SYSTEM: IT’S BIG
Database Development Lifecycle
DatabaseProject
Edit
Refactor
Compare
Data Gen
Test
Compare
BuildDeploy
![Page 9: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/9.jpg)
TEAM SYSTEM: IT’S BIG
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();)
Version Control Challenge
Database
class AuctionApplication( int id; void MethodA(); void MethodB();)
class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)
Revision History
App
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_SK UNIQUE (name)
![Page 10: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/10.jpg)
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
Manual Versioning
![Page 11: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/11.jpg)
TEAM SYSTEM: IT’S BIG
CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
LogicalDatabase
Version ControlThe “Data dude” approach
class AuctionApplication( int id; void MethodA();)
class AuctionApplication( int id; void MethodA(); void MethodB();)
class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)
Revision History
App
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 UNIQUE, start DATETIME NULL, len INT NULL)
Source-controlled and deployed scripts do not need to match
![Page 12: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/12.jpg)
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 PRIMARY KEY, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)
IncrementalDeployment
LogicalDatabase
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)
NewDeployment ALTER TABLE dbo.Auction
WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)
![Page 13: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/13.jpg)
TEAM SYSTEM: IT’S BIG
The Project Model
demo
![Page 14: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/14.jpg)
TEAM SYSTEM: IT’S BIG
AgendaOverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
![Page 15: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/15.jpg)
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–…
![Page 16: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/16.jpg)
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
![Page 17: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/17.jpg)
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
![Page 18: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/18.jpg)
TEAM SYSTEM: IT’S BIG
(Test) Data Generation
demo
![Page 19: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/19.jpg)
TEAM SYSTEM: IT’S BIG
AgendaOverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
![Page 20: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/20.jpg)
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
![Page 21: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/21.jpg)
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
![Page 22: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/22.jpg)
TEAM SYSTEM: IT’S BIG
Database Unit Tests
demo
![Page 23: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/23.jpg)
TEAM SYSTEM: IT’S BIG
AgendaOverviewThe Database Project(Test) Data GenerationUnit TestsManaging ChangeQ&A
![Page 24: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/24.jpg)
TEAM SYSTEM: IT’S BIG
What kind of change can we manage?
• Refactoring• Compare schemas• Compare data
![Page 25: PHX - Session #4 Treating Databases as First-Class Citizens in Development](https://reader034.vdocuments.us/reader034/viewer/2022051817/54834141b07959490c8b49ff/html5/thumbnails/25.jpg)
TEAM SYSTEM: IT’S BIG
Managing Change
demo