SQL Server Performance Forum – Threads Archive

Backup SQL server for network failure

Hi I was wondering if there was a process/method in SQL server that i could use to solve the following problem: The office in the States and France uses the SQL server databases in the UK and so access them through the network connection. However, when the network connection goes down for whatever reason they can not do any of there work as nothing works. Is there some kind of method to use a back up server on the local server until the connection is re-established to the UK server? The problems i can see are when you re-establish everything determining what happens if there are any conflicts in data during the ‘down time’ of the network connection. Thanks for your help in advance
CE

Look to replication or log shipping as a solution. Seehttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6035 for a discussion. If you do set up a server in the US and France which get updated by the UK server and the network goes down, you must decide what status the US and French servers have. Are they going to be read only (easiest) until the UK server comes back? Or to you want to be able to post updates back to the UK box. There are a number of issues and there is a cost associated which each option. You could keep only one backup server (say in France that both the US and France access) in the case of a network outage. This would be simpler

You can also take advantage of Log shipping in this case to maintain in France and States offices, where in case of network failure which is indefinete to know the restore time then meanwhile you can use the secondary servers as a standby for work purposes. Refer to the log shipping articles in this website and books online for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Another possible solution is to have asynchronous processing. It means you can have set of order tables (or even files) where you put changes to be processed. All other data on secondary db servers are read only and replicated from the primary db server. Batch process reads unprocessed orders frequently and then insert order data into db on primary server. When your connection fails you still can do everthing, only batch changes will be postponed untill connection is re-estapblished. That architecture also helps handling peaks. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch12.asp for more details.

]]>