Transcript DAT317 Database Schema Versioning: How to Use Microsoft

DAT317
DAT317
The Database Development Lifecycle
Working in a Team Environment
How to Establish your Project
The Project System
Understanding the Schema
The Schema Object Container
Build and Deploy
Incorporate the Database Professional into the software lifecycle and provide them with a
foundation for change management and process integration.
Change Management
Project Based Development
Project Model that represents schema as objects providing a “personal sandbox” for offline development
that lives within a Visual Studio Solution
Team Collaboration with Work Item and Process Integration with Team Foundation Server
Automated Change Support
Rename Refactoring with the ability to preview pending changes prior to execution
Comparison Tools (Schema & Data Compare) allow comparisons & synchronization of schema and data
with design/test/production databases
Source/Version Control of all database objects with the ability to reverse engineer a database to bring it
under Source Control
Database Unit Testing
Leverages the Test Project Infrastructure
Generate Meaningful Data Values through the ability to import information such as Row Counts and
histograms from a real database
Data Generator provides Repetitive Dataset Generation for tests based on saved settings
Build / Deployment
MSBuild Integration for Database Deployments/Builds based on Projects
Either Create a new Database at the target location or Update an Existing Schema
Difficult to manage
changes to the schema
Production database
is “the” 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
Schema Change now managed in VSTS
and TFS
Production Database is now “the” version
of the truth” for just the Data
Production
Database
Management
Studio Tuning
Monitoring
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
“One Version of the Truth”
for Data
“One Version of the Truth”
for Schema
• Offline
• Under Source Control
Schema
Schema Changes
Changes can be rolled out
in a scheduled, managed
way
Scripts allow administrators
to mange change updates
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Create New Project
Database
Project
Reverse engineer existing .SQL script files
Collection of
.SQL file
containing TSQL DDL
fragments
.SQL
Source
Parse
SqlCode
Dom
Interpret
Schema
Model
Parsing
 SqlCodeDom (based on Abstract Syntax Trees)
Interpretation
 Schema Model
Symbol list
Object References (hard and soft dependencies)
Edit
Refactor
Compare
Deploy
Database
Project
Data
Generation
Test
Compare
Build
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Deploy
SQL
Script
Build project
Create New Project
Database
Project
Deploy project
Reverse engineer existing .SQL script files
SQL
Server
Database
The center of gravity
The database project represents the “truth” with
regards to schema versioning
Optionally database project can be placed under
source control
.SQL script files is the canonical format used
Changes are tracked at the “object level”
For example indexes, constraints, triggers are tracked
independent of the base table definition, in order have
the highest granularity of change tracking
SCM
Check in to
Source Control
DBDev
DBA
Trust Boundary
Staging
Database
Database
Project
Import schema
Production
Database
Sand
box
Sand
box
SCM
Sand
box
DBDev
DBA
Sand
box
• Sync
• Check-out
• Edit/Refactor
• Test
• Check-in
• Work is being driven
and tracked via
work items
Trust Boundary
Staging
Database
Production
Database
Daily
Build
Output
Can also be
used in a
“Continuous”
build environment
SCM
Get Latest
Daily Build
DBDev
DBA
Test
Database
Test
Trust Boundary
Staging
Database
Production
Database
SCM
Sync from Label
DBDev
DBA
Trust Boundary
Deploy
Database
Project
Refine deploy script
Build
SQL
Deploy
Script
Verify
Staging
Database
Production
Database
Managed, project oriented evolution of
database schema
Application and database schema can now be
managed together
Work in “isolation”, deploying only when changes
verified through empirical means
Leverage VSTS work item tracking and process
guidance increases team collaboration and unity
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)
Build-up understanding of the DDL
Fragments in Stages
Phase-1 Parsing
Retrieve the object identifier and object type
Phase-1 Interpretation
Retrieve additional type specifics like schemabinding
Phase-1 SQL Server Compile Validation
Perform compile time validation against (local) SQL Server, design database with is associated with the
project
Phase-2 Parsing
Build a full AST (Abstract Syntax Tree, aka the parse tree) for the DDL fragment
Phase-2 Interpretation
Retrieve the remaining type specific detail from the AST
All stages contribute to building and maintaining
the schema context
Object symbol list
Object dependency graph (tracking)
P1
Parsing
Success
P1
Interpretation
Failed
Failed
Error
List
Update
Schema
Context
Schema
Manager
Failed
Warning
List
Success
P2
Interpretation
Failed
Success
Design
DB
Success
Failed
Success
Schema
Manager
P2
Parsing
Failed
Add to
Schema
Context
Everything is a .SQL file
Associated with the T-SQL editor
Using a two part naming scheme to identify types
This is not required, but helps identification of types
Providing visual feedback (icon)
Associating with code-behind designers in the future
Enforcing the single object per file
By default the file name encodes the object name
Not required
Filename do not have to match the containing type name
Required since SQL Server namespace restrictions do not
match the file system naming restrictions
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Deploy
SQL
Script
Build project
Create New Project
Database
Project
Deploy project
Reverse engineer existing .SQL script files (*)
(*) Not implemented in the current CTP
SQL
Server
Database
Project State
“What you want”
Database
Project
Difference based
build script
Current State
“How it is right now”
Build
Target
Database
SQL
Script
Deploy
Execute Incremental
Update Script
All steps are implemented as MSBuild tasks
SqlBuildTask
SqlDeployTask
DataGeneratorTask
All project properties can be overwritten at the
command line
Note:
Be aware of the “user dependent” settings in the
.dbproj.user file when using TeamBuild
Using the project settings:
msbuild NorthwindOnline.dbproj /t:build
Overwriting project settings
msbuild NorthwindOnline.dbproj /t:build
/p:TargetConnectionString="Data
Source=(local)\sql80;Integrated
Security=True;Pooling=False;"
/p:TargetDatabase="NorthwindOnlineTestDB"
Using the project settings:
msbuild NorthwindOnline.dbproj /t:deploy
Overwriting project settings:
msbuild NorthwindOnline.dbproj /t:deploy
/p:TargetConnectionString="Data
Source=(local)\sql80;Integrated
Security=True;Pooling=False;"
/p:TargetDatabase="NorthWind"
How can I deploy to multiple targets?
The Database Project only understand a single
target server/database at the time
You can use the MSBuild tasks to provision
multiple servers
Using command line or tool that calls the MSBuild
infrastructure
Pseudo code:
for each server+database combination in list
{
SqlBuildTask
SqlDeployTask
}
Build Server infrastructure
Fully integrates with Team Foundation Server
Extensible
Based on MSBuild
Fully extensible XML-based scripting
Rich Command-line support
Remote and Desktop build support
Have to install Team Build separately
Not installed as part of Team Foundation Server
TF Client
Team Build Client
Build Configuration
files - MSBuild Scripts
Build
request and
Reports
Build Server
Build start/stop
Build events
Source
Control
Team Build
Build sources
and scripts
Work item
Tracking
TFS
Team Build
Service
Open and
Update bugs
Build and
Test data
MSBuild
Scripts
and targets
Team Build
logger
Build
Events
Team Build
Store
TF
Warehouse
TFS Data Tier
Static Analysis
& Testing
Build
Drop site
Using Team Explorer
Create Team Build Type using Wizard
Creates a TFSBuild.proj script located in a
TeamBuildTypes folder in version control
You will need to customize this script
Check-out the TFSBuild.proj file for edit
Ensure you are using the Default configuration
<ConfigurationToBuild Include="Default|Any CPU">
<FlavorToBuild>Default</FlavorToBuild>
<PlatformToBuild>Any CPU</PlatformToBuild>
</ConfigurationToBuild>
Add an AfterDropBuild target or AfterCompile if you are
doing testing
<Target Name="AfterDropBuild">
<MSBuild Projects="$(SolutionRoot)\SolutionName\ProjectName\
ProjectName.dbproj" Properties="Configuration=Default;OutDir=
$(SolutionRoot)\..\binaries\Default\" Targets="Deploy" />
</Target>
Check-in changes
Ensure your build references the test list
<MetaDataFile Include="$(SolutionRoot)\MyProject\
MyProject.vsmdi">
<TestList>MyUnitTests</TestList>
</MetaDataFile>
Ensure your build copies the app.config file from
the test project
Database unit tests need to access the app.config
to locate the .dgen file
Note: Relative paths are different in Team Build than in
Visual Studio!
Copy App.Config to TeamBuild.App.Config and
make the path changes
<DataGeneration
DataGenerationFileName="..\..\..\Sources\
MySolutionName\MyProjectName\Data Generation Plans\
MyProjectTestData.dgen" />
Add another task to your Build Type
<ItemGroup>
<TestProjectConfigFile
Include="C:\build\MySolutionName\MyProjectName\src\MySolutionN
ame\MyTestProjectName\TeamBuild.app.config" />
</ItemGroup>
<Target Name="AfterCompile">
<Copy
SourceFiles="@(TestProjectConfigFile)"
DestinationFiles="@(TestProjectConfigFile->
'C:\Build\MySolutionName\MyTestProjectName\Binaries\
Default\MyTestProjectName.dll.config')" />
</Target>
Target server and database settings are stored in
a project.dbproj.user file
This file is not version controlled by default
You will have to manually edit the .dbproj file,
copying lines over from the .user file
<TargetConnectionString>Data Source=TFS\SQL90;
Integrated Security=True;Pooling=False
</TargetConnectionString>
<TargetDatabase>
MyProjectName
</TargetDatabase>
Check changes back in when done
Managed, project oriented evolution of database
schema – no more rollbacks
Application and database schema can now be
managed together
Work in “isolation”, deploying only when
changes verified
Leverage VSTS work item tracking and
process guidance
Further Questions:
Gert: [email protected]
MSDN Forum: Visual Studio Team System Database Professionals
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725&SiteID=1
White papers
What Microsoft Visual Studio 2005 Team Edition for Database Professionals Can Do for You
A Security Overview of Microsoft Visual Studio 2005 Team Edition for Database Professionals
Database Unit Testing with Team Edition for Database Professionals
Samples
http://www.codeplex.com/vsdbpro
Trial Edition
http://www.microsoft.com/downloads/details.aspx?familyid=7de00386-893d-4142-a778992b69d482ad&displaylang=en
Blog
http://blog.msdn.com/gertd
Technical Communities, Webcasts, Blogs, Chats & User Groups
http://www.microsoft.com/communities/default.mspx
Microsoft Learning and Certification
http://www.microsoft.com/learning/default.mspx
Microsoft Developer Network (MSDN) & TechNet
http://microsoft.com/msdn
http://microsoft.com/technet
Trial Software and Virtual Labs
http://www.microsoft.com/technet/downloads/trials/default.mspx
Visit the TLC Area to talk to SQL Server experts
Download the latest SQL Server “Katmai” CTP from:
http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
SQL Server TechCenter:
http://www.microsoft.com/technet/prodtechnol/sql/default.mspx
SQL Server DevCenter:
http://msdn.microsoft.com/sql
SQL Server Best Practices:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx
Complete an
evaluation on
CommNet and
enter to win!
© 2007 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation.
MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.