Configuring Merge Replication

This video is a 12 minute demo of how to quickly configure merge replication and to see it in action between SQL Server 2019 and SQL Server 2017. However most of what is included here will work on SQL Server 2005 or newer.

Transcription:

Steve Stedman 0:02
Hi, I’m Steve Stedman, and welcome to this quick demo on merge replication. Merge replication is a SQL Server feature that’s been available for many years. But let’s take a look at how to actually use it. First thing is, what is merge replication? Well, let’s take a look at the Microsoft docs here. And we can see that what merge replication is, is where you have a publisher and a subscriber, but you end up with changes happening on both sides. And those changes get merged together through what’s called the merge agent. And the distribution database is used for that. The difference between this and transactional replication is transactional replication is one way. And this can be sinking data in multiple ways. But one of the important things you need to know about this is that to track those changes, every row has to have a unique identifier. And in that, it has to have a wrote what’s called a row good on every single table that’s going to be synced through replication, merge replication. And if that doesn’t exist, when you set up merge replication, that’ll be added for you. But in the demo that we’re going to take a look at that table will already exist. What we’re going to do, we’re going to create a database called merge test. And then we’re going to drop the first one the demo, and then create a new table in that database called customer notes, there will be a replication Good, that’s of type unique identifier, but it’s also what’s called a row column good. Which means that that’s the thing that’s going to identify every row when they’re being merged. So you can change all the other columns when you’re working with a table. But if you change that one, it’ll break the replication. And then what we’re gonna do is just throw a couple people into that table for a quick demo, the first thing we need to do is we need to identify the publisher and the subscriber. So in this case, the publisher is going to be SQL Server 2017. And I’ve already set up replication, let’s take a look here, the distributor properties, and in here, there’s a database called distribution. And it just has some settings around how long it’s going to keep changes. So like keeping transactions for 72 hours means that it’ll keep them for that long. If a server is offline for up to 72 hours, it can catch up, otherwise, you have to reinitialize the replication. Okay, so once that’s done, we need to, and I’m gonna run this on the publisher side, which is on SQL Server 2017, is I’m going to drop my existing database set up the new database, but a couple of rows into that customer notes table that we can use for replication. So we’ve got two rows in that table. Next, what I’m going to do is I’m just going to go and create an empty database on the subscriber, which is on SQL Server 2019, in this case, and what we’re going to do is just drop the databases there and create a brand new empty database. Close that, we’ll take a look at the merge test. And this is on the subscriber. And you’ll see there are no tables showing up in there yet. But in our publisher database over our database in the publisher, let’s see, we do have the one table in there called customer notes, the next step is we have to set up a publication. And the publication is making this available for other databases to connect to it. So we’re gonna go here on the publisher side and say, new publication. And we only have one database, but in this case, we’re gonna select merge test, we’re going to use merge replication merge publication, this allows for what’s sort of called multimaster words, thinking from multiple locations, the wizard, so if you were using an old version of SQL Server, you need to specify that here, but we’re going to go with SQL Server 2008 or later, for the type, we’re going to go in and just say, let’s pick the customer notes table here. And with this, you could go in and say, Okay, I’ve got 50 tables I want to do, you will turn on all 50 of those. But for now, let’s just do this one, because all we have, and we’ll set the properties for that. And next we’ll go and we can filter the table. So this is where you can say, filter out anything that’s older than a specific date and only replicate newer data, for instance. And then the snapshot agent, the snapshot is what’s used to start the whole replication process, it basically takes a snapshot of all those tables, moves them over to the other database, and then gets them synced to start with, and then once that’s completed, it can do the merge process going forward. So what I’m gonna do is, say created immediately. The snapshot agent, we’re gonna set up security settings, I’m gonna say run it under the SQL Server agent account. And here we’re just going to run using sa sa is the user Normally, if it wasn’t just a quick demo, I would set up a sequel user that has the specific permissions that you want to be able to talk to the publisher and the subscriber. So here we’re just gonna use the essay user though to make it quick for the demo.

At the end of the wizard, we’re going to create the publication. And the options, you could generate a script and create it later or save it off. If you wanted to. Publication name here, we’re just going to call this merge test is the name of the publication. Alright, so now you can see down in the replication section here on SQL Server, we have a local publication called merge test, we hit the plus next to it doesn’t expand anything, because there’s no subscribers. So right now, it is a database that can be replicated, but it’s not being replicated because nothing is subscribing to it. So now I’m just going to jump over to the subscriber side, and it’s running on SQL Server 2019. And again, this is where we have the empty merge test database. And what I’m going to do is go into replication in the subscription section and say, new subscription. And this is where we’re going to tell it to synchronize this merge test database between the two servers, we need to specify this is defaulting to the subscriber, but we need to change this over to the publisher. So we’ll go connect to SQL 2017, which is the publisher side. And there’s only one publication there that we can grab. So we’re gonna grab the merge test publication, and use that we’re going to tell it to run all of the agents on the distributor, which means everything’s going to run on the sequel 2017. And it’s going to push it over to the other side. That’s important in this specific case, because we happen to be running SQL Express with no agent. On the subscriber side, if you were running full SQL server where you actually had a SQL Server agent running on both sides, you could run each agent on the subscriber as opposed subscription. But instead, it’s all going to run in the distributor, it’s going to push to the subscribers as changes occur, or pull from his subscribers as changes occur. We’re going to be the merge test is the database that we’re going to be doing merge replication on. And then we need to set some permissions here. So to work correctly, typically, if you have your SQL server running under domain account, you would set this up here, but I’m just going to say, use the SQL Server agent account because that’s running locally for the demo. And this, this demo server is not on a domain. And then I’m just going to say login to the subscriber or the other server as the SA user. And this is the case where I said, normally, we would set it up on the subscriber with a specific replication based user that only has the right permissions, it needs to do replication, define a schedule here. And what we’re going to say is we want to run this daily. And we’re going to run this every every one minute. It’ll synchronize between the databases between the publisher and the subscriber. Just for demo purposes, we’re going to initialize it immediately. It has a priority for conflict resolution, each different subscriber can have a different priority. So if you had one server that was like your main office, and you wanted it to have a higher priority when you hit a conflict conflict is when two sites change the same row. And it has to decide which which version of that it’s going to take. We’re going to create the subscription. Alright, this subscription has been created. But you can see here on publisher side, let’s see on the publisher side, we can see the local publication called merge test has a subscriber listed underneath that, which is this run one running on SQL Express over here. So let’s go look here. And we look. And there’s the table. And what’s happened as we initialize that, it created a snapshot immediately replication synchronize that snapshot. And all the data jumped from one server to the other server. And that’s all done via the jobs that are listed here, underneath the SQL Server agent that are specifically around the merge test, publisher and subscriber that are configured here. Well, let’s take a look at that table. So first off here, this query we’re running, select every select top 1000 on that table that’s running against the 2019 SQL Express server. So that’s the subscriber. And I have another query open over here that’s running on the publisher. And you can see that Fred Smith here has the ID of two. And what we’re going to do is give him a new name of Fred Jones rather than Fred Smith. And when we look at what’s in that table here, we’ll see now it’s Fred Jones listed instead of Fred Smith, take a look over on the other side. And it’s already synchronized the other server the subscriber as Fred Jones. Now, we’ll go ahead and rename him again. It’s Fred Anderson. We’ll do this a little bit quicker. See if we can catch it. There’s Fred Anderson there

and he’s still Fred Jones. Over on this side, and I’m going to hit update a few times. And there we go. Fred Jones has now changed to Fred Anderson. We have Okay, so now on this side, what we’ve done here is we’ve just pushed changes from the publisher side, out to the subscriber side. But what happens when a change happens on the subscriber side? Now, if you’re using transactional rep, replication changes on the subscriber side, would not go back to the server, they would only stay on the stryver side. So here we’re on the subscriber, I’m going to change Fred Anderson to be John Anderson. Take a look at what’s in that table here. On the subscriber side, it has changed to John Anderson, but we go look at the publisher side. And he’s still Fred Anderson. And we’ll give it a minute here. And we should see that Fred Anderson down below here should change John Anderson. The next time the replication syncs. There we go. Now it’s John Anderson. Let’s try that again on the other side. So John Anderson will now change it to Beth Anderson. And we look at what’s on the subscriber side. It’s still Beth Anderson, Oreos, Beth Anderson, we go back to the publisher side, and we’ll take a look. It’s John Anderson. And we’ll hit refresh a couple of times. And within a minute, we should see it change. There we go. We see a change to Beth Anderson as it came from the other side. That’s it. That wraps up my quick and easy demo of how to get started with merge replication. check back later for more information on things like resolving conflicts and adding tables and other fun stuff with SQL Server replication. And please, if you want more information, check out my blog at SteveStedman.com on if you need some help with anything. We are SQL Server consultants at StedmanSolutions.com so you can check us out there as well. Alright, have a great day.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!