Updating client SQL Server database structure from text file

Question

We have a "master database structure", and need a routine to keep the database structure on client sites up-to-date.

A number of suggestions have been given to a related question, but I am looking for a more specific solution, along these lines:

1. I would like to generate a text file (XML or other readable format) which describes the entire database structure (this could go into version control). This routine will run in-house, to provide a database schema file to be distributed with the next version of our product.
2. Then I need a way to update the database structure on the client site so that it corresponds to the master database structure. (In other words, I don't want to have to keep track of numerous change scripts for different versions of the database structure, but a more general routine which can get the client database structure updated to the current master database structure.)

So the main feature I'm looking for could be described as "database structure to text" and "text to database structure".

Answer 1

There are a whole lot of diff tools that can give you schema and stored procedures and constraint differences between two databases. You could roll your own, but I think it would be more expensive than one of these tools if you have a complex schema, many give a free trial so you can test.

The problem is you'd have to have the master database online to do so though and accessible from the client database installation (or install it there) which might or might not be feasible.

If you won't do that, the only other sane option I can think of is to use the migration idea, keep a list of SQL scripts + version pairs, plus current version on each database. This could be consolidated by a different tool that could generate a single script from a the client's database version number and the list of changes. And if you haven't the list of changes, you can start with a diff tool run, and keep track of them from there.

The comparing text route (comparing text SQL dumps of both schemas) you seem to prefer looks very hard to do it right and automatically to me, doesn't look like the right path to take.

Answer 2

Several popular strategies are variants of this:

• Add a table to the database:
  CREATE TABLE Release
  (release_number int not null,
  applied datetime not null
  )
• Each release, as part of its release script inserts a row into this table.
• You can now find out with a single query which release each client is running, and run all the releases between that one and the release they want to be running.
• In addition, you could check that their schema is correct for each version (correct table names, columns, etc.) by doing something like this:
  SELECT so.name,
  sc.name
  FROM sysobjects so,
  syscolumns sc
  WHERE type = 'U'
  ORDER BY 1, 2
• then calculate a hash of the result and compare it with a pre-computed hash (generated by running the query on your reference installation) to see if the installation is now correct.