Sql-server – Should I query from a snapshot or use a backup for reporting purposes

sql server

I have a very large select query for reporting purposes that can take up to 1-2 hours to run.

There are a few issues with this query.

  1. It takes up too much system resources and this server can't handle the load, the DB is also shared on the same server with 30 other dbs.

  2. The query is very large and read/write performance suffers, sometimes the tables are locked so I can't update.

The database is used in an application and for reporting purposes. We have another server in a different location where we can host the DB.

The application runs fine as is on this server, the issue comes in with the reporting. My solution is to

solution 1:

  1. Create the same DB on the other server for reporting purposes.
  2. Run timed jobs to to place a backup file in a shared location
  3. Back up the DB from the back up file that is stored in the shared location.

One of the DBA's is telling me this is too much work unless

solution 2:

  1. We move the application DB to the other server.
  2. Create the copy DB and host both DBs on the same server.
  3. Run a timed job for back ups
  4. And then Back up the reporting DB.

I fail to see how this is a too work. I do DB back ups all the time but I've never done one from a file in a shared location. But it seems like it's the same process to me.

If solution 2 is in fact a more hassle free solution, why is that so?

Best Answer

Solution 2 is very confusing as written and your question seems to start with query performance and moves into backup best practices. In solution 2, you still have your app DB and your new reporting DB on the same server. This means you'll still have the same resource contention (memory, CPU). You won't have the same blocking issues since you did create a reporting copy of your. This has nothing to do with the backups though.

Solution 1 is very common. You can even keep the data more "up to date" with log shipping. There's lots of information on this site about that.

Regarding your backups, some general advice would be

  • Run your backups from your App DB (at least). This is the data you really care about.
  • Back it up to wherever you want. Ideally not the same drive your data files are on. Corruption on these drives could mean corruption on your data files, and backups, leaving you dead in the water for recovery
  • Schedule your backups. You shouldn't be doing this manually. The frequency of your backups should be based on your RPO and RTO.