SQL Server Transactional Replication: 3 Easy Steps

Are you new to SQL Server or interested in using its transactional replication feature to make copies of your database objects?

If this applies to you, then you’ve come to the right blog! This blog will introduce SQL Server transactional replication and provide steps on how to use it.

What is Transactional Replication

• SQL Server supports different types of replication, these include merge replication, snapshot replication, peer-to-peer replication, bidirectional replication, updatable subscriptions and transactional replications.
• In transactional replication, changes that occur in the publisher are delivered to the subscriber as they happen (in near real-time). These changes are also applied to the subscriber in the same order as they occurred in the publisher.
• Replication uses a publishing industry analogy and borrows a few terms from it. These terms are widely used to explain replication concepts and understanding these terms enhance our knowledge of transactional replication.
  • They are:
    • Publisher: This is a database instance which makes data available to other locations. 
    • Subscriber: This is the database instance that receives the data that has been replicated. 
    • Distributor: Database instance that stores specific data that is associated with one or more publishers.
    • Article: This identifies a database object that is in a publication.
    • Publication: A collection of articles from a database.
    • Subscription: A request for a copy of the publication which is delivered to a subscriber. 

Transactional Replication Use Cases

The following are some use cases that necessitate the implementation of transactional replication:

• You want subscribers to get incremental changes as they occur.
• Your application needs to access intermediate data states. For example, if a row changes multiple times, the application will respond to each change and will not just respond to the last change.
• The publisher has a very high volume of update, insert and delete activity.
• Either the publisher or the subscriber is a non-SQL Server database.
• You require low latency between changes made at the publisher and those reflected at the subscriber.

Steps for Transactional Replication

Pre-requisites

• SQL Server (with Replication Installed)
• SQL Server Management Studio (SSMS)

The following are the 3 high-level steps to set up SQL Server transactional replication:

• Distributor Configuration
• Publication Configuration
• Subscription Configuration

1. Distributor Configuration

• Open SSMS and connect to the SQL Server Instance.

• Navigate to Object Explorer, right-click Replication, and click Configure Distribution.

• The Configure Distribution Wizard will appear. Click Next. Leave the default settings and click Next again when you are asked to choose the server that acts as the distributor.

• Choose the storage location for your replication snapshot. This can be a folder on the server or a network share.

• Name your distribution database and specify locations to store the data and log files.

• Under Publishers, select the replication publishers that will access the distributor and select Next.

• Under Wizard Actions, select Configure distribution and click Next.

• Finally review the settings and click Finish.

• After the configuration is complete, you will get a success message.

2. Publication Configuration

• Create a Publication and select the database you intend to be your Publication database.

• Specify the “Transactional publication” option for the publication type.

• Select the required tables and filter as needed.

• Run the snapshot to create it and to send data to the distributor.

• Configure security.

3. Subscription Configuration

• Expand the server node and expand the replication folder.
• Right-click on your publication and select new subscriptions.

• Specify whether it is a pull/push subscription.
• Select your server and create a database. Configure your database.

• Configure security.

• Configure schedule and initialize the publication.

After you have followed the steps, you can right-click on the publication and view the snapshot agent status to ensure that it has been completed.

Transactional Replication vs Change Tracking

• Change Tracking similar to SQL Server Transactional Replication is available in the Standard Edition.
• It is generally recommended that users enable snapshot isolation on the database leveraging Change Tracking and explicitly use it while querying Change Tracking tables.
• This ensures that you don’t miss data or cause any blockage with the Change Tracking cleanup process.
• Apart from this, Change Tracking also incurs additional overhead. For every row that gets modified in a tracked table, a row is also added to the changing table.
• For every transaction that commits, a row is inserted as well into an internal transaction table. So, SQL Server Transactional Replication is deemed better than Change Tracking due to more freedom with isolation tables, having much less custom code to write, and no additional inserts in internal tables.

Transactional Replication vs Change Data Capture (CDC)

• Change Data Capture allows you to write your own polling. Say, if you poll every hour, you just need to pick up the latest version of the row and take it with you.
• Since you don’t need to carry forward every command, it is super-lightweight.
• When you enable CDC for an SQL Server table, SQL Server starts tracking updates, inserts, and deletes in the transaction log similar to the SQL Server Transactional Replication.
• However, when you make any changes they are read from the log and then inserted into “change tables” associated with the tracked tables.
• However, CDC’s whitepaper suggests avoiding scenarios where rows can be updated immediately after insert.
• This comparison highlights that even though SQL Server Transactional Replication commands can get backed up in the distributor, the fact that SQL Server Transactional Replication allows distribution to be offloaded to its own server independent from the publisher represents its strength.
• Hence, due to extra logging caused by internal change tables along with the limitations on schema changes SQL Server Transactional Replication beats Change Data Capture.

Requirements for Supporting Transactional Replication

To support mission-critical data, you need to have the following requirements in place:

• pre-production environment
• root cause analysis
• established on-call rotation
• dedicated SQL Server DBAs
• comprehensive incident response system
• good Change Management Processes

Conclusion

• Transactional replication is a powerful tool for SQL Server. Following these steps will help ensure that you are able to copy and synchronize your data to a target instantly.

Leave us a comment about your experience working with SQL Server transactional replication. Get in touch with us in the comments section below.