 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Clustering / July 2008Advantages of an SQL 2005 cluster from transaction point of view
Hi Guys, We are considering whether an SQL cluster (MSCS) may have any advantages from transaction handling point of view. I mean mscs is a solution to correct only hardware failures but it does not have any advantages on transactions level. We are making a comparison between SQL log shipping and clustered sql servers, this is why it is an important question. Do you have pros cons on any side but only from transaction point of view? As i understand transacation loss could be occur on clusters as well if a node crashes so sql log shipping could be more secure in this case. Any advantage on cluster side? Thank you for your help! Sabi Clustering will not effect the transaction count which your SQL Server can handle. Clustering is designed to keep the SQL Server running in the event of a hardware failure. In the event of a hardware failure of the active node, the passive node will start the SQL Service, and begin taking transactions usually within seconds. Log shipping will usually take a few minutes to fail over (assuming that you have it setup to do so) and begin taking transactions. Cluster service is designed as a high availability solution. Log shipping is considered a disaster recovery solution, used to ship data from one data center to another. Denny >Hi Guys, >We are considering whether an SQL cluster (MSCS) may have any advantages [quoted text clipped - 7 lines] >Thank you for your help! >Sabi > As i understand transacation loss could be occur on clusters as well if a > node crashes so sql log shipping could be more secure in this case. Not really. That is, sql log shipping is not more secure if a node crashes. If the node crahes, in-flight transactions are lost in either cases. Linchi > Hi Guys, > We are considering whether an SQL cluster (MSCS) may have any advantages [quoted text clipped - 7 lines] > Thank you for your help! > Sabi > Hi Guys, > We are considering whether an SQL cluster (MSCS) may have any advantages [quoted text clipped - 10 lines] > side? > Thank you for your help! With clustering, the database and the transaction logs are shared between nodes. What this means is if the active node completes the transaction and it is put into the log, then that transaction is not lost in the event of a node failure. If the active node fails while processes a transaction, then the transaction is likely lost. This is just like if you have a single SQL server. Most people code their apps to verify completion of the transaction to get around this potential issue. With log shipping and database mirroring, the transactions need to be copied from one node to another. It is more possible to lose transactions when going from the active environment to the remote location, especially if you are using async transfers. If you are using sync transfers, then the database itself will be hampered by the copy/shipping process and will not be able to handle the same number of transactions as if it were not performing the copy/shipping process. I hope that helps.  SignatureRuss Kaufmann, MCSE: Messaging and Security, MCT, MCITP, MCTS and other stuff ClusterHelp.com, a Microsoft Certified Gold Partner Web http://www.clusterhelp.com Blog http://msmvps.com/clusterhelp The next ClusterHelp classes are: July 28-31 in Pittsburgh Oct 13-16 in New York I understand and thank you for your replies. I found a presentation from Microsoft which says that there is zero work loss on failover clustering. PLs check it http://download.microsoft.com/download/3/d/d/3dd4aa9f-e84c-4c63-a246-d4f3d1bd5d7 b/TECHNET/SQL_SERVER_2005___HIGH_AVAILABILITY_FINAL/SQL%20Server%202005%20-%20Hi gh%20Availability%20Final.ppt#345,4,Database%20Server%20Failure%20Mitigation Interesting! Sabi > > Hi Guys, > > We are considering whether an SQL cluster (MSCS) may have any advantages [quoted text clipped - 28 lines] > > I hope that helps. >I understand and thank you for your replies. I found a presentation from > Microsoft which says that there is zero work loss on failover clustering. > > PLs check it: > http://download.microsoft.com/download/3/d/d/3dd4aa9f-e84c-4c63-a246-d4f3d1bd5d7 b/TECHNET/SQL_SERVER_2005___HIGH_AVAILABILITY_FINAL/SQL%20Server%202005%20-%20Hi gh%20Availability%20Final.ppt#345,4,Database%20Server%20Failure%20Mitigation Sorry, but there is work loss on a failover because Microsoft clustering does not save the state of the transaction. So, transactions in the fly (meaning they are processing and have not been written to the transaction log), will be lost. This also means that your client application will have to reconnect after the failover so it can connect to the node hosting the SQL cluster group. The client can be written to re-try if there is a failure so that they will then reconnect to the cluster and process the transaction after the failover in the event the transaction is not completed. What is meant in the white paper is that if the transactions are written to the transaction log, then there is no work loss on failover. I hope that helps. SignatureRuss Kaufmann, MCSE: Messaging and Security, MCT, MCITP, MCTS and other stuff ClusterHelp.com, a Microsoft Certified Gold Partner Web http://www.clusterhelp.com Blog http://msmvps.com/clusterhelp The next ClusterHelp classes are: July 28-31 in Pittsburgh Oct 13-16 in New York |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.