Synchronize Metadata Across SQL Server Database Copies

DBAs often encounter change-tracking problems when they try to keep multiple copies of a database synchronized across various environments. In addition to the standard development, test, quality assurance, and production versions, there can be copies of the database for implementations by divisions, departments, and development teams. You need to be able to check the status of database definitions quickly and easily to see if they're synchronized to support application changes that depend on the database structure.

We've devised a solution that lets you see whether multiple copies of a database are synchronized, even when there's no direct access between the SQL Server systems containing those databases. The solution produces a database synchronization report that shows discrepancies between copies of a database. You can implement this solution whenever a database change is made or at regular intervals.

The database synchronization solution is relatively simple to implement because it uses metadata (i.e., data about the database objects) that's extracted by running T-SQL queries against databases. The metadata is extracted from system tables and system views in two or more databases and stored in flat files. The flat files are transported to a central location and loaded into a composite table that's queried to group and compare the metadata. A comparison is done on each column's metadata, which consists of the column's object, name, and attributes. The comparison verifies that the data type, length, nullability, and primary key position are the same for the column definitions in all the databases.

The results of the comparison are put in the database synchronization report, which shows the columns and their attributes for each table, view, and index in each database. With just a glance at the report's Summary field, you can find out whether there are discrepancies between the databases' contents.

1. Create the composite table in a central location.
2. Prepare the queries to extract the databases' metadata.
3. Run the queries and transport the results.
4. Load the results into the composite table.
5. Run the database synchronization report.

We'll walk you through each step, then show you what to look for in the report.

The process to obtain a database synchronization report starts with creating the composite table, which is named DB_META, in a central location. This table will hold the metadata extracted from each database. Listing 1 contains the code you can use to create it.

As mentioned previously, a T-SQL query is used to extract the metadata from each database. You can find this query in the MetadataExtractionQuery.sql file, which you can download by going to www.sqlmag.com, entering 101901 in the InstantDoc ID text box, and clicking the 101901.zip hotlink. This query extracts metadata from columns in a database's tables, views, and indexes. A UNION operation is used to combine these data sets, and the metadata is inserted into a table named ZDB_META.

Table 1 shows the 10 fields in the ZDB_META table. Note the first field named DB_Identifier. Each database you want to include in the database synchronization report must have a database identifier, which is a one-character value that uniquely identifies that database. You can use uppercase and lowercase letters and single digits, so you can include up to 62 databases in the report without using special characters. The one-character values allow the results for each column to be aligned in the report's Summary field, making it easy to compare those results.

Table 1: The 10 Fields in the ZDB_META Table. Click to expand.

You need a customized query file for each database you want to include in the database synchronization report. Creating a customized query file is simple. First, make a copy of MetadataExtractionQuery.sql and rename it so that the filename includes the database identifier. For example, if you intend to run the query file against the database that has the identifier of 1, you can name the file something like MetadataExtractionQuery1.sql.

After you've renamed the query file, you need to open and modify it. In two spots, you'll find the code

'database-number-assigned' AS [DB_Identifier],

In this code, replace database-number-assigned with the database identifier.

In two spots, you'll find the code

isc.table_catalog = 'database-name'

In this code, replace database-name with the database's name.

If you need to specify a specific schema to identify the database columns, you must make an additional modification. You need to uncomment the line

-- AND isc.table_schema = 'schema-name'

and replace schema-name with your schema's name. MetadataExtractionQuery.sql's comments indicate where to make the four required and one optional modifications.