October 25, 2007 at 12:26 pm
Hey guys, I thought I'd toss this out to see what kind of feedback I'd see.
I'm in the process of moving a legacy app from SQL 2000 to 2k5. I've purchased new hardware and am doing some virtualization on a number of other machines.
I'm looking to boost my database's availability as well as offload reporting functionality via SSRS to one of my virtual servers. I've been doing some reading on the pros and cons of various strategies and thought I'd toss it out there to see what kinds of experiences you may have had.
Things I'm considering: Clustering of some sort, replication, log shipping. I thought about mirroring, but since I couldn't use the mirror to report from I thought it would be less than advantageous, however the whole supposed 3-5 second fail over would be pretty sweet.
I'd be running with 64 bit SQL 05 Std ed on 64bit Win2k3R2 machines.
Any thoughts would be very much appreciated.
Thanks in Advance.
-Luke.
October 25, 2007 at 1:12 pm
You can configure SSRS in a NLB cluster or you can put it into a web farm with a hardware load balancer. We've done the latter. This assumes the database engine is on a separate server. In that case you'll also have to tackle Kerberos delegation, but it seems to be working for us.
K. Brian Kelley
@kbriankelley
October 25, 2007 at 1:47 pm
Ok I think I need to let you know a bit more about the scale, or lack thereof of our architecture.
We currently house all of our data in one SQL 2000 STD Ed instance.
another Physical machine is running Cognos Reports, but points back to the production database.
What I'd like to do is just have the production app hit my Prod database on 1 machine, then use SSRS (we're kicking Cognos to the curb) and a second database on a second server for both the ability to fail over to it in case of an emergency and to keep all of the Reporting queries (some of which are a bit intensive) separate from the production application.
I guess primarily I'd been looking at Log Shipping, but I'm open to suggestions particularly on the new HA technologies in 2k5.
Thanks.
-Luke.
October 25, 2007 at 8:48 pm
The issue here is the logs can't be restored as long as there are active connections into the database. Replication may be a better option.
K. Brian Kelley
@kbriankelley
October 25, 2007 at 10:38 pm
You could always mirror for the HA and perhaps log ship on a nightly basis if you have enough of a window and the reporting data doesn't need to be absolutely up to date.
It's a shame you can't snapshot on standard edition, it would make mirroring so much more useful.
October 30, 2007 at 6:26 am
hey,
Just to add, you could consider following:
1. Cluster the database engine bit via an Active\Passive configuration
2. Cluster the reporting services database bit via an Active\Passive configuration
3. Network Load Balance (NLB) the reporting services IIS element
Benefits:
1. You have HA on both the database engine and reporting services data
2. You have scalability in terms of reporting service application (e.g. IIS)
The other option would be to consolidate database servers into a multi-instance cluster (e.g. Active\Active) and separate user databases from reporting databases, but this would create a single point of failure in the event one of the nodes fails.
Thank you,
Phillip Cox
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply