When working with SQL Server, it is not uncommon to encounter various issues that can lead to the same symptoms. In this blog post, we will explore one particular reason that can cause empty startup parameters in SQL Cluster.
During my experience with a client, I discovered that registry permissions can be a potential cause for empty startup parameters in SQL Server Configuration Manager. However, in this specific scenario, the account was already part of the Local Administrators group, so permission was not the issue.
Upon further investigation, I found that the registry key itself did not contain any parameters. The specific registry key I examined was:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer\Parameters
Interestingly, on one node, we were able to see values in both the registry and the SQL Server Configuration Manager. This discrepancy raised more questions and led us to explore a potential workaround.
Based on my understanding of Windows cluster, it appears that the issue lies with registry key checkpointing. We discovered that there were some issues with the cluster resources, so my client decided to recreate them manually. As a result, the checkpoints were missing.
To address this issue, we utilized PowerShell commands to add registry checkpoints. Here are the commands we used:
Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Cluster" Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQLServer" Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Replication" Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\Providers" Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\SQLServerSCP" Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\CPE" Add-ClusterCheckpoint -ResourceName "SQL Network Name (MSSQLSERVER)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\SQLServerAgent"
Please note that you should modify these commands based on your specific environment’s registry keys and resource names.
By adding these registry checkpoints, we were able to resolve the issue of empty startup parameters in SQL Server Configuration Manager.
It is important to remember that troubleshooting SQL Server issues can often involve exploring various possibilities. In this case, understanding the role of registry permissions and checkpointing helped us identify and resolve the problem.
I hope this blog post has provided you with valuable insights into the potential causes of empty startup parameters in SQL Server Configuration Manager. If you have any questions or would like to share your own experiences, please feel free to leave a comment below.