Transactional Replication Use Cases

This blog post is a part of a series on SQL Server Transactional Replication.  If you want to find the other posts in the series, check out the main page for the series here.

Why Transactional Replication?

Why in the world would you want to use Transactional Replication?  Isn’t that the thing that is impossible to manage, difficult to configure, and always prone to problems? As a consultant, I see a lot of scenarios where every problem becomes a nail because all you have is a hammer. Sometimes another technology can provide a solution to problems that exist but little is known about the technology, and Transactional Replication tends to fall into this scenario in my experience. In this post we are going to take a look some of the more common Transactional Replication use cases as well as some scenarios where it can be used to solve a business problem in a different way. As with anything, Transactional Replication is just another tool to have in your toolbox.

Offloading Read-only Workloads

Yes today we have Availability Groups and Readable Secondary capabilities, and I’ve already written a blog post Availability Group Readable Secondaries – Just Say No that outlines the challenges you might have with that approach. If you don’t need the entire database for read-only queries, you can minimize the data-set sizes by using transactional publications for only those articles/tables that are needed and keep the size of the readable copies as small as possible. You can also index those specifically for the readable workload and don’t have to maintain excess indexes on the writable copy in the publisher. The best thing about Transactional Replication is that it doesn’t require Enterprise Edition licenses to use on the subscribers, they can be Standard Edition as long as the feature set you need and hardware limitations of Standard Edition apply.

Reporting and ETL operations

Reporting and ETL operations can be taxing on source systems due to the potential volumes of data that might be being read. By moving these operations off to a transactional subscriber, it is possible to remove that load entirely from the production publisher. In addition to this, it is possible to customize the subscriber side with auditing/logging triggers to mark records that have been changed to allow for targeted incremental ETL operations that only handle the most recently changed data. In some cases Indexed Views can be created on the Subscriber database to pre-aggregate the data and reduce the size of the data set that has to be processed, improving reporting performance times without blowing up the size of the primary publisher database or impacting it’s performance.  However, you need to be careful with this and test changes to the subscriber for impacts to the distribution agents ability to replicate changes with low latency.  More than once I have worked with a client having replication performance issues where the root of the problem was due to Indexed Views, Full-Text Indexing and other additions that were made to the subscriber database without testing their impacts.

Isolating client data in a multi-tenant design

Multi-tenant databases are fairly common in today’s IT infrastructure, especially with many vendors providing software as a service type solutions for clients, or even having clients that support multiple locations with a single database solution. When a client needs a copy of all of their data for purposes outside of the normal application use, splitting that data off can be a timely process and is usually accomplished using ETL operations that build the same database schema, but then load 100% of the single clients data into that database to provide a backup copy of just that clients information. If this is a routine operation, moving 100% of the data each time is inefficient and creates impacts to the production database that can be removed through a filtered transactional publication. With a subscriber database for each filtered client, the time to provide a new backup copy of the data is just the time it takes to make a backup of the subscriber database, simplifying the process and making it much faster.

Generating Testing Databases

Many large production databases are commonly backed up and restored to test environments for development purposes, but in some cases, there are auditing and other tables that are not needed in the development environment and these can take a significant amount of storage on disk. I recently worked with a client database that was 10TB in size due to internal auditing tables that held nearly 8TB of the data that were immediately truncated on restore to a development server. However, this required 10TB of storage to be available just to get the backup restored. To solve this, a transactional publication of every table but those Audit tables was created to a subscriber that then became the source for development backups to be restored from. Once restored the database only took 2TB of space and allowed 30TB of storage to be recovered from the test environment for other uses since it had only been provisioned to allow the restores to happen and then get truncated.  Now before you say those tables should be in a separate database, that could be the case but it’s not how things are currently deployed or implemented for this client and you can’t have referential integrity across databases for the auditing tables.  They just don’t need the audit data to exist in a testing environment during testing, and this is an alternative means of saving a lot of storage given their current implementation.

Transactional Replication as an HA Strategy?

This one can certainly generate some debate, and I’m not the first person to blog about using Replication as a part of a HA strategy with SQL Server, Paul has actually blogged about it here.  However, depending on the business requirements, the design of the application, and the SLA’s, replication can absolutely be used as a part of a HA strategy for a database.  My favorite deployment of replication for HA purposes is for read intensive workloads using multiple subscribers behind a network load balancer like a Big IP F5 or Citrix Netscaler.  You can even leverage features like Datastream to do Content Switching and route connections to specific SQL Servers based on the data contained in the TDS stream.  While transactional replication alone is not a total HA solution, it can absolutely be a part of a HA strategy for SQL Server.

Understanding SQL Server Replication

This blog post is a part of a series on SQL Server Transactional Replication.  If you want to find the other posts in the series, check out the main page for the series here.

SQL Server Replication is one of the components of SQL Server that often has a bad reputation for being difficult to manage and prone to errors and problems.  However, when configured and managed properly, replication often just works and requires very little effort to maintain.  Replication is really easy to use and configure once you understand the basics behind how it works and the various components involved, and it’s been a part of SQL Server for over 20 years with very minimal changes and many other features piggy back on the foundation provided by replication.

There are at least three databases that participate in any replicated topology:

  1. Publisher – the main copy of the database that reads and writes can be issued against
  2. Distributor – the central database behind replication where log operations are written from the publisher and read for distribution to subscribers
  3. Subscriber – the database that receives changes made on the articles configured for replication on the publisher

The terminology for replication is based on a publishing industry terminology for print media like a magazine or newspaper.

  1. Publisher can produce one or many publications
  2. Publications contain articles
  3. Articles represent a specific object being published
  4. Subscribers subscribe to a publication to receive the changes to all of the articles within that publication

There are a few more advanced features of replication that go outside of these basics, but for a majority of use cases for replication, just understanding these simple terms is all that is necessary to plan out and build a configuration that meets a majority of business requirements.

The primary functionality provided by replication is handled by three specific agents, that run on the different servers to perform specific tasks:

  1. The Log Reader Agent reads the transaction log records from the publisher databases transaction log that are marked for replication and writes those commands into the distribution database.  The great thing about replicated databases is that they do not require FULL recovery model to be able to publish the changes.  The changes are marked for replication and until the log reader agent has successfully processed those commands to the distribution database they have to remain in the transaction log.
  2. The Snapshot Agent generates a snapshot of the articles as an initialization point for replicating the articles to new subscribers.  This is accomplished through BCP operations that write the contents of the tables out to files on disk that can then be used to create and load the data on new subscribers to initialize the data for further application of commands.
  3. The Distribution Agent applies any snapshots required to initialize the subscriber and then executes commands stored in the distribution database to apply changes to the subscriber database to replicate the data changes happening at the publisher

Since replication is based off of reading the transaction log of the publisher, it is an entirely asynchronous process and has minimal impact to transactional throughput of the source system.  However, this also means that there is latency between when a change is made, and when that change becomes available on a subscriber, but this is typically only a few seconds end-to-end.

Securing Data in SQL Server

Recently I have been having quite a few discussions around security of data inside of SQL Server and how to prevent the massive data breaches that we have been hearing about on the news.  Like most things some people want “THE ANSWER” or THE SOLUTION to securing the data inside of SQL Server. Unfortunately there isn’t a single solution that solves all of the problems that are potentially out there.  Security of data requires defense in depth, starting with a secure configuration and installation of SQL Server.  Often times, defense in depth also means changes to the database, the application, and how you do business.

Features for Securing Data

SQL Server offers multiple options and features that help with securing data and since SQL Server 2016 Service Pack 1, many of them are available in Standard Edition:

  • SSL/TLS Protocol Encryption

    • SQL Server 2005+
    • Uses a SSL certificate to encrypt the network connection between clients and SQL Server, securing data from watching over the wire
    • Can be forced by SQL Server during the handshake when certificate properly installed on SQL Server certificate store
    • Only requires a SSL certificate from a CA for Server Authentication to implement
    • Requires certificate rotation before expiration
    • Generally does not require application changes but may under specific conditions
  • Database Column Level Encryption

    • SQL Server 2005+
    • Uses a certificate or key to encrypt a column securing data from being queried without knowing how to decrypt the values
    • Data remains encrypted at the column level even while the database is online
    • Encryption keys are maintained inside of the SQL Server encryption hierarchy and must be opened properly to decrypt data
    • May require application changes to support encryption
      • Not required if handled by stored procedure access to data but opens risks of decrypted data in process memory
    • Performance impacts when comparing column data in WHERE clause
      • May be mitigated by encrypting value to compare first and performing binary filtering of encrypted values
  • Transparent Data Encryption

    • SQL Server 2012+
    • Encrypts data at rest using database encryption key stored in the boot record of the database and a certificate stored in master
    • Prevents someone copying files or stealing a database backup from restoring the data without the certificate
    • Does not require application changes for securing data at rest
    • Does not protect data from being queried through SQL Server once the database is open
    • If someone has access to the master database or a backup of master, they can get the certificate that encrypts other backups (
    • Without the certificate that encrypts the database you cannot recover from a disaster
    • Certificate expiration is not enforced or checked and does not require certificate rotation once used for encryption
  • Backup Encryption

    • SQL Server 2014+
    • Encrypts the backup file using a certificate or asymmetric key, securing data backups from being restored
    • Prevents someone stealing a database backup from restoring the data without the certificate or key
    • Same potential risks as TDE since certificate or key is stored in the master database
  • Always Encrypted

    • SQL Server 2016+
    • Data remains encrypted at the column level even while the database is online
    • Column encryption keys are stored in the database to encrypt data
    • Column master keys are used to encrypt the column encryption keys in the database and are maintained outside of SQL Server and are not available to the DB, securing data from DBAs
      • Requires installing column master keys for SSMS to query and decrypt data – Windows Certificate Store, Azure Key Vault, or HSM
    • Requires application changes to support the encryption
    • Protects data in use from memory dumps and maintains encryption

How do we apply this for Securing Data?

Generally speaking, the first thing that we need to define is what specifically we are trying to protect against?  If we need to prevent someone from monitoring  or intercepting network packets containing data in clear text then we need to implement protocol encryption for connections.  If we are concerned about someone opening the database on another system or stealing a copy of the database or backups using TDE might be a good solution, but only if we also maintain a defense in depth strategy that separates our backups of master and the certificates used by TDE from the database backup files.  If we don’t separate our backups then TDE is very easy to hack around and is simply checking the box without actually securing our data.  If we want to protect the data at rest and from prying eyes while open, then we need to implement some form of column level encryption of the data, whether that is key based inside of SQL Server or using Always Encrypted. The important thing is that there isn’t a single solution that is going to protect every situation, you have to consider the risks and what a specific feature is designed to protect against and then layer them together to meet the requirements. Even then you might not be protected.

Take as an example, the Microsoft Customer Support Database data exposure that happened December 2019 and Microsoft provided public information about after securing the data.  While we don’t know all the security measures that were in place specifically on the database level, none of the above features would have protected the data from being publicly viewable through an application level security misconfiguration. As a part of any data security discussion, auditing and regular reviews of business practices, and any changes, have to be implemented to ensure that the data remains secured.