Tell me more ×
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.
up vote 6 down vote favorite

I'm looking for some advice on how to handle reporting for our environment. We currently have 16 servers with 20 instances of SQL Server 2005. We have over 6,600 databases and growing across these instances (1 database per customer). The majority of our databases run in size from 200mb to 7gb with about 60 databases running in size from 11GB to 110 gb at the largest.

We are using a SAN for storage and we are running into issues with the running of the reports affecting IO.

One idea we had was to pull the 60 larger databases and then use transactional replication to replicate these databases and run reporting on the replicas.

That would then leave all the smaller databases to run without the stress of the larger databses. In the future we don't believe there will be any more of the the larger databases based on our company goals.

Any thoughts?

share|improve this question
Do you have Analysis Services in this mix? – Atilla Ozgur May 24 '12 at 18:27
No we do not have Analysis services at this point. We are using reporting services with stored procs stored in the oltp customer databases. – pamozer May 24 '12 at 21:03
One further bit of information to request. Who is consuming these reports? Are they internal, or are they reports that you've created for your customers? Are customers allowed to run ad-hoc queries? What about time zone distributions. All of these are simple hurdles, but the answers will go a long way in helping select your architecture. – swasheck May 25 '12 at 21:49
What do you have to report? Can the reporting data be precomputed (either nightly, or ad-hoc, i.e., indexed views)? Are the reporting queries robust -- could the OLTP tables be indexed such that the reports do index scans instead of table scans (chances are you already have indexes -- are they close to what's required for reporting)? Is storage size a limitation? How is the current system suffering from I/O problems? Not enough throughput? Contention of big scans and OLTP writes? What proportion of activity of your application is writes (vs. reads)? – Jon Seigel May 26 '12 at 3:17

3 Answers

up vote 8 down vote

In our shop at my previous job, we had a secondary set of servers where we tested our restores. For our busiest customers we would restore tonight's backup, mark it as read_only, and their reporting tomorrow would connect to that copy of the database for all reports from yesterday back. This offloaded about 90% of the reporting workload and doubled as a backup/restore validation method. So if most reports don't need today's data, you could consider alleviating some of the production workload this way with some cheaper hardware - if you're not using Enterprise features you could even use Express for all the databases that are < 10GB. (Well, I see it's 2005, which had a lower DB size limitation, but you could always restore your copy forward into 2008/R2.) This would allow you to really distribute the databases to many low-end commodity servers (VMs or pizza boxes).

share|improve this answer
1  
Interesting idea. My concern would be restoring the dbs over 20gbs every night. And how long that would take. And if the restore for some reason does fail, which would be great to know for testing purposes, would not be so good for our customers who would now not be able to get to their reports. – pamozer May 25 '12 at 16:08
I have no idea how long your restores should take, too many variables. But what good is taking a backup if you don't test that it is actually a good backup (by restoring)? If the restore fails there should be some way for your application to have logic about whether it can use the backup or should fall back on the production copy. This will be easy TRY/CATCH in the case where the restore leaves the copy unusable, but some other way for the restore job to log somewhere that it was successful - the app can check that flag first to make sure that last night's restore worked. – Aaron Bertrand May 25 '12 at 16:10
up vote 1 down vote

On 2005 I can think of three strategies to offload reporting: 1. Day-old data using log shipping (log shipping to standby kicks users out at each restore, so realistically restores can only happen when they don't expect to be on, like in the evening.) Advantage - incremental updates. 2. Day-old data using full restores. Disadvantage: full restores. 3. Replication. This gives the best user experience, but is harder/takes more manpower to set up and maintain.

Aside - is there any advantage to just increasing the hardware available to the big customers, tuning and isolating them, instead? Enough disks and memory? Flash? Index optimization? Really they don't sound that big by today's standards (admittedly, I have not seen your systems)

share|improve this answer
1  
Log shipping is a good one too, however I might intersperse it with a once-a-week or every-other-week full refresh. I have a few colleagues with log shipping in place for this exact scenario and they have to re-initialize every once in a while - one says "log shipping gets stuck" and, while I haven't been close enough to see exactly what that means in their case, it does seem quite familiar. – Aaron Bertrand May 27 '12 at 23:13
Interesting. Ours has never had that happen (which isn't to say it never does for others). Knock wood. – onupdatecascade Jun 2 '12 at 2:29
up vote 0 down vote

Go with Transactional Replication.

As mentioned in Data Warehousing and Reporting, Transactional Replication is well suited for reporting scenarios by offering:

  • Transactional consistency

  • Low latency

  • High Throughput

  • Minimal overhead

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.