Initializing replication for a large database

This blog demonstrates the detailed steps needed to set up transactional replication using a backup to initialize the subscriptions without taking the snapshot for all the articles, as we know the snapshot option is time-consuming for large databases. 
 

Introduction
Setting up a transactional replication for an extensive SQL Server database is a huge task. Generally, you will set up transactional replication on any database by generating the snapshot without considering how long a snapshot will take and the amount of disk for the snapshot folder. To avoid a very long time to create an initial snapshot and reinitialize the Subscribers, in this blog, you will explore an easy way to initialize the subscriber from an SQL database backup.

Let’s get into the detailed steps of the setup:

To set up a transactional replication, you must first configure the Distributor. 
In this lab, I have already configured the Distributor, and the following is the reference link on how to configure the Distributor.

Process Steps Overview

• Create Publication. 
• Modify Publication Properties.  
• Back up the Publisher database.
• Restore the backup on the subscriber database.  
• Create a Subscription through T-SQL as the GUI doesn’t support initializing from a database backup.

In this scenario, Transactional Replication is going to be configured with the following details:

• Publisher + Distributor: Node1
• Subscriber: Node2
• Publisher Database: ABC_Pub
• Subscriber Database: ABC_Sub
• Publication: ABC_Pub_Bkp
• Subscription: ABC_Sub_Bkp

Step 1: Create the publication by choosing the database for replication. 
Publisher Database: ABC_Pub

Step 2: Select the type of publication and the tables to participate in replication. 

 
Select Articles to replicate: 

Step 3: In our case, we are using a database backup file instead of a snapshot so we will leave them blank and click on next, as shown in the following snapshot. 

 Created publication- ABC_Pub_Bkp 

Step 4: Set ‘Allow initialization from backup files’ to true. 

From T-SQL :

From GUI : 
On Publication Properties, select the Subscription Options and set “Allow initialization from backup files“ to “true” and Click Ok to save the change.

  Disable the Distribution cleanup SQL Server job (right-click on the job name and click Disable):

It is a must to disable the job before taking the backup as a next step. If not, you may experience the following error 

Execute sp_addsubscription on the Publication database by passing the mentioned parameters in the following snapshot. 

After all the other steps are completed, enable the Distribution cleanup job.

Now check the replication status using SSMS under replication → Replication Monitor.

We can see that the performance shows an excellent state and there is no Snapshot agent created in the entire process as we have used the backup file to initialize.