Creating a copy of production database for reporting purposes

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.