Transcript VSTS for DB professionals

Team Edition for
Database
Professionals
Maor David
Senior Consultant (MCAD)
SRL Software Solutions Ltd.
16.07.2015
Agenda
 Acquaintance
 What is Visual Studio Team System?
 Module 1: Getting started with VSTS for DB Professionals
 Module 2:Creating & Updating Version Controlled






Database schemas
Module 3: Editing database scripts and objects with the
T-SQL Editor
Module 4: Generating data with data generators
Module 5: Comparing databases
Module 6: Verifying databases with Unit Tests
Module 7: Refactoring databases
Q&A
Acquaintance
Student background
 Full Name
 Company
 Function in company
 Experience with Visual Studio Team System
 Experience with SQL Server 2000/2005
 Reasons for coming here today
 Expectations from the course
What is Visual Studio
Team System?
Architect
Tester
Developer
Designer
VS Team System
• Expand to development team
• Business results from IT
• Team productivity and
collaboration
• Integrated quality
Project
Manager
CIO
PMO
Architect
Tester
Developer
Designer
Project
Manager
Application
Support
Business
Analyst
Operations
CIO
PMO
Architect
Team Edition for
Database Professionals
Tester
• Expand to database teams
• Manage Database Change
• Extend Team productivity and
collaboration
Developer
• Integrated quality
DB Pro Designer
Project
Manager
Application
Support
Business
Analyst
Operations
Module 1:
Getting started with
VSTS for DB
Professionals
What is “VSTS for DB Professionals”?



We’ll refer to it as TSDB or “Data Dude”
The latest addition to “Team System”
Integrates the Database development life cycle into
“Team System” enterprise development infrastructure.
“Why did we built it…?”
•
•
•
•
Managing Database Change is hard…
•
changes to the schema throughout the development
process and after deployment
Managing Database State is hard…
•
What’s the state for the Dev, Test, or Prod environments
•
It’s hard to track changes to the database
Managing Database Testing is hard…
•
It’s hard to consistently establish the state of the database
•
I can’t use my live production data
Disconnect between development and database teams
•
Developers may make poor database design decisions
•
Needs to be more integrated
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 lifecycle
The database development lifecycle contains the
following phases:
 Establish the project environment
 Perform isolated, iterative development
 Build the project
 Deploy from the project environment
Database Development Life Cycle
Edit
Refactor
Compare
Deploy
Database
Project
Data
Generation
Test
Compare
Build
Establish the Project Environment
Perform Isolated, Iterative Development
Generate Daily Builds of the Project
Deployment from the Project
Environment
Conceptual Overview
 Difficult to Manage Change to
Production
Database
Management
Studio
Tuning
Monitoring
the schema
Schema Changes
 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
Schema
“One Version of the
Truth” for Data and
Schema
Conceptual Overview
 Schema Change now managed in VSTS and TFS
Production
Database
 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
“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
Build & Deploy
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
SQL
Server
Database
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“}
Provisioning Multiple Servers
 How can I deploy to multiple targets?
 The Database Project only understand a single target
server/database at the time
 You can use the MSBuild tasks to provision multiple
servers
 Using command line or tool that calls the MSBuild
infrastructure
 Pseudo code
for each server+database combination in list
{
SqlBuildTask
SqlDeployTask
}
Key Points to Note
 DB PRO project is the master version
 Each DB object is represented by a separate
script (i.e. ‘.sql’ file)
 DB Pro Project not connected to DB !
 Script files Managed through TFS
 Source Control
 Versioning, branching & CheckIn/CheckOut
 TFS & MSBuild is an option for deployment
Module 2:
Creating & Updating Version
Controlled Database schemas
The Project Model
 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
Database Projects Defined
 “Schema” projects
 SQL Server 2000
 SQL Server 2005
 “Script Only” projects
 SQL Server 2000
 SQL Server 2005
 Different types to support different syntax/language
options
 A project is simply a series of files collected together into
a single logical collection
Off-line Development
Production
Database
Create table AUCTION
( id int not null,
title varchar(25) not null,
startDate DateTime not null,
length in not null)
Test
Database
Off-line Development
 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
Database Development Life Cycle
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Create New Project
Database
Project
Reverse engineer existing .SQL script files (*)
File Naming & Extension Scheme
 Everything is a .SQL file
 Associated with the T-SQL editor
 Using a two part naming scheme to identify types
 This is not required, but helps identification of types
 By default the file name encodes the object name
 Not required
 Filename do not have to match the containing
type name
 Required since SQL Server namespace restrictions
do not match the file system naming restrictions
Directory Structure SQL 2000 Project
 Data Generation Plans
 Schema Objects





Functions
Stored Procedures
Tables
Views
Security

Storage

Types
 User-defined Data Types
 Roles
 Users
 Roles
Application Roles
Database Roles
 File Groups
 Full Text Catalogs
 Scripts


Post-Deployment
Pre-Deployment
Directory Structure SQL 2005 Project
 Data Generation Plans
 Schema Objects
 Aggregates
 Assemblies
 Database Triggers
 Functions
 Security



Asymmetric Keys
Certificates
Endpoints
Roles
 Application Roles
 Database Roles
Schemas
Symmetric Keys
Users







Contracts
Event Notifications
Message Types
Queues
Remote Service Binding
Routes
Services





 Schema Objects…
 Storage
 File Groups
 Full Text Catalogs
 Partition Functions
 Partition Schemes




Stored Procedures
Synonyms
Tables
Types
 User-defined Data Types
Service Broker
 User-defined Types (CLR)
 XML Schema Collections

Views
 Scripts
 Post-Deployment
 Pre-Deployment
Lab
Creating & Updating Version Controlled Database schemas
Module 3:
Editing database scripts
and objects with the TSQL Editor
Transact-SQL Editor features
 All common features for Visual Studio editors, which
include find and replace, bookmarks, block indent and
un-indent, integration with the Visual Studio Error List
window, and block commenting and un-commenting.
 Support for shortcut keys compatible with other editors
and SQL Server Management Studio.
 Validation of SQL syntax without executing the script or
query.
Transact-SQL Editor features Cont.
 Ability to edit while disconnected.
 Multiple sets of query results, displayed either as a grid,
as text, or saved to a file on disk.
 Ability to execute multiple queries in one editor instance,
with multiple result sets generated. The queries are
executed sequentially.
 Support for SQLCMD
Lab
Create and Execute a Simple Transact-SQL Script
Module 4:
Generating data with
data generators
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
(*)
Data Generation - Design Time
 Understand domain constraints
 Check constraints (min/max)
 Table cardinality
 Enforce table ratios
 Column value distribution
Generation Data - 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
# of Rows
3501
4001
4501
Data Generation - Execution
 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 relationships between tables
 Number of connections used is currently gated by the
schema relationships.
 Configurable Error Thresholds
Data Generation - Customizing
 Customization of value generation
 RegEx Generator (*)
 Data Bound Generator (*)
 Extensibility
 Custom Generator
 Custom Distribution
Lab
Data Generation
Module 5:
Comparing databases
Schema Compare
What is Schema Compare?
 Allows quick comparisons of:
 project -> database
 database -> database
 GUI interface and/or SQL scripts to compare diffs
 Full SQL Server 2000 and SQL Server 2005 support
 Object level script differences between databases
Schema Compare
 Options for restricting/changing script
 Restrict which object types are compared
 Compare while ignoring schema, filegroups, etc.
 Enables comparison of test to production
 Turn off dependency checking
 Generate backwards compatible scripts
 i.e. script a 2005 database with a 2000 script
 Compare security settings
 Users, roles, and permissions
Data Compare
 Connects to 2 databases and compares all data.
 Tables and Views
 Differences can be:
 Different rows
 Identical rows
 Only in source
 Only in target
 Can update target immediately from GUI or export to
script
 Can customize updates for specific tables
 Can customize updates for specific rows
Lab
Comparing databases
Module 6:
Verifying databases with
Unit Tests
A Rollback Means a LONG Night
 Unit Testing helps ensure that changes do not break
existing code
 Unit test designer is SQL focused
 Work in the language of your choice: TSQL, VB, C#
 Builds on existing Team Test Unit Test functionality
What We Can Test
 Stored Procedures
 Functions
 Triggers
 Arbitrary SQL
 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
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
 Non Empty Result Set
 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
Database Unit Testing Customization
 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
Lab
Verifying databases with Unit Tests
Module 7:
Refactoring Databases
What is re factoring?
“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, …
Refactoring
 Bring power of refactoring to SQL
 Cascading Change
 Update all dependent objects in database project
 Schema objects, Data generation, Unit Tests, SQL
Scripts
 Preview all changes
 Make an atomic change
 Global Undo
 Rename
 Meet corporate standards
 Better express semantic intent – clarity
Database Schema 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
Database Schema Refactoring
 Unit Testing
 Run after a re factoring 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
Lab
Refactoring Databases
Points to take home !
 Managed, project oriented evolution of database schema
– no more rollbacks
 Application and database schema can now be managed
together
 Work in “isolation”, deploying only when changes verified
 Leverage VSTS work item tracking and process guidance
Thank You!
 Email :
 Blog :
[email protected]
http://blog.maordavid.com
Public CTP of VS DB Pro SR1 available
 3+4 part name resolution through a new feature called
"database references"
 File groups and files are now part of the project settings
and can be fully parameterize then to fit your
deployment needs (this replaces the need for the storage
deployment script)
 SQLCMD variable support; this is enable through a new
project property page for creating and editing variables
that you can then use inside your scripts. Variables are
stored inside the project file and can be mapped to
MSBuild properties.
Q & A?
Useful links
Blogs




SRLTeam http://blogs.microsoft.co.il/blogs/srlteam
Gert http://blog.msdn.com/gertd
Mairéad http://blogs.msdn.com/mairead/
Cameron http://blog.msdn.com/camerons
Team System For DB Pro Home
• http://msdn2.microsoft.com/en-us/library/aa833253(VS.80).aspx
White Papers
• What Microsoft Visual Studio 2005 Team Edition for Database
Professionals Can Do for You
• A Security Overview of Microsoft Visual Studio 2005 Team Edition
for Database Professionals
Samples
• http://www.codeplex.com/vsdbpro
Forum
• http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7
25&SiteID=1