| Author |
Topic |
|
Rovastar
Starting Member
38 Posts |
 Posted - 2007-02-15 : 06:17:09
|
| Hi Folks,I am on a project at the moment where a 3rd party development team is creating a application and database for us.We are buying all new kit for this project and although labeled high available solution it is not really. Now they want to advise the company I am contracting for to go for a Database Mirroring solution rather than a Failover Clustering one. There are 2 servers running Win 2003 load balancing the application that is being written and then 2 servers running SQL 2005. (if the mirroring solution an additional Witness box)=======Benefits to Clustering over MirroringShared load ScalabilityBenefits to Mirroring over ClusteringCost (cheaper h/w, licenses)No HCL requirementsDistance (you can have a server in another country)Less complex than clustered so less expertiseFaster failover 2 seconds vs 10 seconds (or whatever)On the issue of uptime and resilience Mirroring solution has an additional point of failure which is the Witness box if that goes down the whole thing stops working.On the clustering solution the weakest link in the chain is the shared disk array.=========For me all the benefits of mirroring (tarted up log shipping from 2000 from what I understand) don’t seem to apply to me and I cannot think of a useful reason for the devs for suggesting this solution.All the kit is brand new, high-end-ish Dell servers so I am not expecting any problems with the HCL to run clustering.Cost isn’t really an issue for this project a few thousand more the licenses (we have ordered the hardware) isn’t relevant.All servers will be based in the same rack so no issues with distance that will benefit. Although touted as high availability it is not there is no millions a minute business loss in downtime. Only 50 users on the same site as the users using this system maximum and not high value work (it is water bills processing not stock trading) and if it is down in the unlikely event of a failure an hour or so recovery time isn’t going to matter. Complexity I am not considering an issue although I haven’t done 2005 clustering I am not imagining it much more difficult that 2000 and plenty of white paper and guides on this anyway.====The downside of mirroring is that we have a high-end-ish server that will be doing nothing in the mirror environment and in the cluster it shares the load and we can scale up if needed.Now I am fairly new to SQL 2005 and I cannot understand the appeal of database mirroring. I can see the usefulness for some situations, cheap hardware for the mirrored solution if the main DB server goes down, etc but these do not apply in my case.I have probably misunderstood some stuff here. I am no SQL expert and as you folks, who know much more than me, can you explain the benefits of data mirroring vs failover clustering and more importantly for my local environment.I look forward to any expertise and thanks for reading. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-15 : 10:14:15
|
| there is no scalability or shared load benefit for a SQL cluster. SQL Server clustering is different than Oracle clustering (RAC) in this regard. SQL clustering is only for high availability and there are no performance benefits to it. On the mirroring side, the witness server is not a single point of failure. If it fails, your mirrored databases will still be mirrored.I would say the main differences between mirror and clustering is that the cluster works at the instance level, where mirroring works at an individual database level. Clustering also has automatic failover where most clients can (theoretically) reconnect once a failover occurs. this is because the servername is virtualized. With mirroring client reconnection is not automatic unless you are using ADO.NET or SQL Native Client for connectivity. Also, in clustering you can run Active/Active which means that the 2nd node in your cluster can be hosting another database instance (or 2 or 3 or more) and be doing work. In mirroring, the mirror is passive until it becomes the primary.Here is a good overview of mirroring http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx-ec |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-02-15 : 10:50:59
|
quote:
In mirroring, the mirror is passive until it becomes the primary.
Just to clarify the mirror of the database is passive however that server instance itself can still be running.I recently went through this decision process myself. It mostly broke down to the client app being older so it wouldn't failover automatically. If you can use the latest client code and failover I'd choose mirroring.-Bill===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-15 : 13:14:36
|
| We use both clustering and database mirroring for our high availability needs. We cluster at our primary site and mirror all of our 2005 prod databases to our disaster recovery site. We also have a duplicate cluster at the DR site. We use asynchronous mirroring though for performance reasons. We don't want a two-phase commit that goes across about 300 miles. We don't use a witness as a result of using asynchronous mirroring.Tara Kizer |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2007-02-16 : 04:30:18
|
| Thank for the replies.Don't I feel stupid about not realising that the clustering doesn't do load balancing or scaling.I could have sworn I read MS blurb when SQL 2005 came out about about load balancing and scalability. And I just presumed as it is now 2007 that is the way it worked.Well for our solution it appears it doesn't really matter if we use clustering or mirroring. As there is negligible difference between them for our needs.So what do big, large scale SQL Server databases solutions use to spread to load? Surely they cannot just use 1 machine for all of the load? *shrug* |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-16 : 11:29:52
|
| Well you can use Active/Active clusters. It spreads the load across instances at least. So if you've got 2 nodes in the cluster and 2 instances installed, you can run one instance on each node. It means you have to license SQL Server on both nodes. It isn't true load balancing though. We've got a 4 node cluster that is Active/Active/Active/Active. We spread our 11 instances across all nodes ensuring not to overwhelm any node.Tara Kizer |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-16 : 13:13:03
|
| Microsoft have Windows Compute Cluster Server 2003 - it would be the way that I'd expect to see load balanced SQL Server at some point although I'm not aware of any announced plans. There are of course things in SQL Server that help you to balance the load although they are certainly not load balancing, this article is a good overview of the various possibilitieshttp://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1133488,00.html |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-02-27 : 07:04:14
|
quote:
Originally posted by tkizer
We use both clustering and database mirroring for our high availability needs. We cluster at our primary site and mirror all of our 2005 prod databases to our disaster recovery site. We also have a duplicate cluster at the DR site. We use asynchronous mirroring though for performance reasons. We don't want a two-phase commit that goes across about 300 miles. We don't use a witness as a result of using asynchronous mirroring.Tara Kizer
Hi,I am new in enterprise use of SQL Server (just got a job) :)We need to provide a HA service, where the downtime should be reduced to a minimum. I agree that the combination of clustering and db mirroring is the optimal combination from the availability view point, but I would like to know something more about performance.I just wanted to know how long does it take for SQL Server 2005 in the cluster configuration to failover to the secondary server, and how long does it take to failover to the mirror server in mirrored database configuration?I found some data on the official Microsoft website, but I would like to know how much time it is in real life situations.Thanks in advance |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-02-27 : 07:52:26
|
| "I just wanted to know how long does it take for SQL Server 2005 in the cluster configuration to failover to the secondary server"....microseconds, the switching is done at hardware level. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-27 : 13:09:25
|
| Database Mirroring isn't quite at the hardware level, but it is still very fast to failover. We perform failovers to our disastery recovery site twice a year to ensure that our DR procedures work. Failing over to the DR site takes less than a second for the databases that are using Database Mirroring.Tara Kizer |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-02-28 : 11:25:20
|
| Thank you all for help.I just have a couple of more questions.If I undestood correctly, one can have as much as eight nodes in a SQL Server 2005 Cluster (depending on the edition and the underlying OS, of course). I wanted to know what, in your opinion would be a more efficient solution, in terms of availability (in case of a failure, down time should be brought to a minimum):1) Having a n-node cluster with shared storage (if so, what kind of configuration would be prefered, i.e. active/active/... or active/passive/...)2) Having a mirrored databaseAlso, in case of a mirrored database, what happens if the witness fails?One point to add: cost of the solution is not critical, but it is also not neglectable.All these questions may seem trivial, but like I said, I am a beginner in all this.Thank you in advance. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-28 : 11:38:18
|
| We use both. We cluster at our primary site so that we can install patches and touch the hardware with minimal downtime on each instance. We also database mirror to our disaster recovery site.We have a 4 node cluster at each site. At the primary site, the cluster is Active/Active/Active/Active. At the DR site, it is just waiting for us to fail to it.We have 99.999% availability requirements which is why we cluster and database mirror. We have redundant everything at both sites.We use asynchronous database mirroring for performance reasons (avoiding the two phase commit across 300 miles), so we don't use a witness. But if you do, nothing would happen. You just wouldn't have the witness functionality.Tara Kizer |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-02-28 : 11:58:22
|
quote:
Originally posted by tkizer
At the primary site, the cluster is Active/Active/Active/Active. At the DR site, it is just waiting for us to fail to it.
Does Active/Active/Active/Active cluster mean there is a load balance?Once again, thank you for all your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-28 : 12:02:09
|
| No. Microsoft clustering doesn't do load balancing. We can attempt to offset the load by moving our 11 instances around the 4 nodes so that no single node is overwhelmed, but that's all manual.Tara Kizer |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-02-28 : 12:07:22
|
| Thanks for such a swift reply!So what does Active/Active/Active/Active configuration mean then? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-28 : 12:12:31
|
Go back and read the rest of this thread, the questions you're asking have all already been answered  |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-02-28 : 12:21:33
|
Thanks, that helped.  What confused me for a second is that we have just one DB (albeit large one) to take care of, and I looked at the problem from that aspect (my logic was: one DB means that Active/Active is a load balancing ). So, if I got this right (finally), if our application deals with just one DB, Active/Active cluster does not make sense, because we need just one instance of DB Server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-28 : 12:28:57
|
| Yes that is correct. You need to think of instances though rather than databases. If you only have one instance installed, then your cluster would be Active/Passive. Instances are what you move between nodes. Inside each instance would be your database(s). We have 11 instances with various databases in each. Node1 has 2 instances, Node2 has 3 instances, Node3 has 3 instances, and Node4 has 3 instances. Which instances go to which node is based upon the instance utilization. Each node has only 1 very active instance, which means we have 4 very active instances total.Tara Kizer |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-03-03 : 08:50:21
|
| Hi everyone,Thank you all for your posts, they were very helpful.I just want to know your opinion on this:if I want to achieve just high availability (one instance of database is always available), without disaster recovery, what would be the better solution: failover cluster or database mirroring? Of course, in this case, the computers would be very near each other (i.e. in the same room).Your personal opinions with pros and cons, please.Thank you all in advance |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-03-03 : 12:22:08
|
| What is your budget? Do you have a SAN already? Do you have people there who can support the cluster, meaning people with cluster experience already?Tara Kizer |
|
|
|
vladimir.stokic
Starting Member
25 Posts |
Posted - 2007-03-05 : 02:27:06
|
quote:
Originally posted by tkizer
What is your budget? Do you have a SAN already? Do you have people there who can support the cluster, meaning people with cluster experience already?Tara Kizer
Budget is not a critical issue, but it is not neglectable either. There is no SAN, yet, but it will be installed should we decide to go for cluster. As for the cluster tech support, there will be people to install it, but not to maintain it all the time.Thanks |
|
|
|
Next Page
|
Database Mirroring vs Failover Clustering