SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustering vs. Mirroring, and Maybe Load Balancing?


Clustering vs. Mirroring, and Maybe Load Balancing?

Author
Message
Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38017 Visits: 7166
I'm a senior database developer / development DBA, and I wanted some advice for our production system, on the available options for dealing with failover in SQL Server 2005. We have what I'd consider medium sized databases, a few gig each, but a separate database for each of our clients, in a software as a service type of application environment.

What experience I have with clustering was in SQL Server 2000 at another company, and my role as a developer there was mostly isolated from the setup and configuration of the cluster. Unfortunately I don't have any 2005 clustering experience nor any experience with mirroring. From what I've read though, it seems that clustering is a more expensive option because it requires approved specialized hardware, and mirroring can just use commodity hardware, and mirroring with a witness can have very fast failover. Our production DBA seems sold on clustering though and has kept me out of the conversations with the infrastructure team. What advantages would there be to clustering over mirroring, or is clustering more of a legacy solution that Microsoft kept in because there are so many clusters out there from SQL Server 2000 and earlier that they still needed to support going forward in their software?

Also, from what I've read in Books Online, I got the impression that clustering in SQL Server is only for failover, but our production DBA seems to think that he can setup load balancing. I thought this could only be done by either using replication, or trying a "data dependent routing" scheme to split the data across multiple instances (this was tried before I was here at this company with near-disasterous performing results). Scalable Shared Databases or Snapshots are not very good options for us because of explicit requirements of real time reporting when data changes. There won't be huge numbers of transactions, but more likely spurts of small data changes throughout the day and evening from each client.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919864 Visits: 48856
Clustering is for redundancy of the entire server, mirroring is for redundancy of individual databases. They both have their place and neither is a replacement for the other.

Neither is a scale-out/load balancing scenario. In a cluster, the database is on shared disks and only one node can own those disks and hence run the database at a time. In mirroring the mirror DB is no accessible unless you use a database snapshot, and even then it's read-only

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38017 Visits: 7166
OK, thanks Gail for confirming that clustering does not have load balancing capability.

Does anyone have insight on what advantages if any clustering would have over mirroring? It still seems to me that clustering is at a disadvantage, because it uses shared disk so there is still one potential point of failure, and would be more expensive because of specialty hardware requirements.
Wilfred van Dijk
Wilfred van Dijk
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8152 Visits: 1373
It's not a matter of an advantage/disadvantage, it's another concept.

Clustering is a failover system for your server (not for your storage), while mirroring is a failover for your complete environment. Mirroring can be setup in different stages, including an automatic switch the the mirrored database server, see BOL.
There are also some limitations about the physical distance between the 2 servers (in both cases) , make sure you know these before making a decision.

For clustering, in most cases one server is active and the other (passive) is doing nothing. One diskgroup is defined and connected to the active node. But you can take advantage of the passive node by adding an additional diskgroup and connect this to the passive node. You cannot share the same disk (same data), but at least you can use the passive node.

Say you have:
* server 1, shared diskgroup a, application fgh, server 2 is the failover for server 1
* server 2, shared diskgroup b, application xyz, server 1 is the failover for server 2

Only if a failover occurs, the remaining server will get both diskgroups and a higher load (but how often will that happen?)

Wilfred
The best things in life are the simple things
GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919864 Visits: 48856
Clustering's good when you need redundancy for the entire server, not just a single databases.

Mirroring happens at the database level, so if the app depends on resources outside of the DB, you have to ensure that the resources are available on both machines. If there are 2 DBs that are both needed and cross DB queries run, then you have to ensure that they're both mirrored and that they both fail over at the same time

If you have other services than SQL (SSAS for eg), mirroring won't help there, but if they're cluster aware, they can fail over with the cluster nodes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919864 Visits: 48856
Wilfred van Dijk (10/1/2008)

Clustering is a failover system for your server (not for your storage), while mirroring is a failover for your complete environment.


Mirroring is a failover for a single database. While multiple databases on 1 server can be mirrored to a second server, they will fail over individually.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Wilfred van Dijk
Wilfred van Dijk
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8152 Visits: 1373
You're right, my specification was not accurate enough.

Wilfred
The best things in life are the simple things
Andy sql
Andy sql
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8528 Visits: 1350
Gail touched on this, but it is a very important point - with DB Mirroring, the application must support mirroring. Most commonly, by using an "SQL Native Client" odbc driver.

With Clustering, the application does not need to know anything about the configuration. Even if the Cluster fails-over, the application is not aware that anything happened.

Andy
Wilfred van Dijk
Wilfred van Dijk
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8152 Visits: 1373
Are you sure? We are running Axapta 3 on a 2K5 Cluster. If failover happens, all connections are broken. So AFAIK your application also needs to be cluster aware.

Wilfred
The best things in life are the simple things
Andy sql
Andy sql
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8528 Visits: 1350
Depends on where the applications are located. But you are essentially correct - during a failover, the SQL Server stops on one node and starts on a new node. This will close all existing connections, and roll forward/backward all open transactions.

Some applications can be coded to be "cluster aware" and thus handle this situation. Other applications will just reconnect without a fuss.

If an application expects the database to always be available, and has little or no error handling which runs when the database fails to respond for a few seconds, then that application will probably throw a nasty error.

Andy
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search