Having a group of developers share a single database is a tough thing to deal with. Changes that one developer makes to the schema or to a stored procedure can break the application for the other developers. So it's generally a good idea to have each developer maintain a local copy of the database on their machine that they can change without fear of breaking other people's code. But how do you propagate changes to the schema to all of the developer machines? Ideally, you should be able to store the database's structure in source control which would allow you to version each of the objects and also easily disseminate changes to other developers. It's an issue that several tools try to deal with, but none of them gave me a Goldilocks-style "just right" solution, so I decided to roll my own.

First, the aforementioned commercial tools: there are a bunch of them, but I focused on DB Ghost, SQL Professional Toolbelt, and Apex SQL. They're all great tools that get almost all of the job done, but they all managed to fall just short. First, my requirements were that the tool had to be able to take a database and generate a full set of scripts for its schema (including tables, stored procedures, users, roles, indexes, etc.) and also be able to take a set of existing scripts and synchronize a database to make the schema of the target database consistent with the scripts. DB Ghost does a decent job of synchronizing an existing database (although it sometimes has trouble with columns whose ordinals are not equivalent), but has an annoying habit of including extra whitespace in output scripts that it kicks out when generating files for a database, making it nearly impossible to determine what actually changed once those scripts have been generated. Red Gate's tools do a great job synchronizing between two databases, but they don't allow you to generate separate script files for each object in the database, meaning that your entire database schema is stored in one file. This kind of defeats the purpose of versioning each database object. ApexSQL did a decent job synchronizing between databases but, when scripting an existing database to files, put all of the files in a single directory (instead of separate directories per object type) and fell victim to the same extra whitespace issues that DB Ghost had.

However, the same company (Red Gate) that published the SQL Professional Toolbelt also publishes something called SQL Toolkit. It's a collection of class libraries that drive database schema and database synchronization, allowing you to build your own applications and fit them to your business processes. This is exactly what I did, and I was amazed at the speed and robustness of the libraries. You'll have to purchase your own copy of SQL Toolkit in order to compile and use this application, but Red Gate offers a free two week trial for evaluation purposes.

First off, the Database Synchronizer has three modes of operation: it can generate scripts for an existing database, synchronize between two pre-existing databases, and take a set of scripts and update the schema of a target database to make it equivalent to that of the scripts. This mode of operation is controlled by the project file that is passed into the application via the /project:projectFile command line parameter. I've attached XSD schema files for each project type to help you when writing them: DatabaseToDatabase.xsd, DatabaseToFiles.xsd, and FilesToDatabase.xsd.
  1. Database to files. This will take an existing database and generate schema scripts for each indicated object as well as data-insertion scripts for each indicated static table. The connection information for the source database is provided under the /SynchronizerProject/Source node and the target directory path that the output scripts will be stored in is provided in the /SynchronizerProject/Target/Directory node. This target directory path can be relative to the location of the project file. For each object type in the database, you can elect to have all objects of that type scripted out or limit scripting to selected objects. For instance, if you want to script out all stored procedures but only two select tables, you would have the following node structure under /SynchronizerProject: <StoredProcedures All="true"/><Tables><Table>[dbo].[Table1]</Table><Table>[dbo].[Table2]</Table></Tables>. Finally, to flag tables as "static" (that is, to have the application generate data-insertion scripts for them), include a node for each of them under /SynchronizerProject/StaticTables. For instance, to flag the previous two tables as static you would include the following node structure under /SynchronizerProject: <StaticTables><StaticTable>[dbo].[Table1]</StaticTable><StaticTable>[dbo].[Table2]</StaticTable></StaticTables>.
  2. Database to database. This will take a source database and update the schema and data of the target database to make the two equivalent. The connection information for the source database is provided under the /SynchronizerProject/Source node and the connection information for the target database is provided under /SynchronizerProject/Target node. For each object type in the source database, you can elect to have all objects of that type synchronized in the target database or limit it to selected objects. For instance, if you want to synchronize all stored procedures but only two select tables, you would have the following node structure under /SynchronizerProject: <StoredProcedures All="true"/><Tables><Table>[dbo].[Table1]</Table><Table>[dbo].[Table2]</Table></Tables>. Finally, to flag tables as "static" (that is, to have the application synchronize the data in those tables in the target database), include a node for each of them under /SynchronizerProject/StaticTables. For instance, to flag the previous two tables as static you would include the following node structure under /SynchronizerProject: <StaticTables><StaticTable>[dbo].[Table1]</StaticTable><StaticTable>[dbo].[Table2]</StaticTable></StaticTables>.
  3. Files to database. This is the simplest of the two: it will build a temporary comparison database using the scripts defined in the source directory (/SynchronizerProject/Source/Directory) and will them synchronize the target database (connection information in /SynchronizerProject/Target) to make its schema and data equivalent. It provides extra functionality in the form of manual scripts: that is, database upgrades or operations that need to be performed but can not be captured in the standard schema and data upgrades performed by the application. To use this, simply add scripts to the "Manual Scripts" folder in the source directory. Scripts get run at various stages in the synchronization process: scripts in "Manual Scripts\Source\[Environment]" get run against the comparison database after it finishes building, scripts in "Manual Scripts\Target\Before Synchronization\[Environment]" get run against the target database before upgrading it, and scripts in "Manual Scripts\Target\After Synchronization\[Environment]" get run after upgrading it. There is also an "All Environments" folder under each manual scripts directory ("Source", "Before Synchronization", and "After Synchronization") that contains scripts that are to be run in all environments.

There is also an overview of the command line options here.

I hope you find this application useful, and be sure to contact me if you have any problems or suggestions.

Last edited Jul 18, 2007 at 7:19 PM by lstratman, version 10