Whats the best way to replicate data without data loss between more than 2 databases

I'm testing possible solutions for database redundancy and high availability.

My objective is to have various applications working simultaneous and communicating with one database only. But having the databases synchronized between them so that in case of one database goes down, all its data is not lost. ![Desired architecture][1]

How can I do this in order that all databases are publishers/subscribers and for a commit to be done, must be done in all databases (a two-phase commit protocol between all databases "online")? [1]: http://i.stack.imgur.com/N8Jhb.gif
more ▼

asked Dec 02, 2011 at 10:17 AM in Default

RagnaRock gravatar image

45 4 4 6

when you say communicating with one database only, do you mean each app/client connects to one of the three databases (as in the diagram) or all clients/apps connect to one database, and in the case of failure all failover to another?
Dec 02, 2011 at 12:56 PM Kev Riley ♦♦
an application can connect to any available database randomly
Dec 02, 2011 at 04:02 PM RagnaRock
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You've tagged this question with SQL2008, but can you wait until SQL2012 is released? There's a feature called AlwaysOn, that will fit your requirements quite well : http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx

This combines the 2 technologies of mirroring (to give multiple copies of the data) and clustering (to give seamless failover). A good introduction can be found here : http://blogs.technet.com/b/andrew/archive/2011/11/15/sql-server-2012-always-on.aspx
more ▼

answered Dec 03, 2011 at 05:53 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.9k 47 49 76

+1 That should put you over 30K, great job Kev!
Dec 03, 2011 at 08:30 AM Scot Hauder
Cheers Scot!
Dec 03, 2011 at 08:56 AM Kev Riley ♦♦
yes, well done @kev!
Dec 04, 2011 at 01:55 PM WilliamD
(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



Answers and Comments

SQL Server Central

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



asked: Dec 02, 2011 at 10:17 AM

Seen: 1031 times

Last Updated: Dec 02, 2011 at 10:17 AM