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.
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.
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
- Create the same DB on the other server for reporting purposes.
- Run timed jobs to to place a backup file in a shared location
- 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
- We move the application DB to the other server.
- Create the copy DB and host both DBs on the same server.
- Run a timed job for back ups
- 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?