x

Replication: Changing the primary server automatically if fail.

I need to replicate data from primary server A to another server B. Write data to A and read from both servers.

So If A fail, how can I automatically use B as primary server (to write and distribute data) ? and If server A up again, I need to synchronize the data with server B and use A as primary again.

What's the best practice to do this?

more ▼

asked Oct 19, 2009 at 11:16 AM in Default

anwarchandra gravatar image

anwarchandra
25 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

There are a few ways to do this and they all have pros and cons.

In SQL Server 2005 and above, database mirroring is a good solution. It is easy to set up and if you use the SNAC client, it will automatically redirect clients to the second server. You can read more about it in Books Online: Database Mirroring Overview

Clustering will help you deal with hardware failures on one server by moving things to another one, but it requires shared storage and doesn't necessarily protect from disk issues. Clustering Overview.

Log Shipping works similar to mirroring, and works with all versions. However it doesnt' automatically fail over. Log Shipping Overview

Replication can work, but not automatic failover.

You could potentially use DNS to fail over clients, but the delays would depend on your TTL. If you have control of the application, you can set up your own failover. If the first server is not alive, then have clients connect to a second server.

more ▼

answered Oct 19, 2009 at 11:26 AM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x305
x61

asked: Oct 19, 2009 at 11:16 AM

Seen: 1499 times

Last Updated: Oct 19, 2009 at 11:18 AM