Here's the scenario.
Two identical databases:
One Live database, one Archive database, they're suppose have the exact same schema (table, view, indexes, SPs, functions), the only difference is the data in the databases. The data in Live DB will be archived with some business rules and apparently the data in Archive DB will be different from in Live DB.
The challenge is that we keep on patching changes (SP change, function change, data change, or even table schema change) to the Live DB in each release. Unfortunately, the changes required on Archive DB are forgotten for a long time and the issues have just not been addressed yet. It will happen one day that the out-of-sync DBs come back and bite us.
Here's what I want to do: I want to synchronize non-data related changes from Live DB to Archive DB. Either automated or manually.
Any idea is welcome. Here are some ideas that have come to my mind:
replication? I find replication does not fit this scenario quite well.
scripting the SP/function/view changes? I can manually pull out the scripts and combine them together. What about the table schema changes? It's difficult for me to track back to find out what's happened on table schema changes.
I know there's Redgate and other product can do the job but I'd like to explore the full potential.
If anybody can point out some feasible way that'd be great.
If you are using SQL Server, Visual Studio Team System Database Edition has a schema comparison and patching tool. Have a look at this article