Can I query a copy of a database without impacting performance of the primary?

Question

I am working with a partner who has a large (150,000,000 records and growing) MSSQL database. They query and populate the database with proprietary software which I cannot easily influence. I would also like to access the data in the db but the company that provides the software are concerned that my additional querying (I may do a lot, in short bursts) would add too much load to an already straining server and cause problems for them.

What are my options here?

Is it possible to set up a server which provides a read-only copy of the data so I can query that without putting much pressure on the original database?

Answer 1

You can do something like log shipping. Assuming you are already taking full and log backups on the primary server then using those backups to maintain a mostly-fresh copy of the database elsewhere will not impact the primary server at all (unless the storage for the backups is the same - which is a no-no anyway). You cannot access the database while logs are restored, but assuming you know when you'll be running your query bursts, you can schedule restore/standby operations outside of those windows (just know that the data won't always be in sync with the production copy, depending on the last log restore).

Your reporting copy can also serve as a warm standby to recover data in the event that the vendor or someone else makes a mistake in production - depending on the log shipping delay you can recover that data from before that mistake without recovering the entire database. I've seen this methodology used in several places, especially where a lot of cretins had ad hoc access to the database. :-)

Answer 2

I too would suggest log shipping as @Aaron answered, but in the spirit of providing suggestions, you have another option: Database snapshot.

Database snapshots may not give you as much performance relief as log shipping, but is quicker/simpler to setup and can be created on the same SQL instance without extra hardware. You can also create multiple snapshots provided you have the space for them. DB snapshots can also be used to fix data corruption issues created by said cretins.

The catch is that you will need enterprise edition to use DB snapshots.