How to Enable TDE on SQL Server 2019 Standard with Always On Availability Groups for Selected Databases

Protecting sensitive business data at rest is now a core requirement for many organisations. For SQL Server environments running critical production workloads, Transparent Data Encryption (TDE) is one of the most effective built-in security features for encrypting database files, log files, and backups without requiring changes to the application.

In this article, we look at how to enable TDE on two selected databases — DB3 and DB4 — in a SQL Server 2019 Standard Edition environment using Always On Availability Groups, and we cover the most important operational questions:

  • Does TDE require downtime?
  • What is the impact on applications?
  • Does Always On replicate the encryption automatically?
  • Do certificates need to be created on secondary replicas?
  • What are the risks and rollback options?

This guide is especially useful for DBAs and infrastructure teams planning a controlled encryption rollout in a high-availability SQL Server cluster.

What is TDE?

Transparent Data Encryption (TDE) encrypts the physical database files at rest. This includes:

  • Data files
  • Log files
  • Backups

The word transparent is important here. TDE works at the storage level, so applications do not need to be rewritten to use it. Existing queries, connection strings, and application logic continue to work as normal.

This makes TDE an attractive option for organisations that want to improve database security without redesigning the application layer.

The Environment

In this scenario, the SQL Server environment consists of:

  • SQL Server 2019 Standard Edition
  • Always On Availability Groups
  • Five databases:
    • DB1
    • DB2
    • DB3
    • DB4
    • DB5

The requirement is to enable TDE only for:

  • DB3
  • DB4

This selective approach is common when only certain databases contain regulated, confidential, or business-critical data.

Can TDE Be Used with Always On?

Yes, TDE works with Always On Availability Groups. However, one important point is often misunderstood:

Always On does not automatically replicate the TDE certificate or key material to secondary replicas.

The encrypted database can replicate within the availability group, but the certificate and master key used to protect the database encryption key must be manually created or restored on each secondary replica.

This is one of the most critical steps in a TDE deployment with Always On. If the secondary replica does not have the correct certificate and private key, failover or database recovery on that secondary may fail.

Does TDE Require Downtime?

In most cases, TDE does not require application downtime.

The database remains online while SQL Server performs the encryption scan in the background. This means users can usually continue reading and writing data while encryption is being applied.

That said, it is still strongly recommended to perform the change during a planned maintenance window because:

  • the initial encryption scan can increase CPU usage
  • storage I/O can rise significantly
  • response times may temporarily slow down
  • tempdb becomes encrypted once TDE is enabled on any user database in the instance

So while a hard outage is usually not required, a controlled change window is definitely a best practice.

What Is the Impact on Existing Applications?

From an application perspective, TDE is normally transparent.

What usually does not change:

  • connection strings
  • application code
  • SQL queries
  • database schema
  • login/authentication flow

What can change:

  • transaction response times during the initial encryption scan
  • CPU usage on the SQL Server
  • disk throughput and I/O pressure
  • backup and restore handling due to certificate dependency

For most business applications, the biggest impact is temporary performance pressure during the encryption phase, not functional incompatibility.

Key Risks to Understand Before Enabling TDE

Before enabling TDE in production, it is important to understand the operational risks.

1. Certificate loss risk

The TDE certificate is essential. If it is lost, encrypted backups may not be restorable, and recovery of the encrypted database can become impossible.

2. Secondary replica readiness

If the certificate is not restored on the secondary replica, failover may not work correctly for the encrypted databases.

3. Performance overhead during encryption

The initial encryption process scans and rewrites database pages. On large databases, this can create noticeable CPU and disk load.

4. tempdb encryption impact

Once TDE is enabled on one user database, SQL Server encrypts tempdb automatically. This can affect workloads beyond just DB3 and DB4.

5. Change timing

Running TDE enablement on multiple large databases simultaneously can put unnecessary pressure on the server. A phased approach is safer.

Recommended Rollout Approach

A practical and low-risk approach is:

  1. Perform all certificate preparation first
  2. Validate health of the availability group
  3. Take fresh backups of DB3 and DB4
  4. Enable TDE on DB3 first
  5. Wait until encryption completes
  6. Recheck AG health and server performance
  7. Enable TDE on DB4
  8. Perform final validation
  9. Securely store certificate backups offline

Encrypting one database at a time gives you more control and reduces the performance impact on the instance.

High-Level Step-by-Step Process

Step 1: Validate the environment

Before making any changes, confirm:

  • DB3 and DB4 are healthy in the availability group
  • synchronization is normal
  • all replicas are online
  • backups are current
  • there is enough I/O and CPU headroom for the encryption process

Step 2: Create the master key and certificate on the primary replica

On the current primary replica, create:

  • a database master key in the master database
  • a certificate to protect the TDE database encryption key

This certificate will be used for both DB3 and DB4 unless your security policy requires separate certificates per database.

Step 3: Back up the certificate and private key

Immediately after creating the certificate, back it up along with the private key and store it securely.

This is one of the most important tasks in the entire implementation.

Without this certificate backup, future restore or recovery operations may fail.

Step 4: Copy and restore the certificate on every secondary replica

This step is mandatory.

On each secondary replica:

  • create the master key in master if it does not already exist
  • restore or create the same TDE certificate using the backed-up certificate and private key files

Only after this step is complete should TDE be enabled on the primary database.

Step 5: Enable TDE on DB3

Create the database encryption key for DB3 and enable encryption.

SQL Server will begin the encryption scan in the background.

Monitor progress until encryption is fully complete.

Step 6: Validate health and performance

Once DB3 is encrypted:

  • confirm encryption state is complete
  • verify Always On synchronization health
  • check for application latency or server resource pressure

If the environment is stable, proceed to the next database.

Step 7: Enable TDE on DB4

Repeat the same process for DB4.

Again, monitor progress carefully and avoid enabling encryption on both databases at once unless the environment has plenty of spare capacity and the databases are small.

Will Failover to the Secondary Work?

Yes — failover or switchover to the secondary will work, but only if the secondary replica already has the correct TDE certificate and private key.

This is the key rule:

Always On replicates the encrypted database changes, but it does not replicate the TDE certificates.

So if the secondary is properly prepared with the same certificate, failover should work normally. If not, the database may not be usable as expected after role change or recovery operations.

Rollback Plan

A rollback plan should always be defined before enabling TDE.

Before encryption starts

Rollback is simple: stop the change and do not enable encryption.

If encryption has started

In SQL Server 2019, the encryption scan can be suspended if required. This is useful if performance issues arise during the scan.

After encryption is complete

If the business decides to reverse the change, TDE can be disabled and the database can be decrypted. After decryption, new backups should be taken as part of the rollback process.

It is important to note that rollback is not just a technical step. It also involves:

  • validating application performance
  • checking AG synchronization
  • ensuring restore strategy still aligns with certificate handling
  • updating operational documentation

Best Practices for Production Deployment

To make the rollout safer, follow these practical best practices:

Encrypt one database at a time

Do DB3 first, then DB4.

Take fresh full backups before the change

This provides a recovery point before encryption.

Store certificate backups securely

Keep multiple secure copies and document where they are stored.

Prepare all replicas before enabling TDE

Do not assume Always On handles certificates for you.

Schedule the work during low business activity

Even without downtime, the initial encryption scan may affect performance.

Update recovery documentation

Your restore procedures must now include the TDE certificate requirements.

Test failover after implementation

A controlled failover test confirms that the secondary replica has been prepared correctly.

Final Thoughts

Enabling TDE on selected databases such as DB3 and DB4 in a SQL Server 2019 Standard Always On environment is a smart way to improve data-at-rest security without changing the application.

The biggest technical misunderstanding is assuming that Always On replicates everything needed for encryption. It does not. The encrypted database can participate in the availability group, but the master key and certificate must be manually created or restored on every secondary replica.

With the right preparation, TDE can usually be enabled without downtime, with minimal application impact beyond temporary performance overhead during the encryption scan.

For production environments, the safest strategy is simple:

  • prepare certificates on all replicas first
  • encrypt one database at a time
  • monitor closely
  • protect certificate backups carefully
  • validate failover readiness after the change

Done properly, TDE becomes a strong and low-friction security control that protects your databases, backups, and compliance posture without disrupting the business.