Read Only Reporting Server Active/Active Configuration

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry Herbinaux - 26 Sep 2006 07:13 GMT

I currently have an active/passive cluster acting as our OLTP TX database
server that is replicating to another active/passive cluster acting as a
Reporting database server.

I have heard that if the database is read-only (e.g. reporting server) that
it is possible to set the sql server application in an active/active mode and
have requests be handled by both of the servers (e.g. shared access to a
single databse). This is what I want to do, but I don't think it is possible
since one node will be given access to the disk resources, correct?

I guess the only way to really use active/active is to have two sql server
instances each with its own disk resources that are supporting two
independent applications and then upon failure the non-failed node handles
both applications. This isn't really what I want to do because I want to
load-balance read-only requests to the same database across two servers;
which I am not able to do. Am I understanding this correctly?

Reply to this Message

Edwin vMierlo - 26 Sep 2006 08:12 GMT

The correct name is scalable shared databases, where multiple SQL 2005
servers are accessing a read-only volume.
there are many restrictions to this

Please see ; http://support.microsoft.com/default.aspx?scid=kb;en-us;910378
for more info on this

HTH,
_Edwin.

> I currently have an active/passive cluster acting as our OLTP TX database
> server that is replicating to another active/passive cluster acting as a
[quoted text clipped - 12 lines]
> load-balance read-only requests to the same database across two servers;
> which I am not able to do. Am I understanding this correctly?

Reply to this Message

Larry Herbinaux - 26 Sep 2006 21:27 GMT

Thanks Edwin,

Is this also supported for SQL 2000 or just SQL 2005?

If reporting really needed to scale and the reporting requirements for some
reports needed to be up to the minute but most of the reports could use data
that was a day old, then this seems like the following configuration would
seem to give you the most bang for your buck, correct?

TX Database - 2-Node Active/Passive

Reporting Database Replicated - 2-Node Active/Passive - This would have the
entire TX Database replicated in real-time like we have today. Client access
to this database would be limited to the reports that needed to be accurate
to the minute.

Reporting Database Manual - N-Node Active/Active/Active... - This would have
two volumes as discussed in the article, and the operational volume would be
swapped out with the newly built volume just after midnight with a newer data
set. Client access to this database would limited to the reports that don't
need to be up to the minute accurate. During the downtime, access to the
Reporting Database Replicated cluster could be changed to allow all reports
to be run from it; given the fact that these changes would be done at
midnight, the load on this servers shouldn't be very great so it should be
able to handle the additional reports.

> The correct name is scalable shared databases, where multiple SQL 2005
> servers are accessing a read-only volume.
[quoted text clipped - 25 lines]
> > load-balance read-only requests to the same database across two servers;
> > which I am not able to do. Am I understanding this correctly?

Reply to this Message

Edwin vMierlo - 27 Sep 2006 07:23 GMT

answers inline

> Thanks Edwin,
>
> Is this also supported for SQL 2000 or just SQL 2005?

SQL 2005 Enterprise Edition

> If reporting really needed to scale and the reporting requirements for some
> reports needed to be up to the minute but most of the reports could use data
[quoted text clipped - 17 lines]
> midnight, the load on this servers shouldn't be very great so it should be
> able to handle the additional reports.

Yes, while you are "swapping" in the new data, you will have downtime

> > The correct name is scalable shared databases, where multiple SQL 2005
> > servers are accessing a read-only volume.
[quoted text clipped - 25 lines]
> > > load-balance read-only requests to the same database across two servers;
> > > which I am not able to do. Am I understanding this correctly?

Reply to this Message