Agile Database Techniques using Visual Studio Team System 2005 (3 days)

Agile Database Techniques using Visual Studio Team System 2005 Overview

This Visual Studio Team System training class provides students with the knowledge and skills to properly manage the SQL Server database development lifecycle in an agile environment. The student will learn how to manage changes to the database structure, ensuring quality through T-SQL unit testing, and how to automate the building and deploying of SQL Server databases. Intended for developers and administrators already comfortable with the concepts of designing, programming, testing, and deploying SQL Server databases, this course focuses on using Visual Studio Team System 2005 Edition for Database Professionals to maximize productivity while minimizing mistakes.

This VSTS course is intended for SQL Server database developers and administrators. Application developers who are involved with writing and testing T-SQL code and working with test data, will also gain value from this course.

Agile Database Techniques using Visual Studio Team System 2005 Course Goals

• Understand the capabilities of the VSTS Database Professional edition
• Understand the areas of integration with Team Foundation Server
• Understand VSTS' support for the Database Development Life Cycle
• Work offline so changes don't impact actual servers
• Create and configure database projects
• Import existing schemas and scripts
• Place database projects under Team Foundation Version Control
• Compare schemas from two SQL Server instances for differences
• Compare data from two SQL Server instances for differences
• Generate scripts to reconcile differences in schemas and data
• Understand and create effective T-SQL unit tests
• Understand and use database refactoring correctly
• Automatically generate test data in various scenarios
• Integrate test data generation with T-SQL unit testing
• Automate the building and deploying of database changes
• Use MSBuild and Team Build to manage build and deployment
• Extend VSTS by building your own test data generators
• Extend VSTS by building your own unit test assertions

Agile Database Techniques using Visual Studio Team System 2005 Course Prerequisites

Experience in the following areas is required:

• SQL Server development and administration

Experience in the following areas would be beneficial.

• Team-based software development project work
• Familiar with your organization's Software Development Life Cycle

Agile Database Techniques using Visual Studio Team System 2005 Course Outline

1. Module 1: Agile Database Development
   • This module reminds us of the reality of developing and maintaining SQL Server databases in an agile environment. Topics include listing the related support from the Visual Studio Team System Edition for Database Professionals and some common scenarios.
   1. Agile database development
   2. Agile database techniques
   3. Visual Studio Team System Edition for Database Professionals
   4. Scenarios
   5. Resources
   6. Lab: Review the team, project, and scenario for all the labs
2. Module 2: Database Projects
   • This module introduces database projects and how they support an offline, disconnected mode of development and testing, which is important in an agile environment. Topics include a discussion of the integration with Visual Studio 2005 and support for importing schema and scripts into these database projects.
   1. Database projects
   2. Creating a database project
   3. Visual Studio integration
   4. Importing schemas
   5. Importing scripts
   6. Lab: Explorer Visual Studio integration
   7. Lab: Create and configure a database project
   8. Lab: Import schema from an existing database
   9. Lab: Import script from a file
   10. Lab: Review the design-time validation database (optional)
3. Module 3: Change Management
   • This module discusses how database projects integrate with Team Foundation Server's change management features. Topics include an introduction to Team Foundation Server, working with Team Projects, version control, work item integration, as well as the intrinsic schema and data change management tools in the Database Professionals edition.
   1. Team Foundation Server
   2. Working with team projects
   3. Methodologies and work items
   4. Team Foundation Version Control
   5. Using Team Foundation Version Control
   6. Placing database projects under version control
   7. Comparing two database schemas for differences
   8. Comparing data from two databases for differences
   9. Generating change scripts to merge differences
   10. Lab: Create a team project and work items
   11. Lab: Configure version control
   12. Lab: Place a database project under version control
   13. Lab: Explore Visual Studio 2005 version control integration
   14. Lab: Compare schemas from two SQL Server instances for differences
   15. Lab: Compare data from two SQL Server tables for differences
   16. Lab: Generate scripts to reconcile differences in schemas and data
   17. Lab: Explore the MSSCCI provider from SQL Server Management Studio (optional)
   18. Lab: Explore the updated MSF guidance for database professionals (optional)
4. Module 4: Unit Testing
   • This module introduces the need for unit testing and related support within the Team Edition for Database Professionals. Topics include test projects, writing good unit tests, and the integration of T-SQL unit tests with your existing .NET unit tests.
   1. Unit testing
   2. Test Driven Development (TDD)
   3. Creating database unit tests
   4. SQL assertions and test conditions
   5. Executing unit tests and evaluating the results
   6. Lab: Create and execute database unit tests
   7. Lab: Use pre-test and post-test actions
   8. Lab: Use an ordered list
   9. Lab: Export and import test results
   10. Lab: Create unit tests automatically from Schema View
5. Module 5: Refactoring
   • This module introduces the concept of refactoring and its support in the Team Edition for Database Professionals.
   1. Refactoring
   2. Refactoring support in Visual Studio 2005
   3. Code refactoring
   4. Database refactoring
   5. Rename refactoring
   6. Regression testing
   7. Lab: Rename schema objects
   8. Lab: Preview changes
   9. Lab: Undo refactoring using Global Undo
   10. Lab: Review the refactoring log
6. Module 6: Data Generation
   • This module discusses the problems surrounding test data, from security risks to the loss of functionality when working with meaningless data. Topics include the data generation architecture and how data generation plans can be used to generate meaningful, deterministic data for any number of reasons.
   1. The problem with data
   2. Data generation plans
   3. Data generators
   4. Regular expression generator
   5. Data bound generator
   6. Integration with unit testing
   7. Lab: Review the existing schema and database unit tests
   8. Lab: Create a data generation plan
   9. Lab: Use a regular express generator
   10. Lab: Use a data bound generator
   11. Lab: Use related tabled and ratios
   12. Lab: Generate data automatically prior to running unit tests
7. Module 7: Build and Deploy
   • This module wraps up the life cycle with the discussion of building and deploying any schema changes to a target SQL Server instance. Topics include the build and deployment architecture, Team Build and MSBuild integration, deployment options and scripts, and how to automate the entire process.
   1. The need for automated build and deployment
   2. Building
   3. Deploying
   4. Integration with MSBuild
   5. Integration with Team Build
   6. Lab: Build the database project
   7. Lab: Deploy the database schema changes
   8. Lab: Use custom deployment scripts
   9. Lab: Use MSBuild directly
   10. Lab: Automate using Team Build
8. Module 8: Extensibility
   • This module describes the various extensibility points found within the Team Edition for Database Professionals, including data generators and distributors, unit test assertions and conditions, and custom MSBuild tasks.
   1. Customization vs. extensibility
   2. Areas of extensibility
   3. Visual Studio templates
   4. Custom test conditions
   5. Custom data generators
   6. Custom check-in policies
   7. Lab: Edit database object templates
   8. Lab: Create a custom data generator
   9. Lab: Create a custom unit test assertion
   10. Lab: Create a custom check-in policy (optional)

Agile Database Techniques using Visual Studio Team System 2005 Course Materials

In addition to a comprehensive set of materials, including course notes and all the programming examples, each student will also receive a one-year subscription to Webucator's online reference library, which contains hundreds of the most current electronic technology books - a $149.95 per student value.

Agile Database Techniques using Visual Studio Team System 2005 Technical Requirements

1. Operating System Options
   • Windows Vista
   • Windows XP with Service Pack 2
2. Visual Studio 2008 Team System
3. Browser Options
   • Internet Explorer 6.0 or later
     • Download
   • Firefox
     • Download

Class Files for Agile Database Techniques using Visual Studio Team System 2005

A DVD with a virtual machine and lab files will be sent ahead of class.