Active/Active SQL Server Cluster and OLTP

Hello folks,

have you ever heard of an Active/Active SQL Sever 2000 Cluster deployed in a pure OLTP environment?

Some 8 years ago I have learned about a bespoke solution for the SAP ERP system (not the BW!) with DB2 Parallel Server for a huge German company. Then again, I would expect that Oracle RAC might fit into an OLTP environment, although I never heard about a real world implementation.

All this led me to believe clustering is good for failover purposes, and for decision support services, not quite for OLTP applications.

So if you see a point in Active/Active Clustering and OLTP please come forward and explain.

Cheers,

Johann

P.S: For those of you who want to know: Consultants from www.hiltes.com want us to deploy an Active/Active Cluster for their Fashion 3000 Net stock software.I'm sure the experts will jump in and kill me for any mis-statements. I have been researching active/active as we get ready to implement such a solution here (and we are primarily an OLTP environment).

MS SQL active/active is NOT AT ALL like an Oracle RAC solution. It is not (really) a way to distribute loads among multiple servers. An active/active installation involves installing SQL two times (creating two instances which effectively know nothing about each other).

1. You can distribute the load, but you have to individually designate on which server (instance) each database will reside.
2. You cannot (easily) switch a database from one SQL instance to another.
3. You cannot (easily) carve a database into two parts and divide the workload among two active servers.

You can do things with federated queries and such; but you really begin to alter the underlying schema of your database when you start doing so.

Think of active/active as a way to better utilize hardware resources. In an active/passive cluster, the passive cluster is just waiting around to take over for the failed active node. In active/active, both nodes (assuming there are just two nodes) are actively taking requests and servicing them (but not for the same database(s)). If a node fails, then the remaining node would have to shoulder twice the burden. On a side note, a better implementation for an active/active cluster involves installation of at least a third node to serve as the primary backup for both active nodes.

There is nothing inherently evil or wrong with an active/active cluster in an OLTP environment; however, it may not be what you are expecting (especially if you are more familiar with Oracle RAC).

Regards,

hmscott|||Hi hmscott,

if I get your point right active/active clustering makes sense for active/active aware - i.e. specially designed - applications.

So as long as we are in doubt we should ask our vendor for confirmation if his OLTP application is designed for active/active clustering, right?

Have you seen or are you developing an OLTP application that can make use of active/active clustering? Or are you about to set up a decision support system or something?

Cheers

Johann|||Hi hmscott,

if I get your point right active/active clustering makes sense for active/active aware - i.e. specially designed - applications.

Johann

You're reaching for an answere here that I don't feel qualified to answer. The reason we are reasearching an active/active cluster is to consolidate two active/passive clusters into a single active/active cluster (with a third node for failover if I can convince someone in management to spring for the extra money).

The current active/passive clusters have no knowledge of each other and have no need to communicate with each other. Going active/active is merely a way for us to go from having four servers down to two (or three).

Regards,

hmscott|||Consolidation of pure availability solution may lead to...unavailability. If the third server is not 3 times beefier than any of the other 2, - consolidation may lead to disaster, which I am sure you're prepared for.

And to answer the original post, - HA is not limited to OLTP or DSS or any other type of system, but it can be implemented and configured differently depending on the usage and availability requirements.

Usually, DSS does not require HA as much as OLTP does, unless this DSS is very dynamic in nature and is used as a driver for other automated processes that cannot wait for the server restart or a disk to be replace. However, in this case I would not call it a DSS, but rather just another OLTP.|||Consolidation of pure availability solution may lead to...unavailability. If the third server is not 3 times beefier than any of the other 2, - consolidation may lead to disaster, which I am sure you're prepared for.


Your point is valid and well taken; I would prefer to knock the two HA clusters into a single active/passive cluster. However, the 8th layer of the OSI model (politics) is a strong force in our office.

I'm just trying to avoid a two-node active/active cluster where it becomes difficult to down either node for updates or any other kind of maintenance.

Regards,

hmscott|||Consolidation of pure availability solution may lead to...unavailability. If the third server is not 3 times beefier than any of the other 2, - consolidation may lead to disaster, which I am sure you're prepared for.

And to answer the original post, - HA is not limited to OLTP or DSS or any other type of system, but it can be implemented and configured differently depending on the usage and availability requirements.

Usually, DSS does not require HA as much as OLTP does, unless this DSS is very dynamic in nature and is used as a driver for other automated processes that cannot wait for the server restart or a disk to be replace. However, in this case I would not call it a DSS, but rather just another OLTP.

The original post was - or should have been - about a vendor's statement that his OLTP application actually performs better on an active/active cluster. This is where I don't believe him. So are there OLTP applications out in the wild that support parallel databases with SQL Server in active/active configurations?

Cheers

Johann

Btw, we are by far the largest - not necessarily the greatest :-) - customer our new vendor ever has dealt with. This is another reason for us to carefully weight his arguments.

Accessing Reports through a web application?

Hi All,

I have developed few reports in SQL Server 2005 Reporting Services and deployed them on a reporting server.

These reports are accessed through a web application that is deployed on a different server. I am using the Report Viewer control in my web application.

The problem is when I access the reports through my web application, it gives me a Page not found error.

If I have both web server and reporting server on the same machine, then this problem does not come.

I need to have separate web and reporting server. Can anyone please provide help on how to solve this authentication issue. My web application is taking the credentials but the credentials are not getting passed if the reports are deployed on a different server.

Any help will be greatly appreciated!!!

Has anyone tried this before?

Regards!!!

Accessing Reports Once Deployed?

I have created several reports in SRS and I deploy them by establishing a VPN connection than deploying. That much works fine.

Now I want to allow user of my website to access the reports by clicking on a link. Do I need to establish user level access for the reports for each user or just one public access account? How do I pass login and credential information with the link so that users can access the report.

Thanks in advance

Hi there,

Reporting Services uses AD out of the box. You can add individual user accounts or groups to the Report Manager as browsers or content managers.

If you are using SOAP access you can pass the credentials in from code, otherwise they'll have to type their info in.

If you want to use forms based or custom security, it is a bit complex but can be done.

Here's a link to more info.

http://msdn2.microsoft.com/en-us/library/aa274428(SQL.80).aspx

cheers,

Andrew

|||Thanks for the link. I think that is the clue I needed

Accessing Reports on local LAN

Hi,

My reports are deployed on reporting server installed on my machine. Now i want to access the reports on other machines which are on local LAN along with my machine. When i try to access reports from some other machine browser gives error page not found.

Can any one tell me how to do this.

Thanks in advance..

If you can put in what the error is and what type of servers you are running from then we can try and help!

Is your SQL seperate?

Did you enter //servername/reports and try and add it in here?

Have you set up permission to access the server!?

|||

Hey i got the solution.....

First thing is ur Server has to be on domain and not a workgroup computer.

Second thing is you will have to give acess rights to the users u want to see the reports.

Cheers

Siddharth...

Accessing Reports on local LAN

Hi,

My reports are deployed on reporting server installed on my machine. Now i want to access the reports on other machines which are on local LAN along with my machine. When i try to access reports from some other machine browser gives error page not found.

Can any one tell me how to do this.

Thanks in advance..

If you can put in what the error is and what type of servers you are running from then we can try and help!

Is your SQL seperate?

Did you enter //servername/reports and try and add it in here?

Have you set up permission to access the server!?

|||

Hey i got the solution.....

First thing is ur Server has to be on domain and not a workgroup computer.

Second thing is you will have to give acess rights to the users u want to see the reports.

Cheers

Siddharth...