Powershell Backup against AG Database Not Hitting Secondary Replica

Question

question

Ryancerney asked Jun 11, '18

Powershell Backup against AG Database Not Hitting Secondary Replica

I am using AG's in SQL 2016. I have a primary and secondary replica, Server1 and Server2 respectively. I am automating a restore process from prod to our test and dev environments via powershell. Something I noticed today is that when I check the last backup time in the database, the backup is going against the primary replica and not the secondary, even though I have the AG configured to only use secondary for backups. I am running a full backup through powershell using the Backup-SqlDatabase command. I am specifying the listener name for the server name with the backup command. What am I missing or misunderstanding? I was hoping to only hit the secondary due to resources and such, one of the benefits of AG with Enterprise right? 03' below is the primary, 07' is the secondary for reference

backup availability-groups

ag-backup.jpg (69.0 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-06-18T07:11:19Z

Shawn_Melton answered Jun 18, '18

I do not believe that Backup-SqlDatabase supports AG backup preferences. Even if you point it at the listener name the backup process itself has to validate that configuration and the PowerShell name will not do it.

If you are wanting to backup the databases via PowerShell you will have to add some logic to check the backup preference and then adjust your connection to that secondary replica that holds that role.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ryancerney commented · Jun 18, 2018 at 01:22 PM

Thanks Shawn. I think I'll add a check using sys.fn_hadr_is_primary_replica and then set my instance for backup based off that.

0 ·

question