Andrew Zanevsky and Katrin Zanevsky
This article provides instructions and a software tool to assist DBAs in comparing database schema.
HAVE you ever compared databases that you knew were identical-same tables, stored procedures, and so on-only to find out that the indexes on your test machine weren’t the same as those on the production system or that a trigger was missing? Or perhaps the only inconsistency was a seemingly innocuous difference in the order of table columns, which precluded you from using bcp to transfer data between them.
Many databases in real production systems have clones on the same or other servers. Databases may be partitioned by customer regions (or some other criterion for performance reasons); historical data may be separated into one or more archive databases, or you may have implemented a “warm” backup server. In addition to your production databases, you may have “sister” databases on a software certification server, a reporting server, a dbcc server, or any of a number of development systems, and your specific system architecture may include servers with other roles. The result may be that you have data inconsistencies you don’t even know about. Some differences may be intended by design, but some have probably emerged as a result of historical changes, compromises, and simple mistakes.
To avoid unintended data inconsistency, you need a tool to compare databases and find any object that has a different definition. This month’s Developer’s Disk contains a new DBA utility called dbCompare. Andrew wrote the original specification, but the full credit for clarifying and implementing it belongs to Katrin.
Let’s describe several common scenarios that lead to unanticipated inconsistencies:
Assume that you have development and production servers with the same set of databases. Originally, you created all tables, views, stored procedures, triggers, and other objects from the same DDL scripts. But then developers demanded more privileges in their environment so they could test different solutions. After some struggle, you caved in and granted them full authority to create and drop tables, indexes, and stored procedures on the development server. After a few weeks, however, you loose track of what objects should be kept on the development server. You find indexes that don’t exist in production or, worse yet, have the same name but different key columns. As a result, procedures show different performance in development and production environment on the same set of data.
Have you ever found out that a trigger is magically missing on one of your production servers? How could that happen? It could be a result of some “one-time-deal” procedure in combination with a human error. For example, imagine that a heavy update query was written to correct many customer records. You decided to drop all triggers and indexes on the table and re-create them afterwards. This job was scheduled to run at night, but it didn’t completely restore all triggers once the update was done.
Are you a member of a team of DBAs who share responsibility for supporting multiple servers and databases? The situation tends to get chaotic as people leave and join the team, alter databases and objects as part of applications enhancements and fixes, or simply in the course of troubleshooting a particular problem. It may be hard to track every change on every server.
Sound familiar?
Comparing databases is like matching two similar pictures in a child’s activity book; however, the activity books usually tell you up front how many differences you should find. With databases, you can never be sure you’ve found all the discrepancies.
We’ve classified typical database inconsistencies into categories and dbCompare 1.10 has been “trained” to find all of them:
Objects from one database that don’t exist in another.
Tables that have specific triggers in one database and not in another. This type differs from the previous one because you could have triggers with the same name in two databases but on different tables.
Tables that have triggers of the same type (INSERT/UPDATE/DELETE), but with different names.
Tables with a different number of columns, column names, sequence order, ordatatype, length, precision, and scale.
Stored procedures with a different number of parameters, parameter names, sequence order, or datatype, length, precision, and scale.
Indexes found in one and not in another database.
Indexes with different index keys or order of key columns.
Indexes with different types.
Indexes with different indid values in sysindexes table. This is important if you use Optimizer hints enforcing specific indexes and refer to them by indid value.
Objects with different source code. This comparison is done by matching source code stored in syscomments table. It’s possible, of course, that you have functionally identical stored procedures that differ only by virtue of an extra comment line or different indent spacing. dbCompare will report these as having different source code, despite their functional identity. Nor does dbCompare report specific source code differences line by line. Another limitation is that encrypted objects can’t be compared. Finding inconsistencies in source code may be expensive (in terms of time and tempdb database space used by dbCompare) and sometimes unnecessary. We’ve included an option allowing you to skip it.
dbCompare 1.10 is a Windows NT command line utility. It has been written for Microsoft SQL Server 6.0 and 6.5 and won’t function on prior versions unless you make certain code changes described in the README.TXT file. It compares the schema of two databases at a time on the same or different servers. You need connectivity to both servers, plus availability of both isql and bcp per your PATH. Compared databases are not affected physically since all the matching work is done in temporary tables. See the README.TXT file for tempdb and file system space requirements. Here’s the syntax:
dbcompar db1 db2 srvr1 srvr2 pswd1 pswd2 [-n] [-s] [FILE]
In the previous syntax, db1 and db2 are names of compared databases, srvr1 and srvr2 are names of respective SQL Servers (may be the same), and pswd1 and pswd2 are sa passwords of compared servers. Two special characters may be used instead of these parameters:
- (dash) indicates that the corresponding sa password is null,
? (question mark) causes dbCompare to prompt for the sa password every time it needs to connect to the corresponding server (about a dozen times per execution).
-n suppresses opening a report file in Notepad. By default the generated report is brought up in Notepad.
-s suppresses objects source code comparison (syscomments table). You may want to use this option if full comparison takes too long, and you know that objects should be the same, or you aren’t interested in it for some other reason.
FILE specifies output filename. The default is dbcompar.rpt.
The first six parameters are mandatory, but the last three are optional-you may use them in any combination or not at all.
The database comparison report is stored as dbcompar.rpt and may be copied into another file. The default file dbcompar.rpt is kept in the same directory and overwritten every time dbCompare is executed. If you want to preserve the output, be sure to use the FILE parameter.
It matters which server/database you use first on the command line because most of the work is done in the second one. To minimize the impact of dbCompare on one of the two compared servers, specify it first.
If you’re lucky, you won’t find any discrepancies between your databases. But if you do, we hope you’ll be able to weed those out before they cause any serious problems. s
Andrew and Katrin Zanevsky are partners not only in life, but also in their consulting firm AZ Databases Inc. Andrew, an independent consultant, has worked as a DBA and as an application developer for several Fortune 500 companies using both Microsoft and Sybase versions of SQL Server. Katrin, a SQL Server consultant, has extensive experience as a programmer-analyst in corporate information systems. 708-609-8783, fax 847-419-0190. 71232.3446@compuserve.com.
To find out more about SQL
Server Professional and Pinnacle Publishing,
visit their website at http://www.pinpub.com/sqlpro/
Note: This is not a
Microsoft Corporation website.
Microsoft is not responsible for its content.
This article is reproduced from the October 1996 issue of SQL Server Professional. Copyright 1996, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call (800)788-1900 or (206)251-1900.