Synching SQL Server Databases

Synchronizing developer databases and synchronizing production with development databases are recurring topics at Stackoverflow:

• keeping sql database schemas synced between developers

small dev team, and “The biggest issue that we are having now is that our DB schema is totally out of sync between all of us”

• Syncing development/production databases

“My main concern is deploying schema + data updates to developer machines from the “master” development copy.”

Databases under version control is a more difficult problem than version control for regular source code, as you not only need

• the SQL source of the data model, but also
• a change script to upgrade from one version to the next,
• and populate application tables with pre-defined values (constants).

I think that dbscript and SMOscript are two useful tools which can help you to address these issues (which is why I wrote them and use them).

SMOscript lets you script all database objects that are scriptable through SMO. You can script the objects to a single file, or as single files in subdirectories named after their type (Table, View, StoredProcedure, etc)

dbscript helps you with the other two items:

Import the current database schemas of two SQL Server databases, and have dbscript compare them. You can then select which changes (on objects and column level) to include in the change script, and have dbscript generate the migration script.

Instead of importing from a database, you can also upload a SQL file containing the schema definition.

Based on the schema data, you can then define all your system tables (those tables which hold pre-defined “magic” values), and let dbscript generate a stored procedure which will script the data as Insert/Update statements, or have the live data scripted directly by dbscript.

To keep your code in sync with database values, you can also create C# constants from your database values.

Last not least, let’s deal with the question of how to keep several programmers in sync.

dbscript keeps all data (projects, schemas, versions, objects) in a database, and lets multiple users work in the same project. Project Versions can be tagged as “development”, “test”, “beta”, “minor”, “major” and other stages. If versions are tagged, they are visible to every member of the project; if they are not tagged, they are only visible to the user who created them.

A strategy to handle multiple developers in dbscript could be implemented as follows:

• Schemas/Versions of the master development database are tagged
• Developer generates migration script to sync his development database with the latest master database version
• Developer applies his changes locally
• Developer uploads or imports private database schema without tagging the created version
• If the master schema has changed since the developer retrieved it, apply change scripts from previous to current master development database schema.
• Developer compares private database schema with latest master schema
• Developer modifies value generation scripts
• Developer generates migration script, and applies the script to the master database
• Developer imports master schema and tags the version

dbscript is available for download here.