Intro to Visual Studio Database Development Tools

Document Sample
Intro to Visual Studio Database Development Tools Powered By Docstoc
					 Using the Visual Studio Database
Development Tools in a Continuous
     Integration Environment

               Ken Powers
 Emerging Health, Montefiore Information
             March 27,   2012

 Introduction
 The Challenge of Making Database Development
   Agile Database Development
   The Challenge of Continuous Integration for
   Implementing a Database Change Management
   Database Testing
                    My Goal for Tonight

 Two things I want you to be able to do when you
 leave here tonight:
    Write a change script against the SQL Server
     “AdventureWorks" database
    Write a database unit test for your change script
 You can download the SQL Server Sample Databases
 from this link to CodePlex:

 Complex application
 Medical data
 Many parts
 Small team

     Wish I had a
     way to           Who crashed
     manage all       the server!?
     these scripts.

                                 Why did I want
                                 to be the DBA
In what         Which            again?
order do I      scripts do I
apply these     need?

 How do I
 verify these

 About Me:
   Worked with SQL Server since 2002
         7.0, 2000, 2005, 2008, 2008 R2
     Champion of agile database best practices
 Contact Info:
   Email:

 My Challenge:
   Our team adopted Scrum in 2009

   My task was to get the database development process under

 Solution
   Implemented the Visual Studio Database tools
       AKA “Database Professional”
 Why?
   Provides:
     Source Control
     Code Analysis
     Deployment
     Unit Testing

 Helps us leverage other technologies
   WiX
   Team Foundation Server

 Demo: Basics of Database Professional
Can Database Development Be Agile?
  Can Database Development Be Agile?

If you had     Can’t we all just   Maybe I
just ASKED     get along?          WOULD ask
us we would
                                   if it didn’t
have told
                                   take your
you we
                                   team 3
already have
                                   months to
a database
with that
      Can Database Development Be Agile?

 Agile Database
    Visionary: Scott Ambler
 Website
 Books
       Can Database Development Be Agile?

 In July 2006, Scott Ambler conducted a survey
   The Current State of Data Management

 Sample questions:
   Do application development teams ever choose to go around
    your data group and design the database themselves?
       66% said Yes
       34% said No

     Why do development teams go around your data group?
Can Database Development Be Agile?
         The Cultural Impedance Mismatch

 Much of the Cultural difference stems from the
 evolution of software development
    Relational Databases came into their own at a time when
     Client – Server was the dominant software development
      Data professionals developed the data schema
      Application developers wrote the application code
      The two groups worked relatively independently of each other
        The Cultural Impedance Mismatch

 As the years went by, things changed
   Object-Oriented Programming

   Agile Methodology

   Test-Driven Development

 Data Professionals were insulated from many of
 these changes
    Tensions developed as expectations changed
    Data teams didn’t see why they had to change their ways of
     doing things
      The Technical Impedance Mismatch

 Object-Oriented Technology
   Supports the building of applications out of objects that have
    both data and behavior
 Relational Database Technology
   Supports the storage of data in tables and the manipulation of
    data via Data Manipulation Language (DML) internally within
    a database
 In order to work together effectively, application
  developers and data professionals need to learn each
  other’s language
      The Technical Impedance Mismatch

Data Professionals need to   Application Developers
learn:                       need to learn:

 Reusability                 Concurrency
 Coupling                    Transactions
 Encapsulation               Referential Integrity
 Unified Modeling
                              Issues affecting
 Language (UML)
                               database performance
    Use Case Diagram
    Sequence Diagram         Data Modeling
    Class Diagram            Data Normalization
 Class Normalization
           Agile Database Development

 Philosophies
   Data

   Enterprise Issues

   Enterprise Groups

   Uniqueness

   Teamwork

   Sweet Spot
               Agile Database Development

 Continuous Integration (CI)
   Agile teams produce a clean build of the system several times
    per day
         The build is usually configured to include
           Automated compilation

           Unit test execution

           Source control integration

           Automated user acceptance testing (sometimes)

     CI presents a number of challenges for database development
              Agile Database Development

 Best Practices:
     Database Refactoring
     Agile Data Modeling
     Database Regression Testing
     Configuration Management for Database Objects
     Developer Sandboxes
     Data Normalization
     Realistic Primary Key Strategies
     Database Encapsulation
     Train Developers in basic data skills
     Train Data Professionals in basic development skills
     Implement Common Development Guidelines
     Lean Data Governance
 Best Practices for Agile Database Development

 Database refactoring
   A small improvement to existing database code
       No new functionality
       Everything works the same as it did before your change

     Includes structural aspects and functional aspects
     Can be difficult when many different applications are coupled
      to your database
     Adding new objects or new functionality are NOT examples of
  Best Practices for Agile Database Development

 Agile data modeling
   This can be the most difficult adjustment for data professionals

   In an Agile environment, data modeling is just one of many
         Sometimes, data-oriented artifacts won’t be the best alternative
     Model with others, rather than model in isolation
         Seek out your enterprise architects and enterprise administrators
 Best Practices for Agile Database Development

 Database Testing
   Test what’s inside your database so that you know:
       You have high-quality data
       You have good performance
       Your functional code works they you intend it to work

     Regression test
         As you make further changes, re-run your existing tests to make
          sure you haven’t broken anything during the process
  Best Practices for Agile Database Development

 Configuration management of database artifacts
   Your data models, database tests, test data, and so on are
    important project artifacts which should be configuration
    managed just like any other artifact
 Developer sandboxes
   The technical environment where your database code is
    developed and unit tested
   Allows you to work in a “de-coupled” environment
Best Practices for Agile Database Development
 Best Practices for Agile Database Development

 Data Normalization
   Always work through the process of data normalization
         Reduces or eliminates data redundancy
     Normalize first, test, then see if you need to de-normalize
         Don’t assume that you need to de-normalize for performance
 Realistic Primary Key Strategies
   Sometimes natural keys are best, sometime surrogate keys are
   Be prepared to refactor if you discover you made the wrong
  Best Practices for Agile Database Development

 Database Encapsulation
   Create a database encapsulation layer to hide the
    implementation details of your database(s) from your business
   Business objects should know nothing about how they are
    persisted, they just are
 Cross-training
   Train Data Professionals in the basic object-oriented skills
    mentioned earlier
   Train Application Developers in the basic relational data skills
    mentioned earlier
 Best Practices for Agile Database Development

 Common Development Guidelines
    Have a common, usable set of development standards which are easy
     to understand and easy to comply with
    Includes
      Programming guidelines
        object-oriented
        relational
      Modeling style guidelines
      Naming conventions
        object-oriented
        relational
      User Interface conventions
        Including report design
  Best Practices for Agile Database Development

 Lean Data Governance
   Take a narrow focus on data fixes that will produce the greatest
    return in the shortest amount of time
       Things that impact your bottom line
       Things that increase productivity
       Things that increase customer satisfaction

     Keep the data governance team small and evolve it as the
      needs of each project change
     Let the data governance board oversee the process without
      managing individual pieces of the project
             Challenges of CI for Database

 Data Protection
    The change process must not corrupt or destroy your data
 Simplicity
    Even the most junior team member should be able to create and
     submit data changes
 Performance
    The change process must not crash the system and must complete in
     a reasonable amount of time
 Notification and Verification
    The change process should keep you informed of its status and
     should be able to verify that the change was applied successfully
 Repeatability
    You must be able to execute the change process repeatedly in the
     same database on the same server
            Challenges of CI for Database

 Data Protection
   DB Pro will not tell you that your data changes succeeded or
    failed out of the box
   You’ll need a custom change script for data changes

   In order to design a change script, you’ll need to learn these:
      PRINT
      TRY .. CATCH
      ERROR_LINE()
            Challenges of CI for Database

 Simplicity
   If you can write the following SQL statements, you have all the
    skills you need to write a change script:
       SELECT
       INSERT
       UPDATE
       DELETE

     A Data Professional can always assist a team member when a
      more complex script is required
             Challenges of CI for Database

 Performance
   Acceptable performance varies in each environment

   The biggest performance hit is usually loading data after the
    database structure is deployed
   T-SQL script files are probably not the way to go here
       Files can quickly become unmanageable
       Loading from the SQL script is the slowest way to load

     We developed our own utility for loading data
       There are others available from CodePlex
       Integration Services may also be an option
           Challenges of CI for Database

 Notification and Verification
   The change script should provide constant feedback about its
   Use T-SQL PRINT statements to send messages to the Visual
    Studio Output window and to the TFS Build log
   The change process should also validate your data after the
    change is applied
               Challenges of CI for Database

 Repeatability
   Your change process must be able to determine if your change
    has already been applied
         If your change has already been applied, the process should skip
          the change
     Your change process should also be able to determine if only a
      part of your change has been applied
     Two more T-SQL statements you need to learn:
       IF EXISTS ()
       IF NOT EXISTS()
               Challenges of CI for Database

 Other Issues
   Relationships between tables
         If there is a foreign key relationship between the table you’re
          changing and another table, you have to account for that
           Parent table has to be dealt with first

     Automation
       You may not know ahead of time exactly what objects will be
        modified during your development cycle
       Need to be able to execute your change process in a manner that
        doesn’t require this knowledge
        Database Deployment Process

 Demo: Change Scripts
    Product Development’s CI Architecture

 The Application
   Six databases
       Many cross-database references between the databases
 The Team
   8 Developers

   3 QA Analysts

   2 Business Analysts

   1 DBA (me)

 Scrum Methodology
The Product Development Ecosystem
      The Product Development Ecosystem

 Workstations
    Every developer has a
     local instance of SQL
     Server and Visual Studio
     2010 Ultimate
    This is their “Sandbox”
      The Product Development Ecosystem

 Build Server
    The “Product Integration
    When you check-in your
     changes, the project builds
    When you want the latest
     code, you get it from here
    Hosts the Team Builds
     that are deployed to other
      The Product Development Ecosystem

 QA Server
    The database server for the
     QA implementation of our
    Has substantially more user
     data than our sandboxes and
     Build Server
    Used to run full-scale
     integration testing before
     releasing our code to the
     Product Owner
    We deploy to this server once
     a day, usually in the early
      The Product Development Ecosystem

 Staging Server
    The database server for
     the Staging
     implementation of our
    “Demo Sandbox”
    Used to demo the most
     up-to-date version of our
    We deploy to this server at
     the end of every sprint
                Database Deployment Process

1. Database Project built
2. Data is exported to a temporary database
     1.    Each project has its own temporary database
3.        Local database is backed up
4.        Local database is dropped
5.        Database Project deployed
6.        New local database is created
7.        Data in the temporary database is imported into the
          new local database
     1.    The temporary database is dropped if the import is successful
8.        Change Scripts executed
                 Database Testing

 The change process expects the database to be in a
  certain state
 How do we know the database is in that state?
                             Database Testing

 An important feature of DB Pro is the ability to run unit tests
  on database code
 Provides auto-generation of test scripts for specific types of
  database objects:
     Stored procedures,
     Functions
     Triggers
 Create tests when:
   You create a Change Script
   You create or modify a
         Stored procedure
         Function
         Trigger
     You discover data incorrect or missing data
Database Testing
                Database Testing

 Types of Database Tests
   Functional Testing

   Relationship Testing

   Data Quality Testing

   Performance Testing

   Structural Testing

   Regression Testing
                              Database Testing

 DB Pro provides several built-in test conditions
   Inconclusive
         Default condition. Remove it as soon as you define a test condition for the unit test
     Data Checksum
         The checksum of the result set must match the checksum of a predefined result set
     Empty ResultSet
         The result set must be empty (no rows returned)
     Execution Time
         The test script must complete in a specified period of time
     Expected Schema
         The columns and data types of the result set must match those specified for the test
     Not Empty ResultSet
         The result set must return at least one row of data
     Row Count
         The result set must return the expected number of rows
     Scalar Value
         A particular value in the result set must match the expected value
                 Database Testing

 Provides an API for creating custom test conditions
   Can also edit the call to the TestMethod()

 The most common test conditions we use are:
   Data Checksum

   Scalar Value

   Empty Result Set

 We favor tests that check for actual values, rather
 than tests that count the number of rows returned
                     Database Testing

 Data Generators
   Can create data that meets your conditions

   Example: You need patient data but HIPAA requirements
    don’t allow you to use real patient data
 Data generators create test data that has the same
  characteristics as the real data
     Can combine real data with data generated by the Data
 Allow you to preview the data that will be generated
                         Database Testing

 Other Notes
   Do not test for identity values
       They may be different on different machines
       If the result set ONLY has identity values, you need to be a little
         “What am I really checking for?”

     Always check to see if there is an existing unit test for the
      object to be tested
         You may need to modify that test or replace it with a new one
     Create one Test Project per Database Project in your solution
     Make sure you test every table affected by your change script
     Only one Test Condition per Unit Test
               Database Testing

 Demo: Database Unit Tests
                          TFS and DB Pro

 TFS 2010 Configuration
   Disable “Analyze Test Impact”
         Applies to the “Upgrade Template” and the “Default Template”
     MSBuild Build Definitions require at least the following two
      XML elements:
       <Target Name = “AfterEndToEndIteration”></Target>
       <Target Name = “StartDeployment”></Target>
     Remember that unit tests usually run during the “Build” phase
      of TFS Build
         You may want to test your database(s) after deployment
     Two important files:
       <SolutionName>.vsmdi
       Local.testsettings
                      Lessons Learned

 Database is different
   Creates unique issues for implementing CI

 Managing database change is a challenge
   Gets easier over time

 Uphold your standards, but be flexible and
     Get your team to want to work with you to improve the process
                    Helpful Resources

 Visual Studio 2010 Database Projects Guidance
 VSTS 2005 and 2008: Building Database Projects
  with Team Build
 Barclay Hill
                         Helpful Resources

 Agile Data Home Page
   Database Testing
     Best Practices
     Vision of the Agile Data Method

 Jamie Thomson
   Database Unit Testing as part of CI

Shared By: