MSDN TechTalk AgendaMSDN TechTalk AgendaVisual Studio Team Edition for Database Visual Studio Team Edition for Database ProfessionalsProfessionals

08:30 – 09:0008:30 – 09:00

RegistrationRegistration

09:00 - 10:15 (Roger Boesch, Microsoft Schweiz)09:00 - 10:15 (Roger Boesch, Microsoft Schweiz)

Einbindung der Datenbank-Entwicklung in den Einbindung der Datenbank-Entwicklung in den Software Life-CycleSoftware Life-Cycle

10:15 - 10:4510:15 - 10:45

KaffeepauseKaffeepause

10:45 - 12:15 (Marcel Lattmann, Trivadis)10:45 - 12:15 (Marcel Lattmann, Trivadis)

Verbesserung der Softwarequalität durch Verbesserung der Softwarequalität durch DatenbanktestsDatenbanktests

Integration of Database Integration of Database Development into the Development into the Software Life-Cycle Software Life-Cycle (TSDATA)(TSDATA)

Integration of Database Integration of Database Development into the Development into the Software Life-Cycle Software Life-Cycle (TSDATA)(TSDATA)

Roger BoeschRoger BoeschTechnology Solution Professional Developer Technology Solution Professional Developer ToolsToolsMicrosoft Switzerland GmbHMicrosoft Switzerland GmbH

http://blogs.msdn.com/rogerboeschhttp://blogs.msdn.com/rogerboesch

AgendaAgenda

Overview of TSDATAOverview of TSDATA

Database DevelopmentDatabase DevelopmentDatabase Project SystemDatabase Project System

Database SchemasDatabase Schemas

RefactoringRefactoring

Team Foundation Server IntegrationTeam Foundation Server Integration

Database TestingDatabase TestingNext Session with Marcel Lattmann, TrivadisNext Session with Marcel Lattmann, Trivadis

OverviewOverviewof Team System for Database of Team System for Database ProfessionalsProfessionals

Visual Studio Team SystemVisual Studio Team SystemTeam Edition for Database ProfessionalsTeam Edition for Database Professionals

Take control of database changeTake control of database changeOffline, change management of the database Offline, change management of the database schemaschema

Automated refactoring of database objectsAutomated refactoring of database objects

Automate database testing to improve Automate database testing to improve qualityquality

Unit testing for database queriesUnit testing for database queries

Automatic generation of meaningful test dataAutomatic generation of meaningful test data

Bring data professionals into the lifecycleBring data professionals into the lifecycleProvide full Visual Studio Team System supportProvide full Visual Studio Team System support

Support Work Item tracking for database Support Work Item tracking for database changeschanges

Visual Studio Team SystemVisual Studio Team SystemVisual Studio Team SuiteVisual Studio Team Suite

MS

F P

rocess a

nd

Gu

idan

ce

MS

F P

rocess a

nd

Gu

idan

ce

Visual Studio Team Foundation ServerVisual Studio Team Foundation Server

VisualStudio

IndustryPartners

SoftwareSoftwareArchitectsArchitects

SoftwareSoftwareDevelopersDevelopers

SoftwareSoftwareTestersTesters

DatabaseDatabaseProfessionalsProfessionalsVisual

StudioTeam

ExplorerApplication Application ModelingModeling

Infrastructure Infrastructure and Deployment and Deployment

ModelingModeling

Code AnalysisCode Analysis

Performance Performance TuningTuning

Security AnalysisSecurity Analysis DatabaseDatabase Deployment Deployment

Database Database Change Change

ManagementManagementDatabase Database TestingTesting

Performance Performance TestingTesting

Manual TestingManual Testing

Test Case Test Case ManagementManagement

Visual Studio Professional Visual Studio Professional EditionEdition

Change ManagementChange Management

Work Item TrackingWork Item Tracking

ReportingReporting

Project SiteProject Site

Integration ServicesIntegration Services

Project ManagementProject Management

Load Test AgentLoad Test Agent

Visio and UML Visio and UML ModelingModeling

Class Class ModelingModeling

Unit TestingUnit Testing

Code Code CoverageCoverage

New!

Database DevelopmentDatabase Developmentwith Team System for Database Professionalswith Team System for Database Professionals

“The conceptual Idea “The conceptual Idea behind”behind”

Database Development Life Database Development Life CycleCycleThe cycle of life for database developersThe cycle of life for database developers

DatabaseDatabaseProjectProject

DatabaseDatabaseProjectProject

EditEditEditEdit

CompareCompareCompareCompare

TestTestTestTest

BuildBuildBuildBuild

Data Data GenerationGeneration

Data Data GenerationGeneration

DeployDeployDeployDeploy

RefactorRefactorRefactorRefactor

CompareCompareCompareCompare

Database Development Life Database Development Life CycleCycle

DatabaseDatabaseProjectProject

DatabaseDatabaseProjectProject

Import database schemaImport database schema

Reverse engineer existing .SQL script filesReverse engineer existing .SQL script files

Create New ProjectCreate New Project

SQLSQLScriptScript

DatabasDatabasee

ProjectProjectTemplatTemplat

ee

SQLSQLServerServer

DatabasDatabasee

DeployDeploySQLSQL

ScriptScript

SQLSQLServerServer

DatabasDatabasee

Build projectBuild project

Deploy projectDeploy project

Database DevelopmentDatabase Development

Database Projects & Database Projects & SchemasSchemas

“Start with a Baseline”“Start with a Baseline”

Project ModelProject ModelThe center of gravityThe center of gravity

The database project represents the The database project represents the “truth” with regards to schema versioning“truth” with regards to schema versioning

Optionally database project can be placed Optionally database project can be placed under source controlunder source control

.SQL script files is the canonical format .SQL script files is the canonical format usedused

Changes are tracked at the “object level”Changes are tracked at the “object level”For example indexes, constraints, triggers are For example indexes, constraints, triggers are tracked independent of the base table tracked independent of the base table definition, in order have the highest granularity definition, in order have the highest granularity of change tracking of change tracking

Database Projects DefinedDatabase Projects Defined

““Schema” projectsSchema” projectsSQL Server 2000SQL Server 2000

SQL Server 2005SQL Server 2005

““Script Only” projectsScript Only” projectsSQL Server 2000SQL Server 2000

SQL Server 2005SQL Server 2005

Different types to support different Different types to support different syntax/language optionssyntax/language options

A project is simply a series of files collected A project is simply a series of files collected together into a single logical collectiontogether into a single logical collection

The files represent the truth of your The files represent the truth of your schemaschema

Schema ObjectsSchema Objects

The “Meat and potatoes” of the project The “Meat and potatoes” of the project systemsystemAll SQL Server objects are reflected hereAll SQL Server objects are reflected here

All objects within user databases, that isAll objects within user databases, that isAnd that are user-created objects (no system And that are user-created objects (no system objects)objects)

Schema folders reflect the product versionSchema folders reflect the product versionSo “Service Broker” objects only show up in the So “Service Broker” objects only show up in the SQL Server 2005 project typeSQL Server 2005 project type

Table and View are containers for child Table and View are containers for child objectsobjects

Indexes, Triggers, Constraints, Statistics, Full-Indexes, Triggers, Constraints, Statistics, Full-texttext

Goal is to be as compatible as possible Goal is to be as compatible as possible with SQL Server Management Studio with SQL Server Management Studio Object Explorer viewObject Explorer view

We use the same icons, folder view when We use the same icons, folder view when possiblepossible

Import Database SchemaImport Database Schema

Also sometimes referred to as reverse Also sometimes referred to as reverse engineerengineer

Imports an existing database schema into Imports an existing database schema into a database projecta database project

We use the schema compare engine under We use the schema compare engine under the covers to generate the scriptthe covers to generate the script

Then submit the script one piece at a time Then submit the script one piece at a time as if you’d typed them in yourselfas if you’d typed them in yourself

We then turn each object into a separate We then turn each object into a separate file in your projectfile in your project

Source Control IntegrationSource Control Integration

Full Integration with Visual Studio SCC Full Integration with Visual Studio SCC InterfacesInterfaces

Support out of the box for Team Foundation Support out of the box for Team Foundation Server, Visual SourceSafeServer, Visual SourceSafe

However, any SCCI compliant source system However, any SCCI compliant source system should workshould work

Standard Graphical InterfaceStandard Graphical InterfaceIf you work with VB/C# projects and source If you work with VB/C# projects and source control, we’ll behave the samecontrol, we’ll behave the same

Same icons on project itemsSame icons on project items

Same top level/context menu itemsSame top level/context menu items

DEMOMake the baseline:

- The Project System- Import Database Schema- Source Control Integration

DEMOMake the baseline:

- The Project System- Import Database Schema- Source Control Integration

Database DevelopmentSchema CompareRefactoring “Changes are daily business”

Database DevelopmentSchema CompareRefactoring “Changes are daily business”

Schema CompareSchema Compare

What is Schema Compare?What is Schema Compare?GUI interface and/or SQL scripts to compare GUI interface and/or SQL scripts to compare diffsdiffs

Viewer based on “Schema Engine”Viewer based on “Schema Engine”

Allows quick comparisons ofAllows quick comparisons ofProject Project Database Database

Database Database Database Database

… … On Object levelOn Object level

FunctionalityFunctionalityFull SQL Server 2000 and SQL Server 2005 Full SQL Server 2000 and SQL Server 2005 supportsupport

RefactoringRefactoring

Bring power of refactoring to SQLBring power of refactoring to SQLCascading ChangeCascading Change

Update all dependent objects in database Update all dependent objects in database project project

Schema objects, Data generation, Unit Tests, Schema objects, Data generation, Unit Tests, SQL ScriptsSQL Scripts

Make an atomic changeMake an atomic change

RenameRenameMeet corporate standardsMeet corporate standards

Better express semantic intent – clarityBetter express semantic intent – clarity

See more in next session…See more in next session…

Database DevelopmentTeam Foundation Server Integration Build & Deployment “Be part of the project team”

Database DevelopmentTeam Foundation Server Integration Build & Deployment “Be part of the project team”

Team Foundation ServerTeam Foundation ServerIntegration into the Life-CycleIntegration into the Life-Cycle

Work ItemsWork ItemsFlexible work item trackingFlexible work item trackingSystem for requirements…System for requirements………Change requests, bugs and issuesChange requests, bugs and issues

The other aspects of team collaborationThe other aspects of team collaborationTeam Portal – SharePoint site for team collaborationTeam Portal – SharePoint site for team collaborationProject Management – manage project resources, Project Management – manage project resources, timelines and quality timelines and quality Version Control – robust version control system forVersion Control – robust version control system forall project artifacts, including branching, change sets, all project artifacts, including branching, change sets, and shelvingand shelvingReporting – central data warehouse for real-time project Reporting – central data warehouse for real-time project metrics and analyticsmetrics and analytics

Build & DeploymentBuild & Deployment

Used for Team BuildsUsed for Team BuildsDaily Builds/Nightly BuildsDaily Builds/Nightly Builds

Deployment SupportDeployment SupportDeploy via SQL query toolDeploy via SQL query tool

Deploy via MSBuild taskDeploy via MSBuild task

Is a standard MSBuild taskIs a standard MSBuild taskConfigurationsConfigurations

New vs. Existing buildsNew vs. Existing builds

Project properties for buildProject properties for build

Schema compare used for buildSchema compare used for build

Pre/Post Deployment scriptsPre/Post Deployment scripts

Build results in .SQL script fileBuild results in .SQL script file

DEMOImplement change requests:

- Use work item(s)- Schema Compare- Refactoring- Build and Deploy the Database Project

… and take a look into team collaboration…

DEMOImplement 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.