从两个不同的数据库插入和选择数据的最佳实践 [英] best practice for insert and select data from two different databases

问题描述

哪种方法是从SQL Server中两个不同服务器上的两个不同数据库插入和选择数据的最佳实践，我该如何处理假设server-1处于停机状态的情况?

Which one is the best practice for insert and select data from two different databases that are on two different servers in SQL Server and how can i handle the situation like suppose server-1 is in down time ?

推荐答案

好吧，我不确定是否有针对此问题的最佳实践，但我将告诉我如何做到这一点.我遇到的一种情况是，与应用程序相关的数据位于与具有地图和地理相关数据的数据库服务器不同的数据库服务器上(说到SQL Server 2008 R2).

我的应用使用了自己数据库中的纬度和经度，但是将它们发送到了映射数据库，以经纬度运算并返回经过地理编码的路由结果.为此，首先，我确保我的应用程序是一个n层应用程序，具有用于表示，业务，对象和数据访问的单独的层.然后，我在web.config中添加了两个指向这些数据库的连接字符串，最后在我的数据访问层中连接了适当的数据库.

至于停机时间，我不太确定，因为幸运的是我们还没有一台，但是我认为，根据数据的关键程度，您可能想要创建一个备份服务器，以备万一负载下降时可以承担负载.但是这种方法仅适用于一般情况，如果全部或多于一台服务器宕机怎么办?冗余比听起来要复杂，可能是这里的其他人可以加入并填写.

Well, I am not sure if there is a best practice for this problem but I will tell how I did it. I had a situation where the application related data was on different database server than the one that had mapping and geography related data (speaking of SQL Server 2008 R2).

My app used latitude and longitudes from its own database but sent them to the mapping database to operate on lat/longs and return, say, geocoded routing results. For this, first I made sure that my application was an n-tier application with separate layers for presentation, business, objects and data access. Then I added two connection strings in my web.config pointing to these databases and finally in my data access layer connected to the appropriate database.

As for the down time, I am not too sure because thankfully we haven''t yet had one but I reckon depending on the criticality of the data you might want to create a backup server that can take the load in-case one goes down. But this approach only works in average case scenarios, what if all or more than one servers went down? Redundancy is more complicated than it sounds, may be someone else on here can chip in and fill in.

这篇关于从两个不同的数据库插入和选择数据的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！