SQL Server 2008 - 数据库合并和别名？
假设我有两个 SQL Server 2008 数据库 A 和 B。它们最初是为了分开而创建的，但随着时间的推移，它们都不断相互引用（在存储过程、视图等中）。已经到了这样的地步：它们实际上只是同一个数据库的两半。
所以，我们正在考虑合并它们。有谁知道我们如何才能最好地进行这次合并？我们有相当多的内部应用程序引用其中之一，其中包括许多面向客户的应用程序，因此最大限度地减少停机时间非常重要。为了不必查找和更新访问这些数据库的所有内容，我们对某种数据库“别名”特别感兴趣，其中应用程序可以调用数据库“A”中的存储过程，并且“A”重定向以某种方式到新的合并数据库“C”。有没有人对这种情况有任何经验或见解？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
以下是我将如何处理这个问题的概述。这是一个完全不平凡的修改，因此您的里程将根据您的实际设置而有所不同。
(1)构建新的复合数据库。我会采用现有的两个数据库之一，并将另一个的代码添加到其中，而不是创建一个新的（第三个）数据库。
(1a) 您（可能）需要一个例程来从头开始构建新数据库。
(1b) 您（肯定）需要一个例程来将现有的一对数据库升级为单数据库形式。
(2)构建修订后的“第二”数据库。该数据库中的所有内容都是占位符，引用修订后的“第一”数据库中的相关对象。正如 Tom H. 所建议的，sysnonyms 应该可以很好地解决这个问题（它们在 SQL 2005 中也可用）。视图也可以。存储过程应该只是调用（新的）第一个数据库中的类似物的包装器。
(3) 试验，试验试验。
(4) 返回并再次执行步骤3。
(5) 将您的更改一次全部应用到现有系统（这就是我们进行步骤 4 的原因），一次一个环境。如果做得正确，并且很大程度上取决于您的系统，您甚至可能不需要调整用户或应用程序访问数据库的方式。
(6) 在时间和资源允许的情况下，修改所有外部用户和应用程序以引用新的/单个数据库，最终弃用对第二个数据库的所有使用或引用。
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.
由于您使用的是 SQL 2008，您可以考虑使用同义词，我相信它可以跨数据库（如果需要，甚至可以跨服务器）。我对他们没有太多经验，所以恐怕我无法提供任何建议或“陷阱”来考虑。
您的最终目标应该是最终更改访问代码以指向单个数据库，然后摆脱其他两个“虚拟”数据库。根据我的经验，长期进行这种重定向最终会导致问题。
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.