Database Backup and Maintenance job to use secondary replica on a SQL Instance with Always On Availability group

Environment

Backup_Setup.PNG

Requirement

-> Database backup should happen on secondary replica.
-> Integrity checks should happen on secondary replica.

Alwayson Availability Group Backup Preference Setting

Backup_Preference.png

Solution

-> We will be using Ola Hallengren’s SQL Server Maintenance Solutions.

-> Execute Maintenance.sql on SQL Server Instance JBSERVER1 and JBSERVER2.

-> On JBSERVER1 modify SQL Server Agent job that performs User Database – Full backup to execute below,

 	EXECUTE [dbo].[DatabaseBackup]
	@Databases = 'USER_DATABASES', -- All User DB's included
	@Directory = 'G:\SQL\BACKUP',  -- Database backup Location
	@CopyOnly='Y', -- Copy only backups
	@BackupType = 'FULL', -- Full database backup
	@Verify = 'Y', -- Verify database backup set to YES
	@CleanupTime = 74, -- Cleanup time of 74 Hours
	@CheckSum = 'Y',@LogToTable = 'Y'

-> On JBSERVER1 modify SQL Server Agent job that performs User Database – Log backup to execute below,

	EXECUTE [dbo].[DatabaseBackup]
	@Databases = 'USER_DATABASES', -- All Appropriate User DB's included
	@Directory = 'G:\SQL\BACKUP',  -- Database backup Location
	@BackupType = 'LOG',-- Log Backups
	@Verify = 'Y', -- Verify database backup set to YES
	@CleanupTime = 74, -- Cleanup time of 74 Hours
	@CheckSum = 'Y',@LogToTable = 'Y'

-> On JBSERVER1 modify SQL Server Agent job that performs User Database – Integrity Check to execute below,

	EXECUTE [dbo].[DatabaseIntegrityCheck]
	@Databases = 'USER_DATABASES', -- All User DB's included
	@AvailabilityGroupReplicas='PREFERRED_BACKUP_REPLICA', -- Preferred backup replica will be checked
        @LogToTable = 'Y'

-> Script the modified jobs in JBSERVER1 and create them on JBSERVER2 also.

-> Try executing each job and make sure the backups and Integrity checks are running on the secondary replica. Check the backup file location and also CommandLog table to confirm.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

Adding Transparent Data Encryption (TDE) Enabled User Database to Always On Availability Group

Environment

TDE_Blog.PNG

Enabling TDE on JBSERVER1


	--Create database JB_DB

	create database JB_DB
	go

	-- Create Master Key for TDE

	USE master;
	GO
	CREATE MASTER KEY ENCRYPTION
	       BY PASSWORD='QKyrk@%wIj$p97';
	GO

	-- Create Certificate 

	USE master;
	GO
	CREATE CERTIFICATE JB_TDE_Certificate
	       WITH SUBJECT='Certificate for TDE';
	GO

	-- Create Encryption key

	USE JB_DB
	GO
	CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256
	ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;  

	-- Enable TDE for database JB_DB

	ALTER DATABASE JB_DB SET ENCRYPTION ON;
	GO

-> Enable TDE for another database.


	--Create database JBS_DB

	create database JBS_DB
	go

        -- Create Encryption key

	USE JBS_DB
	GO
	CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256
	ENCRYPTION BY SERVER CERTIFICATE JB_TDE_Certificate;  

	-- Enable TDE for database JBS_DB

	ALTER DATABASE JBS_DB SET ENCRYPTION ON;
	GO

-> Execute below query and make sure the databases are encrypted.

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
       WHEN '0' THEN 'No database encryption key present, no encryption'
	   WHEN '1' THEN 'Unencrypted'
	   WHEN '2' THEN 'Encryption in progress'
	   WHEN '3' THEN 'Encrypted'
	   WHEN '4' THEN 'Key change in progress'
	   WHEN '5' THEN 'Decryption in progress'
	   WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
	   ELSE 'No Status'
	   END,
	   percent_complete,encryptor_thumbprint,encryptor_type
 from sys.dm_database_encryption_keys

-> This is what I see,

Encrypt_DB.PNG

Backup the Certificate and Private Key

USE master;
GO
BACKUP CERTIFICATE JB_TDE_Certificate
TO FILE = 'C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JB_TDE_Certificate.pvk',
ENCRYPTION BY PASSWORD='QKyrk@%wIj$p97');

Create certificate on JBSERVER2

-> Copy certificate C:\temp\JB_TDE_Certificate.cer and private key C:\temp\JB_TDE_Certificate.pvk from JBSERVER1 to JBSERVER2.

-> Execute below on JBSERVER2.

USE master;
GO
CREATE MASTER KEY ENCRYPTION
       BY PASSWORD='QKyrk@%wIj$p97';
GO

CREATE CERTIFICATE JB_TD_Certificate
FROM FILE='C:\temp\JB_TDE_Certificate.cer'
WITH PRIVATE KEY (
FILE = 'C:\temp\JB_TDE_Certificate.pvk',
DECRYPTION BY PASSWORD='QKyrk@%wIj$p97')

-> Backup a database from JBSERVER1 and Restore it in JBSERVER2 and make sure restore works.

Adding Database JB_DB and JBS_DB to an Existing Alwayson Availability group JB_AG

-> Perform a Full database backup of JB_DB and JBS_DB on JBSERVER1.

-> Perform a Transaction Log backup of JB_DB and JBS_DB on JBSERVER1.

-> Try adding databases JB_DB and JBS_DB to Availability group JB_AG using GUI.

Add_DB.PNG

-> Adding a TDE enabled database using GUI is not possible. We need to use TSQL.

-> Create a database called Test and make sure it is not enabled with TDE. Create availability group JB_AG using database Test. Once the Availability group JB_AG is created on JBSERVER1 and JBSERVER2, Remove Test database from Availability group and delete the database Test from JBSERVER1 and JBSERVER2. This step is to create JB_AG Availability group before performing below steps.

-> Execute below query on JBSERVER1.

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JB_DB]

USE master
GO
ALTER AVAILABILITY GROUP [JB_AG] ADD DATABASE [JBS_DB]

-> Restore Full database backup and Transaction Log backup with NO RECOVERY on JBSERVER2.

-> Execute below query on JBSERVER2 to join the databases JB_DB and JBS_DB to Always On availability group JB_AG


USE master
GO
ALTER DATABASE [JB_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

USE master
GO
ALTER DATABASE [JBS_DB] SET HADR AVAILABILITY GROUP = [JB_AG];

-> Check if the databases are added to Availability group JB_AG.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Application Timeout with Multi-subnet Always On Availability Group

-> I recently worked on an Application timeout issue involving Multi-subnet Always On availability group.

Environment

AG_Multisubnet.PNG

-> Environment consists of two (2) Windows 2016 virtual machines. One server is located in the Primary Datacentre and the second in the Secondary Datacentre.

-> These servers were configured as nodes of a two (2) node Windows Server Failover Cluster (without shared storage). An instance of SQL Server 2016 Enterprise Edition is installed on each server; each instance act as an Always-On Availability Group Replica.

-> The replica in the Primary Datacentre will be a member of an Automatic Failover Set with synchronous commit to the replica in secondary Datacentre. Latency between Primary and secondary Datacentre is less than 2 MS.

-> This environment is configured as a Multi-Subnet environment.

-> Application team advised of intermittent connectivity issue .

Issue

-> The availability group Listener is configured with an IP address from each defined  subnet. This means that the Availability group listener will have an IP address of 192.150.10.15 when it resides in Primary datacentre and 192.150.0.15 when it resides on Secondary datacentre.

-> Client Operating system queries the DNS server to resolve the Listener name to IP address. DNS will return 2 IP address in this environment with one (1) IP address that the subnet currently hosting AG Primary replica will be online and the other IP Address that the subnet hosting secondary replica will be offline. Client application using the AlwaysOn Listener can have connectivity issues while connecting to it.

-> Application tries all IP Address one by one and connects to the one that is online. Since this is done serially, there is high possibility that the application reaches its timeout value and the connection terminates with a timeout error.

Workaround\Fix

-> Adding MultiSubnetFailover parameter to True in Application connection string. When True, Application connection tries all AG Listener IP Address in parallel. This will avoid the connection timeout caused when trying serially.

-> If adding MultiSubnetFailover parameter to the Application Connection String is not possible. Then Below should be performed.

-> Parameter RegisterAllprovidersIP should be set to 0. When RegisterAllprovidersIP is set to 0, only the active Listener IP Address is registered in the DNS. When set to 1 (DEFAULT), all of the IP Address the Listener is dependent on is registered with the DNS.


# Note down the LISTENER name from the output of below command
PS C:\Windows\system32> Get-ClusterResource

#  Replace LISTENERNAME_FROM_OUTPUT with the value noted from above output. Check RegisterAllprovidersIP value and it should be 1
PS C:\Windows\system32> Get-ClusterResource -Name LISTENERNAME_FROM_OUTPUT | Get-ClusterParameter

# Execute below to set RegisterAllProvidersIP to 0
Get-ClusterResource -Name LISTENERNAME_FROM_OUTPUT | Set-ClusterParameter RegisterAllProvidersIP 0

# Check RegisterAllprovidersIP value and see if it set to 0
PS C:\Windows\system32> Get-ClusterResource -Name LISTENERNAME_FROM_OUTPUT | Get-ClusterParameter

-> Parameter HostRecordTTL should be set with a value 60 to 300 from the default value of 1200 (20 Minutes). HostRecordTTL parameter decides how long in seconds the Client operating system will query the DNS for the current IP address. Reducing this value can have an adverse effect on your DNS server if there are several servers connecting to the DNS to resolve the Listener IP Address. Hence it is advised as 60 to 300. Personally, I have set this value to 60 and never seen any issue.


# Note down the LISTENER name from the output of below command.
PS C:\Windows\system32> Get-ClusterResource

# Replace LISTENERNAME_FROM_OUTPUT with the value noted from above output. Check HostRecordTTL value and it should be 1200
PS C:\Windows\system32> Get-ClusterResource -Name LISTENERNAME_FROM_OUTPUT | Get-ClusterParameter

# Execute below to set HostRecordTTL to 60
Get-ClusterResource -Name LISTENERNAME_FROM_OUTPUT | Set-ClusterParameter HostRecordTTL 60

# Check HostRecordTTL value and see if it set to 60
PS C:\Windows\system32> Get-ClusterResource -Name LISTENERNAME_FROM_OUTPUT | Get-ClusterParameter

-> Failover the Availability group to have the above changes take effect.

-> Speak to your DNS team and check for the Listener’s Host (A) record in the DNS Server and confirm that these records are not static.

Testing

-> Once the above tasks are performed. Run a “nslookup <Listener_Name>” on each of the replica and make sure you see the IP Address with respect to the subnet currently hosting AG Primary replica on all your replica. It may take sometime to get the correct IP Address on the replica that is not in the subnet currently hosting AG Primary replica. This depends on the DNS replication schedule. Note down the delay and speak to your DNS and Application team. If they are fine with this delay, I think everything is set. If they are not fine, then your DNS Team will basically have to change the DNS Sync as appropriate.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.