Backing up SQL Database for Reports

Question

Hi all!

I'm looking for some help/suggestions for backing up two large databases to one server dedicated to reports. The situation is;

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

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

We do not want to point reporting services to the live databases for performance/security reasons so we currently backup both databases on a daily basis and restore them to our Reporting Services server.

However this means we are putting a strain on our networks by backing up the entire databases, and also the data is only up-to-date by midnight yesterday.

Our aim is to have the data up to date by at least 15 minutes, it has been suggested to look at Log Shipping so I wondered if anyone had any experience in setting this up and what are the pros and cons and whether there is a better alternative?

Any help would be greatley appreciated, Thanks

Answer 1

You should look at replication as an alternative to backups.

Answer 2

Log shipping is a great solution for this. We've got articles about it over at SQLServerPedia's Log Shipping section, and I've got a video tutorial on there talking you through your different options. One thing to keep in mind about log shipping is that when the restores happen, your users will be kicked out of the reporting database.

Replication doesn't have that problem, but replication is nowhere near "set-it-and-forget-it" - it's time-intensive to manage, and isn't quite as reliable as you'd like it to be. In addition, you may have to make schema modifications in order to use replication. Log shipping is more automatic & stable, but at the cost of kicking users out at restore time.

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

Answer 3

We developed a similar environment. We used Mirroring to get the data off to our reporting server and created an automated routine to create Snapshots of the database every 15 min. These snapshots only take 1 to 2 seconds to create in our environment and give us a read only copy of the database. Let me know if you would like me to go into deeper detail.

Note we are running Enterprise on both servers.

Answer 4

Hi,

I would recommend that you look into using Transactional Replication.

It sounds as though you are looking to implement a scenario that is similar to what we are currently implementing ourselves.

We use Transaction Replication (albeit real time, you would most likely wish to synchronize your environment on a less frequent schedule) to offload a copy of our live production database to another server for reporting purposes.

Offloading 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 in that there is indeed an element of configuration required with Replication, along with security considerations that would need to be addressed however, there are a number of key advantages to using Replication in my opinion, including:

• Reduced latency in comparison to log shipping.
• The ability to Publish only the Articles (tables) that are required for reporting.
• Reduced storage requirements.
• Less data being published means less network traffic.
• Access to your reporting data/database at all times.

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

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