Problem
SQL Server offers us several different ways to replicate data, but how do we know which type of replication is suitable for us? In this tip, we will look at the different types of replication, including the pros and cons and some scenarios where replication might be suitable for you.
Solution
This is the first tip in a series of tips on SQL Server Replication, there are 3 main types of SQL Server replication which we can implement, these are Snapshot, Transactional and Merge Replication. While the general idea of each is similar i.e., they replicate data, they do it in 3 different ways.
SQL Server Replication Terminology
Before we see what these are, it’s best if we familiarize ourselves with the terminology used in SQL Server Replication.
Article
- An article is an object that can be replicated. These include tables, views, stored procedures, and user defined functions.
Publication
- A publication is a collection of articles that exist on a publisher.
Publisher
- A publisher is a SQL Server instance that has publications which contain articles to be published to subscribers.
Subscriber
- A subscriber subscribes to one or more publications, it will receive each article in the publication.
Distributor
- The distributor collects articles from the publisher and delivers them to the subscribers.
Types of SQL Server Replication
Now we understand some of the terminology used in replication, let’s look at the different types and when we would use each. We will start with what I consider to be the simplest and then move onto the more advanced. While the methods used by the 3 types do differ, fundamentally they are built off the same foundation, that is, they all use the publisher, distributor, subscriber, and they all rely on agent jobs.
SQL Server Snapshot Replication
I believe this is the simplest replication, and as you might guess from the name it replicates a snapshot of the data.
It does this by running something called the Snapshot agent, this can be on a schedule or it can be run on demand. The snapshot agent locks the table and takes a snapshot which it stores in a snapshot folder, then it updates the distributor with details of the snapshot. The distributor delivers the snapshot to the subscribers. This is useful for tables that don’t update very often so you could schedule the snapshot to be taken daily, weekly, monthly, etc. For example, a quarterly bonus table, in this scenario it might be worthwhile using snapshot replication to pull over the infrequent changes to the subscribers.
Pros
- Simple to set up, low maintenance, doesn’t need a primary key on the table.
Cons
- High impact when the snapshot agent runs, high latency, modifications on the subscriber would be lost when the new snapshot is delivered.
SQL Server Transactional Replication
Transactional replication, again as the name suggests, delivers transactions.
Unlike the snapshot agent which takes a snapshot of the whole article (table, view, etc), in transactional replication we have a log reader agent, it reads the logs and looks for transactions which are marked for publication, it delivers these transactions to the distributor. The distributor then delivers the transactions to each subscriber, in transactional replication, there are 2 types of subscription for a subscriber, a push subscription or a pull subscription. In a push subscription, the agent job runs on the distributor to deliver transactions to the subscriber. In a pull subscription, the agent job lives on the subscriber and pulls from the distributor. This is useful when the data needs to be as close to the publisher as possible on the subscribers as transactions can be delivered within a few seconds, this way the subscribers can serve multiple purposes without anyone needing to access the publisher.
Pros
- Great for highly transactional data, low latency, make modifications on subscribers that don’t get sent to the publisher and won’t be overwritten e.g. you could add a column.
Cons
- Slightly more work to set up, more difficult to maintain, easily broken e.g. if someone inserted a row on the subscriber to create a PK violation.
SQL Server Merge Replication
Merge replication is similar to transaction replication; however, it allows updates at the subscribers to be merged with the publisher.
To track the rows, it adds a unique identifier column to your table. Subscribers can be offline and the changes are tracked, once the subscriber is online it can send the changes to the publisher. If multiple subscribers are updating the same rows this can cause conflicts which need to be managed. The typical scenario where this type of replication is useful is sales agents using tablets in the field that can take sales whilst offline and merge the changes when they get back online.
Pros
- Allows subscribers to update records, lets offline subscribers make data changes.
Cons
- More complicated to set up and maintain, creates a unique identifier column on your table, creates conflicts which need to be managed.
SQL Server Replication Example Scenario 1
A remote office has reports that run monthly to calculate commissions for the sites salespersons, currently, the data is updated by exporting a csv file and emailing it over to the remote site, they then import the csv file and run the report. How could we solve this with replication?
- We could cut out all this manual effort of CSVs and set up a monthly replication snapshot of the data to the remote site.
SQL Server Replication Example Scenario 2
A busy website has visitors from all over the world. When users log into their account, information is pulled from the database and displayed on the user’s profile. As the SQL Server which stores customer data is located in New York, customers from other parts of the world have complained about slow page loading times, how could we solve this issue?
- This can be solved by using transactional replication to send customer profile data to subscribers geographically closer to the users to decrease page load times.
Next Steps
