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.