3

I am working on setting up a secondary copy of our prod DB for Data Warehousing and even something we can possibly use to offload other processes and reporting on. Basically just a read only copy of prod. Issues is, I am running into options that breaks our point in time backup strategies and that can reliably handle 20 TB of data.

After reading this post, Always On Availability Groups looks promising. But I have a few questions and I am new to DBA and most of Microsoft's documentation on the subject is another language to me at this point.

  1. Does Always On Availability Groups mean the database will basically be mirrored and that I can use that server to offload the heavy reporting to?
  2. Does that break point in time transaction logs backup of the prod server?
  3. Can this cause a strain on the prod server?
  4. Will this cause prod traffic to be redirected to this server? I do not want this to become HA DB server, just want a read only copy of a few prod DBs.
  • 1
    I would recommend reading the new documentation on SQL Server...most of the community have ensure it is more readable than what was formally known as "BOL". Overview of Availability Groups – user507 Feb 14 '18 at 19:14
  • Good good good question! – DmitryBoyko Jul 26 '18 at 8:52
4

1.Does Always On Availability Groups mean the database will basically be mirrored and that I can use that server to offload the heavy reporting to?

It'll be a full copy of the database. You can think of it as mirrored but it's not really the same thing.

2.Does that break point in time transaction logs backup of the prod server?

No.

3.Can this cause a strain on the prod server?

It might. This will depend on many different items such as latency and bandwidth available, server hardware, transactional load (and style), commit mode, etc. Sounds like you'd want Async, but please note that there is no specific guarantee on data freshness which will depend on latency, redo, and when the query starts.

4.Will this cause prod traffic to be redirected to this server? I do not want this to become HA DB server, just want a read only copy of a few prod DBs.

If that's the case, and you are either running 2017 or can upgrade to 2017, there are read scale availability groups where you don't need a cluster.

| improve this answer | |
  • +1 on the read scale groups! This sounds perfect, just don't know if our software will let us upgrade. Our prod DB is 2014 however. Do you think this would still apply to a 2017 server with the DBs in 2014 compatibility mode? I don't see an answer to this in the doc. If not, I can test this theory out. – alexander7567 Feb 15 '18 at 0:20
  • 1
    @alexander7567 Compatibility mode doesn't make it look and act like 2014, it changes some of the database and query optimization settings - I don't know if it'd work or not... but generally, if it works on 2014 it'll work on 2017 (from the work I've done) – Sean Gallardy - Retired User Feb 15 '18 at 0:47
4
  1. The secondaries can be used as read only. Keep in mind data is still being added to the secondaries in near real time.

  2. Transaction log backups are conducted against the primary AG instance.

  3. If the secondary can not keep up with the volume of transactions coming across, it can cause the transaction logs on the primary to fill and continue to grow.

  4. In the event the primary becomes unavailable, traffic would then transfer to the secondary. This is the point of Availability Groups.

If you just want a copy of the database to run reports against, you might look into setting up log shipping to another instance. It's a cheaper, and simpler solution than AGs.

| improve this answer | |
  • +1 to log shipping : sqlperformance.com/2014/10/sql-performance/… – Aaron Bertrand Feb 14 '18 at 23:38
  • Great tips. Thanks! Only thing I have against log shipping (woild be my preferred solution) is the fact it effects our current recovery strategy and messes with our 15 minute trn logs of our prod DB. – alexander7567 Feb 15 '18 at 0:02
  • I don't understand - you can't ship those 15 minute tran logs to the secondary? That is actually the best case scenario - you're building a secondary (or many secondaries) out of log backups you're already taking anyway. – Aaron Bertrand Feb 16 '18 at 2:13

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