Visual Studio Team Edition for Database Professionals Mario

Visual Studio Team Edition for Database Professionals Mario Szpuszta Software Architect Developer & Platform Group Microsoft Österreich Gmb. H. marioszp@microsoft. com http: //blogs. msdn. com/msz. Cool 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 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 Deploy Data Generation Test Compare

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 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 Project Template SQL Script Import database schema Create New 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, start. Date. Time 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] ( [Territory. ID] [nvarchar] (20) NOT NULL, [Territory. Description] [nchar] (50) NOT NULL, [Region. ID] [int] NOT NULL ) ON [PRIMARY] Primary Key ALTER TABLE [dbo]. [Territories] ADD CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED ([Territory. ID]) ON [PRIMARY] FK ALTER TABLE [dbo]. [Territories] ADD CONSTRAINT [FK_Territories_Region] FOREIGN KEY ([Region. ID]) REFERENCES [dbo]. [Region] ([Region. ID])

Agenda Team System Review VSTS for Database Professionals Project System Change Management Database Unit Testing Database Refactoring Advanced Topics, Extensibility Summary

Managed 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 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, bigint, float…) Binary (varbinary, image…) Date and Time UUID and Bit Complex generators Foreign Key Regular Expression Data Bound

Data Generation Distributions @ Work 36

Data Generation Design Time Understand domain constraints Check constraints (min/max) Table cardinality Enforce table ratios Column value distribution 37

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 Result. Set Row Count Execution Time, … Pre & Post Test Scripts 39

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 40

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 43

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 44

Database Schema 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 45

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 CTP 3 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 – New database script msbuild Northwind. Online. dbproj /t: build /p: Configuration="New Deployment" Build – Update for defined target server msbuild Northwind. Online. dbproj /t: build /p: Configuration="Update Deployment" /p: Target. Connection. String="Data Source=(local)sql 80; Integrated Security=True; Pooling=False; " /p: Target. Database="Northwind. Online. Test. Run"

Deploying Using MSBuild Deploy – New database msbuild Northwind. Online. dbproj /t: deploy /p: Configuration="New Deployment" Deploy – Update Database msbuild Northwind. Online. dbproj /t: deploy /p: Configuration="Update Deployment" /p: Target. Connection. String="Data Source=(local)sql 80; Integrated Security=True; Pooling=False; "

Misc. Actions Using MSBuild All (Build + Deploy) msbuild Northwind. Online. dbproj /t: all Clean msbuild Northwind. Online. dbproj /t: clean /p: Configuration="New Deployment" msbuild Northwind. Online. dbproj /t: clean /p: Configuration="Update Deployment"

MSBuild Task: Sql. Build/Sql. Deploy Build. Type { "New Deployment" | "Update Deployment“ } Target. Connection. String Target. Database Build options: Default. Collation {"True" | "False“} Enable. Full. Text. Indexing {"True" | "False“} Script. Create. DBStatement {"True" | "False“} Generate. Drops. If. Not. In. Project {"True" | "False“} Source. Database {"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 { Sql. Build. Task Sql. Deploy. Task }

Data Generation Customization & Extensibility Customization of value generation Reg. Ex Generator Data Bound Generator Extensibility Custom Generator Custom Distribution 56

Data Generator Extensibility Generators Implement: IDesigner IGenerator Base class Generator Attributes Generator. Attribute Generator. Name. Attribute Distributions Implement: IDistribution Registration

Data Generator – Registration Generators and Distributions have to: %Program. Files%Microsoft Visual Studio 8DBProExtensions %Program. Files%Microsoft Visual Studio 8DBProMicrosoft. Visual. Studio. Team. System. Data. Extensions. xml Be strong key signed <? xml version="1. 0" encoding="us-ascii"? > <types version="1"> <type>Microsoft. Visual. Studio. Team. System. Data. Generators. Regex. String, Microsoft. Visual. Studio. Team. System. Data. Generators, Version=2. 0. 0. 0, Culture=neutral, Public. Key. Token=b 03 f 5 f 7 f 11 d 50 a 3 a </type> <type>Microsoft. Visual. Studio. Team. System. Data. Generators. Exponential, Microsoft. Visual. Studio. Team. System. Data. Generators, Version=2. 0. 0. 0, Culture=neutral, Public. Key. Token=b 03 f 5 f 7 f 11 d 50 a 3 a </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 60

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? Family. ID=40 14554 e-903 a-4 a 62 -b 429 -2 b 027321 c 32 d&Display. Lang=en Team Website http: //msdn. microsoft. com/vstudio/teamsystem/products/dbpro/ default. aspx Product Forum http: //forums. microsoft. com/MSDN/Show. Forum. aspx? Forum. ID= 725&Site. ID=1 Power. Toys and Samples http: //gotdotnet. com/Workspaces/Workspace. aspx? id=378460 fd -1254 -427 b-aa 7 d-e 777 a 826 a 564 64

Agenda Team System Review VSTS for Database Professionals Project System Change Management Database Unit Testing Database Refactoring Advanced Topics, Extensibility Summary