Help: SQL Server

If you have searched for checkpoint in SQL Server and landed on this page, then you might get disappointed. This blog is NOT about the checkpoint background process which executes in SQL Server. This blog is about the checkpoint which is windows cluster piece.

Checkpoint Manager is the component of Cluster Service which monitors the changes in the Resources on a Cluster. In simple words, checkpoint is a process in cluster which synchronize the registry key values between the nodes. SQL Server startup depends on the registry key values. By default there are three registry keys: –d for path of master.mdf, –l is for the location of mastlog.ldf fie and –e is for the location of SQL Server ERRORLOG file. More startup parameters can be added based on requirement. For example if you are troubleshooting deadlock then you may add –T1222 as startup parameter. In case of cluster, you make change to registry key by using SQL Server Configuration Manager (a.k.a. SSCM). Once value is saved in SSCM, it would be saved in registry. Once failover is performed, the same change would be applied to other node. This is done by cluster checkpoint.

What would happen if checkpoints are not defined? The changes made on active node would not propagate to other nodes during failover. Another interesting point which you must note is that if any change is made to registry key when resource is offline, the changed would be reverted back. When the resource goes offline, the values are saved into registry.

Since this is windows piece, there are windows commands and PowerShell cmdlets available to view and edit them. In earlier version of windows, cluster.exe was used to view and edit the checkpoints and in newer version of operating systems where admins loves PowerShell, there are cmdlets exposed. Here is the list of commands to add checkpoint

Old Version (if you have cluster.exe)

cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Cluster"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\MSSQLServer"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Replication"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Providers"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerSCP"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\CPE"
cluster res "SQL Network Name (VSName)" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerAgent"

PoweShell

Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Cluster"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\MSSQLServer"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Replication"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\Providers"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerSCP"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\CPE"
Add-ClusterCheckpoint -ResourceName "SQL Network Name (VSName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.VSName\SQLServerAgent"

Here are the command to get list of checkpoint (my Virtual Server Name is RajaSQLServer).

cluster res "SQL Network Name (RajaSQLServer)" /checkpoints

Here is the PowerShell command

Get-ClusterCheckpoint

You would notice the difference in registry key values as compared to the command. This is because of the fact that the command is for SQL 2014 that’s why we are seeing “MSSQL12”. The exact value can be fetched from registry.