Making the SQL developer one of the family with Visual Studio Team System

1
Making the SQL developer one of the family with
Visual Studio Team System

• Richard FennellEngineering DirectorSQLBits IV
  28th March 2009

2
Agenda

• Why do we need DB Pro.?
• What is Visual Studio Team System
• DB Pro. Features
• Project Life-cycle
• Testing
• Deployment
• Power Tools
• The Future

3
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 Microsoft standard way to
  manage the DB until now

4
Visual Studio Team SystemApplication Life Cycle
Management (ALM) Solution
5
Visual Studio for Database Professionals

• 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

6
Visual Studio for Database Professionals

• Was known as DataDude in beta phase
• Released in 2006
• Release VS 2008 was really more of a service pack
  than a major release.
• VS 2008 GDR Released November 2008

7
Visual Studio Team System 2008Database Edition
GDR

• Builds on top of Visual Studio 2008 SP1
• Adds support for SQL Server 2008
• Introduces a new product architecture
• Database Schema Provider model
• Separation of Build Deploy
• Public Extensibility
• Incorporates functionality previouslyshipped in
  the Power Tools
• T-SQL Static Code Analysis
• Dependency Viewer
• Many product enhancements and improvements

8
Database Project Ecosystem
DSP Extensions
DSP Extensions
DSP Extensions
DSP Extensions
DSP Extensions
DSP Extensions
Database Refactoring
Database Unit Testing
Data Generation
T-SQL Static Code Analysis
Schema Compare
Data Compare
3rd Party Designers
3rd Party Tools
Project Features
Database Eco Project System Solution
Explorer Schema View Dependency Viewer Editor
SQL Server 2008 DSP Parser ScriptDOM Interpreter
Reverse Engineer Deploy
SQL Server 2005 DSP Parser ScriptDOM Interpreter
Reverse Engineer Deploy
SQL Server 2000 DSP Parser ScriptDOM Interpreter
Reverse Engineer Deploy
3rd Party DSP Parser ScriptDOM Interpreter Revers
e Engineer Deploy
DSP Extensions
Database Model API
9
Database Edition Features

• Database Unit Testing
• Leverages the existing Test Project
  Infrastructure
• Supports data generation
• Build and Deployment
• MSBuild and Team Build integration

10
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

11
Database Edition Power Tools

• Power Tools 2008 added additional features that
  didnt make the product release cycle
• Dependency Viewer
• New Refactorings
• Data Generation Wizard
• MSBuild Tasks
• T-SQL Static Code Analysis
• Schema Manager API
• At present no GDR Power tools released

12
Roles in a DB Project
Writes Tests Writes DB Code Refactors Runs
Tests Checks In Works with other developers to
integrate
Reviews Changes Compares Updates to
Production Builds Deploy Package Deploys to
Production
Creates New DB Project Reverse Engineers DB to
Project Creates Data Generation Plan
Manage
Develop
Deploy
DB Developer
DB Administrator
DB Administrator
13
Creating the DB Project
TFSServer
DBPro
Check in to Source Control
DBA
Staging Database
Database Project
Production Database
Create a Project
Import schema
14
Isolated Iterative Development
Sandbox Database
TFSServer
Sandbox Database
DBPro
DBA
Staging Database
Sandbox Database
Production Database
15
Automated Build Testing
Reports
TFSServer
Test Environment
Test
Get Latest
DBPro
Build Server
DBA
Staging Database
Production Database
16
Deploy the Project
TFSServer
DBPro
Sync from Label
DBA
Deploy
Staging Database
Publish
Database Project
Production Database
Refine deploy script
Build
Verify
17
Schema Compilation
Source Database
Reverse engineer schema into DDL artifacts
Interpret, Analyze and Validate Schema
Model
Schema Model
.dbschema file
Build
Compose model representation from source code
fragments
18
Schema Deployment
Target Database
.dbschema file
Schema Model
Schema Model
Model Diff
Plan Executors
.SQL

Deployment Engine
Incremental Target Update
Additional schema artifacts
19
demo
DB Life Cycle
20
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, sparse columns, XML bound, Word
  bound and web search bound
• Plus what you write yourself.....

21
demo
Data Generation
22
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

23
demo
Automated Testing
24
Is DB Testing Valid?

• 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

25
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

26
The Future

• Developer Database Team Editions merged
• Database Schema Providers
• Publicly extensible in Visual Studio 2010
• Quest Software announced that they will offer a
  Database Schema Provider (DSP) for Oracle
• Partnership with IBM provides DB2 support
• Contextual Project Feature Extensibility
• Publicly extensible in Visual Studio 2010

27
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

28
Good VSTS Blogs

• Gert Drapers (was Architect/Development Manager
  Visual Studio Team Edition for DB Professionals,
  but moved to new team Mar 09)
•  http//blogs.msdn.com/gertd
• VSTS DB Team Blog (replaces Gerts blog)
• http//blogs.msdn.com/vstsdb/
• Brian Harry (Product Unit Manager for Team
  Foundation Server)
• http//blogs.msdn.com/bharry

29
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_at_blackmarble.co.uk
• WebSite www.blackmarble.co.uk