September 26, 2011 at 4:27 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 6:37 pm
Spend some time here Patti:
http://www.sqlserver-training.com/video-how-to-setup-replication-with-sql-server-2008/-
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
September 27, 2011 at 2:45 am
September 27, 2011 at 7:07 am
Thanks. I was hoping for someone to help set me straight. I'm reading but I not getting it yet.
September 27, 2011 at 7:30 am
WaIIy, I looked at these videos and I don't see anything on Merge Replication. Do you see it?
September 27, 2011 at 10:06 am
Patti Johnson (9/27/2011)
WaIIy, I looked at these videos and I don't see anything on Merge Replication. Do you see it?
Hmmm... I had that bookmarked as a merge-rep video.. 😉
My bad... :angry:
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
September 27, 2011 at 10:55 am
I watched the snap shot and peer to peer. Awesome videos. My hangup is knowing if I should have 2 distributors, 2 publications and 2 subscribers. One of each on each instance/server.
September 27, 2011 at 10:59 am
You're talking about a peer-to-peer transactional replication setup. I'll tell you right now that these can be tricky to keep running reliably, especially if it has a high traffic volume, and even more so if they're located far away (i.e. one in US, one in Europe).
I had a setup like this and had 2 sql servers, and both were distributors, publishers, and subscribers. I did not have separate distributor boxes so it was just the two talking back and forth.
September 27, 2011 at 11:17 am
Derrick, so you are saying when you have 2 distributors, 2 publishers and 2 subscribers it's peer to peer?
I was told to setup merge replication. What the user wants is to have the databases adds/dels/updates on SRVR1/INSTANCE/DB applied to the SRVR2/INSTANCE/DB, and they want the adds/dels/updates on SRVR2/INSTANCE/DB applied to SRVR1/INSTANCE/DB.
Right now I have setup a Distributor and Publisher on SRVR1/INSTANCE for the DB. It created a snapshot. Now I am setting up a SUBSCRIBER on SRVR2/INSTANCE connecting to the SRVR1/INSTANCE publisher. As part of the process it creates a DB on SRVR2/INSTANCE. I think it will apply the snapshot to the SRVR2/INSTANCE/DB. I am almost getting to this point.
I can leave it like this provided that the updates from SRVR2/INSTANCE/DB are applied to SRVR1/INSTANCE/DB. That's the unknown part at the moment.
Patti
September 27, 2011 at 11:25 am
When you want to have multi-master, yes it's called peer to peer in sql server. It can technically be up to 8 total servers I believe, but nobody should really be attempting this with more than 3.
I was typing this out but someone already posted the basic steps here on how to set it up and it looks pretty accurate:
http://www.replicationanswers.com/P2P.asp
Also as a quick note, I find it easier to set this up when you're remoted into one of the servers to set up the peer to peer topology/etc. Trying to do it from your local SSMS sometimes gives weird results.
September 27, 2011 at 11:33 am
So when the user said they wanted Merge Replication, there were really describing peer to peer. Derrick, Do you think that is a fair assumption? Since this is really new to me, I guess need to find out what the differences are between merge and peer-2-peer. I really appreciate having someone to talk to. I'm out here on a limb! 🙂
September 27, 2011 at 11:57 am
Merge and Peer to Peer are similar, just meant for different things.
Peer to peer was designed for server-server communication where both servers will pretty much always be online. From what I've tested, it's faster and results in fewer conflicts.
Merge was designed for more of a client-server environment where the client sometimes syncs up with the server but is disconnected quite often. Changes are tracked by triggers instead of by the log reader agent, and latency isn't as low as it is with peer to peer.
My original setup was SQL 2005 so I'm not sure how much Merge has improved in 2008+. Functionally, either method should work for you..it was just a huge performance gap in favor of peer to peer in my case. I haven't given merge rep. much attention since then.
September 27, 2011 at 12:33 pm
These users want to be able to use SRVR2/INSTANCE/DB as a backup server in case SRVR1/INSTANCE/DB is down and vice versa. Technically both SRVR1/INSTANCE/DB and SRVR2/INSTANCE/DB are the same DB. That is what they want to achieve. In this environment, the SRVR1/INSTANCE/DB is located in NY and SRVR2/INSTANCE/DB is located in BOSTON. The users would just have to change the application front end to point to the other SRVR/INSTANCE. Both sites are up and going all the time. They want something like a 5 minute down time max on either side.
September 27, 2011 at 12:38 pm
Patti Johnson (9/27/2011)
These users want to be able to use SRVR2/INSTANCE/DB as a backup server in case SRVR1/INSTANCE/DB is down and vice versa. Technically both SRVR1/INSTANCE/DB and SRVR2/INSTANCE/DB are the same DB. That is what they want to achieve. In this environment, the SRVR1/INSTANCE/DB is located in NY and SRVR2/INSTANCE/DB is located in BOSTON. The users would just have to change the application front end to point to the other SRVR/INSTANCE. Both sites are up and going all the time. They want something like a 5 minute down time max on either side.
If they don't want to do any reporting or other operations on the backup server then I would consider mirroring vs. replication.
[font="Verdana"]-- Wally
-- Reluctant DBA[/font]
September 27, 2011 at 12:42 pm
They do though. Both sides have an app server and the app server points to the SRVR/INSTANCE/DB located close to them. They both want to updae and they both want to run reports. They want both sides up and alive at all times and if either side goes down, the use the other.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply