Read Only Reporting Server Active/Active Configuration

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?
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...b;en-us;910378
for more info on this
HTH,
_Edwin.
"Larry Herbinaux" <LarryHerbinaux@.discussions.microsoft.com> wrote in
message news:981C4446-DC47-43D3-B071-742BCEE767F3@.microsoft.com...
> 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?
>
|||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.
"Edwin vMierlo" wrote:

> 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...b;en-us;910378
> for more info on this
> HTH,
> _Edwin.
>
> "Larry Herbinaux" <LarryHerbinaux@.discussions.microsoft.com> wrote in
> message news:981C4446-DC47-43D3-B071-742BCEE767F3@.microsoft.com...
> that
> and
> possible
>
>
|||answers inline
"Larry Herbinaux" <LarryHerbinaux@.discussions.microsoft.com> wrote in
message news:F5FD9768-8D48-409C-8431-338CFD7B9D49@.microsoft.com...
> 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
> 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.
Yes, while you are "swapping" in the new data, you will have downtime
[vbcol=seagreen]
> "Edwin vMierlo" wrote:
http://support.microsoft.com/default...b;en-us;910378[vbcol=seagreen]
database[vbcol=seagreen]
a[vbcol=seagreen]
mode[vbcol=seagreen]
a[vbcol=seagreen]
server[vbcol=seagreen]
handles[vbcol=seagreen]
to[vbcol=seagreen]
servers;[vbcol=seagreen]

Read Only

If I change my Replicated db to Read Only, will this result in any adverse effects for Replicating to this db?
I don't want users to have any access to the db other than reporting, and I read on sql server performance:
"If you have a database that acts as a datamart or data warehouse, set the "read only" database option to true. This will turn off locking and greatly speed queries against the data."
Thoughts anyone?
Thanx!
JLS,
for replication I think you'll have to achieve the same effect (protecting the database) using permissions. Alternatively you could use this option and 'undo' it in the replication synchronization job as a custom step.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||nice idea. however. readonly is readonly and replication i think is not an
excuse
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"JLS" wrote:

> If I change my Replicated db to Read Only, will this result in any adverse effects for Replicating to this db?
> I don't want users to have any access to the db other than reporting, and I read on sql server performance:
> "If you have a database that acts as a datamart or data warehouse, set the "read only" database option to true. This will turn off locking and greatly speed queries against the data."
> Thoughts anyone?
> Thanx!
>