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.

We previously ran Crystal Reports straight off our production database. This caused a direct impact to the users using the database. We moved to a new (dedicated) server and a new database and ditched Crystal Reports in favor of SSRS. We also have a dedicated reporting server to which our production database is copied over to twice a day. We run queries off of the reporting server as to not slow down the production environment.

Is this the best solution to our problem? The backup restorations kick everyone off the reporting server whenever they occur and management is asking we step up this process (backup restoration) to once every two hours. However, just twice a day gives us issues. For example, as I write this, the database is stuck in "Restoring..." and has been for at least 12 hours. So in the meantime that the DBAs come in (I only write reports) I thought I'd ask. Is this the best possible setup we've got going on?

share|improve this question

migrated from stackoverflow.com Mar 28 '13 at 1:49

This question came from our site for professional and enthusiast programmers.

4 Answers 4

up vote 2 down vote accepted

This is what Replication is for. This is a much easier way to keep a reporting database in sync than backup/restore.

share|improve this answer
    
We talked to the DBA and he confirmed this was the perfect solution for us. Not sure why he was a couple of days from setting us up with a log shipping restore every two hours. Would have made the DB almost inaccessible. –  Langosta Mar 27 '13 at 17:13

If the database copy can be readonly then try to use: database snapshots. It creates a readonly copy of the current database state in a small amount of time.

More info here: http://msdn.microsoft.com/en-us/library/ms175158(v=sql.100).aspx

Schedule this code into a job that runs once or multiple times a day:

Basic t-sql syntax: CREATE DATABASE FOO AS SNAPSHOT OF FOO1

share|improve this answer

This is one of the exact use cases that Microsoft created always on availability groups for. Queries with read intent (like your reporting queries) can be automatically routed to the secondary replica.

The downside is this requires upgrading to SQL server 2012 and requires enterprise edition. I don't know if that is a possibility for you.

share|improve this answer

Rather than burden the production system with the overhead (and potential changes required) to support replication or other techniques, if it is still acceptable to have "stale" data for a couple of hours, why not use two restored copies for reporting?

  • At 6 AM, take a COPY_ONLY backup of production.
  • Restore it on the reporting server as Report_A.
  • When the restore is finished, users connect to Report_A.
  • At 8 AM, take a COPY_ONLY backup of production.
  • Restore it on the reporting server as Report_B.
  • When the restore is finished, users connect to Report_B.
  • Rinse, repeat.

You could update some table after every restore, and have your application be smart enough to generate a connection string based on what's newest.

There still may be times where a user is connected to the "old" when the restore takes place, but this should be a much less frequent problem, since the new copies are restored in the background while they continue to work, instead of replacing what they're working on.

share|improve this answer

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.