I have a heavy load DWH server. I wish to create a copy of that server for report purposes (Cognos reports to be exact). In today's situation I have many reports and can cause locks on other processes, can be locked or get only 1 thread and take a long time to finish. The reports run on several huge databases. I have thought of several ideas, non are good enough:
- First thing comes to mind is Always On but something tells me that changing the databases to FULL recovery is not a good idea and perhaps the sync will fail all the time because of the transactions size.
- Maybe snapshots? Don't know if it is a good idea on production and what about the fact it will kill reports once snapshot is switched.
- Any hardware solution I'm unaware - files copy or whatever.
- 3rd party tools?
Any idea will be much appreciated! It doesn't have to be online like always on (it's preferred of course) but once a day if there is no other choice.
