Windows Server 2008 Cluster: Understanding the Cluster Log Behavior

In any system, logs are very important to troubleshoot the issue. In the case of windows cluster, cluster logs are like a black box which will have all information about the failure of cluster.This is very important information, that we need, to raise a case with Microsoft. In this post let us discuss only about cluster log behavior and generating the cluster log.In the coming post we will,discuss about deciphering the cluster log.

When you trouble shoot the cluster issue, I am sure that, in most of the cases  cluster log will give you the root cause of the issue. You can link the windows event log entries for further analysis.In windows 2008, the cluster logs are captured using the new eventing and diagnostic channel called ETW (Event Tracing for Windows). You can see this tracing in the Reliability and Performance Monitor  under "Data Collector Sets\Event Tracing Session". Below is the snapshot of the same.

The log file generated by ETW are stored in the folder %windir%\system32\winevt\logs

Each time the server is rebooted, a new file will be generated like clusterlog.etl.002 and start logging in that until the server is rebooted again. Up to 3 log files are kept, so after the third  consecutive reboot, you will start loosing old log entries. Please find below a snapshot of etl file in the folder.

For example, in the above case, after two consecutive restart on 29th september, we lost all the logs till 12th september. If we had one more reboot on 29th, we might have lost all the logs till 29th. The ETL log file name incremented each time and has 00X suffix appended to it. Once the maximum number of log files (3) reached, it will start overwriting the first one.At any point of time, only one log file being actively used.

The default log file size is 100 MB (for each etl file). Once the file reached the limit of 100 MB, it will start deleting entries from the beginning of the file to make room for the current logging. In our case the active log is 'ClusterLog.etl.003' which created on 29th September 2012 1:41 AM and it is reached the limit of 100MB. Now the log entries at the beginning of the file ClusterLog.etl.003 will get deleted to make room for new entries.

Generating Cluster Log

Now we have the cluster log spread across three ETL files.The easiest way to read the ETL files is, use the Cluster Log command from the command line. The syntax is :

cluster log /g

This will merge the three etl files of each node and create a output file cluster.log. The output file will be stored in the %windir%\Cluster\Reports directory on each node of the cluster. 

One more interesting switch available for Cluster Log command is /Span:<minutes> , Which will help us to generate the log only for last 30 minutes. For example /Span:15. This will help us to quickly troubleshoot the recent issues.

Missing entries in the cluster log

When you generate the log using the cluster log , in the output file, you might have notice a gap of log, that is log is not available for some days in between. This is happening due to the truncation of log once it is reached the limit of 100 MB. In our example, the etl file ClusterLog.etl.003' which created on 29th September 2012 1:41 AM, has already reached 100MB limit and might have truncated some data at the beginning. So when you merge the etl file using the cluster log command, you can notice a gap after 29th September 2012 1:41 AM. May be for some hours or days/week. In the output file ,you might have log from September 12th 5.21 AM  (might have truncated at the beginning as it reached 100 MB) till 29th September 2012 1:41 AM. These entries are coming from the ClusterLog.etl and ClusterLog.etl.002. After that you might  notice a gap of log data for few hours/days as the data at the beginning of ClusterLog.etl.003 got truncated to make room for current log entries.

Changing the cluster log configuration

we can change the default configuration of cluster using the following command. To change the default size use the below command.

cluster log /size:400

This will change the default size of the log to 400 MB.

You can check this change using the command

cluster /prop

If you liked this post, do like my page on FaceBook 

Windows Cluster: Moving Quorum Disk to Another Node

In my earlier post I have explained about various quorum settings available in Microsoft windows 2008 cluster environment. While we plan to perform the maintenance in a cluster environment ,as per the design, it is not mandatory to move  the resources and witness disk manually  to another node. While cluster services is stopping on a node, resources owned by that node will automatically be arbitrated to another node in the cluster. 

I personally do not prefer this approach.I prefer to move all the resource manually to another node while we planning to perform maintenance in cluster environment. SQL instances, MSDTC and any other application/services can be moved using the UI, but to move the witness disk we have to rely on the command prompt.

Open a command prompt and type cluster group ,it will list all the group available in the windows cluster as shown in the below picture.

Fig 1

From the Fig-1 you can see SQL server groups,MSDTC group and other two groups.These two groups(available storage and cluster group) are created by default in windows 2008 cluster. The quorum disk resides in the group 'Cluster Group'. In the Fig-1 the 'cluster group' is owned by the node5. To move it other node we have run below command.

cluster group "Cluster Group" /move 

In you have more nodes in your cluster and to move the 'cluster group' to a specific group use the below command.

cluster group "Cluster Group" /moveto:node5

If you liked this post, do like my page on FaceBook

Windows Cluster : Understanding the Quorum settings

In my earlier post , I have explained about the windows cluster and how Sql server works on cluster environment. In this post let us try to understand the quorum settings of windows cluster environment. When I say quorum, do not interpret as quorum disk. Quorum has literal meaning in the cluster environment. In this post I will use the word witness disk to refer the quorum disk.Let us see what are all the possible quorum settings and how it will affect the windows cluster.

What is a quorum ?

As per Wikipedia, quorum is the minimum number of members of a deliberative assembly necessary to conduct the business of that group. In short quorum is minimum number of votes required for majority.As I explained in my earlier post, the nodes participating in the windows cluster are connected through a private network and communicate through User Datagram Protocol (UDP) port 3343.The quorum configuration in a failover cluster determines the number of failures (failure of nodes) that the cluster can sustain while still remain online. If additional failure happened beyond this threshold, the cluster will stop running.Quorum is designed to handle the Split Brain scenario. When nodes are unable to communicate each other, each node assume that, resource groups owned by other nodes have to brought online. When same resource brought online on multiple nodes at the same time,data corruption can occur. This scenario is called Split Brain.

Let us assume that we have four node cluster and one instance of sql server is running on each node. Node1 and Node2 lost the communication with Node3 and Node4. Node1 and Node2 can communicate each other and  Node3 and Node4 can communicate each other. In this scenario each group does not know what happened to other  two nodes. Are they offline or just a communication failure ?. In this scenario, Node1 and Node2 try to bring online the Sql instance(resource) owned by Node3 and Node4. In the same way Node3 and Node4 will try to bring online the Sql instance (resource) owned by the Node1 and Node2, which will lead to disk corruption and many other issues.The windows cluster quorum setting is designed to prevent this kind of scenario.By having the concept of quorum, the cluster will force the cluster service to stop in one of the subsets of nodes to ensure that there is only one true owner for the particular resource group.

Voting

Having quorum (majority) is based on the voting algorithm where more than half of the voters must be online and able to communicate each other. The cluster knows how many node are used to form the the cluster and will know how many votes constitutes a quorum. If the number of votes drop below the majority, the cluster service will stop on the nodes of that group.Cluster requires more than half of the total votes to achieve the quorum.This is to avoid the tie in the number of votes. In a 8 node cluster , 5 voters must be online and able to communicate each other to have quorum. Because of this logic, it is recommended to always have an odd number of total voters in the cluster and the quorum setting define the the voters in a cluster.This does not necessarily mean an odd number of nodes is needed to form the cluster since both a witness disk (quorum disk) and a file share can contribute a vote, depending on the quorum settings.

Quorum Settings

Windows 2008 cluster supports four quorum models.

1 Node Majority 

2 Node and Disk Majority 

3 Node and File Share Majority

4 No Majority (disk only)

Node Majority: Node majority option is recommended for cluster with odd number of nodes.This configuration can handle a loss of half of the number of cluster nodes rounded off downwards. For example , a five node cluster can handle failure of two nodes. In this scenario three of the nodes (N1,N2,N3) can communicate each other but other two(N4 and N5) are not able to communicate. The group constituted by three node have the quorum (majority) and cluster will remain active and cluster service will be stopped on the other two nodes (N4 and N5). The resource group (sql server instance) hosted on that two nodes goes offline and come online on one of the three nodes based on possible owner settings.

Node and Disk Majority: This option is recommended for cluster with even number of nodes.In this configuration every node gets one vote and witness disk (quorum disk) gets one vote which makes total votes a odd number. The witness disk is a small ( approx 1 GB ) clustered disk.This disk is highly available and can failover between nodes. It is considered as part of the cluster core resource group.In a four node cluster, if there is a partition between two subsets of nodes, one of the subset will have witness disk and that subset will have quorum and cluster will remain online. This means that the cluster can lose any two voters,whether they are two nodes or one node and the witness disk.

Node and File Share Majority: This configuration is similar to the the Node and Disk Majority, but in this case the witness disk is replaced with a file share which is also known as File Share Witness Resource (FSW). This quorum configuration usually used in multi-site clusters (nodes are in different physical location) or where there is no common storage. The File Share Witness resource is a file share in any server in the same active directory which all the cluster nodes have access to. One of the node in the cluster will place a lock on the the file share to consider that node as owner of the file share.When this node goes offline or lost the connectivity another node grabs the lock and own the file share.On a standalone sever, the file share is not highly available , however the file share can also put on a clustered file share on an independent cluster,making the FSW clustered and giving it the ability to fail over between node. It is important that, this file share should not put in a node of the same cluster, because losing that node would cause for loosing two votes. A FSW does not store cluster configuration data like witness disk. It contain information about which version of the cluster configuration database is most recent.

No Majority (Disk only) : This configuration was available in windows server 2003 and has been maintained for compatibility reason and it is highly recommended not to use this configuration. In this configuration,only witness disk has a vote and there are no other voters in the cluster. That means if all nodes are online and able to communicate , but when witness disk failed or corrupted, the entire cluster will go offline.This is considered as single point of failure.

Hope you got a fair idea about various quorum settings available in windows 2008 cluster.

If you liked this post, do like my page on FaceBook

SQL SERVER:How Can I Get Notification Alert When Fail Over Happened ?

It was long time I was searching for simple solution to get notification alert in my inbox when ever fail over happened in the cluster environment. SCOM will help us to do that , but I do not have direct control over it and the mail first goes to the Infrastructure support team and they forward the alert to the respective team.

Let us see how  DBA's can schedule an email alert with out depending on the SCOM or any other third party tool. When  fail over happen, both SQL server and Agent service will get restarted.In sql server job scheduler, there is an option to schedule a job when SQL server agent starts. For that we have to select the schedule type as "Start Automatically when sql server agent start".

To get an alert when a fail over happened, create a job with following code in the job step

DECLARE @importance AS VARCHAR(6)
DECLARE @body AS NVARCHAR(1000)
DECLARE @Subject  AS NVARCHAR(1000)
DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)
DECLARE @recipientsList VARCHAR(100)SELECT  @recipientsList ='abc@yahoo.com,xyz@gmail.com'
SELECT  @InstanceName =@@SERVERNAME
SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))
SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName
SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName
SELECT  @importance ='High' 
EXEC msdb.dbo.sp_send_dbmail
     @profile_name ='MyMailProfile',
     @recipients=@recipientsList,
     @subject = @subject ,
     @body = @body,
     @body_format = 'HTML' ,
     @importance=@importance


In the schedule pane select schedule Type as "Start Automatically when sql server agent start".

Do this on all instances of the cluster and it is ready . Wait for next fail over. Mail will be there in your inbox.Please be careful that you will get a mail even if the instance got restarted in the same instance or you just stopped and started the agent service. But that can be easily ignored or can be avoid by tweaking the above code little bit.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

Windows 2008 Server : Moving Cluster Quorum Disk to New SAN

In my earlier post, I have explained how to move the MSDTC disk to new SAN. In this post we will go through the procedure to move the quorum drive to the new SAN. Follow the steps mentioned in the earlier post to add the new disk to the Available Storage Group.

In our environment the existing Quorum drive is Q and new designated drive is X. Once the new drive is available in the Available Storage Group, follow the steps given below:

• Open the cluster manager and select the cluster group in the left pane.
• In the right pane , you can see an option called More Actions. On clicking on that , a popup  menu will be opened as given below.

• Click on the first option, Configure Cluster Quorum Settings, which will open a screen as given below.

• Select Next button , which lead to to Quorum configuration settings screen.Select appropriate setting based on your environment . The default is the second option and that might suits to almost all environments.

• On clicking next , disk selection page will open. Select the appropriate disk. In our case we have to select the X drive.You can expand the disk to see the drive letter. 

• After selecting the appropriate disk, click on Next which will lead to Confirmation page and on clicking next from the confirmation page , the quorum will be moved to new disk. Now you can see a folder Cluster in the new drive (X). The old drive will be available in the Available Storage Group. 

Moving Quorum is completely an online operation and does not required any down time. If you really want to keep the drive letter Q for the Quorum drive , we can do it in two ways. 

• Unassign the drive letter Q from the  old drive and change the drive letter of new quorum drive(X) to Q , but you will get a warning message and I did not proceed with this as I do not want to take any risk with our cluster environment.
• The second method is , change the drive letter of the old disk to any available drive letter. For example Y. Follow the steps mentioned earlier to move the quorum to  Y drive. Change the drive letter X to Q. Again follow the same step to move to Q drive which is the new quorum drive.

I followed the above steps to move the quorum disk to new SAN and it worked well.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

Microsoft Failover cluster 2008 : Moving MSDTC to new SAN disk

We have four node cluster on Windows 2008 where the MSDTC disk hosted on a SAN array. Now we have to move the MSDTC disk to new SAN drive as we are going to decommission the existing SAN. In this post  I will explain the steps that we have followed in our environment to move the MSDTC disk to new SAN.The same steps can be followed for configuring MSDTC in a new cluster environment.

From the current host server(the node where quorum drive visible) ,open the Failover Cluster Management. In the left pane expand the Services and Application group and select the MS-DTC resource. From the summery window note down the Server network name and IP address to use in our later steps.

The next step is to get  the new SAN disk in the cluster. To add the disk to the cluster follow the steps given below:

• Open the windows disk manager.
• Right click the new disk  in Windows Disk Management and select online.
• Right click again and select initialize.
• Finally format it by right clicking the disk and selecting New Simple Volume.
• This will open Simple Volume Wizard. Provide the new drive letter(For Example U) and label for the disk.
• In the Cluster Manager ,select the storage in the  left pane.
• Select the Add Disk in the Action pane and select the proposed new disk.

At this point we can see this disk under the available storage group. Now everything is ready to make the move. Unfortunately we can't move existing MSDTC to the new disk directly.The only option is delete the existing one and add new MSDTC. 

Follow the below steps to delete and add new MSDTC resource:

• In the cluster manager ,expand the services and applications
• Right click  the MSDTC  group and select Delete.
• Now the existing MSDTC  disk (in our environment X drive) will be available under the available storage.
• Select the old disk (X drive) under the storage and click on change drive letter option available in the activity pane
• Select None as new drive letter and click Ok button
• Select the new disk ( U drive ) under the storage  and click on change drive letter option available in the activity pane
• Select X as new drive letter and click Ok button
•    The last four  steps are done to keep the drive letter for new disk as same as the old one. These four steps are not mandatory to perform.
• Right click on the Services and Application and select Configure Services or Application option
• This will open the high availability wizard screen.
• Select next which will list Services or application that can be configured for the high availability.Select the Distributed Transaction Coordinator(DTC)

• Select Next which will open the client access point configuration screen as given below.Enter the network name and IP address which we have noted down in the first step.

• Select Next,which will open the storage configuration screen.This will list all the disk available under the available storage group. Select the appropriate disk and click Next

On clicking Next, it will directed into the confirmation screen and click again on Next will complete configuration. Now the MSDTC is configured in the SAN disk and can be verified by opening the disk in the windows explorer. Now we should be able to see the MSDTC  folder inside the new drive which confirms the movement. Now bring this service online.Set the preferred owner through the properties window and test the failover.

In the later post I will be explaining about quorum disk and System/user database movement to the new SAN array.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba