1 / 34

DAT320 Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals

DAT320 Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals . DAT320 Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals (Part 2) . Sachin Rekhi Program Manager Microsoft Corporation. Gert E.R. Drapers

liz
Download Presentation

DAT320 Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DAT320Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals

  2. DAT320Testing and Refactoring Your Database with Visual Studio Team Edition for Database Professionals (Part 2) Sachin Rekhi Program Manager Microsoft Corporation Gert E.R. Drapers Architect/Development Manager Microsoft Corporation

  3. Agenda • The Database Development Life Cycle • The Database Developer Story • Data Generation • Database Unit Testing • Schema Refactoring • The Power of Integration • Summary

  4. Other Sessions • Introducing Visual Studio Team Edition for Database Professionals • DEV217 - 6/13/2006 10:15AM - 11:30AM, room: 104 ABC • Managing and Deploying your SQL Server Schemas with Visual Studio Team Edition for Database Professionals • DAT312 - 6/13/2006 1:00PM - 2:15PM, room: 156 ABC • Testing & Refactoring your Database with Visual Studio Team Edition for Database Professionals • DAT320 - 6/14/2006 8:30AM - 9:45AM, room: 160 ABC • Under the Hood of Visual Studio Team Edition for Database Professionals • DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC

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

  6. Project ModelThe center of gravity • The database project represents the “truth” with regards to schema versioning • Optionally database project can be placed under source control • .SQL script files is the canonical format used • Changes are tracked at the “object level” • For example indexes, constraints, triggers are tracked independent of the base table definition, in order have the highest granularity of change tracking

  7. Database Development Life CycleThe cycle of life for database developers Import database schema SQLServerDatabase Database Project Template Database Project Create New Project SQL Script Reverse engineer existing .SQL script files (*) (*) Not implemented in the current CTP

  8. Database Development Life CycleThe cycle of life for database developers Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

  9. DeploySQLScript SQLServerDatabase Database Development Life CycleThe cycle of life for database developers Import database schema SQLServerDatabase Build project Database Project Template Database Project Create New Project Deploy project SQL Script Reverse engineer existing .SQL script files (*) (*) Not implemented in the current CTP

  10. Continuing the Journey… • In part 1 we established: • A Database Project representing our schema • Placed it under version control • Create a sandbox environment using Build & Deploy • Verified if the sandbox matches the project and the original database, using Schema Compare • Now we are ready to start making changes! • First we are going to create test data • Which will be used by the test bed we are creating • So we can validate our changes made using Schema Refactoring

  11. Data GenerationDesign Time • Setting up Data Generation implies defining: • Which generator to use • Which distribution to attach to the generator • Changing setting on the generator & distribution • The numbers of rows to generate • Optionally defining the rowcount ratios between tables • By default: • Each column is bound to the generator matching the column data type • FK columns are mapped to the Foreign Key generator • Uniqueness is inferred from PK, UC constraints and indexes • Using the Uniform distribution when not unique

  12. Data GenerationDesign Time • Value generators • Simple generators for each data type • Strings: ASCII and Unicode ((var)char, n(var)char, (n)text) • Numbers: tinyint, smallint, int, bigint, real, float, decimal, numeric, money • Binary ((var)binary, image) • Date and Time • UniqueIdentifier (GUID) • Bit • Complex generators • Foreign Key, Regular Expression, Data Bound • Distributions • Uniform, Normal, Inverse Normal, Exponential, Inverse Exponential • Can import column distributions from production database (*) (*) Not implemented in the current CTP

  13. Data GenerationDistributions @ Work

  14. Data GenerationDesign Time • Understand domain constraints • Check constraints (min/max) • Table cardinality • Enforce table ratios • Column value distribution

  15. Data GenerationExecuting a Data Generation Definition • Validation of • Security requirements • Fails when security requirements are not met! • Target schema against DGEN definitions • Fails the generation when bindings do not match! • Optionally purge tables • Required to guarantee repeatable data generation • Spin up parallel streams of INSERT statements • Based on relation ships between tables • Number of connections used is currently gated by the schema relationships. • Configurable Error Thresholds

  16. Data GenerationCustomization & Extensibility • Customization of value generation • RegEx Generator • Data Bound Generator • Extensibility • Custom Generator • Custom Distribution • Discussed in detail in: • Under the Hood of Visual Studio Team Edition for Database Professionals • DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC

  17. Data Generation Sachin RekhiProgram Manager Visual Studio Team System

  18. Database Unit TestingDesign Time • Automatically generate unit tests stubs for: • Stored Procedures, Functions (*), Triggers (*) • Test Validation (assertions) • T-SQL (server based) Assertions • RAISERROR command • Client Side Assertions • None Empty ResultSet • Row Count • Execution Time, … • Pre & Post Test Scripts (*) Not implemented in the current CTP

  19. 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 (*) Not implemented in the current CTP

  20. Database Unit TestingCustomization & 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 • Discussed in detail in: • Under the Hood of Visual Studio Team Edition for Database Professionals • DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC

  21. Database Unit Testing Sachin RekhiProgram Manager Visual Studio Team System

  22. Database Schema RefactoringWhat 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 • For example: • Rename a Schema Object Name for consistency, understandability, maintainability… • Objective: Rename ALL schema object references; direct and indirect inside all: • Tables, views, stored procedures, user defined functions, …

  23. Database Schema Refactoring Rename Refactoring… • Rename any SQL 2000 & SQL 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

  24. Database Schema Refactoring Refactoring Safety Net • Unit Testing • Develop a battery of tests to run after a refactoring to ensure database still functions as expected • Version Control • Store all previous versions so you can always go back to a prior baseline in source control • Schema Compare • Analyze the exact differences between the project and live database to understand the impact of the update

  25. Schema Refactoring Sachin RekhiProgram Manager Visual Studio Team System

  26. Integrating In To The Cycle of Life Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

  27. End-to-End Database Development Sachin RekhiProgram Manager Visual Studio Team System

  28. Summary • Handle Schema Change Management and Deployment • Mitigate the Risks Involved with making and deploying changes • Integrate the Database Professional in to the Development Life Cycle

  29. Resources • Break-out Sessions • Under the Hood of Visual Studio Team Edition for Database Professionals • DAT433 - 6/15/2006 1:00PM - 2:15PM, room: 160 ABC • Hands on Labs • DEV008 Take a Tour of Visual Studio 2005 Team System for Database Professionals • Chalk Talks • Visual Studio Team Edition for Database Professionals: Overview • DEV TLC Theatre 6/15/2006 9:45AM-11:00AM

  30. Resources… • CTP 3 Download Site • http://download.microsoft.com/download/1/a/3/1a32ea84-11a3-4adf-953e-7a65b9831f5a/VSDATAD1.img • 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 • Blogs • http://blogs.msdn.com/gertd

  31. Fill out a session evaluation on CommNet for a chance to Win an XBOX 360!

  32. © 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.

More Related