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.
- 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?
- Does that break point in time transaction logs backup of the prod server?
- Can this cause a strain on the prod server?
- 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.