First published on TECHNET on Jun 11, 2014
Authored by Santos Martinez
You implemented a SQL Cluster for SysCtr 2012 R2 ConfigMgr and you forgot what?
I was configuring a SQL Cluster this past week and I always try to configure it the best way I can. The cluster was an all-in-one cluster type for multiple System Center Products. One of the key things I always remember to do is to configure each drive the cluster uses. When we are Configuring ConfigMgr and we specify where we are going to store the data there is an important component that often gets missed. This component is the Backup Service and Monitoring for ConfigMgr.
The Problem
The component thread is SMS_SITE_SQL_BACKUP_SERVERNAME.
More Information about this component https://docs.microsoft.com/en-us/previous-versions/system-center/configuration-manager-2003/cc180537...
The main problem is when the component is trying to install on the cluster it installs on any available drive; therefore, so you need to be careful because of this behavior. The component may install on the cluster drive and this is where problems are going to start.
The main solution is simple. Before you install the ConfigMgr on the cluster you need to create a no_sms_on_drive.sms file and include it on every clustered drive and the C drive. What I normally recommend is having another local drive with space to install this service. This will be a non-clustered drive of course, in my case I use the P Drive:
This is how a clustered drive should look:
And this is how a clustered drive should not look:
Now we all can make mistakes (that includes me) and that is why I am writing this blog post: so I do not forget again.
You may ask yourself, “What happens if you leave it on the clustered drive?”
Well, as soon as the cluster failover to the other node you will start receiving several errors that are visible in the SMS_SITE_SQL_BACKUP_SERVERNAME component thread. Here is an example of some of those errors:
Here you can see the node is in a Critical State. Then, I proceed to validate the service is running.
As you can see in here the service is not available> Next, I proceed to look at the status messages. I found the following Message IDs: 1020, 1076 and 1093. Each of these Status Message indicates there is a problem with the path that is in use on the cluster.
Solution
To fix this problem you will need to copy the files from the clustered drive to the local drive:
Once the files are on the local drive:
We then need to perform some registry changes. Warning: Perform these changes at your own risk.
Open Regedit.exe on the Primary Site Server or CAS that manages the affected clustered site database and go to HKLM\Software\Microsoft\SMS\Components\SMS_SITE_COMPONENT_MANAGER\Multisite Component Servers\<servername>\Installation Directory
You will change the Drive only, on my case from J: to P:
On the affected SQL Node, you will also perform the following registry change:
Open Regedit.exe on the SQL Node and go to:
HKLM\SOFTWARE\Microsoft\SMS\Tracing\SMS_SITE_SQL_BACKUP_SERVERNAME
Once you perform these changes, restart the following services on the site server.
SMS_Site_Component_Manager and SMS_Executive.
It may be better to restart the site server, but I will leave that up to you.
Confirmation
To confirm your changes have taken effect , the best way be to validate it again is in the Monitoring Workspace and check the SMS_SITE_SQL_BACKUP_SERVERNAME component thread.
Conclusion
When setting up a SQL Cluster that used for ConfigMgr site database, the first step that you need to do is confirm that there is a local drive available for the SMS_Site_SQL_Backup_XXX Service and that a no_sms_on_drive.sms file exists on every clustered drive.
Santos Martinez – Sr. PFE – ConfigMgr and Databases
Steven Hernandez – PFE – ConfigMgr Contributor
Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use