SQL Clustering vs. Application clustering

Question

SQL Clustering vs. Application clustering

clementhuge

SSC-Addicted

Points: 475
More actions
December 18, 2008 at 6:42 am

#197329

Hello,
I would like to develop a high availability strategy for a company (both automatic failover and load balancing)
If I well understood clustering, it allows you to have n nodes on the same IP. Microsoft cluster server is geared for high availability clustering and not for load balancing.
Application clustering seems to be super easy to implement and you can set up on connection string to your data a failover partner so that automatic failover is possible without SQL clustering.
I am in a transactional environment where I can write my transactions on any production server. Let's say I have three production servers.
My application cluster does round-robin with sticky sessions on three application server instances.
I assign to each of them a data source (with principal and failover partner)
I use database mirroring on each of my three database servers, with a corresponding secondary server each (the failover partner). I use high safety automatic failover by seting up a witness server. ok. easy.
What is wrong with that? Will I have a worse performance than setting up Sql clustering?
I would like to know your thoughts about it. I still cannot figure out what is so great about SQL clustering.
Thanks for your replies 🙂
Clement

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

clementhuge (12/18/2008)
Hello,
I would like to develop a high availability strategy for a company (both automatic failover and load balancing)
If I well understood clustering, it allows you to have n nodes on the same IP. Microsoft cluster server is geared for high availability clustering and not for load balancing.

Indeed. Clustering is not a load balancing/scale-out scenario. Only one instance can use a database at a time

Application clustering seems to be super easy to implement and you can set up on connection string to your data a failover partner so that automatic failover is possible without SQL clustering.

Automatic failover (as in the failover partner in the connection string) is Database Mirroring. It's also a high availability, not a scale-out scenario.

I use database mirroring on each of my three database servers, with a corresponding secondary server each (the failover partner). I use high safety automatic failover by seting up a witness server. ok. easy.

Ok. Why?

The mirrored databases are not readable and not writeable. They'll be in Recovery

What is wrong with that? Will I have a worse performance than setting up Sql clustering?

Neither clustering nor mirroring have anything to do with performance.

I would like to know your thoughts about it. I still cannot figure out what is so great about SQL clustering.

Full server redundancy with all services able to automatically fail over in the case of a failure of any kind.

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

clementhuge SSC-Addicted Points: 475 More actions · Answer 2

Thanks for the quick reply. I apreciate your answers.

I saw how effective application clustering is (by watching a load testing), so I am 100% under the application clustering solution.

Database mirroring is what I like to implement and indeed, I like to have my secondary servers not available (licence wise, this is what I like). So, yes, I am looking for active/passive automatic failover strategies with database mirroring.

I tested it and works as a charm. I still need to test load testing.

The scalability, I like to gain it by duplicating servers. I can because I can federate the transactions on different databases.

I did that with a telecommunication companies and it really improves performance.

When I want to report, I just extract data from production servers.

Your answer confirms what I thought about clustering (for me, pretty much pointless).

I do not understand your answer when you talk about no scalability. Database Mirroring, I agree, does not improve performance (there is actually a small hint with keeping the data in sync).

However, by load balancing with several servers and let the round-robin clustering done by the application, I should achive load balancing, no?

any thoughts would be very much appreciated

Clement

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

clementhuge (12/18/2008)
However, by load balancing with several servers and let the round-robin clustering done by the application, I should achive load balancing, no?

How are you replicating the changes from one server to another?

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

clementhuge SSC-Addicted Points: 475 More actions · Answer 4

I do not need to replicate any changes for one principal server to another principal one as it only contains different sessions and "transactions"*. I would use database mirroring only for automatic failover reasons.

*"Transactions" do not refer to sql transactions. It refers to isolated operations like:

- a payment

- an email sent

- an order

etc...

The only trick is to keep sessions onto the same server. That is it and application clustering will do that by applying sticky sessions.

Clement

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ok, so you're implementing application load balancing, completely outside of SQL and DB mirroring for high availability.

What's the question then? I think I've missed something.

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

clementhuge SSC-Addicted Points: 475 More actions · Answer 6

The questions are:

1. Is my solution ok or stupid? Before setting this up, I would like some thoughts from experienced gurus 🙂

2. what does SQL clustering will add to this solution? or Does it add anything? I am completely inexperienced in SQL clustering and wonder if this can add something?

(I just do not understand what is the fuzz about SQL clustering. I think this is very complex to install, expensive and does not add much. But I have never implemented SQL clustering, so that is why I ask)

Clement

Jerry Hung SSChampion Points: 12968 More actions · Answer 7

I know clustering will add $$$$$ compared to Database Mirroring for sure

but Clustering seems the de facto way for large Enterprise who needs 24/7 uptime, period

I personally like DB Mirroring as a cheap startup High Availability

Problem is it only supports 2 nodes (unlike Cluster where you can add more)

And also DB Mirroring is less proven (vs Cluster), and the Application needs to be mirror-aware for auto failover (.Net is) where Cluster goes by Virtual IP/Name

For reporting, if you are using SQL Enterprise edition, you can create Database Snapshots on the mirrored server for read-only query

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

clementhuge SSC-Addicted Points: 475 More actions · Answer 8

Thank you very much for this answer.

When ou talk about snapshot, are you considering snapshots on mirrored databases ? because the mirrored databases are in recovery state, so I thought they cannot be read?

Are you considering snapshots with the cluster solution?

Please explain your point of view on snapshots, I like it very much. Thanks.

Clement

clementhuge SSC-Addicted Points: 475 More actions · Answer 9

oh yes I forgot. My application server is also mirror-aware.

Clement

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

clementhuge (12/18/2008)
Thank you very much for this answer.
When ou talk about snapshot, are you considering snapshots on mirrored databases ? because the mirrored databases are in recovery state, so I thought they cannot be read?

They can't, but a database snapshot on one can

Are you considering snapshots with the cluster solution?

There's only one copy of the database in a cluster. What would you put the snapshot on?

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

clementhuge SSC-Addicted Points: 475 More actions · Answer 11

grea answer! This is a good news. I never thoughtI can do a snapshot on a database "in-recovery".

I am going to check this. cool.

Thanks

Clement

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

clementhuge (12/18/2008)
grea answer! This is a good news. I never thoughtI can do a snapshot on a database "in-recovery".

In general you can't. It only works for a mirrored database that's synchronised.

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

clementhuge SSC-Addicted Points: 475 More actions · Answer 13

clementhuge

SSC-Addicted

Points: 475

December 18, 2008 at 1:00 pm

#914837

cool. Tanks.

Clement