Transactional Replication is a powerful feature in SQL Server that allows you to synchronize changes made to a database across multiple servers. It is particularly useful when you need to replicate data in real-time or near real-time to ensure data consistency across different locations or for reporting purposes.

The main idea behind Transactional Replication is that you have a Log Reader Agent on your Publisher SQL Server database running, which analyzes the Transaction Log File and synchronizes changes on specific Articles through a Distribution database down to your Subscription databases.

When setting up Transactional Replication through SQL Server Management Studio, you can initialize the Subscription database through a Snapshot. A snapshot is a point-in-time “picture” of your database, which is dumped out to a file share. However, when dealing with Very Large Databases (VLDBs), creating and transferring a snapshot can be time-consuming and impractical.

Fortunately, there is an alternative option to initialize a Subscriber from a backup file. This option is not available through SQL Server Management Studio, but it can be implemented using the Replication Stored Procedures that are used in the background by SQL Server to implement the actual Replication.

In this blog post, we will walk through a step-by-step tutorial on how to initialize a Transactional Replication from a backup file.

Step 1: Creating the Publisher Database

First, we need to create a new database that we want to use with Transactional Replication. Let’s call it “PublisherDatabase”.

USE master
GO

-- Create a new database that we want to use with Transactional Replication
CREATE DATABASE PublisherDatabase
GO

USE PublisherDatabase
GO

-- Create a new table that we want to replicate
CREATE TABLE Foo (
    Col1 INT IDENTITY (1, 1) PRIMARY KEY NOT NULL,
    Col2 CHAR (1000) NOT NULL,
    Col3 CHAR (1000) NOT NULL
)
GO

-- Insert 1000 records
INSERT INTO Foo VALUES ('xyz', 'xyz')
GO 1000

-- Retrieve the inserted records
SELECT * FROM Foo
GO

-- Make an initial full database backup
BACKUP DATABASE PublisherDatabase TO DISK = '\\dc\temp\PublisherDatabase.bak'
GO

-- Insert another 1000 records
INSERT INTO Foo VALUES ('xyz', 'xyz')
GO 1000

-- Retrieve the inserted records. The last 1000 records are not part of the initial full database backup.
SELECT * FROM Foo
GO

In this example, we create a simple table called “Foo” and insert several records into it. We then create a full database backup and insert additional records that are not part of the initial backup.

Step 2: Creating the Publication

Next, we need to create a Publication on the Publisher. This can be done through the UI provided by SQL Server Management Studio or by scripting out the creation of the Publication.

USE PublisherDatabase
GO

-- Adding the transactional publication
EXEC sp_addpublication @publication = N'FooPublication',
    @description = N'Transactional publication of database ''PublisherDatabase'' from Publisher ''SQL2008HADR1''.',
    @sync_method = N'concurrent',
    @retention = 0,
    @allow_push = N'true',
    @allow_pull = N'true',
    @allow_anonymous = N'false',
    @enabled_for_internet = N'false',
    @snapshot_in_defaultfolder = N'true',
    @compress_snapshot = N'false',
    @ftp_port = 21,
    @allow_subscription_copy = N'false',
    @add_to_active_directory = N'false',
    @repl_freq = N'continuous',
    @status = N'active',
    @independent_agent = N'true',
    @immediate_sync = N'false',
    @allow_sync_tran = N'false',
    @allow_queued_tran = N'false',
    @allow_dts = N'false',
    @replicate_ddl = 1,
    @allow_initialize_from_backup = N'false',
    @enabled_for_p2p = N'false',
    @enabled_for_het_sub = N'false'
GO

-- Add the Snapshot Agent for the Publication
EXEC sp_addpublication_snapshot @publication = N'FooPublication',
    @frequency_type = 1,
    @frequency_interval = 1,
    @frequency_relative_interval = 1,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 8,
    @frequency_subday_interval = 1,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0,
    @job_login = null,
    @job_password = null,
    @publisher_security_mode = 1
GO

-- Add an Article to the Publication
EXEC sp_addarticle @publication = N'FooPublication',
    @article = N'Foo',
    @source_owner = N'dbo',
    @source_object = N'Foo',
    @type = N'logbased',
    @description = null,
    @creation_script = null,
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509F,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'Foo',
    @destination_owner = N'dbo',
    @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dboFoo',
    @del_cmd = N'CALL sp_MSdel_dboFoo',
    @upd_cmd = N'SCALL sp_MSupd_dboFoo'
GO

In this example, we create a Publication called “FooPublication” and add the table “Foo” as an Article to the Publication. We also configure the Snapshot Agent for the Publication.

Step 3: Allowing Initialization from Backup

After creating the Publication, we need to change a setting to allow initialization from backup files for subscribers. This setting is not available through SQL Server Management Studio and needs to be changed through the UI of SQL Server Management Studio.

To change this setting, go to the Properties window of the local publication on the Publisher, navigate to the “Subscription Options” page, and set the option “Allow initialization from backup files” to “True”.

Step 4: Creating the Subscription

Now we are ready to create the Subscription. This involves making changes on both the Publisher and the Subscriber.

On the Publisher, execute the following T-SQL code to create the Subscription:

USE PublisherDatabase
GO

-- Add the Subscription on the Publisher
EXEC sp_addsubscription @publication = N'FooPublication',
    @subscriber = N'sql2008hadr3',
    @destination_db = N'PublisherDatabase',
    @sync_type = N'initialize with backup',
    @backupdevicetype = 'Disk',
    @backupdevicename='\\dc\temp\PublisherDatabase_Tran1.trn',
    @subscription_type = N'pull',
    @update_mode = N'read only'
GO

In this example, we add a Subscription to the Publication on the Publisher. We specify that we want to initialize the Subscribers through a database backup by setting the parameter @sync_type to “initialize with backup”. We also provide the last backup that we restored on the Subscriber through the parameter @backupdevicename.

Next, on the Subscriber, execute the following T-SQL code to create the Pull Subscription:

USE PublisherDatabase
GO

-- Add the Pull Subscription
EXEC sp_addpullsubscription @publisher = N'SQL2008HADR1',
    @publication = N'FooPublication',
    @publisher_db = N'PublisherDatabase',
    @independent_agent = N'True',
    @subscription_type = N'pull',
    @description = N'',
    @update_mode = N'read only',
    @immediate_sync = 0
GO

-- Add the Pull Subscription Agent
EXEC sp_addpullsubscription_agent @publisher = N'SQL2008HADR1',
    @publisher_db = N'PublisherDatabase',
    @publication = N'FooPublication',
    @distributor = N'SQL2008HADR2',
    @distributor_security_mode = 1,
    @distributor_login = N'',
    @distributor_password = null,
    @enabled_for_syncmgr = N'False',
    @frequency_type = 64,
    @frequency_interval = 0,
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0,
    @frequency_subday_interval = 0,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 20120805,
    @active_end_date = 99991231,
    @alt_snapshot_folder = N'',
    @working_directory = N'',
    @use_ftp = N'False',
    @job_login = null,
    @job_password = null,
    @publication_type = 0
GO

In this example, we add the Pull Subscription on the Subscriber and configure the Pull Subscription Agent.

Step 5: Finalizing the Replication

After completing these steps, you have a fully working Transactional Replication that was initialized by a database backup instead of a snapshot. This approach can be advantageous for VLDBs as it avoids the need to transfer a large snapshot over the network.

However, it’s important to note that when initializing from a backup, the whole database is restored on the Subscriber, including unnecessary data. If you are replicating Articles with filters, you will need to manually delete the unnecessary data on the Subscriber.

Transactional Replication is a powerful feature in SQL Server that allows you to keep your data synchronized across multiple servers. By understanding the concepts and following the steps outlined in this blog post, you can successfully implement Transactional Replication using a backup file for initialization.

Thank you for reading!

– Your Name