September 26, 2011 at 4:14 pm
Hey Everyone,
I am trying to setup Merge Replication on SQL Server 2008 R2. The idea is to have 2 identical databases on 2 separate instances on two separate servers. Each database on the instance/server can have updates made and all updates are replicated back to the other database on the other instance/server and vice versa. I know I have to create a distributor and publication on one instance/server. What I am not sure about is should I create another distributor and publication on the other server/instance? Then the next question is the subscriber. On the first server/instance do I have to create a subscriber that points to the database on the second server/instance? And on the second server/instance do I create a subscriber that points to the database on the first server/instance?
I hope this makes sense to you all.
Patti
September 26, 2011 at 11:51 pm
What you will need is one (and only one) distributor, one publication and one subscription to that publication.
The subscription from the "second" database sorts out getting updates from both databases to the other database. It actually checks both database for differences and merges the changes based on the rules you define in the publication.
December 19, 2011 at 4:26 am
Yeah..you need to have a little read up on replication. Although merge is one of the more complicated forms, it's still fairly easy to set up. And as mentioned you have a typical Publisher/Subscriber scenario. Distribution whilst required is less important here than with Trans Rep..but have aread.
One thing to consider here is what data is being updated at each site. If similar data is updated at both ends you may have to consider conflicts. If each site is updating distinct record sets then this will make life easier for you.
HTH
Graeme
December 19, 2011 at 1:53 pm
also make sure none of the tables uses identiy columns
as you will have to program around these
December 20, 2011 at 4:48 am
If you can set things up so that all your writes go to one server, you will have a MUCH simpler environment. One of the pains of Merge Replication and P2P Replication if how to deal with conflicts when a record is updated within the latency time on multiple servers. Your planning for Merge Replication with multiple updaters must include how you deal with update conflicts, or you will not get the results your business needs.
There are also problems with schema updates with Merge Replication that are not so much a problem with P2P (but P2P cannot include any application logic for dealing with update conflicts). If all your writes are directed at one server then these problems effectively cease to exist.
I assume that you want the capability of writing to either server to cope with DR and planned downtime, but this does not mean you have to be continually writing to both servers.
One way to direct your writes to a given server is to set up a DNS alias, and use the alias name when your applications connect to SQL. This allows you to change the DNS alias to the other server if the first one goes down for any reason, and is completely transparent to your applications.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 20, 2011 at 6:48 am
Thanks everyone for your replies. I set up merge replication with the publisher and distributor on one server and the subscriber on another server. Both sides have update capabilities. Having to resolve conflicts are going to be an issue and I'm not happy about that. The customer however is determined that merge replication is what they want. We have already had issues just in testing.
One of the is the issues with uniqueids. I received this error: Msg 548, Level 16, State 2, Line 1 The insert failed. It conflicted with an identity range check constraint in database 'XXX', replicated table 'dbo.NODE_ATTRIB_RSLT', column 'ID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. The statement has been terminated. The customer was inserting 2400 or so records.
I spoke to the developer of this application and they said that they use uniqueids on almost all of their tables. He also said that he had merge replication setup one before on SQL 2000 and didn't have this problem.
Right now I have merge setup to include triggers replicated and do not have stored procedures replicated. The customer said not to include stored procedures. I also have schema changes being replicated.
I just came back from a SQL2008 class where we talked about merge replication and they said turning off replicating triggers was the way to go. They said that if we don't replicate triggers, that the application needs to develop another to update the subscriber side. They couldn't help me much with uniqueids (identity columns). I need to set the tracking level from row to column.
December 20, 2011 at 2:02 pm
also there is an overhead with merge replication
if there are a large number of updates going both ways you will probably notice a performance hit
December 21, 2011 at 3:03 am
Merge Replication can be great when you get it to work smoothly, but getting it to work can be more hassle than some organisations will put up with. It also needs a higher skill level within the Operations team to deal with it, both at the DBA level and at WSE level.
All of this results in costs to the organisation. I still recommend that you talk to your customer again and outline the potential cosst of having simultaneous dual-node update compared to single node update. They may decide that while multi-node update sounds nice the benefits are not worth the costs.
If you can show that you can get multi-node update working but are showng them a way to cut cost and complexity by using single node update then you have a good story to bring to your customer, which could make them more willing to come back to you. If you deliver to them a working multi-node update system they cannot cope with operationally, the project may be a technical success but it will be a business failure for you and your customer. If they insist on continuing with multi-node update then you should make sure they are getting ready to cope with the operational challenges.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 21, 2011 at 3:19 am
I agree merge has the highest support overhead, but it is pretty resilient and if you have the right system in place it should be fine. A careful use of NOT FOR REPLICATION can save a lot of hassle especially with triggers.
We don't actually know how much data is being replicated here.
How big are these databases ?
How complictaed are the business rules for the data ? Are updates at the subscriber dependant on inputted data at the publisher and vice versa
Graeme
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply