Hi Mates,
One of my friends contacted me saying that she was not able to perform Differential backups on Primary Replica.
I was little surprised to hear this however I quickly asked her to check if they were part of Distributed Availability Groups.
So why you were not able to achieve this? If you understand DAG properly it is nothing but Group of Availability Groups . We can configure this across different domains and clusters.
The Fundamental difference between Always ON AG and DAG is in traditional AG(Single or Multi) there will be only one replica sending log records across all the replicas where as in DAG the primary will send the records to all the replicas in one Data center and to primary replica in another Data Center. In short we call this as forwarder replica and this replica will send the log records to other replicas in the cluster.
Above is the image what I got from her even though it is primary replica in her case as this replica is being part of DAG it is technically secondary replica.
when it comes to Log Backups I wrote an article which helps you in configuring them.
https://www.chiranjeevivamsydba.com/2020/06/ola-hallengren-script-for-distributed.html
In case of full backups if you make use of Ola Hallengren scripts and left with default settings of Backup preference (prefer Secondary) then you will see databases being backed up on both Data centers.
Now the question is Do you want the Full backups to happen on Both Datacenters? If yes then you can live with your defaults. Otherwise you need to add a logic to your jobs so that the full backups only on one Data center.
I am using the same logic of what I made use for Log Backups. First check if DAG is primary or not and if it is then check whether the replica is secondary inside the primary DAG. If so then run the Full backup.
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'\\XXXXXXXXX',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 72,
@CopyOnly='Y',
@CheckSum = 'Y',
@LogToTable = 'Y'
END
END
FETCH NEXT FROM cursor_product INTO
@DAG
END
CLOSE cursor_product;
DEALLOCATE cursor_product;
If you see the above script I am searching for string DAG which means in your environment it can be different. As always you need to follow proper naming conventions to be hassle free.
Comments