Configuring High Availability Replica with SQL Server Availability Groups (TDE Enabled DB)

Setting Up SQL Server High Availability with Availability Groups in TDE Enable Databases

Ensuring high availability for your SQL Server databases is critical for business continuity. One way to achieve this is by configuring Availability Groups. In this blog post, we'll guide you through the process of setting up Availability Groups for a SQL Server database with Transparent Data Encryption (TDE).

Prerequisites

Before we begin, make sure you've met the following prerequisites:

·        Both SQL Server instances must run under the same user account.

·        You have a TDE-enabled database that you want to make highly available.

 

Step 1: Backup the TDE-Enabled Database

-- Take a full backup of the TDE-enabled database

BACKUP DATABASE YourDatabase TO DISK = 'C:\Backup\FullBackup.bak';

 

-- Take separate log backups

BACKUP LOG YourDatabase TO DISK = 'C:\Backup\LogBackup1.bak';

 

Step 2: Backup the TDE Certificate

-- Back up the TDE certificate and private key

BACKUP CERTIFICATE YourCertificate

TO FILE = 'C:\Backup\Certificate\YourCertificate.cer'

WITH PRIVATE KEY (

    FILE = 'C:\Backup\Certificate\YourCertificate.key',

    ENCRYPTION BY PASSWORD = 'YourPassword'

);

 

Step 3: Copy Backups and TDE Certificates to the Replica Server

Copy the database backups (FullBackup.bak, LogBackup1.trn) and TDE certificate files (YourCertificate.cer and YourCertificate.key) to the replica server. Ensure that the SQL Server service account on the replica server has the necessary permissions to access these files.

 

Step 4: Restore the TDE Certificate on the Replica Server

-- Restore the TDE certificate and private key on the replica server

USE master;

CREATE CERTIFICATE YourCertificate

FROM FILE = 'C:\Backup\Certificate\YourCertificate.cer'

WITH PRIVATE KEY (

    FILE = 'C:\Backup\Certificate\YourCertificate.key',

    DECRYPTION BY PASSWORD = 'YourPassword'

);

Step 5: Restore the Database on the Replica Server in No Recovery Mode

-- Restore the database in No Recovery mode

USE master;

RESTORE DATABASE YourDatabase

FROM DISK = 'C:\Backup\FullBackup.bak'

RESTORE LOG YourDatabase FROM  DISK = N'F:\Backup\log.bak'

WITH NORECOVERY;

Step 6: Create an Availability Group on the Primary Server

Now, it's time to create an Availability Group on the primary server. This group will define the high availability configuration for your database. You will need to specify the primary and secondary replicas, network settings, and other relevant configurations.

 

For example:

-- Sample T-SQL script for creating an Availability Group

CREATE AVAILABILITY GROUP YourAvailabilityGroup

WITH (

    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,

    DB_FAILOVER = OFF,

    DTC_SUPPORT = NONE,

    CLUSTER_TYPE = NONE,

    REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0

)

FOR DATABASE YourDatabase

REPLICA ON 'PrimaryServer' WITH (

    ENDPOINT_URL = 'TCP://PrimaryServer:5022',

    FAILOVER_MODE = MANUAL,

    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

    BACKUP_PRIORITY = 50,

    SEEDING_MODE = MANUAL,

    SECONDARY_ROLE(ALLOW_CONNECTIONS = YES)

),

'SecondaryServer' WITH (

    ENDPOINT_URL = 'TCP://SecondaryServer:5022',

    FAILOVER_MODE = MANUAL,

    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,

    BACKUP_PRIORITY = 50,

    SEEDING_MODE = MANUAL,

    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)

);

 

Step 7: Join the Replica to the Availability Group

On the replica server, join the replica to the Availability Group created in the previous step. This step establishes synchronization between the primary and secondary replicas and prepares the database for failover.

-- Join the replica to the Availability Group

ALTER AVAILABILITY GROUP YourAvailabilityGroup JOIN WITH (CLUSTER_TYPE = NONE);

ALTER DATABASE YourDatabase SET HADR AVAILABILITY GROUP = YourAvailabilityGroup;

 

Step 8: Set Availability Group to Read-Only Mode

Finally, you can set the Availability Group to read-only mode through SQL Server Management Studio (SSMS) or by executing the appropriate T-SQL commands. This ensures that read-only operations can be performed on the secondary replica.