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.
RDFozz
  • 11,731
  • 4
  • 25
  • 38
alexander7567
  • 173
  • 5

2 Answers2

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.

Michael Green
  • 25,255
  • 13
  • 54
  • 100
SQLMac
  • 182
  • 2
  • 12
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.

Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91