640 likes | 661 Views
Explore the features of Visual Studio Team Edition for Database Professionals including database unit testing, refactoring, change management, and more. Learn how to enhance your database development lifecycle.
E N D
Visual Studio Team Editionfor Database Professionals Mario Szpuszta Software Architect Developer & Platform Group Microsoft Österreich GmbH. marioszp@microsoft.com 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
The Development Engine IT Governance Operational Excellence • Deployment • Impact analysis • Updates and Maintenance • App Health Monitoring • Security • Automated failover and recovery plans • Workflow customization • Business process re-engineering • Demand generation • Business Value Capture • Outsourcing • Resource planning Test Arch PM Dev IT Solution Lifecycle Value through “Better Together” integration across the Solution Lifecycle
Process and Architecture Guidance Code Profiler Change Management Work Item Tracking Reporting Project Site Integration Services Project Management Dynamic Code Analyzer Build Server Unit Testing Static Code Analyzer Team Foundation Client Code Coverage Deployment Modeling Visio and UML Modeling Application Modeling Test Case Management Logical Infra. Modeling Load Testing Class Modeling VS Pro Manual Testing Visual Studio Industry Partners Visual Studio Team System Visual StudioTeam Architect Visual StudioTeam Developer Visual StudioTeam Test Visual StudioTeam Foundation
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 CycleThe cycle of life for database developers Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare
Production Database Management Studio Schema 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 Tuning Monitoring Schema Changes “One Version of the Truth” for Data and Schema
Production Database Management Studio 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 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 “One Version of the Truth” for Schema • Offline • Under Source Control Schema Changes
Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary
Project ModelThe center of gravity Collection of .SQL file containing T-SQL DDL fragments Import database schema SQLServerDatabase Database Project Template Database Project Create New Project SQL Script Reverse engineer existing .SQL script files (*)
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)
Production Database Test Database Offline Model • 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)
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] ADDCONSTRAINT [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
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 GenerationDesign 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 GenerationDesign 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 GenerationDesign Time • Understand domain constraints • Check constraints (min/max) • Table cardinality • Enforce table ratios • Column value distribution
Generate Test-Data DEMO
Database Unit TestingDesign 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 RefactoringWhat 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
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"