Ola Hallengren Script For Distributed Availability Groups Always ON

Hi All,

Today I am going to share Backup Script for Distributed Availability Groups that are being configured with Ola.

I already wrote a post on Always ON how to configure backup jobs using Ola.


We have 3 servers in one Data Center ( 2 of them operated in synchronous and one in Asynchronous) and 3 more in another Data center (2 of them operated in synchronous and one in Asynchronous)

DC1: NODE1, NODE2, NODE3
DC2: NODE4, NODE5, NODE6

As you know we will have one availability group on each data center. So what happens when you leave  with  default settings(prefer secondary). In that case backups will happen on secondary servers on both the data centers when you run the below command the output will be 1. 

SELECT sys.fn_hadr_backup_is_preferred_replica ('DBA_TEST');

In my case it is NODE2 and NODE5.

So why are we discussing this? Will we face any issues? The Answer is yes some times the log backup will fail with the below error message

Log backup for database "DBA_Test" on a secondary replica failed because the last backup LSN (0x00000028:00000993:0001) from the primary database is greater than the current local redo LSN (0x00000028:00000992:0001). No log records need to be backed up at this time. Retry the log-backup operation later. [SQLSTATE 42000]

I am not going to decipher the root cause of this but my focus will be on how to overcome this altogether.

when writing the logic we should consider 2 things.

1) check if distributed availability group is primary or not using the below query

SELECT ag.name, ag.is_distributed, ar.replica_server_name, ar.availability_mode_desc, ars.connected_state_desc, ars.role_desc, 
ars.operational_state_desc, ars.synchronization_health_desc FROM sys.availability_groups ag     
JOIN sys.availability_replicas ar on ag.group_id=ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars
ON ars.replica_id=ar.replica_id
WHERE ag.is_distributed=1



if you see role_desc you will find one of them in Secondary so you don't want your backups to run on secondary.(Forwarded server along with their Secondaries)

2) check on primary distributed availability group if the server is secondary or not as we are running with default (prefer secondary)

Here is the code what I have developed 

1) Checks if the DAG is Primary on that server

2) If the DAG is Primary then check if server is secondary in the Availability Group

Otherwise it won’t backup the database part of the Availability Groups.


DECLARE @DAG VARCHAR(MAX), @AG varchar(50), @primary varchar(50) DECLARE cursor_product CURSOR FOR select s.primary_replica from sys.dm_hadr_availability_group_states s inner join sys.availability_groups g on s.group_id=g.group_id where g.name like '%-DAG%' and s.primary_replica in (select g.name from sys.dm_hadr_availability_group_states s inner join sys.availability_groups g on s.group_id=g.group_id inner join sys.availability_replicas r on s.group_id =r.group_id where r.replica_server_name in ('NODE1','NODE2','NODE3')) OPEN cursor_product; FETCH NEXT FROM cursor_product INTO @DAG WHILE @@FETCH_STATUS = 0 BEGIN BEGIN select @primary = s.primary_replica from sys.dm_hadr_availability_group_states s inner join sys.availability_groups g on s.group_id =g.group_id where g.name=@DAG BEGIN IF @primary != @@SERVERNAME EXECUTE [dbo].[DatabaseBackup] @AvailabilityGroups = @DAG, @Directory = N'\\xxxxxxxxxxxx', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y' END END FETCH NEXT FROM cursor_product INTO @DAG END CLOSE cursor_product; DEALLOCATE cursor_product;

I hard coded the server names but it works perfectly fine.

Thanks for Reading.

Comments

I agree with a lot of the points you made in this article. If you are looking for the Data Center Relocation And Logistics, then visit IT Recycling Solution. I appreciate the work you have put into this and hope you continue writing on this subject.
Kote Easwar said…
This comment has been removed by the author.
Kote Easwar said…
Very good article vamsy...thanks for sharing