Transcript Slide 1

MAKING THE SQL DEVELOPER ONE
OF THE FAMILY WITH VISUAL STUDIO
TEAM SYSTEM
black
marble
the strategic IT asset for your organisation
Richard Fennell
Engineering Director
SQLBits IV
28th March 2009
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
black
marble
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
black
marble
Visual Studio Team System
Application Life Cycle Management (ALM) Solution
Excel
Project
Operations, QA
and Help Desk
Team System
Web Access
Non-Microsoft
Developer
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
black
marble
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
black
marble
Visual Studio Team System 2008
Database 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 previously
shipped in the Power Tools
– T-SQL Static Code Analysis
– Dependency Viewer
• Many product enhancements and improvements
black
marble
Database Project Ecosystem
DSP
Extensions
DSP
Extensions
DSP
Extensions
DSP
Extensions
DSP
Extensions
DSP
Extensions
Schema
Compare
Data
Compare
Database
Refactoring
T-SQL
Static Code
Analysis
Database
Unit
Testing
Data
Generation
3rd Party
Designers
3rd Party
Tools
Project Features
SQL Server
2000 DSP
SQL Server
2005 DSP
SQL Server
2008 DSP
Parser
ScriptDOM
Interpreter
Reverse
Engineer
Deploy
Parser
ScriptDOM
Interpreter
Reverse
Engineer
Deploy
Parser
ScriptDOM
Interpreter
Reverse
Engineer
Deploy
Parser
ScriptDOM
Interpreter
Reverse
Engineer
Deploy
Database Model API
Database Eco
Project System
Solution Explorer
Schema View
Dependency
Viewer
Editor
DSP Extensions
3rd Party
DSP
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
black
marble
Database Edition Power Tools
• Power Tools 2008 added 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
• At present no GDR Power tools released
black
marble
Roles in a DB Project
Writes Tests
Creates New DB
Project
Reverse Engineers DB
to Project
Creates Data
Generation Plan
Manage
DB Administrator
Writes DB Code
Refactors
Reviews Changes
Runs Tests
Compares Updates to
Production
Checks In
Builds Deploy Package
Works with other
developers to
integrate
Deploys to Production
Develop
DB Developer
Deploy
DB Administrator
Creating the DB Project
TFS
Server
DBPro
Check in to
Create a Project
Source Control
DBA
Database
Project
Staging
Database
Import schema
Production
Database
Isolated Iterative Development
TFS
Server
Sandbox
Database
Sandbox
Database
DBPro
• Sync
• Check-out
• Edit/Refactor
• Test
• Check-in
• Work is being driven
and tracked via
work items
• Other team members can
pick up changes
DBA
Sandbox
Database
TFS Shelving
allows DBA
to provide guidance
and evaluate work
Staging
Database
Production
Database
Automated Build & Testing
Reports
TFS
Server
Test
Environment
Test
Get Latest
DBPro
Build Server
DBA
Staging
Database
Production
Database
Deploy the Project
TFS
Server
DBPro
Refine deploy
script
Sync from Label
DBA
Database
Project
Deploy
Staging
Database
Publish
Build
SQL
Deploy
Script
Verify
Production
Database
Schema Compilation
Source
Database
Reverse engineer
schema
into DDL artifacts
Interpret,
Analyze and
Validate
Schema Model
Project System
Build
DDL Scripts
(artifacts)
Schema Model
Compose model representation
from source code fragments
black
marble
.dbschema
file
Schema Deployment
.dbschema
file
Target
Database
Schema Model
Schema Model
Model Diff
Plan Executors
.SQL
…
Additional schema artifacts
Deployment
Engine
Incremental Target Update
demo
DB Life Cycle
black
marble
the strategic IT asset for your organisation
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.....
black
marble
demo
Data Generation
black
marble
the strategic IT asset for your organisation
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
black
marble
demo
Automated Testing
black
marble
the strategic IT asset for your organisation
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
black
marble
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
black
marble
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
black
marble
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
black
marble
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 Gert’s blog)
– http://blogs.msdn.com/vstsdb/
• Brian Harry (Product Unit Manager for Team
Foundation Server)
– http://blogs.msdn.com/bharry
black
marble
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 – [email protected]
WebSite – www.blackmarble.co.uk
black
marble