Introduction to SQL Server replication

This time I'll talk about some basis with SQL Server replication, whether you need it for High availability (HA), Disaster Recovery (DR), load balance your applications or any other reasons, you have ever considered one of the different types of replication available with SQL Server, some of the components that come in play when you will it setup as well as a brief description as to how each one works and the benefits. So let's get started:

Requirements:

Distribution DB: This is a system database you need to setup before configuring three types of replication (Transactional and Merge). This database comes into play when you need the changes to be distributed across the different subscribers you have to your publications. This is the database used by the Distribution agents to distribute the pending transactions you can also query this database to troubleshoot any errors shown in the replication monitor.

Publisher: This is the database server supporting your database publications, this applies the same as if you think of a book, you can find from a publisher different books (Publications).

Publications: This are the published databases for the replications in there you configure the articles (Tables) that will be part of the publication.

Subscriber: This are the servers that will receive the data from the publisher, the subscriber can be the same server as the publisher or can be different.

Snapshot: This is a copy sort of speak of the publication, it is used to initialize the subscribers or in the case that you setup that type of replication (Snapshot) it synchrony you set it up.

Replication Types

Snapshot: This type of replication is usually used when you need to move some data for reporting purposes or in the case that you will only read that data to run some extensive analysis and that processing will impact the transactional server.

Transactional Replication: This replication ships (depending on your settings) all the transactions from the publisher to the subscriber, this type of replication is helpful when you have reports that needs to be done online and you can't add more stress to your current production database, this is also useful in scenarios of HA or DR because it ships the data fast to the subscribers. This replication depends of the Distributor database to send replicate the data.

Peer-to-Peer replication: This publication enables multi-master replication. The publisher streams transactions to all the peers in the topology. All peer nodes can read and write changes and the changes are propagated to all the nodes in the topology. With this type each of the servers work as publishers and subscribers and you need to configure a Distribution database on each of the servers. This replication depends of the Distributor database to send replicate the data.

Merge replication: This replication "merges" the changes made at the publisher and the subscribers only after they have been initialized with a snapshot, you need to setup a merge schedule so that they can combine the changes from one site to another. This replication depends of the Distributor database to send replicate the data.

Log Shipping: This type of replication is one of the oldest but most trusted methods for Disaster recovery, what this replication does is it backups the transaction log of your database, copies and restore the file in your subscribers, all this based on the schedule that you setup, the more frequent, the closer to the failure you can restore. The good thing about this, is that it also helps you to keep your log files to a managed size so they don't grow indefinitely as you are constantly running this backups. You won't find this type of replication inside the replication folder in the management tree, instead you will find it on the "Properties" of each of the databases.

Mirroring: Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record (Microsoft). This type of replication is extremely useful when you seek scenarios of HA or DR, because the fail-over time is considerably fast if you do it manually or you can set up to do it automatically. I like to say, this is the preview version of what Always On does now, that is one of the reasons why you will see Microsoft's comment that this feature will be deprecated.

Always On: This is the newest feature in terms of replication that was added to SQL Server, and thus the one that has received a lot of enhancements with each of the versions. It was introduced with SQL Server 2012 and received a lot of improvements with each version even the name has changed, originally it was "AlwaysOn" and in SQL 2016 changed to "Always On availability groups". This takes the functionality described with the mirroring replication and the benefits of the Log Shipping feature where you can have your replicas with Read-Only. An specific post for Always On will be released were we will review all the features as well as how to get it setup. This type is frequently used to support both scenarios HA and DR.

My wish is that you can give a better idea as to which replication will work better for each of the needs you have so you can get the better of what the SQL engine can provide to you.

Thanks for reading