This post is about turning on TDE encryption with Certificates on AlwaysOn Availability Groups Servers with Automatic Failover.

Step 1. Use the master key and certificate I created in the MSSQL Mirroring guide.

Copy master key and certificate to C:\CERTS onto our new AlwaysOn SQL servers nfsec-alwayson-primary and nfsec-alwayson-secondary.

Step 2. Restore the master key and certificate on both servers.

Now, restore master key and certificate on our new AlwaysOn SQL servers nfsec-alwayson-primary and nfsec-alwayson-secondary.

USE MASTER;  
RESTORE MASTER KEY  
    FROM FILE = 'C:\CERTS\principal_key.key'
    DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'
    ENCRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke';
GO

USE MASTER  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'  
CREATE CERTIFICATE AG_cert  
  FROM FILE = 'C:\CERTS\principal_cert.cer'
  WITH PRIVATE KEY (FILE = 'C:\CERTS\principal_cert.key',
  DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke');
GO

USE MASTER  
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'H0tSh1tS3cUre/ToOh4rDto-Br4ke'  
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY  
GO  

Step 3. Turn on TDE encryption ON on the nfsec-db-primary node NFSEC-AG-SQL database.

Turn TDE encryption ON on a database.

ALTER DATABASE NFSEC-AG-SQL SET ENCRYPTION ON  

Step 4. Take a backup on nfsec-db-primary and restore to nfsec-db-secondary.

On the Primary Replica instance nfsec-db-primary, create a full database and transactional backup of the TDE encrypted database NFSEC-AG-SQL.

USE master  
GO  
BACKUP DATABASE NFSEC-AG-SQL TO DISK = 'C:\NFSEC-AG-SQL_full.bak';

BACKUP LOG TDE_DB TO DISK = 'C:\NFSEC-AG-SQL_log.trn';  

Restore the full backup and transactional log on the Secondary Replica nfsec-db-secondary with NORECOVERY.

USE master  
GO  
RESTORE DATABASE NFSEC-AG-SQL FROM DISK = ‘C:\NFSEC-AG-SQL_log.bak’ WITH NORECOVERY;

RESTORE LOG NFSEC-AG-SQL FROM DISK = ‘C:\NFSEC-AG-SQL_log.trn’ WITH NORECOVERY;  

Step 5. Prepare SQL servers for AlwaysOn Availability Groups setup.

Next create endpoints on both new servers giving them permissions for the domain SQL user [NFSEC\sqlservice].

USE [master]  
GO  
CREATE ENDPOINT HADR_ENDPOINT  
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATA_MIRRORING (ROLE=ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

USE [master]  
GO  
GRANT CONNECT ON ENDPOINT::HADR_ENDPOINT TO [NFSEC\sqlservice];  
GO  

Step 6. On the Primary Replica nfsec-db-primary create an Availability Group.

T-SQL script to create* AlwaysOn Availability Group on Primary Replica **nfsec-db-primary.

CREATE AVAILABILITY GROUP [nfsec-alwayson-sql]  
   FOR 
      DATABASE [NFSEC-AG-SQL] 
   REPLICA ON 
      'nfsec-alwayson-primary' WITH 
         (
         ENDPOINT_URL = 'TCP://nfsec-alwayson-primary.nfsec.local:5022', 
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = AUTOMATIC
         ),
      'nfsec-alwayson-secondary' WITH 
         (
         ENDPOINT_URL = 'TCP://nfsec-alwayson-secondary.nfsec.local:5022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC
         ); 
GO  

Step 7. Create AlwaysOn Listener.

T-SQL script to create AlwaysOn Listener.

USE [master]  
GO 

ALTER AVAILABILITY GROUP [nfsec-alwayson-sql]  
ADD LISTENER N'nfsec-alwayson-listener' (  
WITH IP  
((N'192.168.1.100', N'255.255.255.0')), PORT=1433); 
GO  

Step 8. On the Secondary Replica nfsec-db-secondary join the replica to the existing Availability Group - nfsec-alwayson-sql.

Join the Secondary Replica nfsec-db-secondary to the existing Availability Group nfsec-alwayson-sql.

ALTER AVAILABILITY GROUP [nfsec-alwayson-sql] JOIN;  
GO  

Step 9. On the Secondary Replica nfsec-db-secondary join the database to the existing Availability Group - nfsec-alwayson-sql.

Join the Secondary Replica database NFSEC-AG-SQL to the existing Availability Group.

ALTER DATABASE [NFSEC-AG-SQL] SET HADR AVAILABILITY GROUP = [nfsec-alwayson-sql];  
GO  

Adding additional databases to the Availability Group

To add more databases to availability group is easy enough. Backup the database and transaction log from nfsec-db-primary and restore them to nfsec-db-secondary with NORECOVERY.

Add the databases to the existing Availability Group on nfsec-db-primary.

USE master  
GO  
ALTER AVAILABILITY GROUP [nfsec-alwayson-sql] ADD DATABASE [NFSEC-NEWDB]  

Join the database to the secondary replica instance nfsec-db-secondary.

USE master  
GO  
ALTER DATABASE NFSEC-NEWDB SET HADR AVAILABILITY GROUP = [nfsec-alwayson-sql];