DAT303 Managing Databases with Visual Studio

Greg Low

• Wed 9/16 | 15:45-17:00 | New Zealand Room 4
• Developers have had a rich set of options for managing their projects, source code and dealing with change for a long time. Visual Studio Team Edition for Database Professionals (aka DataDude) now lets database developers streamline their development experience too. In this session, Greg will show how it can be used to manage database schema changes, compare schemas and data, assist with making schema changes and analyse T-SQL code for quality. He will also show how unit testing can be applied to database code and data can be automatically generated for testing.
• Principal Mentor and MD, SolidQ Australia Pty Ltd
• Greg Low is an internationally recognized consultant, developer and trainer. Greg is the country lead for Solid Quality, a SQL Server MVP and a Microsoft Regional Director for Australia. Greg also hosts the popular SQL Down Under podcast (www.sqldownunder.com), organizes the SQL Down Under Code Camp and co-organizes CodeCampOz. He is a board member of PASS (the Professional Association for SQL Server). He regularly speaks at SQL Server and Microsoft events around the world and is one of a handful of people to have achieved the new Masters level certification on SQL Server 2008.

Agenda

• Manage change through Database Projects
• Testing and Data generation
• Building and Deployment

Visual Studio Team Edition for Database Professionals

• Now supplied with Developer Edition
• aka “DataDude’
• Released as 2005 edition
• 2008 edition basically a service pack
• GDR (general distribution release) provided 2008 support
• GDR2 release recently

VSTE-DBPro: Project Management

• Model-based development
• Team collaboration
• TFS
• Workitems and tasks

VSTE-DBPro: Change Management

• SCCI source code management integration
• Refactoring
• Comparison tools
• Schema
• Data

VSTE-DBPro: Testing

• Database Unit testing
• MSTest integration
• Automated data generation system
• Extensible data generator
• Seed-based generators

Build/Deploy

• MSBuild integration
• Command line tools
• Allows for multiple inconsistent schema

Project System

• Offline development
• Reverse-engineer existing often easiest
• Consists of series of .sql files
• Projects can be included in other solutions
• Project relates to a database

Managed Change

• Changes are offline within project
• Can compare project to database

Refactoring

• Allows for cascading changes within DB
• Also updates dependent project objects
• Schema objects
• Data generation plans
• Unit tests
• SQL scripts
• Allows for atomic changes
• Understands intent

GDR (RC and R2) Updates

• Require VS2008SP1
• Supports SQL Server 2008
• RC must be uninstalled before R2
• New architecture (not just an update)
• Schema provider model (allows extensibility)
• Build and Deploy separation
• Integration of power tool features
• Static code analysis
• Dependency viewer

TFS Integration

• Team Foundation Server for source code control
• Team Projects

Unit Testing

• Still uncommon in database development
• Helps avoid painful rollbacks

Data Generation

• Testing with meaningful data
• Data generation plan
• Customised data generators
• Implement Generator

Session Summary

• Outstanding new capability
• GDR R2 re;ease much more flexible
• Now extensible provider model
• Should be more extensible in VS23010
• Get control of your database projects

Online Resources

• Gert Draper’s blog
• Etc