More Related Content
Similar to Compare Clustering Methods for MS SQL Server
Compare Clustering Methods for MS SQL Server
- 1. Available Clustering TechnologiesAlexanderPrusakovSenior SQL Server DBAJune, 2010
- 2. Consultant - Sr.MS SQL Server DBA20+ years of IT experience (business users, ERPs, databases and applications support, software development, data warehousing, etc.)10+ years of experience with MS SQL Server and RDBMSMBA, MCITP: DBA 2005/2008, IndyPASS, IndyNDA, IIBA memberIndustries: banking/finance, energy, education, health care, manufacturing, IT consulting.Speaker’s Background:
- 3. What we aretalking about and whyWhat is clustering: pure theoryClustering with Microsoft, HP, and othersPluses and minusesConclusionQ&AAgenda:
- 4. Target: Hardware failure Database availabilityImprove hardware qualityDuplicate componentsDuplicate serversMirroringLog shippingClustering
- 5. Main Entry: clusterPronunciation: \’kləs-tər\Function: nounEtymology: MiddleEnglish, from Old English clyster; akin to Old English clott clotDate: before 12th centuryA number of similar things that occur together
- 6. Two or moreconsecutive consonants or vowels in a segment of speech
- 7. A group ofbuildings and especially houses built close together on a sizable tract in order to preserve open spaces larger than the individual yard for common recreation
- 8. An aggregation ofstars or galaxies that appear close together in the sky and are gravitationally associatedMerriam-Webster Online.21 October 2009
- 9. What we aretalking aboutand why
- 10. IMPORTANT!Clustering is differentfrom mirroring and/or replication;
- 11. What is clustering:pure theoryCluster (computing) - is a group of linked computers, working together closely so that in many respects they form a single computer. Clusters are usually deployed to improve performance and/or availability over that provided by a single computer. (From Wikipedia)Cluster categorizationsLoad-balancing clusters - clusters operate by distributing a workload evenly over multiple back end nodes.Compute clusters - are used for primarily computational purposes, rather than handling IO-oriented operations such as web service or databases. For instance, a cluster might support computational simulations of weather or vehicle crashes. The primary distinction within compute clusters is how tightly-coupled the individual nodes are.Grid computing - usually compute clusters, but more focused on throughput like a computing utility rather than running fewer, tightly-coupled jobs. Often, grids will incorporate heterogeneous collections of computers, possibly distributed geographically distributed nodes, sometimes administered by unrelated organizations.High-availability (HA) clusters – (also known as failover clusters) are implemented primarily for the purpose of improving the availability of services. They operate by having redundant nodes to eliminate single points of failure. Two nodes is the minimum requirement to provide redundancy.
- 12. What is clustering:pure theory (cont)Cluster members are relatively close;Hardware capabilities are important;SCSI/iSCSI or Fiber drives only;No dynamics disks;MS DTC and Quorum – must exist (with size at least 1Gb each)Two network cards: Local – health check (LooksAlive test)Public – access point
- 13. What is clustering:pure theory (cont)Cluster organization:Logical host or Cluster logical hostis used to describe the network address which is used to access services provided by the cluster.
- 14. Nodeis physical computerwith its own IP address, OS, applications.Examples:MS SQL Server cluster – high availabilityScale upFederationOracle Real Application Cluster – load balancingScale outScale up
- 15.
- 16. IMPORTANT!Cluster is away to reduce risk of failure:Storage (if it is not clustered itself):Power failure;Storage hardware failure;Geographical location:Disaster (fire, flood, earthquake);Power outage 2003 (New York, Cleveland, Ohio, Detroit, Michigan, Toronto and Ottawa);User’s mistake:From database drop to network cable pull out;Probability of simultaneous hardware failure is not zero.
- 17.
- 18. Probability of failureMathematictheory.Murphy's law:Anything that can possibly go wrong, doesToo many, too close (hardware, software, network, humans…)"Every program has at least one bug and can be shortened by at least one instruction — from which, by induction, it is evident that every program can be reduced to one instruction that does not work." by Ken Arnold
- 19. Money, money, moneyTimeis moneyMore hardware means more moneyMore software means more moneyMore hardware and/or software means more moneyDoes it worth to go all the way?
- 20. Cluster configurationActive/Passive —Provides a fully redundant instance of each node, which is only brought online when its associated primary node fails.Active/Active — Traffic intended for the failed node is either passed onto an existing node or load balanced across the remaining nodes.N+1 — Provides a single extra node that is brought online to take over the role of the node that has failed.N+M — In cases where a single cluster is managing many services, having only one dedicated failover node may not offer sufficient redundancy. In such cases, more than one (M) standby servers are included and available.N-to-1 — Allows the failover standby node to become the active one temporarily, until the original node can be restored or brought back online, at which point the services or instances must be failed-back to it in order to restore High Availability. N-to-N — A combination of Active/Active and N+M clusters.
- 21.
- 25. IMPORTANT!Clustering on virtualservers might not protect from hardware failure.Adds complexity of sizing
- 26. Compare direct costs*- pricing details have to be complained to Microsoft Licensing
- 27. Key features:Microsoft Clusteringfor Standard Edition
- 28.
- 29.
- 30. Microsoft Clustering forEnterprise Edition 2005:
- 31. OS limited ##of CPUs;
- 32.
- 33.
- 34.
- 35. Indexed view matchingby the query processor;Key features:Microsoft Clustering for Enterprise Edition 2005 (continue):On-line restore;Fast recovery;Oracle replication;Scale-out report server;Data driven subscriptions;Advances SSAS (like proactive cashing).
- 36. Key features:Microsoft Clusteringfor Enterprise Edition 2008:Policy-Based Management;Performance Data Collection;Data Compression;Backup Compression;Resource Governor;Transparent Data Encryption;External Key Management / Extensible Key Management;
- 37. Key features:Microsoft Clusteringfor Enterprise Edition 2008 (continue):Data Auditing;Server Group Management;Hot-Add CPUs and Hot-Add Memory;Streamlined Installation;Upgrade Advisor;Partition Aligned Indexed Views;Extended Events;More, more, and more…;
- 38. Key features:HP PolyServeMatrix with MS SQL Server Standard EditionNon-NTFS file system;Increase disk size dynamically (no limits on disk size/RPM);Usage of files over 2Tb in size (16Tb limit);No need in identical hardware (Be careful!!!);Any SQL instance can be moved to any note;Software monitoring;Dynamic Re-hosting;4 or 8 Kb disk segment size;Performance cost: 10% ?
- 39. Key features:XkotoGridscale:Horizontal scalability~ 0.85*n-servers;Cost ~ 30% in software and hardware (SAN costs are not included);Ability to test new database versions in real production environment;Storage redundancy ;Flexibility;Time stamps?
- 40.
- 43.
- 44. How PolyServe works:Whathappens during failover:Removes the IP address for the Virtual SQL Server from the original node and then adds it to the backup node;Updates registry keys and then starts SQL Server and SQL Agent on the backup node;
- 45.
- 46. PolyServe Matrix DiskManagement:
- 47. PolyServe Matrix DiskManagement:
- 48. PolyServe Matrix DiskManagement:
- 49. You get whatyou paid for:Microsoft:Hardware is very important;Huge advantage in features;You already have it all;Easy to play with;A lot of documentation;A lot of knowledge around;Cheaper labor (more people know).
- 50. You get whatyou paid for:HP:HP – is HP (customer support, buyer experience);Costly training and limited ($2500 pp);Limited accessibility to working cluster;Limited resource pull;Limited second opinion option;Clouded future.
- 51. Think, analyze, thinkagain – it is not black and white;What is the real business need;Create clear business requirements;Do cost – benefit analysis because cost of the solution should be less than benefits from implementation;Negotiate;Create a clear plan;Conclusion:
- 52. 100% utilization isfiction:80% per box utilization;N+1 configuration;Do you really like that vendor;Evaluate alternatives (SSD, SST)How good is your SysAdmin;Do not be afraid – clustering is easy;Just Do It!Conclusion (continue):
- 53. Internet – findyour way.http://www.sql-server-performance.com/articles/clustering/clustering_intro_p1.aspxhttp://en.wikipedia.org/wiki/Computer_clusterVendor –pushing sales technique.http://www.microsoft.com/windowsserver2003/enterprise/clustering.mspxhttp://technet.microsoft.com/en-us/magazine/2007.03.sqlclusters.aspxhttp://h18006.www1.hp.com/products/storage/software/polyserve/support/hp_polyserve.htmlhttp://www.xkoto.com/products/gs-sql.phpWhere to look for more:
- 54. Email: a_prusakov@yahoo.comQuestions &Open DiscussionThank You!