Slide 1 - MSDN Blogs

Report as inappropriate Your report has been received
Shared by: pengxuebo
Tags
Stats
views:: 6
posted:: 8/5/2011
language:: English
pages:: 64
Public Domain
Document Sample
							Visual Studio Team Edition
for Database Professionals
Mario Szpuszta
Software Architect
Developer & Platform Group
Microsoft Österreich GmbH.
marioszp@microsoft.com
http://blogs.msdn.com/mszCool



                             Microsoft Confidential
MSDN Briefings – Organisation

 Monthly technical briefings
   Currently released technology
   Your current needs


 Invitation / Registration / Feedback
   http://blogs.msdn.com/msdnat
   http://blogs.msdn.com/talk


 Well, what I am doing here?
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
Static Code Analysis
Unit Testing
   IT Solution Lifecycle

        Value through “Better Together” integration across the Solution Lifecycle



 IT                            The Development Engine                Operational
 Governance                                                          Excellence
                                          PM                        • Deployment
• Business                                                          • Impact analysis
  process re-engineering                                            • Updates
• Demand generation             Test               Arch                and Maintenance
• Business                                                          • App Health Monitoring
  Value Capture                                                     • Security
• Outsourcing                                                       • Automated failover
• Resource planning                       Dev                         and recovery plans
                                                                    • Workflow customization
                                    Visual Studio Team System
                                         Visual Studio                             Visual Studio                       Visual Studio
Process and Architecture Guidance




                                         Team Architect                            Team Developer                      Team Test




                                                                                                                                               Visual Studio Industry Partners
                                        Application Modeling                       Dynamic Code Analyzer           Load Testing

                                        Logical Infra. Modeling                    Static Code Analyzer            Manual Testing

                                        Deployment Modeling                        Code Profiler                   Test Case Management

                                                                                     Unit Testing

                                                                                     Code Coverage

                                           Class Modeling

                                           Visio and UML Modeling

                                             Team Foundation Client

                                             VS Pro



                                    Visual Studio
                                    Team Foundation                   Change Management             Reporting           Integration Services

                                      Build Server                    Work Item Tracking            Project Site        Project Management
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
Product Overview (1)

 Database Project System
   Schema and Script Versioning
   SCC Integration
   T-SQL Editor with Query Execution
 Database Schema Management
   Build & Deploy
   Schema Compare
   Data Compare
Product Overview (2)

 Database Unit Testing
   (Test) Data Generator
   Stored Procedure Unit Testing
 Schema Refactoring
   Version 1: Change names, only
   Future versions: indexes, constraints, relations
 Team Foundation Server Integration
   Work Item Tracking
   Process Integration
Database Development Life Cycle
The cycle of life for database developers

                  Edit

                  Refactor

                  Compare


                     Database
   Deploy             Project     Build



                  Data
                  Generation
                  Test
                  Compare
Conceptual Overview
                             Production   Management
                             Database     Studio
 Difficult to Manage
                                                  Tuning
                                                     Monitoring
 Change to the schema
 Production Database is                     Schema Changes
                             Schema       “One Version of the
 one version of the truth                 Truth” for Data and
 for Data and Schema                      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
Conceptual Overview
 Schema Change now managed in          Production       Management
 VSTS and TFS                          Database         Studio  Tuning
 Production Database is now “One                                     Monitoring
 version of the truth” only for Data
 DBA doesn’t have access to
 changes until he/she has deploy or
 reject choice
                                                        “One Version of the
 “One Version of the truth for
                                                        Truth” for Data
 Schema” is Under Source Control

                                                     Changes can be rolled out in
                        “One Version of the Truth”   a scheduled, managed way
                        for Schema                   Scripts allow administrators
                         • Offline                   to mange change updates
                         • Under Source Control


     Schema

  Schema Changes
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
Project Model
The center of gravity
                                                                Collection of
                                                                .SQL file
   SQL        Import database schema                            containing T-
   Server                                                       SQL DDL
   Database                                                     fragments



  Database
                                    Database
  Project
              Create New Project    Project
  Template




    SQL
    Script    Reverse engineer existing .SQL script files (*)
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)
Offline Model                           Production
                                        Database
 Import database schema to populate
         Create table AUCTION
 project from existing database
         ( id int not null,
         title varchar(25) not null,

 Changes to schema traditionally have
         startDate DateTime not null,
         length in not null)

 immediate affect
 With off-line project nothing changes
 until you deploy the change
                                         Test
                                         Database
Reverse Engineering a Schema




DEMO
Shredding in to SQL Fragments

 Loading, importing or reverse engineering
   Shreds the schema definition
   Smallest possible DDL fragments
 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])
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
Managed Change

 Changes are local
 Comparison between databases
   Test database
   Production database
 Elements under source control
   Any SCCI compliant version system
 Template driven
   Version specific SQL 2000 or SQL 2005
Working With the Project

 Make changes
   Add new elements
   Modify existing elements
   Delete Items
 Compare databases
   Build update script
 Deploy new or incremental update
   Visual Studio
   MSBuild action
Build/Deploy

 Standard VS build task
 Configurations
    New vs. Update 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
    RTM: SQLCMD command support
Changing the database




DEMO
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
What you need for Testing?


 Updated schema



             Test drivers (unit tests)



                                 Tons of data (realistic)
Data Generation
Design Time

 Data generator component
   Strategy for generation
 Distribution for generator
   Range of values
   Relation between values
 Settings for generator & distribution
   Number of rows
   Row-count ratios between tables
Data Generation
Design Time – Default Behavior
 Per column generator
   Matching data type
   Aware of CHECK constraints
 Special attributes
   Foreign Keys  Foreign Key generator
   Uniqueness  PK, UC, indexes
 Default distribution
   Uniform distribution when not unique
Data Generation – Value Generators

 Simple generators for each data type
     Strings (char, varchar, nvarchar…)
     Numbers (smallint, int, bigint, float…)
     Binary (varbinary, image…)
     Date and Time
     UUID and Bit
 Complex generators
     Foreign Key
     Regular Expression
     Data Bound
Data Generation
Distributions @ Work
                                                       Data Generation Distributions

                              12000



                              10000
   Generate Value (0-10000)




                               8000
                                                                                          Uniform
                                                                                          Normal
                               6000                                                       InverseNormal
                                                                                          Exponential
                                                                                          InverseExponential
                               4000



                               2000



                                  0
                                      1   501   1001 1501 2001 2501 3001 3501 4001 4501
                                                             # of Rows
Data Generation
Design Time


 Understand domain constraints
   Check constraints (min/max)
 Table cardinality
   Enforce table ratios
 Column value distribution
Generate Test-Data




DEMO
Database Unit Testing
Design Time
 Automatically generate unit tests:
   Stored Procedures, Functions, Triggers
 Test Validation (assertions)
   T-SQL  RAISERROR
   Client Assertions
      None Empty ResultSet
      Row Count
      Execution Time, …
 Pre & Post Test Scripts
Database Unit Testing
Test Execution
 Automatic Deployment Integration
   Automatically deploy database project prior to
   running tests
 Data Generation Integration
   Automatically generate data based on
   generation plan prior to running tests
 Execution & Validation connections
   Validation connection can be higher privileged
   account
Create a Unit Test




DEMO
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
Database Schema Refactoring
What is refactoring?


 “A database refactoring is
 a small change to your
 database schema which
 improves its design
 without changing its
 semantics.”
    Agile Database
    Development, Scott Ambler
Database Schema Refactoring
Rename Refactoring…

 Rename any SQL 2000/2005 schema object
 Updates all references in…
   Schema Objects
   Data Generation Plans
   Scripts
   Database Unit Tests
 Preview changes prior to commit
 Global undo to reverse all changes
Database Schema Refactoring
Refactoring Safety Net

 Unit Testing
    Generate tests after refactoring
 Version Control
    Store all previous versions before refactoring
 Schema Compare
    Analyze the exact differences between the
    project and live database to understand the
    impact of the update
Refactoring an Object




DEMO
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
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“}
Building with MSBuild




DEMO
Provisioning Multiple Servers

 Deploy to multiple targets?
 Database Project
   Single target server/database, only
 Use MSBuild tasks to provision
   Command line or tool calling MSBuild
   for each server+database combination in list
   {
     SqlBuildTask
     SqlDeployTask
   }
Data Generation
Customization & Extensibility
 Customization of value generation
    RegEx Generator
    Data Bound Generator
 Extensibility
    Custom Generator
    Custom Distribution
Data Generator Extensibility

 Generators
    Implement:
       IDesigner
       IGenerator
    Base class
       Generator
    Attributes
       GeneratorAttribute
       GeneratorNameAttribute
 Distributions
    Implement:
       IDistribution
 Registration
Data Generator – Registration
    Generators and Distributions have to:
       %ProgramFiles%\Microsoft Visual Studio 8\DBPro\Extensions
       %ProgramFiles%\Microsoft Visual Studio
       8\DBPro\Microsoft.VisualStudio.TeamSystem.Data.Extensions.xml
       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>
Custom Data Generator




DEMO
Database Unit Testing
Customization & Extensibility
 Database Unit Test designer
    Generates C# or VB.NET code
 Can customize generated code for:
    Custom test validation logic
    Parameterized test support
    Managing transactions
    Additional test setup and teardown of tests
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary
Summary

 Team Edition for Database Professionals
   Database development life-cycle
 Basic functionality
   Reverse-Engineer database schema
   Source Control for schema
   Refactor database schema
   Generate test data, create unit tests
   Compare schemas
 Extensible infrastructure
Pricing, Licensing and Availability

 Included in Team Suite at No Extra Cost
 Purchase as an individual Edition
   Same pricing as other Team System Editions
 Availability
   CTP 4 Available Today
   RTM By the end of 2006
Resources…
 CTP 5 Download Site
   http://www.microsoft.com/downloads/details.aspx?FamilyID=40
   14554e-903a-4a62-b429-2b027321c32d&DisplayLang=en
 Team Website
   http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/
   default.aspx
 Product Forum
   http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=
   725&SiteID=1
 PowerToys and Samples
   http://gotdotnet.com/Workspaces/Workspace.aspx?id=378460fd
   -1254-427b-aa7d-e777a826a564
Agenda

 Team System Review
 VSTS for Database Professionals
   Project System
   Change Management
   Database Unit Testing
   Database Refactoring
 Advanced Topics, Extensibility
 Summary