Synchronize Metadata Across SQL Server Database Copies

After the composite table contains the metadata for all the databases, you can run DatabaseSynchronizationReport.sql, which you'll find in the 101901.zip file, to create the report. DatabaseSynchronizationReport.sql is written to compare five databases. To compare more than five databases, you need to make three modifications.

For example, suppose you want to compare six databases. To do so, you first need to change the Summary column code in Listing 5. Specifically, you need to change the code in callout A to

'.'+RTRIM(ISNULL(DB5.DM_DBID,'-'))+
'.'+RTRIM(ISNULL(DB6.DM_DBID,'-'))+
AS Tracking changes when you have several copies of a database can be a huge chore. These step-by-step instructions will let you produce a report that will find differences for you.

Next, you need to add an additional LEFT OUTER JOIN statement, which Listing 6 shows. You'd put this statement after the last LEFT OUTER JOIN statement in DatabaseSynchronizationReport.sql.

Finally, you need to change the GROUP BY code in Listing 7. Specifically, you need to change the code in callout A to

'.'+RTRIM(ISNULL(DB5.DM_DBID,'-'))+
'.'+RTRIM(ISNULL(DB6.DM_DBID,'-'))

The database synchronization report presents the contents of all the queried databases so that the information can be viewed together. The data for each object is grouped and sorted by object and column name. Figure 1 shows a sample report for three queried databases.

Figure 1: Annotated Sample Database Synchronization Report. Click to expand.

The report's Summary field shows whether there's a discrepancy across the databases. Ideally, the Summary field of each column record should list the database identifiers for all the queried databases (e.g., "1.2.3.4.5" for five queried databases, "1.2.3.-.-" for three queried databases). This makes missing columns easy to spot. For example, in the sample report in Figure 1, we highlighted a missing-column discrepancy in yellow.

Attribute discrepancies appear as multiple entries for a column. In Figure 1, we highlighted this type of discrepancy in purple. The information in the Data-Type, Max-Length, Key-Pos, and Null fields can help identify what attribute differs between the databases.

Note that the report includes an object-separator row at the start of each new object. These rows split the results into groupings, making it easier to find and differentiate between objects.

The database synchronization solution is highly adaptable. For example, although we designed MetadataExtractionQuery.sql and DatabaseSynchronizationReport.sql to work with metadata in SQL Server 2008, 2005, and 2000 databases, you can adapt these scripts to work with similar data in other database systems, such as Oracle or DB2.

Similarly, we designed the solution to work in an environment in which multiple copies of a database reside on servers that can't directly access each other. However, this solution will work in all types of environments. If there's network access between servers or if multiple database copies (with different names or in different instances) reside on the same server, you can streamline the process. For example, you might be able to load the metadata in the ZDB_META tables directly into the DB_META table. Your environment will determine the best extraction, transport, and load methods to use.

You can even adapt the database synchronization report to better meet your needs. For example, you can remove the header row or the object-separator rows. Or if all you want to see is the Summary field for each column record, you can add a WHERE clause to the report query to select only those columns. If there are five queried databases, you'd add the clause

WHERE RTRIM(ISNULL(DB1.DM_DBID,'-'))+
 '.'+RTRIM(ISNULL(DB2.DM_DBID,'-'))+
 '.'+RTRIM(ISNULL(DB3.DM_DBID,'-'))+
 '.'+RTRIM(ISNULL(DB4.DM_DBID,'-'))+
 '.'+RTRIM(ISNULL(DB5.DM_DBID,'-'))
 <> '1.2.3.4.5'

Discrepancies between copies of a database might not seem like a significant problem, but the impact of a missing database change can be quite serious. A major database-definition error can cause a new release to be backed out of production. Even correcting a minor database-definition error during a project's implementation can take several weeks. These delays can hurt your schedules and your project's credibility.

Manually rechecking the column definitions in large numbers of tables, views, and indexes is an extremely tedious and error-prone task. To save time and avoid errors, try the database synchronization solution. Using the information that the database synchronization report provides, you'll be able to quickly find and fix all discrepancies so that your database copies are perfectly synchronized.