Always on AG

• Admingod

  SSCertifiable

  Points: 5997

  More actions

  August 23, 2020 at 9:16 pm

  #3782735

  I was planing to configure AG between 3 servers. So Primary and Secondary Replica 1 on the same location and then Secondary Replica 2 on DR location.

  Primary Replica - Full, Diff and Log backups & Maintenance Jobs etc.

  Secondary Replica 1 (same location Sync mode for fail over) -  Reporting purposes and data transfer(with alias),Allow some users to do select queries so no one should run any queries in Primary, would this cause and performance issue or blocking?

  Secondary Replica 2 (Asyc mode) - For DR

  Do you guys see any performance issue with SR1 is used for multiple purposes and then it fail over between Primary and SR1 whenever there is windows updates or different reasons. Any advise and thoughts?

• JMartDBA

  Grasshopper

  Points: 21

  More actions

  August 24, 2020 at 3:35 am

  #3782779

  SQLSkills has a great article for you check out if you're considering a readable secondary. Many of the points come down to the business needs.

  • Do you absolutely need real time results from for reports?
  • If your readable secondary is getting slammed, can the hardware handle it (see the RCSI section in the article for example)?
  • If you need an index for a report, you'll need to add that to the primary; this will affect your write speeds during updates on those tables on your primary.
  • If near real-time is good enough, have you considered log shipping as a reporting option with SR1 while keeping your SR2 for DR?

  -John