Click here to Skip to main content
15,501,276 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 ?

1 solution

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.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900