SQL Server 2008 - Database Merger and Aliasing?

Question

Suppose I have two SQL Server 2008 databases, A and B. They initially were created with the intention to be separate, but over time have grown to both have constant references (in sprocs, views, etc) to each other. It's gotten to the point that they're effectively just two halves of the same database.

So, we're considering merging them. Does anyone know how we could best perform this merger? We have quite a lot of internal applications that reference one or the other, including many that are customer facing so minimizing downtime would be very important. In order to not have to find and update all of the things hitting these databases, we're especially interested in some sort of database 'aliasing', where an application could be calling a sproc in database 'A', and 'A' redirects that to the new merged database 'C', somehow. Does anyone have any experience with or insight into this type of situation?

Answer 1

Since you're using SQL 2008 you could look at using SYNONYMs, which I believe can go across databases (and even servers if needed). I don't have much experience with them, so I'm afraid I can't give any advice or "gotchas" to consider.

Your ultimate goal should be to eventually change the accessing code to point to the single database and then getting rid of the other two "dummy" DBs. Having this kind of redirection for the long term eventually causes problems in my experience.

Answer 2

Here's an outline of how I'd approach this. This is a totally non-trivial modification, so your mileage will vary depending upon your actual setup.

(1) Construct the new composite database. I'd take one of the existing two, and add the other's code into it, rather than create a new (third) database.

(1a) You'd (presumably) need a routine to build the new database from scratch.

(1b) You'd (definitely) need a routine to upgrade an existing pair of databases into the single-db form.

(2) Construct a revised "second" database. Everything in this database is a placeholder, referencing the correlated objects in the revised "first" database. As Tom H. recommends, sysnonyms should work well for this (they're available in SQL 2005 as well). Views would work as well. Stored procedures should just be wrappers that call their analogs in the (new) first database.

(3) Test, test test.

(4) Go back and do step 3 again.

(5) Apply your changes all at once to existing systems (this is why we have step 4), one environment at a time. Done properly, and depending a lot on your system, you might not even have to adjust how your databases are accessed by users or applications.

(6) As time and resources allow, revised all outside users and applications to reference the new/single database, ultimately deprecating all usage of or reference to the second database.