0

A SQL Server that serves as a DW for reporting purposes (OLAP) is also used directly by users to perform direct ad-hoc queries. User queries add a lot of extra load on this server because of number and concurrency and since this is not its primary role I am considering replicating the relatively big database to another machine and have users execute their queries on that machine to offload the DW. This replication should be done daily.

My question is: what is the faster solution for this - Doing a Backup/Restore of the original DB or setting up Snapshot Replication ?

Is there any other approach to this problem - any suggestions?

jayt.dev
  • 975
  • 6
  • 14
  • 36
  • How about *neither*? SSRS can cache reports. SSAS *accelerates* ad-hoc queries and makes them almost instantaneous. Why do you think you need another server instead of fixing the current design? – Panagiotis Kanavos Dec 15 '16 at 12:55
  • @PanagiotisKanavos Yes you are absolutely right. But requirements do not include SSRS, users want to directly execute T-SQL queries on the database. I simply have to follow this rules and replicate DB somewhere else. – jayt.dev Dec 15 '16 at 13:07
  • You can create columnstore indexes on the tables and get a huge improvement. Snapshot or backup though? You'll have to test it. It's somewhat easier to manage and monitor replication though than a hand-written job – Panagiotis Kanavos Dec 15 '16 at 13:11
  • Another reason for redirecting users to other server is that they create locking problems on tables while important SSIS packages are running – jayt.dev Dec 15 '16 at 14:46
  • Then you have a bad design. Users shouldn't be querying *staging* tables and SSIS shouldn't be updating fact tables, only inserting final rows. Even with big updates, you can use partition switching to update a copy of a modified partition then swap it with the old data without any downtime. Your DW is supposed to be the *copy* that users can freely query. If you have locking issues and have to copy the copy, something is seriously wrong – Panagiotis Kanavos Dec 15 '16 at 14:52

0 Answers0