04-09-08, 10:06 #1Registered User
- Join Date
- Jun 2005
Unanswered: bidirectional replication solutions
According to this article from last year:
These are the main options:
* Merge Replication
* Bi-directional Transactional replication
* Immediate Updating
* Queued Updating
* Peer to Peer
Are there any new alternatives that have popped up over the last year? Are all of six above still good options based on needs?
We currently have a three server topology using merge replication.
ServerA (App1DB) <--> ServerB (App2 DB)
ServerA (App1DB) <--> ServerB (App3 DB)
ServerA (App1DB) <--> ServerC
ServerA supports 1 intranet application using 1 DB
ServerB supports 2 extranet applications using 2 DB's (1 per application)
ServerC is our DW server that we have installed a Search DB which is used by all applications
Prior to our "upgrade" to merge replication we were using 1-way Transactional Replication so our topology looked like:
ServerA --> ServerB (App2 DB)
ServerA --> ServerB (App3 DB)
We also had linked servers between ServerB and ServerA as well as between ServerC and ServerA to update data on ServerA. We would simultaneously update/insert the tables on ServerB/C and create custom stored procedures to handle the data already processed from the subscribers.
With our new implementation we are seeing more latency as well as locking since merge replication is not running off of transaction logs anymore.
My main question is would we see an increase in performance and less locking as a result of a topology like this:
Master <--> ServerA (App1 DB)
Master <--> ServerB (App2 DB)
Master <--> ServerB (App3 DB)
Master <--> ServerC
Where Master is a server and DB supporting no applications (hence no OLTP). Would latency be the same/better/worse? Should we stick with our current implementation and just performance tune it?
A secondary question I have is given the bidirectional replication options above did we choose the best one for us? These servers are all on the same network hosted by the same provider over Gigabit Ethernet (I assume). I think we have the polling interval set at 5 seconds and we are thinking of moving it to 10 seconds at most. Real-time latency is not critical to our business but it would be a "nice to have". For conflict resolution we are keeping it simple, whichever was inserted/updated last "wins". It looks like Bi-directional Transactional replication might be a better option for us. Would it give us the autonomy we are looking for? Any major "cons" to using Bi-directional Transactional replication over merge replication (beside scalability). Scalability may come into play a few years down the road but for now it is not a high priority. Also would the Master model described above using Bi-directional Transactional replication be a successful implementation?
ETA - One thing merge replication gives us is autonomy between our application servers, particularly when ServerA needs to come down for upgrades, the applications on ServerB can still function without any dependencies like we had before with 1-way transactional replication with linked server calls.
Last edited by Gagnon; 04-09-08 at 10:21.
04-09-08, 18:26 #2Registered User
- Join Date
- Jun 2005
04-28-08, 11:27 #3Registered User
- Join Date
- Jul 2003
- San Antonio, TX
If you introduce "master" server with merge replication your latency may not improve, even though it will not be used by any application. But you definitely increase complexity and introduce another potential point of failure. Since you raise latency as an issue, you need to identify the bottlenecks and address them. One of the most common ones is disk, and most likely on ServerA. Ensure that the log device is on a separate controller/physical disk (set of disks). Can't put here RAID-1 because will catch a lot of fire, even though it's the best considering budgetary and hardware constraints, but definitely not RAID-5. If you can afford, - RAID1+0 or RAID0+1. Also, move tempdb devices to a separate controller/disk (set of disks), because in SQL2K5 under merge replication its use quadruppled."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."