February 8, 2008 at 2:15 pm
Hey all,
I'm very familiar with mirroring, but I've never really done anything with clustering. Just for knowledge sakes, what really are the main differences between clustering and mirroring and why would you choose one over the other?
Thanks
February 8, 2008 at 4:31 pm
refer link qa.sqlservercentral.com/articles/Disaster+Recovery/sqlserver2005highavailability/2421
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 10, 2008 at 8:19 pm
The two can be combined together. Database can be mirrored from one cluster (SQL virtual Server) to another.
February 11, 2008 at 1:18 am
Major difference is Cost; Cluster is expensive as it require identical hardware and expensive storeage SAN/SCSI.
Mirror is configured on database level while Cluster is set up on Instance/Server level.
If your requirement is multiple database mirroring than Cluster is better option.
In addtion to this Cluster is having Virtual IP & SQL Server Instance name while in Mirror Principle & Mirror is different.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 11, 2008 at 4:22 am
Check if the below link helps,
http://sql-articles.com/index.php?page=articles/diffdbmrr.htm
[font="Verdana"]- Deepak[/font]
August 25, 2010 at 5:45 am
Please visit the page :-
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx
Thanks,
Abhishek Singhal
Project Manager- HCL
August 25, 2010 at 6:26 am
Server Level VS. Database Level Solution
Clustering is a Server Level Solution, if one node fails, reboots, shuts down, or is taken offline the resources owned by that node (if any) are moved to another node in the cluster (provided one is available).
The SQL Server Service is offline while a failover occurs. When SQL comes back online the databases go through recovery, rolling out the uncommitted transactions and writing the committed transactions to disk, before bringing the database online.
Mirroring is a Database level solution. You can Mirror only one database on an instance with multiple databases, you can failover one database on one instance and have that same instance serving as principal to another mirrored pair of databases.
Automatic Failover
Both can be configured for automatic failover, mirroring has 3 different operating modes, the only mode that has automatic failover is High Safety with automatic failover. When you do this mirroring requires 3 instances of SQL a Principal, a Mirror, and a Witness. You could combine them but that would make any combination a single point of failure as you need at least 2 instances to be online and one of them must be the Witness (and technically the other being the mirror) in order to perform automatic failover.
Hardware
Either option could get really expensive really quick, but you would want your hardware to line up for mirrors as well as clusters in case you had a failover. The witness doesn't need to be as powerful as a Principal or Mirror Servers in mirroring, and you can reuse the witness for your other database mirrors as well.
Database Settings
For Clustering you can have whatever recovery model that you like, Simple, Full, Bulk-logged it doesn't matter.
Mirroring requires your databases be in Full.
SQL Edition
for a 2 node cluster you could go Standard on 2005 or 2008 or 2008 R2, but you would loose other options that if your business uses may not be worth it. You would do well to investigate this
Mirroring requires Enterprise Edition.
Health check
Mirroring doesn't do health checks, if your database is online but your server has the processor spike because something is grabbing all the processor space, it will not failover.
Clustering does a Health Check as well as a ping test and if one fails it repeats, and if it continues to fail then a failover is initiated.
Page Repair
High Safety High Availability mirroring in SQL 2008 and above where mirroring is enabled and Page_verify is set to checksum has automatic page repair. the page will still get logged in the suspect_pages table, and i haven't had this occur in to me personally so I can't vouch for it but it is an option that is out there.
Both are good, and there are very valid reasons to use each, and some places use a combination of the 2 (Mirrored Clusters). Hope this help.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
August 25, 2010 at 6:47 am
Bradley B (8/25/2010)
Mirroring requires Enterprise Edition.
Mirroring requires Standard or Enterprise editions of 2005\2008
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 25, 2010 at 7:01 am
Your right should have specified, HA. Mirroring is available in Standard however it is single threaded and High Saftey, the automatic failover option which would make it on par with clustering for automatic failover requires Enterprise Edition.
http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.105).aspx#High_availability
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
August 26, 2010 at 6:49 am
The difference is that you must dot a lot more i's and cross a lot more t's with clustering. It can really be a PITA. If you go down that road, get some good training and/or consulting before you just try to 'slap one together' or it will end poorly.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2010 at 8:44 am
IMHO replication can be even more of a royal PITA, clustering is fairly smooth as long as you know\understand the technology!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 9:03 am
Please note: 2 year old thread.
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
August 26, 2010 at 12:27 pm
GilaMonster (8/26/2010)
Please note: 2 year old thread.
originally yes, but not now 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 6:03 pm
If you can afford it have both!
Clustering for high availability and mirroring for reporting 🙂
September 13, 2010 at 2:09 am
In database Mirroring can I have a virtual IP...
Please tell fast..
Actually we were trying to install the sql server cluster but thats not possible bcoz it is in production of Domino Server.My confusion is "If one database goes down in database mirroring then other will come up but because there is no Virtual IP type of thing in this then how my application will connect to another without manual interruption of IP change..."
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply