December 9, 2011 at 3:55 am
At month-end for 2-3 days our accounting application database (SQL 2008), is heavily used for producing reports compared to lighter input and queries for the rest of the month.
I have been asked whether it is possible to replicate the database to another server, for month-end reporting. The replica would need to be an up to date (with-in 10 mins) copy of the live database.
The articles I have read on Database mirroring and log shipping, suggests the replica can only be used in a fail over situation and that both copies can not be on-line at the same time.
Has anyone created a database replica that can be accessed by SSRS or Business Objects. Is the overhead of creating the replica greater than the benefit achieved or would it be more efficient to increase the resources of the existing SQL server.
I would be interested in others views on this.
December 9, 2011 at 4:02 am
IN logshipping we can do by stand by mode... that will solve your problem.
December 9, 2011 at 4:11 am
Pradyothana Shastry (12/9/2011)
IN logshipping we can do by stand by mode... that will solve your problem.
To follow on from this, there will be an additional charge for SQL licences should you go down this route as the secondary server has queries being issued against it while production is active.
Along with logshipping you could take a look at replcation, more specifically transactional replication which will also perform the goals you need in keeping the second database upto date.
edit
Also you have to take into consideration the longest time possible that reports run for, you say you need up to 10 minutes delay, this means you will be disconnecting users every 10 minutes, so if a report runs for longer than that interval, logshipping is not the option and you will want to look at replication
December 9, 2011 at 7:33 am
Guys, thanks for your replies.
I'll have a readup on transactional replication
December 9, 2011 at 8:27 am
Transactional replication is the best way to do this. The regular disconnections required by Log Shipping are no end of frustration for end users.
Keep in mind, however, that you need to educate your users if you do this as many of them will expect that their reports match. However the data will be constantly changing as data is replicated, so reports run seconds apart could differ. It's probably the same thing now, reporting on the live system, but I've found end users sometimes expect that if they all run reports before a meeting that the numbers will match.
Note that you can potentially negotiate this with end users. Show them how things can change, and perhaps schedule replication at certain periods so that your reporting system appears more stable.
December 9, 2011 at 9:02 am
Thanks for the extra info Steve. I will setup the transactional replication in my test lab next week and test how it all works.
December 9, 2011 at 10:31 am
Another note to aid in what has been already offered above...when thinking about running reports off the standby db's (logshipped) you need to remember that you cannot create indexes there. The indexes that already exist are there as a result of the logshipping, so, if specific indexes are/were needed for specific reports you woud be running them without proper indexes - resulting in potentially poorly performing queries, index scans, etc.
Might I reccomend you take a look at RedGate's Virtual Restore software...this will allow you to restore any backup of a database, which can allow you to point other reporting processes to it during those heavy reporting times.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 9, 2011 at 11:43 am
>>Is the overhead of creating the replica greater than the benefit achieved or would it be more efficient to increase the resources of the existing SQL server.
You would have to analyze your bottlenecks. Perhaps your reports could be optimized. If your server is starved, throwing hardware at the problem is the simplest. No additional license fees and no constantly explaining to the users why they don't see their changes and why their reports are inconsistent.
The 'throwing of hardware' solution assumes your performance analysis is good enough to definitely determine that additional resources will improve your performance to an acceptable level. Not an easy task.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply