Just wondering how everyone manage the TL size on an AG. If we take the TL backups on the Secondary node, the TL size on the primary will keep growing. So is it advisable to take TL backups on the Primary server ?
It depends more on whether your secondary replicas are synchronized or not. If one or more replicas fall behind or stop synchronizing, the transaction log cannot be cleared by a log backup, regardless of which replica you perform the backup on. As long as the replicas are synchronized with the primary, then any replica can do a log backup.
There's more detail here:
Also note that if your AG databases are using replication or Change Data Capture, those processes also read from the transaction log, and may prevent it from clearing/reusing when a log backup is performed. You can find out what's causing log clearing issues via sys.databases, log_reuse_wait_desc column.
Databases are in synchronized mode. There is no replication or CDC. Its a heavily used database. We need to keep the transaction log manage .In our current environment (sql 2014 cluster ), we are taking TL backups at every 10Mins, and want to make sure we can manage the log size when we migrate to an AG. So here is what I am trying to figure out
- if we take TL backups on the primary, will the TL size on the secondary sync (assuming there are no active transactions )
- In case we take the backup only on the secondary, the TL on primary will continue to grow ?
- What if we take the TL backups on both primary and secondary ? will it mess up the TL chains ? I'm just curious
We are planning to use AG, no test servers to test these yet ?
Short answers:
-
Backups on either replica won't affect log file size
-
No, see above
-
No, see below
I would suggest testing the AG first on a non-production system so you can try each backup scenario and understand their side effects. As the document link indicated, you can make transaction log backups on any replica, as long as it's in synchronizing or synchronized state.
You don't need to use a backup of a production DB, just something that you can use to generate write activity and take multiple log backups, before and after you join it to an availability group. Also practice failing over the AG, and setting replicas offline, and watching how the log file will grow.
If you have a replica that is offline/not synchronizing, you can't make a log backup there, and any log backup you make will not clear/reuse the transaction log file, so the log will grow until you get that replica synchronizing, or you remove it from the AG. This is a very good scenario to test so you can get a feel for how to resolve it, without the pressure of it happening in production. It's very easy to freak out when sync fails and the log file is growing, I've been there.
Transaction log backups (all backups, actually) are independent of whether you're using availability groups or not. They will not break the log chain no matter how many you make or how often. As long as you avoid changing the database recovery model, and you're not upgrading the SQL Server instance to a new version, all of your backups will preserve the log chain and can be restored in sequence.
Naturally, you shouldn't assume that. Always test your backups by restoring them periodically. If you find that your log chain is broken you should make another full database backup as soon as possible.
Transaction log file size is a topic of its own, and if you want to avoid problems you should size your transaction log to be as large as necessary, but leave some room for growth. You also need to have enough free space on the disks to allow for such growth. You should also consider setting a max size for the transaction log file, as letting it fill the disk can cause additional problems.
If you are planning to set up an AG on two or more machines with different sized drives, reconsider. You want the two systems to be as identical as possible, or failing that, that the lesser/least replica system has enough space/CPU/RAM to serve as your primary replica for production workloads. Do not shrink your log files on any regular basis.
Apologies if you're already aware of this, just want to be informative.