Redgate’s SQL Compare is an indispensable tool for anyone doing development against Microsoft SQL Server. It’s ability to export the database structure to a scripts folder and then synchronize changes from a database back to the scripts folder is a its greatest feature; It allows the database structure to be placed under source control with a high degree of granularity since each database object becomes a script file.
Our build process includes a step to deploy a test/demo version of our web to a server. As part of that process, we need to update the database structure from the scripts folder. Thanks to the SQL Compare API that was included with RedGate’s SQL Toolbelt, I was able to put together an NAnt task that does the job. Here is the actual Nant target from our build script:
<target name="updateDatabaseStructure">
<schemaSync sourceScriptsPath="${project.dir}\db\OnpointConnect" destinationServerName="${sqlServerInstance}" destinationDbName="OnpointConnect"/>
</target>
You’ll notice the target uses a couple of properties to point at the location of the project root (${project.dir}) and the SQL Server instance name (${sqlServerInstance}). Since the task does not specify a user name and password, the schemaSync task uses the Windows security credentials of the user running the build to logon to the database. In the case of our automated build, the user account that runs the build process has access to the database. You can change to a SQL Server login by providing sourceDbUserName and sourceDbPassword properties to schemaSync.
The complete list of supported attributes is as follows:
| Attribute | Description |
| sourceServerName | If synchronizing from a database, the name of the source database server. |
| sourceDbName | If synchronizing from a database, the name of the source database. |
| sourceDbUserName | If using SQL Server authentication, the name of the user in the source database. Omit this attribute to use Windows authentication. |
| sourceDbPassword | If using SQL Server authentication, the password of the user in the source database. Omit this attribute to use Windows authentication. |
| sourceScriptsPath | If synchronizing from a scripts folder, the path to the scripts folder. |
| destinationServerName | The name of the server to synchronize to. REQUIRED. |
| destinationDbname | The name of the database to synchronize to. REQUIRED. |
| destinationDbUserName | If using SQL Server authentication, the name of the user in the destination database. Omit this attribute to use Windows authentication. |
| destinationDbPassword | If using SQL Server authentication, the password of the user in the destination database. Omit this attribute to use Windows authentication. |
You can download the source code from my GitHub SouthSideDevToys repository.
RedGate’s licensing will require you to purchase the SQL Comparison SDK after a demo period. At this time, the product sells for $695 and you should be able to get a 15 day demo from RedGate.
The code is fairly self-explanatory. I wrapped an Nant task around a set of classes that works with the API. The Nant task assumes that the destination will always be a database even though the underlying code is capable of synchronizing to a scripts folder. If you have questions about the code, feel free to comment and I will respond with more information.
RedGate just released Data Compare 8.0 that allows data comparisons and synchronization with scripts. RedGate says that the next version of their API, due out at the end of August 2009 (UPDATE: Can be obtained by licensed customers through support), will include this capability as well. Our database includes configuration data that we currently rollout via SQL scripts. This process is a pain to maintain because each time the data changes we have to update our scripts and test them. As soon as RedGate’s new API becomes available I plan to change this process to use Data Compare via Nant so that everything is automatic. I’ll publish the Nant task as soon as I have it working.
Tom Cabanski 