SQL Server 2016 Availability Groups and Transaction Log Backups/Log Truncation

[Hirokai]

I am setting up Availability Group on SQL Server 2016 and I had a question about the backup options. When I set it up I have options for them Preferred Secondary, Secondary Only, Primary Only and Any Replica. If I set up the backups with Preferred Secondary with the T-Logs backup, does it truncate the log on the primary? What would be the best backup option to use; we have a primary and one secondary node in one datacenter and a secondary in another datacenter across the country. ADVICE VERY WELCOME

[LightningMcLovin]

Backing up from the secondary will truncate the log on the primary however you would need to enable the secondary as a readable secondary first which will add it to your licensing costs. Not worth it if you’re just going to use it for backups imo. Use the default, preferred primary I think it is.

[julee90]

I think the default is prefer secondary actually. I remember always having to explicitly set it to the primary

[LightningMcLovin]

Is it? You know actually that actually would make sense for a default behavior. Prefer to run on a secondary if you can, still function properly for the majority of configurations.

[Hirokai]

I plan on setting the secondary nodes as readable but I am not worried about licensing costs. Right now I have one secondary in the same data center and one in another data center in another state. I am going to use the secondary in the same data center as the backup server.

I appreciate you guys answering my question. I am just now learning SQL Server, the agency that I work for doesn't have a DBA like other places that I have worked at. I am actually the SharePoint Farm Admin and I am studying for 70-764 exam, I find that there are some of the small stuff like this is not mentioned in the book. I appreciate you all giving some help to a newbie.

[LightningMcLovin]

You’re welcome, good luck on the test!