Take control of "building" your database!

Published 30 April 07 10:35 AM | dmckinstry

Part I – Introduction to Microsoft Visual Studio 2005 Team Edition for Database Professionals

Microsoft Visual Studio 2005 Team Edition for Database Professionals adds a great deal of functionality for getting your database development in line with your standard application development. It provides the tools enabling you to gracefully manage changes and quality for your Microsoft SQL Server database development. Among the great features is the ability to actually perform automated database builds.

This blog is the first of a small series that discuss using MSBuild and Team Build from Team Foundation Server to automate the database build and deployment. First, I’ll set the stage for how you might use Team Edition for Database Professionals in a development environment. In subsequent blog entries I’ll discuss a few different aspects of how you can customize Team Build to get your desired results. Note that the MSDN documentation provides a good starting point but I intend to take things a bit deeper.

Evolving Team Development

If you think back at web development 5-10 years ago it was a common practice to have a shared development web server and have all web developers modifying ASP (or your favorite legacy web technology) directly on the server. At that time it made sense; web servers weren’t readily available on the desktop.

In theory you would want the changes from one developer usable to other web developers. Unfortunately, that is where things started to break down. If I broke a critical include file in a shared environment, all developers could easily be shut down until the problem was fixed. These days we get around this by giving web developers their own instance of a web server on their own workstation. Now if I break a critical web file, I have only caused myself pain, not the entire team.

Using a version control system such as TFS or VSS, developers share their code with each other after it has become stable. And as a member of a team, I can choose to pull down your changes to my workstation when I’m ready for it and not simply as a side-effect of when you changed it on a shared server.

The ability to “build” and “test” applications based on what is checked into version control has also evolved. These days it is common for development teams to check in their shared code and have an automated build /deploy process to make the changes available on a shared server for integration testing. In fact, in many cases the integration testing is automated as well!

What about Databases?

This evolving team development model isn’t followed everywhere but the idea of letting developers work in isolation and then share changes through a version control system has certainly become popular in most shops. But the same hasn’t been true for database development. Depending upon your shop, any of the following are likely to be true regarding your database development:

  1. Development on a shared database server: If someone breaks a critical database object, the entire development team is shut down!
  2. Manual movement of database changes: Even if developers are working on their own local database instances, moving changes between the local server and the integration server is a manual process which may be error prone. Even worse is trying to get updates from other team members onto the local database servers.
  3. Promotion of database changes between environments: As a corollary to the previous, when database changes are ready to be moved into the test environment or into production, there is a manually intensive process for promoting changes between environments. Frequently the person in charge of the database promotion will manually move objects from database to database.
  4. Lack of an audit history: In less formal shops, promotions are made directly from database to database. That means you always have a single point in time for each environment. There is no real tracking of what has changed.
  5. History of changes, not definition: In more mature shops, teams are already taking the time to create change scripts and check them into version control. This allows users to improve the process of how and when things change in the different environments. Changes to the different environments are facilitated by the scripts checked into version control. This makes it possible to review the scripts that were run from deployment to deployment and makes the process more reproducible. Unfortunately, these change scripts can be painful to create. Perhaps more important, they provide a list of changes per release which makes it almost impossible to track across releases. That is, you have to manually reconstruct what has changed from version 1 to version 3 whereas in standard version control practices you can directly compare the definition of the object as it existed in version 1 against its definition at version 3.

So in most companies these days, we are as bad at database development (if not worse) as we were 5-10 years ago in web development.

The Punchline? Team Edition for Database Professionals!

Of course the reason I started this rant is that I intend to explain how Visual Studio 2005 Team Edition for Database Professionals can bring the database components of your development into this century. Here is the quick bullet list of features that help address issues of the previous section. This edition of Visual Studio:

  • Captures the database object definitions and not just the change scripts.
  • Supports version control integration, allowing the comparison and promotion of individual changes over time.
  • Provides comparison tools to easily script and move differences in objects between database and/or database projects. This makes it relatively easy for developers to develop in a database instance but still keep their version controlled projects in sync.
  • Support easy deployment to a target database. For example, I can pull down the current definition of the database from version control and ‘deploy’ it to my local development database instance.
  • Data generation and unit testing support at the data tier! This allows easy regression testing of database changes by developers before checking their database changes in and accidentally causing other developers grief.
  • Support for MSBuild, enabling automated build, testing and deployment for SQL Server databases!

Now the last bullet is the reason for this series of posts, but the prior bullets describe some of the tools that we will be using in the build process.

Hopefully this provides a context for understanding why Team Edition for Database Professionals is an important and can have a profound impact on how we develop database application on top of Microsoft SQL Server. In subsequent posts I’ll provide details on how to use the capabilities in this product to enable automated builds, including deployment of database updates, complete with unit testing across all tiers of the application!

Comments

No Comments

This Blog

Microsoft VSTS Blogs

MSDN Forums

VSTS Community Blogs

Syndication