Geek Answers Handbook

Backing up SQL database for reports

I am looking for some help / suggestions for backing up two large databases to a single reporting server. The situation is as follows:

My company has two databases for the internal website. One for the UK and one for Europe. Both are reflected for DR.

I have a server in Europe that is dedicated to Microsoft Reporting Services, where we run reports based on the data collected in these two databases.

We don’t want to specify reporting services for active databases for performance / security reasons, so we currently back up both databases daily and restore them to our Reporting Services server.

However, this means that we put a strain on our networks by backing up all the databases, and the data was only updated by midnight yesterday.

Our goal is to update the data for at least 15 minutes, it was suggested to look at the delivery log, so I thought, does anyone have experience in setting up and what are the pros and cons and is there a better alternative?

Any help would be greatly appreciated, Thanks

+4
source share
4 answers

We have developed a similar environment. We used Mirroring to output data to our report server and created an automatic procedure for creating database snapshots every 15 minutes. These snapshots take 1 to 2 seconds to create in our environment and give us only a read-only copy of the database. Let me know if you want me to go into more detail.

Note , we run Enterprise on both servers.

+2
source

Magazine delivery is a great solution. We have articles about this in the SQLServerPedia Log Submission Section , and I have a video tutorial on how you talk about your various parameters. One thing to keep in mind about log shipping is that when you restore, your users will knocked out of the report database.

Replication does not have this problem, but replication is nowhere near the “set-it-and-forget-it” - it takes a lot of time to manage and is not as reliable as you would like. In addition, you may need to make schema changes to use replication. Log delivery is more automatic and stable, but at the cost of throwing users out during recovery.

You can minimize this by having two delivery schedules for magazines - one for daytime during business hours and one for the rest. During business hours, you restore data only once per hour (or less), and the rest of the time you do this every 15 minutes.

+2
source

You can view replication as an alternative to backups.

+1
source

I would recommend you study the use of transactional replication.

It sounds as if you want to implement a scenario similar to what we are currently implementing.

We use transactional replication (although in real time you most likely want to synchronize your environment with a less frequent schedule) to upload a copy of our live production database to another server for reporting purposes.

Offload reporting data is a common replication scenario and is described here in the Microsoft Replication documentation.

http://msdn.microsoft.com/en-us/library/ms151784.aspx

Brent is right that there really is a configuration item needed for replication, along with security considerations that need to be addressed, however, in my opinion, there are a number of key benefits to using replication, including:

  • Reduced latency compared to delivery journal.
  • The ability to publish only Articles (tables) that are required for reporting.
  • Reduced storage requirements.
  • Less published data means less network traffic.
  • Access your reporting data / database anytime.

For example, in our environment, we decided to replicate only specific tables (articles) from our production database, which we actually require for reporting.

I hope that what I have described is clear and makes sense, but please feel free to contact me if you have any questions.

+1
source

More articles:


All Articles