Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Red Gate SQL Bundle

Last updated Mar 28, 2003.

Red Gate has been making SQL Server utilities for years, most notably a tool called "SQL Compare." Recently, the company has begun to offer this tool combined with a few others, in a set of products called "The SQL Bundle," designed to make it simpler to compare databases and the data they contain.

If you've ever worked in a software shop, the beauty of these tools is obvious. If not, allow me to introduce you to the reason this suite of tools is so useful.

I used to work for a firm that sells an Internet-Tier application using SQL Server as a back-end. The software has four layers. Presentation is provided via a browser, a web server (IIS) serves as the true "client", COM+ sits in the middle tier, and SQL Server holds the data. It's a common infrastructure in modern software design.

There are a lot of advantages to this layout. For one, there's no client footprint; everyone already has a browser. That means no client software distribution.

Another advantage is that the servers are scalable. You can put all three functions on a single box, or you can break any or all of the three out onto separate servers. As a matter of fact, each server can be federated or clustered to scale out as well as up. This means that one design can have multiple configurations.

Of course, with these advantages come a few disadvantages. The most notable difficulty is support. The problem arises from the astonishing variety of possible server arrangements and configurations. When the time comes to upgrade the software, you have to take into account that there may be one server or many, and multiple servers might be federated or clustered. You have to plan for this variety when you create any upgrade scripts.

These problems pale, however, in comparison to upgrading a database. Here's why.

Most vendor databases include three types of data: data that controls the program in some way (called "metadata"), data that the users save (called user data), and data that combine a bit of both (such as personal settings).

When you upgrade the database to a new software release, you have to consider all three types of data, whether you're applying changes to the data or the structure of the database objects.

It might seem that the metadata would be the easiest to change. The problem lies with constraints. You may have data changes that are almost impossible to code, since there are so many dependencies.

User data, of course, needs to be retained. Here, the issue becomes making structural changes to the tables. Because of the dependencies and order of operations required, altering columns, changing primary or foreign keys, even changing indexes becomes problematic.

The most difficult data to deal with is the mixed area. Your challenge here includes all of the previous types, and is compounded by the need to change your own data, but not harm the user's data.

You have three basic choices when you want to control the version of the systems you deal with.

The first choice is to export all data, delete the current database, create the new database with the proper structure but without constraints, import the data, reapply the constraints and then check for errors. The danger with this approach (trust me, I speak whereof I know!) is that the upgrade can fail, and you won't always be able to find out why.

Another pitfall with this approach is the level of space you need. You'll have to back up the database first, keep the old one to transfer the data, and then fill the new one with the data. If the database is large, say a hundred gigabytes or so, that really starts to add up.

The final failing with this method is the time factor. When you back up a hundred gigabytes, transfer a hundred gigabytes, and then delete a hundred gigabytes, you're talking several hours. Heaven forbid the upgrade fail and you have to put it all back.

The second approach you can take is to script the changes and apply only those to the target system. While this sounds like the sanest method, the logic is very difficult to follow due to, once again, the dependencies. The real flaw with this method is that you don't always know where the database started. Often, the users stopped upgrading the software at update 3.2 and you're up to 4.5. Sometimes the users alter your database objects directly. Without a known state, what changes do you apply?

The third method is by far the easiest, most bullet-proof way to upgrade a system. You use a tool like Red Gate's SQL Server Bundle. This suite of programs examines the source and target databases and creates the proper scripts to bring them into alignment.

What Red Gate's Tool Provides

Red Gate has long made a tool for tracking structure changes in a database, and now they've included other tools in SQL Server Bundle. It's made up of the following tools:

SQL Compare compares and synchronizes the structure of Microsoft SQL Server databases. SQL Data Compare compares and synchronizes the contents of Microsoft SQL Server databases. DTS Compare compares DTS packages, jobs, logins, and other server settings of Microsoft SQL Server instances. SQL Packager packages scripts for database creation or updates into executable files or C# projects.

Each of the programs costs around $200. If you buy the bundle, though, you get them all for around $350.

In each product, the basic process is the same. You connect to two databases. The package examines one and compares it to the other. All of the packages have various options to include or exclude various objects, and you can execute or save those scripts.

The SQL Packager product goes a bit further. It's designed for creating executable programs to install or upgrade your database. A very useful feature is the ability to generate the C# code that will perform the install or upgrade. I used to code all our installations, and this would have saved me a few 2:00am coding frenzies.

The programs are very easy and intuitive to operate. The options are simple and clear, and you shouldn't even need the help files. New features from the last version include warnings and information tabs. These tabs are very helpful when the script just won't completely automate the deltas.

The program does a really great job of scripting some fairly complicated database objects. In previous versions, the software would tie up a machine for a few hours when comparing our next release, but in this version the routines run a bit faster.

I wasn't able to trip the program up in any of the upgrade scenarios I tested, although the changes in the databases I manage now are not quite as complex as the systems I previously dealt with.

The software is well worth the price. Before I had Red Gate SQL Compare, I wrote a Visual Basic program using DMO to create a script of all objects in the source system. I then scripted the target database and wrote them out to disk. I then had to use a comparison algorithm and try to figure out the logic to get the order right.

The flaw in doing something like this "by hand" like I did is that comments, order of operations and so forth will cause a database object to appear different, even when it isn't. Red Gate simplifies and automates the process, and if you have to keep more than one database in synch you should definitely check it out.

You can download all their products for a 15-day trial at http://www.red-gate.com.