How can I maintain consistent DB schema accross 18 databases (sql server)?

Question

We have 18 databases that should have identical schemas, but don't. In certain scenarios, a table was added to one, but not the rest. Or, certain stored procedures were required in a handful of databases, but not the others. Or, our DBA forgot to run a script to add views on all of the databases.

What is the best way to keep database schemas in sync?

Answer 1

For legacy fixes/cleanup, there are tools, like SQLCompare, that can generate scripts to sync databases.

For .NET shops running SQL Server, there is also the Visual Studio Database Edition, which can create change scripts for schema changes that can be checked into source control, and automatically built using your CI/build process.

Answer 2

SQL Compare by Red Gate is a great tool for this.