250 likes | 448 Vues
Development Life Cycle using Visual Studio Team Edition for DB Professionals . Richard Fennell Engineering Director SQLBits II Birmingham 1 st March 2008. Agenda. Why do we need DB Pro.? What is Visual Studio Team System DB Pro. Features Project Life-cycle Testing Deployment
E N D
Development Life Cycle using Visual Studio Team Edition for DB Professionals Richard FennellEngineering DirectorSQLBits II Birmingham 1st March 2008
Agenda • Why do we need DB Pro.? • What is Visual Studio Team System • DB Pro. Features • Project Life-cycle • Testing • Deployment • Power Tools
Pain points in DB development • What is the ‘correct DB schema’? • How do we version control it? • How do I know the impact of a change? • How do we publish a DB? • Where do we get test data from? • How do we test it anyway? • There has been no standard way to manage the DB – until now
Excel Project Team System Web Access Operations, QA and Help Desk Non-Microsoft Developer Visual Studio Team SystemApplication Life Cycle Management (ALM) Solution
Visual Studio for Database Professionals • Was known as ‘DataDude’ in beta phase • Came out in 2006, a year after the main VS 2005 release. • New release VS 2008 is really more of a service pack than a major release.
DBPro Features • Incorporates the Database Professional into the software development lifecycle • Provides a foundation for change management and process integration • Exposes database schema as individual script files • Provides a set of essential tools • Version Control via Visual Studio supported providers • Rename Refactoring • Schema Comparison Tools • Data Comparison Tools
DBPro Features • Database Unit Testing • Leverages the existing Test Project Infrastructure • Supports data generation • Build and Deployment • MSBuild and Team Build integration
Power Tools • Power Tools add additional features that didn’t make the product release cycle • Dependency Viewer • New Refactorings • Data Generation Wizard • MSBuild Tasks • T-SQL Static Code Analysis • Schema Manager API
Team Foundation Server Integration • As a member of the Visual Studio Team System family, DBPro is integrated with all of the team features • TFS provides • Team project with prescriptive guidance • Version control management • Work Item tracking • Team Build integration
Roles in a DB Project Writes Tests Writes DB Code Refactors Runs Tests Checks In Works with other developers to integrate Creates New DB Project Reverse Engineers DB to Project Creates Data Generation Plan Reviews Changes Compares Updates to Production Builds Deploy Package Deploys to Production Manage Develop Deploy DB Administrator DB Developer DB Administrator
Creating the DB Project TFSServer DBPro Check in to Source Control DBA Staging Database Database Project Production Database Create a Project Import schema
Sync • Check-out • Edit/Refactor • Test • Check-in • Work is being drivenand tracked viawork items • Other team members can pick up changes TFS Shelving allows DBAto provide guidance and evaluate work Isolated Iterative Development Sandbox Database TFSServer Sandbox Database DBPro DBA Staging Database Sandbox Database Production Database
Automated Build & Testing Reports TFSServer Test Environment Test Get Latest DBPro Build Server DBA Staging Database Production Database
SQL Deploy Script Deploy the Project TFSServer DBPro Sync from Label DBA Deploy Staging Database Publish Database Project Production Database Refine deploy script Build Verify
demo DB Life Cycle
So what DBs did I end up with? VS Dev DB Live DB Local Test DB Live production DB You may also have a test or staging DB DB Created by Visual Studio Used by IDE for design time validation Developers Test DB Used for sandbox testing (might be shared)
Data Generation • Shipped with the product • Data-bound, Regular Expression, Random Number • Available as a Power Tool • File-bound and a Wizard tools to ease the process • Codeplex – DbProGenerators • Lorem Ipsum, soon XML bound, Word bound and web search bound • Plus what you write yourself.....
demo Data Generation
Testing in DB Pro • Firstly remember that can test any CLR code before loading it into SQL Server • DBPro adds database tests that can be used to test any stored procedure, function, trigger or DB object • A single test project can contain a variety of test types • Can be used to auto generate test stubs
Is DB Testing Valid? • But you have to ask how useful it is to test at the raw data layer? • Usually more effective to test the DB via the data access layer or as part of integration testing
MSBuild • All the key DB tasks can be scripted • Database operations can become part of a scheduled build • However this can all get a bit complex and DBPro MSBuild tasks are a bit idiosyncratic. • Check the web for examples
Summary • Visual Studio Team Edition for Database Professionals make SQL development part of the project mainstream. • Team System coupled with integrated version control helps to mitigate risks associated with DB schema change • Build integration provides for quality tracking and improvement • Process reduces last minute problems and the need to rollback changes out of production
Good VSTS Blogs • Brian Harry (Product Unit Manager for Team Foundation Server ) • http://blogs.msdn.com/bharry • Rob Caron (Lead Product Manager for Developer Content Strategy at Microsoft) • http://blogs.msdn.com/robcaron • Gert Drapers (Architect/Development Manager Visual Studio Team Edition for DB Professionals ) • http://blogs.msdn.com/gertd
For Further Information • My random thoughts ‘But it works on my PC!’ http://blogs.blackmarble.co.uk/blogs/rfennell • You can also get in touch via: Email – richard@blackmarble.co.uk WebSite– www.blackmarble.co.uk