Database Synchronization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Database Synchronization

Author

Topic

vna_mhars
Starting Member

37 Posts

Posted - 2011-02-10 : 19:54:04

Hi,

I need an opinion of the experts, this is the scenario.
I have 3 servers with the same database on each server.
and then each database server has an application connected, all 3 application in the servers are the same. For Example Server1 is for Location1, Server 2 is for location2 and then Server3 is for location3. What I want to do is to synchronize all of them.. Just in case the Server1 having a problem..i can reroute the point of connection to Server2 so that the transaction will continue and the data is updated.
Advise from the expert is highly appreciated.

Thanks,

vamodente

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-02-10 : 23:20:02

Hi,

You Will Use Peer to Peer Transactional Replication.

--
Ragu Thangavel
Junier Sql Server DBA

vna_mhars
Starting Member

37 Posts

Posted - 2011-02-10 : 23:25:41

Peer to Peer Transactional Replication will work to synchronize 3 servers? S(Server) S1 will update S2 and S3, then S2 will update S1 and S3, and then S3 will update S1 and S2? and then those three servers has access by the clients...

vamodente

Sachin.Nand

2937 Posts

Posted - 2011-02-10 : 23:58:28

For automatic fail-over use Database Mirroring if you want to do it for a single database or else go for clustering for the whole server.

PBUH

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-02-11 : 01:01:36

Yes.In Peer to Peer Replication,all subscribers are also publishers.

[url]http://technet.microsoft.com/en-us/library/ms151196.aspx[/url]

--
Ragu Thangavel
Junier Sql Server DBA

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 01:16:51

quote:
Originally posted by raguyazhin

Yes.In Peer to Peer Replication,all subscribers are also publishers.

[url]http://technet.microsoft.com/en-us/library/ms151196.aspx[/url]

--
Ragu Thangavel
Junier Sql Server DBA

Please read what exactly the OP wants.

quote:

Just in case the Server1 having a problem..i can reroute the point of connection to Server2 so that the transaction will continue and the data is updated.

If any one of the server in the replication link fails the whole replication setup is bound to fail.

PBUH

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-02-11 : 05:08:44

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by raguyazhin

Yes.In Peer to Peer Replication,all subscribers are also publishers.

[url]http://technet.microsoft.com/en-us/library/ms151196.aspx[/url]

--
Ragu Thangavel
Junier Sql Server DBA

Please read what exactly the OP wants.

quote:

Just in case the Server1 having a problem..i can reroute the point of connection to Server2 so that the transaction will continue and the data is updated.

If any one of the server in the replication link fails the whole replication setup is bound to fail.

PBUH

only Replication setup will fails but other servers or still running?

Ex

Server A Fails then Replication will fails,But Server B And
Server C Still Running. Just reroute connection to server B Or Server C,Application will running. and recreate the replication or remove Server A From replication.

--
Ragu Thangavel
Junier Sql Server DBA

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 05:53:48

So with the solution you posted you will have to go through the replication set up whole over again in event of server failure right?

Also have you considered the cost of downtime till you reroute the application to a different server(hope that does not happen at 3:00 am when you are having sweet dreams :)) and time taken to set up the replication whole over again.

PBUH

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-02-11 : 06:08:07

quote:
Originally posted by Sachin.Nand

So with the solution you posted you will have to go through the replication set up whole over again in event of server failure right?

Also have you considered the cost of downtime till you reroute the application to a different server(hope that does not happen at 3:00 am when you are having sweet dreams :)) and time taken to set up the replication whole over again.

PBUH

So What can we do? this our job.

In Mirroring at a time we use a principle server Database.
So how we synchronize the database both side?

--
Ragu Thangavel
Junier Sql Server DBA

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 06:45:56

I never said that the mirroring should be a substitute for replication for synchronization.So same way replication cannot be a substitute for mirroring in case of failure.
You were trying to suggest that replication can be a substitute for mirroring which is not feasible.

PBUH

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-11 : 10:41:50

The entire application will not fail in a 3 node peer to peer topology if one server fails, as long as the distributor is still available.

Mirroring won't help in the OPs scenario. Nor will failover clustering. How are you going to have 3 live mirrored instances? You aren't.

Peer to Peer replication is the way to accomplish this. You'll have to manage the connection part yourself in the application(s). So if Server1 becomes unavailable, you'll need to make sure that Location1 can read/write from either Server2 or Server3. If the network at Location1 becomes unavailable, then users there aren't going to be able to connect to the other locations.

With Peer to Peer, you'll need to seed any identity columns so that you don't get collisions. For example:
Server1 - IDENTITY(1, 3)
Server2 - IDENTITY(2, 3)
Server3 - IDENTITY(3, 3)

You can have Replication AND mirroring however. This gives you local failover capability as well as remote synchronization. This is a common strategy for geographically dispersed applications.

By the way, if a server fails, any currently running transactions will be lost. Users will have reconnect and retry the transaction.

Sachin.Nand

2937 Posts

Posted - 2011-02-11 : 11:27:36

Aren't you contradicting here?

quote:
Mirroring won't help in the OPs scenario. Nor will failover clustering.

quote:
You can have Replication AND mirroring however. This gives you local failover capability as well as remote synchronization. This is a common strategy for geographically dispersed applications.

What I meant is you can have peer to peer replication for synchronization but then you can have 3 mirrored instances which are "local" to that particular server instance in their respective location.