March 9, 2022 at 1:10 pm
I am an admin with limited AG experience. I've only set up fairly simple layouts and groupings. I'm about to embark on a complete rebuild of a SQL environment that has to include a 2-node failover with an accompanying reporting server. I will be creating about 10 of these sets. Rather than have a reporting server that I replicate to, how can I leverage AG? Is what I'm describing possible? Can I attach a readable/readonly node to a 2-node failover? And the failover will never try to make that readable node the primary?
March 9, 2022 at 2:06 pm
Yes this is totally possible, but does require you to have Enterprise edition of SQL Server in play.
You would setup the AG as normal, and set the 3rd node as a async manual failover replica instead so that it never plays a part in failover (unless you force it to become primary) and it will not hinder performance on the write primary due to waiting for hadr_sync_commit and hardening of the transactions etc.
If you want to reconfigure the app to use read offloading you can setup read-only routing and change the application to also read from secondary, write to primary, or if its just the reporting you can direct it just to the machine which is only read-only.
If you have standard edition though you cannot do this so you would need to fork out for Enterprise licenses or you would have to use something like replication or log shipping instead.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply