SQL SERVER – GetRegKeyAccessMask : Could Not Get Registry Access Mask For Registry Key – SQL Server Cluster

I have been getting many requests from my HIRE-ME page and a few of them are getting change to my blog. This is the outcome of one of my clients who was having a strange issue. They were having 2 nodes SQL Server Cluster (NODE1 and NODE2) and SQL Server resource was not able to come online on NODE21 but it was working fine when they failover to NODE2.

SQL SERVER - GetRegKeyAccessMask : Could Not Get Registry Access Mask For Registry Key - SQL Server Cluster fci-no-online-01-800x824

I asked them to look at ERRORLOG when they failover to NODE2 and it doesn’t come online. SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location

I was surprised when they told me that they are not seeing the file being generated when they attempt to bring SQL to NODE2. I thought it could be a permission issue, but there was no such permission error in Application and System Event Log. Finally, I asked them to generate Cluster Log SQL SERVER – Steps to Generate Windows Cluster Log?

Here is the relevant information I found in Cluster log

Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "Software\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\Replication"
Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\MSSQLServer"
Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\Cluster"
Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\SQLServerAgent"
Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\Providers"
Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\CPE"
Add-ClusterCheckpoint -ResourceName "SQL Server (SQL_INST1)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL_INST1\SQLServerSCP"

Error 2 = The system cannot find the file specified. So now, it’s clear that we are having issues with registry missing on this node. When I compared the key, I found that they were having missing startup parameter earlier which they added manually. Now this is next key which is missing. In general, windows cluster registry checkpoint takes care of syncing the value of registry keys for SQL Server. Here is an article by Balmukund about registry checkpoint Information: Checkpoint in SQL Server Cluster Resources

I was able to re-add the checkpoint using below PowerShell command.

As soon as the checkpoint was enabled, the registry came in sync on both the nodes and issue was resolved. Have you ever come across such issues?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Fix Error – Cannot use the backup file because it was originally formatted with sector size 4096 and is now on a device with sector size 512
Next Post
SQL SERVER – Whitepaper – Optimizing SQL Server Network Performance

Related Posts

2 Comments. Leave new

  • Pinal, in your article you said “I was able to re-add the checkpoint using below PowerShell command.” However, the command is not shown in the article. Thanks

    Reply
  • @Duane Lopez you can export that registry form other node and then import it in.

    Reply

Leave a Reply

Menu