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