Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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. :-)

share|improve this answer
    
Can I do log shipping on the express version of sql server? –  Graeme Feb 21 '12 at 14:32
    
Yes, just not with a wizard IIRC - you'll have to set up the backup / restore jobs manually. –  Aaron Bertrand Feb 21 '12 at 14:58
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.