Transcript Under the Hood of Visual Studio Team Edition for Database

DAT433
Under the Hood of
Visual Studio Team Edition
for Database Professionals
DAT433
Under the Hood of
Visual Studio Team Edition
for Database Professionals
Gert E.R. Drapers
Architect/Development Manager
Microsoft Corporation
Agenda
The Project System
Understanding the Schema
The Schema Object Container
Build and Deploy
Data Generation
Generator Extensibility
Distributor Extensibility
Generating from the Command Line
Database Unit Testing
Using the Code Underneath
Data Driven Testing
Project Model
The center of gravity
SQL
Server
Database
Database
Project
Template
SQL
Script
Import database schema
Create New Project
Database
Project
Reverse engineer existing .SQL script files (*)
(*) Not implemented in the current CTP
Collection of
.SQL file
containing TSQL DDL
fragments
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)
Shredding in to SQL Fragments
Loading, importing or reverse engineering shreds the
schema definition into the smallest possible DDL
fragments, for 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] ADD
CONSTRAINT [FK_Territories_Region] FOREIGN KEY
([RegionID]) REFERENCES [dbo].[Region] ([RegionID])
Understanding Your Schema
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)
Understanding Schema Objects
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
File Naming & Extension Scheme
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
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
Directory Structure SQL 2000
Project
Data Generation Plans
Schema Objects
Functions
Stored Procedures
Tables
Views
Security
Roles
Users
Roles
Application Roles
Database Roles
Storage
File Groups
Full Text Catalogs
Types
User-defined Data Types
Scripts
Post-Deployment
Pre-Deployment
Directory Structure SQL 2005
Project
Data Generation Plans
Schema Objects
Aggregates
Assemblies
Database Triggers
Functions
Security
Asymmetric Keys
Certificates
Endpoints
Roles
Application Roles
Database Roles
Schemas
Symmetric Keys
Users
Schema Objects…
Storage
File Groups
Full Text Catalogs
Partition Functions
Partition Schemes
Stored Procedures
Synonyms
Tables
Types
User-defined Data Types
User-defined Types (CLR)
XML Schema Collections
Service Broker
Contracts
Event Notifications
Message Types
Queues
Remote Service Binding
Routes
Services
Views
Scripts
Post-Deployment
Pre-Deployment
Exploring the File Structure
Build & Deploy
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
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"
MSBuild Task: SqlBuild/SqlDeploy
BuildType { "New Deployment" | "Update Deployment“ }
TargetConnectionString
TargetDatabase
Build options:
DefaultCollation {"True" | "False“}
EnableFullTextIndexing {"True" | "False“}
ScriptCreateDBStatement {"True" | "False“}
GenerateDropsIfNotInProject {"True" | "False“}
SourceDatabase {"True" | "False“}
Set options:
ARITHABORT {"True" | "False“}
NUMERIC_ROUNDABORT {"True" | "False“}
ANSI_NULLS {"True" | "False“}
CONCAT_NULL_YIELDS_NULL {"True" | "False“}
ANSI_PADDING {"True" | "False“}
ANSI_WARNINGS {"True" | "False“}
QUOTED_IDENTIFIER {"True" | "False“}
Command Line Build & Deploy
Provisioning Multiple Servers
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
}
Data Generator Extensibility
Generators
Implement:
IDesigner
IGenerator
Base class
Generator
Attributes
GeneratorAttribute
GeneratorNameAttribute
Distributions
Implement:
IDistribution
Registration
Registration
Generators and Distributions have to:
Live in or under the: %ProgramFiles%\Microsoft Visual Studio
8\DBPro\Extensions directory
Get registered in the %ProgramFiles%\Microsoft Visual Studio
8\DBPro\Microsoft.VisualStudio.TeamSystem.Data.Extensions.x
ml file
Be strong key signed
<?xml version="1.0" encoding="us-ascii"?>
<types version="1">
<type>Microsoft.VisualStudio.TeamSystem.Data.Generators.RegexString,
Microsoft.VisualStudio.TeamSystem.Data.Generators,
Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a
</type>
<type>Microsoft.VisualStudio.TeamSystem.Data.Generators.Exponential,
Microsoft.VisualStudio.TeamSystem.Data.Generators,
Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a
</type>
</types>
Extending the Data Generation
Database Unit Testing
Database Unit Test Designer
Similar to WinForms Designer
Provides T-SQL View of Database Unit Test
Round-trips C# / VB Team Test Unit Test Code
Generates C# or VB.NET Code
Standard Team Test classes &methods
Standard ADO.NET Data Access Code
Extending Database Unit Tests
Customizing generated code
Custom Verification Logic
Go beyond supplied test conditions with custom
C#\VB verification logic
Managing Transactions
Capability of putting tests in automatic rollback mode
to always maintain original state of database
Data Driven Testing
Drive database test with parameters supplied from
specified data source
Extending Database Unit Tests
Sachin Rekhi
Program Manager
Visual Studio Team System
Resources
Chalk Talks
Visual Studio Team Edition for Database
Professionals: Overview
DEV TLC Theatre 6/15/2006 9:45AM-11:00AM
Hands on Labs
DEV008 Take a Tour of Visual Studio 2005 Team
System for Database Professionals
Resources…
CTP 3 Download Site
http://download.microsoft.com/download/1/a/3/1a32ea84-11a34adf-953e-7a65b9831f5a/VSDATAD1.img
Team Website
http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/de
fault.aspx
Product Forum
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7
25&SiteID=1
PowerToys and Samples
http://gotdotnet.com/Workspaces/Workspace.aspx?id=378460fd1254-427b-aa7d-e777a826a564
Blogs
http://blogs.msdn.com/gertd
Summary
Understanding the schema references and
relationships
Using .SQL files as canonical representation
Command Line Access through MSBuild tasks
Extensibility of Data Generation
Customization of the Database Unit Testing
Fill out a session
evaluation on
CommNet for
a chance to
Win an XBOX 360!
© 2006 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.