Database Schema Validation - deployable/compilable

Question

Looking for a tool that will allow building database schema validation that can be compiled into a standalong tool or as a module to be used in another application.

The scenario is - a client/server application that is sold to customers, and maintained through service releases. The service releases include database scripts to updated the database with any schema changes etc.

However, since this is a database and the customer could either

• run upgrade scripts incorrectly, or
• tinker around and mess up something in the DB

I would like to have this tool available with each release so we can verify the struture, and possibly some data, is valid. Also to have it available for remote debugging when a customer has a service ticket.

Schema compare tools I've researched work great for comparing local databases, but I haven't seen something that can generate something that is deployable with our application.

Thanks!

Answer 1

Take a look at SchemaCrawler, which is ideally suited to this purpose. SchemaCrawler can generate human-readable database schema (and data) "snapshots", which are designed to be compared using plain-text file comparison tools. SchemaCrawler is database and operating system independent, free, open-source, and can be invoked from the command-line, as well as through a Java API.

Answer 2

You say that schema compare tools don't meet your requirements, but have you considered using a schema compare tool SDK and write your own validation code? I product manage Red Gate's SQL Compare and its API, the SQL Comparison SDK, will, for example, allow you to compare a schema snapshot against a target database and abort the deployment if it doesn't match, as well as saving a log of the differences, which could be emailed back to you.

http://www.red-gate.com/products/SQL_Comparison_SDK/index.htm