A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.

In the previous article SQL Replication: Basic setup and configuration, I’ve discussed a lot about setting up a SQL Server transactional replication solution and synchronizing the subscription with a snapshot. As we all know, by default, subscriptions in a SQL Server transactional replication publication are reinitialized with a snapshot, which is generated by the Snapshot Agent and applied by the Distribution Agent. In most of the scenarios, such as those working with very large database and large initial datasets, it is preferable to reinitialize a subscription using another method.

Other methods of initializing a Subscriber include:

1. Specifying a backup
2. Copying the initial dataset

Specifying a backup method:

This is a very simple method. First, restore the backup on the Subscriber, and then setup a subscription using T-SQL. After the setup, the Distribution Agent copies the required SQL Server transactional replication metadata and system procedures that are required for data propagation. It considered as an efficient way to reinitialize a subscriber and it is the fastest way to deliver data to the subscriber. The most recent backup can be used if it was taken after the publication was enabled for initialization with a backup.

In this article, we’ll discuss more about “Specifying a backup” method

Copying an initial dataset:

In this method, the schema-and-data are copied to the Subscriber through any of the known schema-and-data copy method. And then setup a subscription with “replication support only” option. The Distribution Agent copies any required metadata and system procedures. At the Publisher on the publication database, execute sp_addsubscription. Specify the name of the database at the Subscriber containing the published data for @destination_db, a value of push for @subscription_type, and a value of “replication support only” for @sync_type.

It will be discussing more about this method in my next article.

SQL Server transactional replication diagram:

Problem statement

As we all know, by default, subscriptions in a transactional publication are reinitialized with a snapshot, which is generated by the Snapshot Agent and applied by the Distribution Agent. A snapshot is a point-in-time picture of the related objects (articles) of the publication. By default, in SQL Server transactional replication, the data will be loaded via BCP or Bulk Insert. Let us assume a scenario where it is required to publish articles of VLDB (Very Large Database) to Subscribers. By default, for the initial synchronization, requires a snapshot generation. In some cases, space is a big constraint and also it will lock the articles. It will create concurrency issues as well. For mission-critical database this is not practical as it can take a very long time to create an initial snapshot and reinitialize the Subscribers from that generated snapshot.

Getting Started

To set up SQL Server transactional replication, you must configure the Distributor and create a Publication and a Subscription using backup option

To reinitialize a subscription with a backup, you first must enable “Initialize with backup” option when you create a publication, and then specify values for @sync_type, @backupdevicetype, and @backupdevicename options when you create a subscription. Publication option can be enabled through the New Publication Wizard using SSMS or programmatically using T-SQL. However, the values required for the setting up a subscription option can only be specified programmatically using T-SQL.

Let us get into the details of the setup:

1. To configure the SQL Server transactional replication distributor, refer to the Configure distributor section of the article How to setup a basic SQL Server Transactional Replication
2. To configure the SQL Server transactional replication publisher, refer to the Configure Publisher section of the article How to setup a basic SQL Server Transactional Replication. In the 8^th step, On the Snapshot Agent page, the options “Create a snapshot immediately and keep the snapshot available to reinitialize subscriptions” and
   “Schedule the Snapshot Agent to run the following times” are left blank and Click Next.

   

3. The other steps remain the same

   

4. You could notice that there is no snapshot step in the Creating Publication page.

   

5. After creating the publication, you need to set the ‘Allow_Initialize_From_Backup’ parameter to true at the publisher. You can either do this using T-SQL or SMSS.

   1 2 3 4 5 6 7 8 9

   USE [AdventureWorks2016] GO DECLARE @publication AS sysname SET @publication = N'AdventureWorks2016_Publisher_BKP' EXEC sp_changepublication   @publication = @publication,   @property = N'allow_reinitialize_from_backup',   @value = 'true' GO

   

   OR

   Browse to the Publication Properties and select the Subscription Options, set “Allow initialization from backup files“ to “true” from the drop-down list and Click Ok to save the change.

   

6. Create a backup of the SQL Server transactional replication publication database using the Backup command. Let’s initiate a full backup or T-log backup if you’ve already initiated a full backup.

   1	BACKUP DATABASE AdventureWorks2016 TO DISK = 'f:\PowerSQL\AdventureWorks2016PublisherDB.bak' WITH FORMAT

   
   

   

7. Next, restore the database backup on the SQL Server transactional replication Subscriber using the RESTORE database command

   1 2 3 4 5

   USE [master] RESTORE DATABASE [AdventureWorks2016] FROM  DISK = N'F:\PowerSQL\AdventureWorks2016PublisherDB.bak' WITH  FILE = 1,   MOVE N'AdventureWorks2016_Data' TO N'f:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\AdventureWorks2016_Data.mdf', MOVE N'AdventureWorks2016_Log' TO N'g:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Data\AdventureWorks2016_Log.ldf',   NOUNLOAD,  REPLACE,  STATS = 5

8. On the publication database, execute the sp_addsubscription system stored procedure. Specify the following parameter

   • @sync_type – “reinitialize with backup”
   • @backupdevicetype – the type of the backup device. In this case it’s Disk
   • @backupdevicename – Physical or logical backup device name. In this case, its physical, so type in the full path of the backup file. If the most recent backup is transactional log then specify the transaction log file full path.

   • This script uses sqlcmd scripting variables

   :setvar publicationDB N’AdventureWorks2014′;
   :setvar publication N’AdventureWorks2016_Publisher_BKP’;
   :setvar job_login N’Domain\ID001′;
   :setvar job_password N’thanVitha@2016′;
   :setvar subscriber N’hqdbt01\SQL2017′
   :setvar subscriptionDB N’AdventureWorks2016_REPL_Rpt’

   1 2 3 4 5 6

   DECLARE @publication AS sysname; DECLARE @subscriber AS sysname; DECLARE @subscriptionDB AS sysname; SET @publication = $(publication); SET @subscriber = $(subscriber); SET @subscriptionDB =$(subscriptionDB);

   1 2 3 4 5 6 7 8 9 10 11

   --Add a push subscription to a transactional publication. USE [AdventureWorks2014] EXEC sp_addsubscription   @publication = @publication,   @subscriber = @subscriber,   @destination_db = @subscriptionDB,   @sync_type= N'reinitialize with backup',   @backupdevicetype='Disk',   @backupdevicename='F:\PowerSQL\AdventureWorks2016PublisherDB.bak',   @subscription_type = N'push',   @update_mode = N'read only';

9. The easiest way to launch the SQL Replication Monitor is from SSMS, as described in the following steps:

   • Using SSMS, connect to the replication SQL Server instance
   • In Object Explorer, locate the Replication folder, right-click the folder, and then click Launch Replication Monitor

   • You can see that the performance is in an excellent state

     

   • If you select the Agents tab, you can see that there is no Snapshot agent created in the entire process.

   

Summary

So far, we discussed the details to reinitialize a SQL Server transaction replication subscription to a publication from a backup, enable the publication to allow initialization from a backup, and then specify backup information when creating the subscription.

This method has a big advantage in terms of bypassing the snapshot generation step and a bulk copy of data over a network but the drawback is that, we have to restore the full database on the subscriber. In this case, you need to perform a deletion of those objects that are not necessary for SQL Server transactional replication.

In the next article, we will see how to build a SQL Server transactional replication solution using the “Schema and data copy only method”.

That’s all for now… Happy replicating!

Table of contents

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021