up vote 1 down vote favorite
share [g+] share [fb]

I have an application that connects to SQL Server 2008. What I want is to make a standby server (this standby server should be a mirror of the primary one). So that when the connection fails, the primary server should automatically switch to standby server without modifying my application. If there is way, please tell me in detail or even if there is third party product.

Note that I need to set the connection in my application to one server only.

link|improve this question
feedback

4 Answers

up vote 2 down vote
  • Set up the second server.
  • Read up into MIRRORING. This keeps a copy of the databe on a second server in "hot standby".
  • Bad news: For automatic failover you need a third server (can run the free express edition) to act as what is called a witness (deciding who is active).

Applications can then after a failure reconnect to the backup server. This can be coded into the connection string, so no applciation change is needed.

Without witness you can only manually fail over.

This require no shared storage (Which is also a single point of failure in case the db is corrupt) and no expensive third party tools.

link|improve this answer
i would still go with some sort of clustering if he wants no manual intervension. The witness server is a single point of failure, i would always use a floating ip address. – beakersoft Dec 28 '10 at 15:47
The witness then can be clustered, but that again uses a pretty large financial layout (2 servers, shared storage). If you assume one server can fail at a time - the witness failing is not a problem ;) – TomTom Dec 28 '10 at 19:43
feedback
up vote 0 down vote

If you want it to fail over without any intervention what so ever you are really into some sort of high availability cluster, you cant really make your applications talk to another server or bring the other server online without something sat in the middle checking the status of your services

link|improve this answer
+1 Cluster is the way to go – JamesK Dec 24 '10 at 10:15
-1. MIRRORING is the way to go according to the OP. It is also cheaper (no need for shared storage) until you get really high in performance needs. – TomTom Dec 28 '10 at 12:13
feedback
up vote 0 down vote

You should all do a DR test on your Database's we have been doing weekly assured recovery test using CA RHA r15 and r16 and the databases are suspect or corrupted almost everytime... I think CA has issues with keeping the data in a usables state.

link|improve this answer
feedback
up vote -1 down vote

You might consider some kind of Replication software that can do an automated failover and failback. There are enterprise class sotwares available that can do such a re-direction. Major vendors are CA Technologies(formerly Computer Associates), Symantec and Doubletake.

CA ARCserve High Availability can create a mirrored Replica of the Production site server either over the LAN or WAN(optimised for WAN links). This allows the changes at the Master Server to trickle to the Replica in such a way that the Replica is only a few seconds or minutes behind the Master Server.

When the Master Server fails, the Replica comes online and the users are automatically Redirected to the Replica Server. The users are not even aware of the switchover that happened. After the Master is up again, the failback is also automatic with ARCserve. Both File and Application Servers are supported for High Availability.

If you wish to try out the Softwarwe, free Trails are available on the ARCserve site: http://www.arcserve.com/us/software-trials.aspx

link|improve this answer
1  
Yeah. There are tools for that. Also part of SQL Server, you know. – TomTom Dec 28 '10 at 12:13
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.