1

I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica.

Are there any differences if my Full & Transaction Log Backups are stored on Server A vs Server B?

Are there any differences if my backup retention maintenance plan and job is ran on Server A vs Server B (regardless of where the backups live)?

My current setup is Server A has a SQL Agent job that runs a maintenance plan on Server B to back up my databases to Server B. In that same job on Server A there's a subsequent step that runs a maintenance plan on Server A that cleans up old backups from Server B.

Improve this question
5
  • 1
    The biggest concern I can think of is that databases on server B may not be synchronized. Even if the replicas are synchronous, you can still end up in a situation (such as a server reboot) where the secondary is not in sync, so your backups are simply missing whatever data is still in the log send queue on the primary.
    – Tony Hinkle
    Feb 1, 2020 at 20:52
  • @TonyHinkle oOo this is a very interesting point. I'm new to AlwaysOn AGs so I'm not aware of all angles. Are you saying if there's a server reboot with Server A before the the transaction log is hardened to disk and synchronized to Server B, that is the data that would be lost between backups (specifically transaction log backups).
    – J.D.
    Feb 1, 2020 at 21:17
  • 1
    Yes. If they are set to synchronous, it's not an issue in most cases. But if secondary is down for a while, transactions will be stuck in log send queue on primary until they can be sent. So if you start backups on secondary before all that queue is received, those transactions won't be in the backups. This would be a rare case if primary and secondary are local and synchronous.
    – Tony Hinkle
    Feb 2, 2020 at 1:25
  • Cool, excellent point. So would it be more standard to do the backups on the primary replica's database and also store the maintenance plans and jobs on that server as well?
    – J.D.
    Feb 2, 2020 at 6:14
  • 1
    You want to have backup jobs running on both servers so that backups occur even if one is down. You can use sys.fn_hadr_is_primary_replica('dbname') to check for primary status in the first step of the job and exit or continue accordingly. Ola's backup job will run transaction log backups based on the AG backup preference setting. Third-party backups typically use the AG backup preference as well.
    – Tony Hinkle
    Feb 3, 2020 at 9:33

1 Answer 1

Reset to default
2

You should not have your backup files on the SQL server as you would lose those backup if ever there is an issue with the SQL server.

Best practice would be to store your backup outside of the SQL Server (on a shared folder for example) that can be accessed by both node of the AG.

As the primary node can switch from one or the other node, you should code your backup job to only backup on the "prefered" node (which will be set in your AG).

I would also recommand you to use something else then SQL Maintenance jobs (have a look at : https://ola.hallengren.com/)

Usually, the backup are running on the primary node as you cannot do diff backup on a secondary node and as the secondary node data files could be behind the prod one (even in synchronous mode).

For the cleanup job, you could simply use a powershell script that will delete files from that shared folder.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.