Av rating:
Total votes: 5
Total comments: 0

 send to a friend
 printer friendly version

If you develop SQL databases, you know how difficult it can be to promote from the development environment to production with any level of confidence that the databases are the same.

When you have upgrades, it’s difficult to identify changes as well, especially when there are multiple developers making changes to multiple databases. The problem is compounded if your target environment is a client’s system over which you have no control. It’s staggering to think about what can happen if there are 600-plus client systems that have various versions of your database.

Red Gate’s SQL Bundle Developer Edition incorporates SQL Compare and SQL Data Compare, tools that enable you to compare the schema and data in two databases and produce a script that synchronizes them. SQL Packager, another tool in the bundle, packages the schema and data and creates a .NET executable that can be distributed to a client system, for example, to install a new database or update an existing one.

While the tools work well using the standard user interface, their real power lies in the ability to access their APIs and command-line utilities to develop your own customized tool. This article provides an overview of how our customized tool works.

Our company, a leading manufacturer of metal building systems, needed a fully automated way to build deployment and upgrade packages for multiple SQL databases that would reside in a custom instance of MSDE on the computers used by our 500-plus external dealers and 150 internal users. The deadline was one month.

As you can see in Figures 1 through 5, the user interface we developed is as plain as they come. Using Red Gate’s APIs, the .NET Framework and the C# command-line compiler, we created a tool we call SQLDeployer. The tool enables us to specify, at a high-level, the details needed to build a package that installs or upgrades each of the five databases required for our application.

We started out "pinning a version" to take a snapshot of the database schema and contents using SQL Packager. We assigned a version number to the snapshot and updated its version stamp in a version table in each database, as shown in Figure 1. We then set such options as user database access, object-level permissions, and whether to exclude an object or exclude the data in a table.

The security on the destination server was controlled by the profile we created in SQLDeployer. The source server and the database’s security were ignored, as seen in Figure 2.

After the versions were established and configured, we specified either installer or upgrade packages. Installer packages only run if the database does not exist, or they may be set to drop an existing database before installing. An upgrade package is run only if the correct version stamp is found. That is, each upgrade package knows only how to upgrade from one version to another. Multiple upgrade packages can be created to upgrade from various versions.

Building an install package is as simple as recreating the snapshot as a temporary database, using the SQL Compare and SQL Data Compare engines to build the scripts, and then packaging those scripts using SQL Packager.

Building an upgrade package is slightly more complicated:

1. Install two different snapshots as temporary databases.
2. Use SQL Compare to build the script for the schema changes.
3. Apply that script to the earlier version to upgrade its schema.
4. Use SQL Data Compare to build the script for the data changes from the two databases whose schema now matches.
5. Use SQL Packager to build the package.

After SQL Packager performed its magic, we added additional resource files so the installer would know how to handle the package and then compile the package solution with the C# command-line compiler. We use the C# command-line compiler, csc.exe, because Visual Studio does not enable you to upgrade a solution and project file through automation. We didn’t use the managed C# compiler because we couldn’t determine how to specify multiple resource files.

We customized SQL Packager’s C# template so that it produces a DLL that has no user interface and has several additional checks for things that could cause errors in the execution of the package.

To create a single progress bar for all of the packages that needed to execute while installing the packages on the end-user’s system, we built a separate executable that reads from its .config file and loads the DLLs one at a time and executes them through Reflection. Once the packages were executed, we invoked an additional DLL that applies the security profile specified through our tool. This included dropping and recreating users, setting user database access, and applying object-level permissions.

Red Gate’s tools made it possible to build deployment and upgrade packages within our tight schedule. The availability of the APIs enabled us to customize the tools to our process, rather than relying on someone else to determine our requirements.

No off-the-shelf product would have enabled us to automate the process to such a degree. Now, when it’s time to build a new release of our databases, instead of hunting down myriad SQL scripts from a half-dozen developers, we pin the versions of our databases, set security on new objects, select the versions from which we want to upgrade, and click Build.

In a future article, I will explain how our tool was developed.

Figure 1: The SQLDeployerVersion table that resides in each controlled database.

Figure 2: The SQLDeployer user interface. Every database has a number of versions, each with its own group of settings. Each database also has a number of packages that define source and target versions.

Figure 3: The dataset schema that is used to save database profiles, stored as XML.

Figure 4: Each snapshot, installer and upgrade is saved by SQL Packager as a C# solution that is further customized and compiled using csc.exe.

Figure 5: These files make up our complete package of databases. BertDBPackages.exe reads its .config file to find the DLLs it must load and execute.