Transcript Database Project

MSDN TechTalk Agenda
Visual Studio Team Edition for Database Professionals
08:30 – 09:00
Registration
09:00 - 10:15 (Roger Boesch, Microsoft Schweiz)
Einbindung der Datenbank-Entwicklung in den Software
Life-Cycle
10:15 - 10:45
Kaffeepause
10:45 - 12:15 (Marcel Lattmann, Trivadis)
Verbesserung der Softwarequalität durch Datenbanktests
Integration of Database
Development into the Software
Life-Cycle (TSDATA)
Roger Boesch
Technology Solution Professional Developer Tools
Microsoft Switzerland GmbH
http://blogs.msdn.com/rogerboesch
Agenda
Overview of TSDATA
Database Development
Database Project System
Database Schemas
Refactoring
Team Foundation Server Integration
Database Testing
Next Session with Marcel Lattmann, Trivadis
Overview
of Team System for Database Professionals
Visual Studio Team System
Team Edition for Database Professionals
Take control of database change
Offline, change management of the database schema
Automated refactoring of database objects
Automate database testing to improve quality
Unit testing for database queries
Automatic generation of meaningful test data
Bring data professionals into the lifecycle
Provide full Visual Studio Team System support
Support Work Item tracking for database changes
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
Database Development
with Team System for Database Professionals
“The conceptual Idea behind”
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
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
Database Development
Database Projects & Schemas
“Start with a Baseline”
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
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 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
Import Database Schema
Also sometimes referred to as reverse engineer
Imports an existing database schema into a
database 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
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
DEMO
Make the baseline:
- The Project System
- Import Database Schema
- Source Control Integration
Database Development
Schema Compare
Refactoring
“Changes are daily business”
Schema Compare
What is Schema Compare?
GUI interface and/or SQL scripts to compare diffs
Viewer based on “Schema Engine”
Allows quick comparisons of
Project  Database
Database  Database
… On Object level
Functionality
Full SQL Server 2000 and SQL Server 2005 support
Refactoring
Bring power of refactoring to SQL
Cascading Change
Update all dependent objects in database project
Schema objects, Data generation, Unit Tests, SQL
Scripts
Make an atomic change
Rename
Meet corporate standards
Better express semantic intent – clarity
See more in next session…
Database Development
Team Foundation Server Integration
Build & Deployment
“Be part of the project team”
Team Foundation Server
Integration into the Life-Cycle
Work Items
Flexible work item tracking
System for requirements…
…Change requests, bugs and issues
The other aspects of team collaboration
Team Portal – SharePoint site for team collaboration
Project Management – manage project resources, timelines and
quality
Version Control – robust version control system for
all project artifacts, including branching, change sets, and shelving
Reporting – central data warehouse for real-time project metrics
and analytics
Build & Deployment
Used for Team Builds
Daily Builds/Nightly Builds
Deployment Support
Deploy via SQL query tool
Deploy via MSBuild task
Is a 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
DEMO
Implement change requests:
- Use work item(s)
- Schema Compare
- Refactoring
- Build and Deploy the Database Project
… and take a look into team collaboration…
© 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.