How to validate the clients database against my database schema?

Question

Our clients use SQLServer/Oracle databases. Over the years, we've sent them many update scripts which they had to run manually. Most of the time, everything went smooth, but every now and then a script did not run completely to the end or had some errors in it (which weren't detected at the time of the upgrade). Also, sometimes even "smart users" added indexes/tables into those databases themselves, for whatever reason. Later on, those irregularities lead to problems.

Now I have been tasked to figure out a way to verify/validate our clients databases against our own database schema (tables, datatypes, indexes, views, ...). The output should be some kind of difference file indicating what is missing/what should not be in the database. I could do this in code (C++) from inside our application or I can create an external tool for just this one purpose.

Now before I start coding, I wanted to ask if there is already a tool out there that would produce the necessary results, or that at least could help me produce a decent xml file from our master-databases (Oracle and SQLServer)? Or is there a library which could help me write my own tool?

Answer 1

We use Redgate SQL Compare for this and it's served us well over the years.

We also use Redgate SQL Data Compare for comparing the content of lookup tables.

Answer 2

The folks at redgate have a great tool called SQL Compare.

Answer 3

Can you create a schema dump like MySQL's SHOW CREATE TABLES?

Answer 4

If you're on Windoze, I have used Advanced Query Tool for years, and can attest that, for the money, it does more than anything else. In particular, it will generate a diff report between databases. It is ODBC/VB6, and can run against dozens of databases. Check it out. (No, I am not of QueryTool nor do I own any part of it, just a happy client.)

Answer 5

I've used this technique before and it doesn't require buying any tools.

Enterprise Manager has a "Create Script" feature. Perform this on your reference database and the comparison database. Select the appropriate options to generate scripts for the objects you care about. Next, just compare the two generated files with your favorite diff tool.

You can do a similar procedure with Oracle tools that let you export the DDL scripts.

Answer 6

There are three options using Red Gate's tools:

1. Have your client run the comparison. You would need to convince your clients to purchase a license of SQL Compare and send them a schema snapshot of your database.
2. Write an application of your own using Red Gate's SQL Comparison SDK ($595 for 10 distributions) which can be run at the client site.
3. Ask your client to send you a schema snapshot and run the comparison yourself using your own copy of SQL Compare. Red Gate supplies a free schema snapshot tool called SQL Snapper that will create snapshots that can then be emailed to you by your client. As this doesn't include any data, it may be something your client is willing to consider.

The SQL Snapper tool and SQL Comparison SDK sample code can be downloaded from our labs.red-gate.com website.

Oracle compatibility is now available in the form of an Early Access Build. If you're interested or would like to try out the tool visit the product page. You can use this for free until the full release of the tool.

David Atkinson, Product Manager, Red Gate Software.