SQL Server Performance Forum – Threads Archive
Replication Latency !
Hi, I have 2 Servers running MS SQL7 SP4, Standard Edition.Merge Replication is setup, Server 1 as Publisher and Server 2 as its subscriber.
The subscription is setup push from the Server1 to Server2.
It agent is setup to run continuously. At an instance one Server is Active and other is at standby state.
There is nearly 30 seconds delay in between the two which ever is online
It replicates its data after 30 seconds to the backup server. Is there any way I can make both the servers to commit the data at the same instance?
So if one server get fails the other have the same data which is committed on the failed server. Right now when active server gets fails/ abrupt power down/ or even just give shut down command, then the other server unable to get the complete data. This way the nearly 30 seconds data is missing as for the new server. How to make replication to commit both at the same time ??? Do I need to change the Replication Type ??
Just want to achieve that both servers replicates data either way, and there should be minimum latency between replications. Regards
Try using TRANSACTIONAL replication process but be aware about performance overhead.
You can use LOG SHIPPING which works in similar fashion. HTH _________
Satya SKJ
I would like to know more about the big picture. For example, this the purpose of your replication for failover only, or are there other reasons? The reason I ask is that replication is a poor way to handle failover needs. It presents a lot of issues that are hard, if not impossible to resolve. If you are really interested in failover, you need to look at Microsoft Clustering, Log Shipping, or a third party program such as SQL UP. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Hi, It was long time, I post this issue.
Ultimately I was able to solve it by decreasing the polling time from 30 sec to 15, although it was not a real solution of the problem, but it makes the things working well. Actually the big picture was that one custom based application run on the active server and it collects data from nearly 60 field devices and saved information in the database, if some how the custom application get crashed, the redundant server starts the application on it , and does the same task.. (Heart beat verifies the status of two servers). So If the other server takes over when one got fail, and there is missing of the data in the database then this causes operation problems of the different devices, so must have always same data on both the sever. Also have introduced 20 seconds time when the backup server takes over the failed primary server, this way I am able to solve the issue, doing modification at both ends at the application level and the database level…… Thanks for your guidance…..
]]>