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

avatar image

45 4 4 7

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

avatar image

Kev Riley ♦♦
66.8k 48 65 81

  • 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.

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: 1181 times

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

Copyright 2018 Redgate Software. Privacy Policy