Transcript DAT312 - Managing and Deploying Your SQL Server Schemas with

DAT312
Managing and Deploying your
SQL Server schemas with Visual
Studio Team Edition for Database
Professionals
Mairead A. O’Donovan
Richard Waymire
Program Manager
PM Architect
Microsoft Corporation Microsoft Corporation
Agenda
Overview of Team System for Database
Professionals (TSDATA)
Database Projects
Source Control Integration
Schema Compare
Build and Deploy
Overview of Team System for
Database Professionals
Visual Studio Team System
New!
Visual Studio Team Suite
MSF Process and Guidance
Visual
Studio
Team
Explorer
Software
Architects
Software
Developers
Software
Testers
Database
Professionals
Application
Modeling
Code Analysis
Performance
Testing
Infrastructure and
Deployment
Modeling
Performance
Tuning
Manual Testing
Database
Change
Management
Database Testing
Test Case
Management
Database
Deployment
Security Analysis
Unit Testing
Code Coverage
Class
Modeling
Visio and UML Modeling
Visual Studio Professional
Edition
Load Test Agent
Visual Studio Team Foundation Server
Change Management
Reporting
Integration Services
Work Item Tracking
Project Site
Project Management
Visual
Studio
Industry
Partners
Product Overview
Database Project System
Schema and Script Versioning (SCC Integration)
Schema Build & Deploy
Schema Compare
Data Compare
Database Unit Testing
(Test) Data Generator
Schema Refactoring
T-SQL Editor with Query Execution
Work Item and Process Integration with TFS
Database Development Life Cycle
The cycle of life for database developers
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
Database Development Life Cycle
The cycle of life for database developers
Edit
Refactor
Compare
Deploy
Database
Project
Data
Generation
Test
Compare
Build
Database Development Life Cycle
The cycle of life for database developers
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 Model
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
Other Sessions
Introducing Visual Studio Team Edition for Database
Professionals
DEV217 - 6/13/2006 10:15AM - 11:30AM, room: 104 ABC
Managing and Deploying your SQL Server Schemas with
Visual Studio Team Edition for Database Professionals
DAT312 - 6/13/2006 1:00PM - 2:15PM, room: 156 ABC
Testing & Refactoring your Database with Visual Studio
Team Edition for Database Professionals
DAT320 - 6/14/2006 8:30AM - 9:45AM, room: 160 ABC
Under the Hood of Visual Studio Team Edition for
Database Professionals
DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC
DATABASE PROJECTS
Database Projects Defined
“Schema” projects
SQL Server 2000
SQL Server 2005
“Script Only” projects
SQL Server 2000
SQL Server 2005
Different types to support different
syntax/language options
A project is simply a series of files collected
together into a single logical collection
The files represent the truth of your schema
Schema Projects
Support three areas:
Data Generation Plans
Schema Objects
Scripts
Data Generation will be covered fully in talk
DAT320
Schema Objects
The “Meat and potatoes” of the project system
All SQL Server objects are reflected here
All objects within user databases, that is
And that are user-created objects (no system objects)
Schema folders reflect the product version
So “Service Broker” objects only show up in the SQL
Server 2005 project type
Table and View are containers for child objects
Indexes, Triggers, Constraints, Statistics, Full-text
Goal is to be as compatible as possible with SQL
Server Management Studio Object Explorer view
We use the same icons, folder view when possible
Templates
For each type of object you can have in the
project system, we have a version-specific
template to create that object
Wherever possible, the template will generate
valid SQL
But probably not the SQL you want
They give you a starting point to write your
SQL statements
Error States
Any given project item (file) can have three error states:
Clean
You just see the expected icon for the object
Dirty
You get a red exclamation point superimposed on the icon for
the object
This means the script/DDL wasn’t understood by us, or
The syntax is wrong
Not fully interpreted/implemented yet
You get a yellow warning triangle superimposed on the icon for the
object
This means we’re not done yet understanding some SQL code in the
object
So you shouldn’t see this when we release
You can’t refactor over the object in this file
But, it will build/deploy correctly
Errors and warnings show up in the Error List
Scripts
The Scripts folder contains two children folder
by default:
PreDeployment
PostDeployment
Each contains a single script (for now) that will
be either pre-pended or post-pended during
project build
More on that later…
You can also insert any folder or .SQL script file
into this area
Import Database Schema
Also sometimes referred to as reverse engineer
Imports an existing database schema into a
database project
For CTP3 it only supports importing into an empty
SQL Server 2000 project
We use the schema compare engine under the
covers to generate the script
Then submit the script one piece at a time as if
you’d typed them in yourself
We then turn each object into a separate file in
your project
Project Properties
Project properties set a variety of information about a
given database project
Project Version
Eventually you’ll be able to “upgrade” a project from SQL Server
2000 to SQL Server 2005
Default Schema
When a new object is created, what schema does it go into by default
Is FullText Indexing enabled
Off for CTP3
Default Collation
What collation should be assumed as default for all schema objects
Import Existing Schema will reset this to whatever the collation was
on the source database
Build Properties will be discussed later…
SOURCE CONTROL
INTEGRATION
Source Control Integration
Full Integration with Visual Studio SCC Interfaces
Support out of the box for Team Foundation Server,
Visual SourceSafe
However, any SCCI compliant source system should
work
Standard Graphical Interface
If you work with VB/C# projects and source control,
we’ll behave the same
Same icons on project items
Same top level/context menu items
The Project System, Import Database
Schema, and Source Control
Integration
Mairead A. O’Donovan
Program Manager
Database Development Life Cycle
The cycle of life for database developers
Edit
Refactor
Compare
Deploy
Database
Project
Data
Generation
Test
Compare
Build
Schema Compare
Schema Compare
What is Schema Compare?
Allows quick comparisons of:
project -> database
database -> database
GUI interface and/or SQL scripts to compare diffs
Full SQL Server 2000 and SQL Server 2005
support
Object level script differences between
databases
Handles Data Motion
Schema Compare (cont)
Options for restricting/changing script
Restrict which object types are compared
Compare while ignoring schema, filegroups, etc.
Enables comparison of test to production
Turn off dependency checking
Generate backwards compatible scripts
i.e. script a 2005 database with a 2000 script
Compare security settings
Users, roles, and permissions
Building and Deploying
Database Projects
Build/Deploy
Standard MSBuild task
Configurations
New vs. Existing builds
Project properties for build
Schema compare used for build
Pre/Post Deployment scripts
Build results in .SQL script file
Deploy
Deploy via SQL query tool
Deploy via MSBuild task
FUTURE: SQLCMD command support
Schema Compare, Build and
Deploy Database Project
Mairead A. O’Donovan
Program Manager
Database Development Life Cycle
The cycle of life for database developers
Edit
Refactor
Compare
Deploy
Database
Project
Data
Generation
Test
Compare
Build
Wrap Up
Database Projects will change the way
developers and DBAs work and think about
change management in SQL Server
Go get the CTP and try it out on your databases
Report feedback (good & bad)
Further Questions:
Richard: [email protected]
Mairead: [email protected]
Other Sessions
Testing & Refactoring your Database with
Visual Studio Team Edition for Database
Professionals
DAT320 - 6/14/2006 8:30AM - 9:45AM, room: 160
ABC
Under the Hood of
Visual Studio Team Edition for Database
Professionals
DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160
ABC
Resources
Technical Chats and Webcasts
http://www.microsoft.com/communities/chats/default.mspx
http://www.microsoft.com/usa/webcasts/default.asp
Microsoft Learning and Certification
http://www.microsoft.com/learning/default.mspx
MSDN & TechNet
http://microsoft.com/msdn
http://microsoft.com/technet
Virtual Labs
http://www.microsoft.com/technet/traincert/virtuallab/rms.mspx
Newsgroups
http://communities2.microsoft.com/
communities/newsgroups/en-us/default.aspx
Technical Community Sites
http://www.microsoft.com/communities/default.mspx
User Groups
http://www.microsoft.com/communities/usergroups/default.mspx
Visual Studio Team System
Breakout Sessions
DEV304 - Delving into Visual Studio 2005 Team Edition for Software Architects
6/12/2006 10:45AM-12:00PM 160ABC
DEV307 - Delving into Visual Studio 2005 Team Edition for Software Developers
6/12/2006 1:30PM-2:45PM Grand Ballroom A
DEV311 - Delving into Visual Studio 2005 Team Edition for Software Testers
6/12/2006 5:00PM-6:15PM 104 ABC
DEV217 - Introducing Visual Studio 2005 Team Edition for Database Professionals
6/13/2006 10:15AM-11:30AM 104 ABC
DAT312 - Managing and Deploying Your SQL Server Schemas with Visual Studio Team Edition for Database
Professionals
6/13/2006 1:00PM-2:15PM 156 ABC
DAT320 - Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals
6/14/2006 8:30AM-9:45AM 160 ABC
DEV327 - Visual Studio 2005 Team Foundation Server (Part 1): Applying Version Control, Work Item Tracking and
Team Build to Your Software Development Project
6/14/2006 10:15AM-11:30AM 160 ABC
DEV429 - Visual Studio 2005 Team Foundation Server (Part 2): Developing Custom Process Templates, Work Item
Types and Policies
6/14/2006 2:00PM-3:15PM 160 ABC
DEV233 - Visual Studio 2005 Team Foundation Server: Using Metrics to Manage and Troubleshoot Your Projects
6/14/2006 5:30PM-6:45PM 153 ABC
DEV237 - Visual Studio 2005 Team Foundation Server: Step-by-Step Migration and Adoption Planning
6/15/2006 9:45AM-11:00AM 104 ABC
DEV439- Visual Studio 2005 Team System and Microsoft Solution Framework: Implementing an Agile or CMMI
Process
6/15/2006 1:00PM-2:15PM 156 ABC
DAT433
Under the Hood of Visual Studio Team Edition for Database Professionals 6/15/2006
1:00PM-2:15PM 160 ABC
Visual Studio Team System HOL / TCL
Hands on Labs
DEV005 Streamlining your Development Process with Visual Studio 2005
Team System
DEV008 Take a Tour of Visual Studio 2005 Team System for Database
Professionals
Chalktalks
Building Quality In: Visual Studio Team System and Continuous
Integrated Testing
DEV TLC Theatre 6/14/2006 8:30AM-9:45AM
Teamlook and TeamSpec in Context Modeling that Supports Visual
Studio 2005
DEV TLC Theatre 6/14/2006 5:30PM-6:45PM
Modeling that Supports Visual Studio 2005
DEV TLC Theatre 6/14/2006 5:30PM-6:45PM
Visual Studio Team Edition for Database Professionals: Overview
DEV TLC Theatre 6/15/2006 9:45AM-11:00AM
Code Generation and Model Execution from DSL Tools
DEV TLC Theatre 6/15/2006 2:45PM-4:00PM
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.