Learning the Differences: SQL Server Log Shipping vs Replication

Two recovery methods in SQL Server confuse a lot of users. So to resolve that confusion, we will do a comparison of SQL Server Log Shipping vs Replication. The two methods that we are going to compare are the SQL Server log shipping and the SQL Server replication. 

Both these methods have similar purposes, which is one reason the users get confused between the two. To get a clearer idea between the two, let’s understand these methods one by one. 

What is SQL Server Replication?

The replication in SQL Server allows the users to copy the database object and the data from one server to other multiple servers. This method is used for data distribution in SQL Server for higher data availability. For the replication in SQL Server, various components are used. Here are the key components that are required for SQL Replication.

• Publisher: The publisher is the primary server database that is required to be replicated. The whole replication process starts with the publisher as it defines the data and the database objects that will be copied to other SQL Servers.
• Distributor: The Distributor acts as the mediator server that ensures the smooth flow of the replication process between the source server and the destination server. This mediator stores the changes during the process and can be present in the same server or a different server. 
• Subscriber: These are the destination servers. The database objects copied from the publishers are stored here. Subscribers can be either one or many servers depending on what kind of replication the user wishes to carry out. 

Let’s now take a look at the agents used in the process one by one.

• Snapshot Agent: This Agent’s job is to take snapshots [pictures] of the primary database to initiate the replication process. This is the initial step of SQL Server replication. 
• Log Reader Agent: It is responsible for keeping notes of relevant changes in the primary database during the replication process to be sent to the destination server or database. 
• Distribution Agent: The Distribution Agent takes all the data collected by the Log Reader and the Snapshot agent and then sends the data to the destination server.
• Merge Agent: The Merge Agent is responsible to synchronize data and resolve conflicts during shared changes made by primary and secondary servers.

Types of SQL Server Replication

There are three types of replication in SQL Server. 

• Snapshot Replication: The Snapshot replication works by creating a snapshot of the database at a specific point of time. After taking the snapshot it sends them to the subscribers. It does not offer a real-time replication. 
• Merge Replication: The Merge Replication in SQL Server allows the publishers (primary)  and subscriber(secondary) servers to make changes in the data. Even if any conflicts occur, they are resolved by the Merge Agents. 
• Transactional Replication: The Transactional Replication constantly copies the changes made in the primary database to the destination database.

As we now know the types and the components used in the replication process, let’s take a look at how it works. 

How SQL Server Replication Works?

To configure the replication in SQL Server, follow the given steps:

Step-1 For Distributor Server Configuration

1. In SSMS, go to Server explorer, and then right-click on the Replication option. 
2. Next, select the Configure Distributor option. Then choose whether you will use the current server as the distributor or change it to any other server.
3. Then add the distributor name, path and folder to configure the distributor server. 
4. After adding the file paths, click on Finish to complete the Distributor configuration process.

Step-2 For Publisher Server Configuration

1. From SSMS, go to the replication option, and then click on the new publication option. 
2. Then, select the primary database that you wish to replicate.
3. From the three given types of replication, choose the replication you need to carry out. 
4. Now, you have to choose the database objects that you wish to replicate to the secondary server. 
5. To schedule how often the snapshot replication should be done, add the snapshot schedule. This can be scheduled weekly or monthly. 
6. After all these steps are completed, click on finish to set the publisher configuration. 

Step-3 To Configure Subscriber Server

1. Open SSMS > go to Replication option. Then choose the new subscription option. 
2. Then select the publisher created earlier.
3. From the given Subscriber options, Push Subscription and Pull Subscription, choose one type. 
4. Then choose the database or server that will act as the destination server or the subscriber. 
5. Schedule the replication frequency. It means how frequently you wish the subscriber and publisher should synchronize. 
6. After completing the steps, click on the finish button. 

Step-4 To Monitor Replication

1. Go to the Replication Monitor to check the replication progress and performance.
2. After the replication is completed, ensure that the complete data has been copied to the Subscriber. 
3. If there are any issues that occurred during the replication, resolve them accordingly. 

SQL Server Replication Steps Overview

As we can see here, the steps for replication configuration include four stages. The first stage is for creating the distributor or the mediator database that will monitor the complete process. The second stage is for creating the publisher. The publisher is the primary database that will be replicated. Then we configure the subscriber server. This is the destination server or database where the data will be copied after replication. 

After completing these steps, the final stage starts. In this stage the user has to monitor the process and ensure if the replication has been completed successfully or not. 

As we have now understood the complete replication process. Let’s take a look at how log shipping works and the SQL Server Log Shipping vs Replication difference. 

What is Log Shipping in SQL Server?

Log shipping in SQL is the disaster recovery process in which the transaction logs of the primary database are copied to the secondary database as a backup. In SQL log shipping, the users can create and store backups on secondary servers(one or multiple). 

Lets understand the steps to how Log Shipping is configured in SQL.

1. Open SQL Server Management Studio and connect it to the primary database. 
2. Right click on the database and go to properties. Choose Transaction Log Shipping.
3. Click on the checkbox to enable the log shipping in SQL.
4. Add network and location path to specify the destination of the primary database. 
5. Schedule the backup time and add the secondary server path. A window will show up, add the required credentials in it. 
6. After adding the necessary details and scheduling the backup time, click on OK to configure log shipping. 

By following these steps, one can enable SQL Server log shipping. Now we know about both the methods. And also that both log shipping and replication have same purposes to create copies for the database objects of a primary database. So what can be the difference between the two? Let’s move to the comparison between the two. 

Also Read: How to Configure SQL Server Log Shipping? A Complete Manual

SQL Server Log Shipping vs Replication

Let’s now take a look at the differences between the two methods and understand how these methods look similar but are not. 

SQL Log Shipping

• It’s major purpose is disaster recovery.
• The primary database is backs up to the secondary database on the secondary servers.
• In log shipping, there is synchronization delay  as it takes place only at the Transaction log backup frequency.
• Log shipping works at the database level as the complete primary database backup copies to a secondary server.
• The Log Shipping method completely depends on the transaction logs of the SQL Server. 
• The affect on primary database is minimum as it only takes the transaction log backup. 
• The log shipping process is not complex and quite easy to carry out. 

On the other hand, if we talk about SQL Replication;

• The major purpose of using the replication is better data distribution and better synchronization on multiple SQL Servers. 
• The Replication offers better and near real time synchronization of data among the primary and the secondary servers. 
• Replication works at object level as it majorly deals with the tables and the schema of the database. 
• The replication method works by using different mechanisms and agents in the SQL Server. 
• As the replication process is all about copying data for wider accessibility, the primary database gets more work to keep a track of the changes made. Then send it to the secondary servers. 
• As compared, the replication process is more complex to carry out than the log shipping method.  

Conclusion

Through this write-up, we have majorly focused on the similarities and differences of two methods. We have explained the two methods along with a SQL Server Log Shipping vs Replication comparison. To help the users get a clearer idea of the two; SQL log shipping and SQL replication, we have provided the detailed steps for the two methods.