Microsoft Visual Studio Team Edition for Database Professionals VSTEDP

1
Microsoft Visual Studio Team Edition for Database
Professionals (VSTEDP)

• Justin King (JustinKing_at_ssw.com.au)

2
Agenda

• Overview History
• Database Development Life Cycle
• Creating a Project and Importing a Database
  Schema
• Defining New Database Objects
• Synchronizing Schemas
• Schema Comparison
• Data Comparison
• Generating Test Data
• Unit Testing Stored Procedures

3
1. Overview History
4
History

• www.Red-gate.com
• Schema Compare
• Data Compare
• Refactor
• www.apexsql.com
• xx

5
Overview
New!
Visual Studio Team Suite
SoftwareArchitects
SoftwareDevelopers
SoftwareTesters
DatabaseProfessionals
VisualStudioIndustryPartners
VisualStudioTeamExplorer
Application Modeling
Code Analysis
Performance Testing
Database Change Management
Infrastructure and Deployment Modeling
Performance Tuning
Manual Testing
Database Testing
Test Case Management
Database Deployment
Security Analysis
Unit Testing
Code Coverage
MSF Process and Guidance
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
6
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

7
2. Database Development Life Cycle
8
Database Development Life Cycle
Import database schema
SQLServerDatabase
Database Project Template
Database Project
Create New Project
SQL Script
Reverse engineer existing .SQL script files ()
9
Database Development Life Cycle
Edit
Refactor
Compare
Database Project
Build
Deploy
Data Generation
Test
Compare
10
Database Development Life Cycle
Import database schema
SQLServerDatabase
Build project
Database Project Template
Database Project
Create New Project
Deploy project
SQL Script
Reverse engineer existing .SQL script files ()
11
How we do it at SSW

• Setup the DEV database (Import the schema from a
  production database)
• Create a baseline (Setup a TEST database to match
  the imported database schema)
• Schema Comparison
• Data Comparison
• Modify the schema within the development
  environment (Add table, keys, stored procedures)
• (SSW) Save .sql files
• Refactor Database Objects
• (SSW) Save .sql files
• Perform a Schema Comparison between development
  and test databases
• (Not at SSW) Script the changes
• (SSW) Use SQL deploy to run the update scripts
  against the TEST database

12
Continues

• Perform another Schema Comparison between DEV and
  TEST databases to ensure there are no changes
• Unit Testing
• Data Generation Plans
• (SSW) Use SQL deploy to run the update scripts
  against the PROD database
• Perform a Schema Comparison between TEST and PROD
  databases

13
Creating a Project and Importing a Database Schema
14
Creating a Project and Importing a Database Schema
15
Creating a Project and Importing a Database Schema
16

• Defining New Database Objects

17
Defining New Database Objects
18
Synchronizing Schemas
19
Synchronizing Schemas
20
Synchronizing Schemas
21
Generating Test Data
22
Generating Test Data
Figure 15. The three main tables selected (with
related tables partially selected).
23
Generating Test Data
Figure 16. Setting the Generator column to Data
Bound Generator
24
Unit Testing Stored Procedures
25
Unit Testing Stored Procedures
Figure 19. Choosing Pre-test, Test, or Post-test
26
Summary

• Overview History
• Database Development Life Cycle
• Creating a Project and Importing a Database
  Schema
• Defining New Database Objects
• Synchronizing Schemas
• Schema Comparison
• Data Comparison
• Generating Test Data
• Unit Testing Stored Procedures

27
All done Questions?

• JustinKing_at_ssw.com.au

http//kingjustin.com