Optimizing Database Design using VSTS DB Pro for Developers and DBAs

1
Optimizing Database Design using VSTS DB Pro for
Developers and DBAs

• Sajee Mathew
• Developer Tools Specialist
• SajeeM_at_microsoft.com
• Microsoft Corporation

2
Visual Studio Team System
Visual Studio Team Suite
SoftwareArchitects
SoftwareDevelopers
DatabaseProfessionals
SoftwareTesters
VisualStudioIndustryPartners
VisualStudioTeamExplorer
Application Modeling
Code Analysis
Database Deployment
Performance Testing
Infrastructure and Deployment Modeling
Performance Tuning
DatabaseChange Mgmt.
Manual Testing
Database Testing
Test Case Management
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
3
Database Change Management Conceptual Overview

• Schema Change now managed in VSTS and TFS
• Production Database is now One version of the
  truth only for Data
• One Version of the truth for Schema is Under
  Source Control

Tuning Monitoring
One Version of the Truth for Data

• Changes can be rolled out in a scheduled, managed
  way
• Scripts allow administrators to mange change
  updates

One Version of the Truth for Schema

• Offline
• Under Source Control

Schema Changes
4
Database Change Management

• Change Management
• Import database schema and place it under source
  control.
• -TFS, VSS, other
• Work with schema offline
• Changes are versioned
• Quickly build update scripts or packages
• Provides a mechanism to deploy updates to the
  database of your choice
• The DB developer can control version her code
  just like the application developer!

5
Establish the project environment
Check in to Source Control
DBDev
DBA
Database Project
Import schema
6
Isolated Iterative Development
DBDev
DBA
7
Build Cycle
Get Latest
Daily Build
Test
DBDev
DBA
8
Deploy the project environment
Sync from Label
DBDev
DBA
Deploy
Database Project
Build
Verify
Refine deploy script
9
Benefits of Approach

• Managed, project oriented evolution of database
  schema
• Application and database schema can now be
  managed together
• Work in isolation, deploying only when changes
  verified through empirical means
• Leverage VSTS work item tracking and process
  guidance increases team collaboration and unity

10
DB Import, Project and version control
11
Test Data Generation

• Use production data
• Privacy, regulatory issues
• Come up with test data from scratch
• Tedious
• Team System can generate realistic test data
• Efficiently generate test data from scratch that
  is representative of your production environment.

12
Test Data Generators

• Highly configurable value generators.
• Each table column can be assigned a value
  generator. We have value generators for each
  column data type. Each generator has a set of
  properties which let you fully control the
  parameters of the generated values.
• Additional powerful generators
• regular expression generator, generates values
  based on the specified regular expression (think
  phone numbers)
• data bound generator, pulls values for data
  generation from a known data source.

13
Test Data Generators

• Smart default assignment of generators.
• We automatically assign by default the
  appropriate value generator to each column based
  on the columns data type.
• Recognition of database constraints.
• We understand some of your database constraints
  and automatically configure the assigned
  generator to adhere to these constraints.

14
Test Data Generators

• Support for data distributions
• Apply a distribution on a value generator to
  generate values according to the distribution
  (i.e normal or exponential curve)
• Generator distribution extensibility
• extend the library of generators and
  distributions
• Data generation is repeatable
• The values generated are based on a seed value,
  ensuring that the data generated is repeatable.
  Ideal for use with unit testing.

15
Test Data Generators

• Enforcement of table ratios
• We provide the ability for you to model certain
  relationships between your tables and enforce
  specified ratios between them.
• Given an Auctions and Bids table, setup a 125
  ratio between the two tables such that for every
  auction generated, 25 associated bids will be
  generated.

16
Data Generation
17
DB Unit Testing

• Structured and automated way for testing
  individual components of a system
• A battery of tests that can be run during
  development to ensure features work as expected.
• With good unit test in place, a developer can
  optimize, refactor or otherwise change code and
  be confident that any defects introduced by her
  changes are caught by unit tests.

18
Database unit tests types

• Feature Tests
• test the core features, or APIs, of your database
• Schema Tests
• ensure that a view returns the expected set of
  columns, of the appropriate data type, in the
  appropriate order.
• Verify that your database does contain the 1000
  tables that it is expected to.

19
Database unit tests types

• Security Tests
• Ensure that particular users exist in your
  database and various users are assigned the
  appropriate permissions.
• Create negative tests that attempt to retrieve
  data from restricted tables or views and ensure
  that the access is appropriately denied.
• Stock Data Tests
• Test reference data, i.e a particular town
  returns the correct zip code.

20
Unit Testing
21
Database Refactoring

• Def a small change to your code which improves
  its design without changing its semantics
• Cascading schema changes
• Rename Refactoring
• allows you to easily rename any object in your
  database and be assured that all references to
  that object will be renamed to correspond to the
  change

22
Database Comparisons

• Schema Compare
• Compare the schema of
• Any two SQL Server databases
• A source controlled DB project and another
  database
• script updates to bring the database schemas into
  sync
• Data Compare
• Compare data from two databases
• Script updates to bring the data in these
  databases into sync the database

23
Refactoring Comparisons
24
(No Transcript)
25
Roadmap
2006
2007
Future
26
Visual Studio Team SystemCode-named Orcas

• Database Professionals integrated into the
  lifecycle
• New DB Pro guidance built into MSF

Team Collaboration

• Code metrics and profiling enhancements
• Improved web test and load modeling
• AJAX support for web tests

Testing and Application Quality

• Continuous integration and build
• Performance and version control improvements
• Better administration, operations and setup

Team Productivity
27
Call to action / Next steps

• Evaluate VSTS
• DL, Trial Editions, VPC
• Learn
• Lots of videos and articles on MSDN
• Test Driven Development
• Embrace a quality first mindset

28
Additional Information

• Microsoft in Financial Services
• http//www.microsoft.com/financialservices
• Technology Community for Financial Services
• http//www.financialdevelopers.com
• Platform Migration Alliance
• http//www.mainframemigration.org
• Financial Services companies using Microsoft
• http//www.microsoft.com/industry/financialservice
  s/businessvalue/casestudies.mspx
• Sign up to receive the free quarterly FS
  Developer Newsletter
• Go to Newsletter Signup on the top left hand side
  on http//www.financialdevelopers.com/