Some things to consider before installing the ConfigMgr database on a SQL cluster

Note: I’ve already posted this article a while ago, but since my ex-employer decided to whipe the whole website without asking me if I would like a backup it got lost. Therefore i decided to place it here again. Note that this is still valid for ConfigMgr 2012 installations, i’ve modified the article to reflect that.

The System Center Configuration Manager (ConfigMgr) database can be installed on a clustered SQL Server, some things work different though compared to installing ConfigMgr on a normal SQL server installation, and you should be aware of them before starting your installation.

To summarize in front, what you should take into account before starting your installation:

1. Make sure that the SQL administrators are aware that during installation extra righs are needed and that after installation the Site server must stay local administrator on the cluster nodes
2. Make sure that the SQL administrators are aware of the fact that ConfigMgr will install a service on the Cluster Nodes, which is used for creating the backup using VSS
3. Make sure that each shared cluster disk contains a no_sms_on_drive.sms file on the disk
4. Configure the Site Backup tasks, so that it is either configured to backup to an UNC path or a drive which is suitable for storing the backup files on both the site server and the cluster nodes using the “Different Paths for the Site Backup and Database backup” option

First off all, before you can even start the installation, you must make sure the following prerequisites are taken care off:

• The computer account of the site server and the account used to execute the installation should be made local administrator on all the cluster nodes.
• The user running the ConfigMgr installation must have sysadmin rights on the instance of the remote SQL server
• The correct Service Principal Names must be registered in Active Directory

Please note that after installation, the user performing the installation can be removed from the local administrators group on the cluster nodes, and its sysadmin rights on the SQL instance can be removed.

Before actually starting the installation, you should run the prerequisite checker in order to determine if al prerequisites are met.

The installation procedure creates the database on the SQL cluster, including the necessary rights for ConfigMgr to function normally, unfortunately there is no SQL script available to create the database upfront.

The installation also installs a service on each of the cluster nodes, used for backing up the database. This service is called SMS_SITE_SQLBackup_<machinename-netbios> in case of a ConfigMgr 2007 installation and SMS_SITE_SQLBackup_<machinename-fqdn> in case of a ConfigMgr 2012 installation and this service is reponsible for creating the database backup, which you configure as a maintenance task later on.

There are two caveats here, which aren’t clearly explained in the documentation on technet:

1. First of all, the service is installed on the disk which has the most free space available, and ConfigMgr setup doesn’t take into account whether this disk as a cluster resource or not. In order to prevent ConfigMgr to install its binaries on a cluster shared disk, make sure that you create a no_sms_on_drive.sms file on each of the cluster disks before starting the installation of ConfigMgr

1. As just mentioned, you configure the backup using a maintenance task. When configuring this maintenance task, you also provide a location to store the backup. In this case though, the location on where to store the backup files should be configured differently between the Site server and each of the cluster nodes. You can accomplish this by choosing the “Different Paths for the Site Backup and Database backup” option and provide a suitable location on where to store the backup files.

The above solution has some disadvantages though, if you choose a cluster shared disk to store the backup files on the cluster, there is no problem. But if you choose a local disk you should be aware that the most recent SQL backup resides on the drive of the cluster node which was the owner of the resource at the time the backup was made.

It may be better to store the backup on a central location though, so that you have one location which you backup using your normal backup solution. In order for this to work, you should choose the option: “Network path (UNC Name) for site data and database”

Keep in mind that when using UNC Backup, that you need to authorize the computer accounts of the Site server and each cluster node, with write access to the share.

What to do if you forgot this upfront:

At the blog of the Mad Virtualizer, someone who provides Virtualization and Manageability Support at Microsoft, I found a solution to a similar problem, for me the solution worked a little bit differently, so therefore i described the solution the way i implemented it below.

If you didn’t put the no_sms_on_drive.sms file on each of the cluster shared disks, and you only have a C: drive on your cluster nodes, there is a big chance that on the non-active cluster node the files needed for the backup service are installed on the c: drive, and on the active cluster node on the shared cluster disk with the most free space available at that time. As long as the resources stay on that node nothing is wrong, but once the cluster resource moves, things start to go wrong resulting in non-working backups.

In order to reconfigure the cluster node so that the files are installed locally instead on a cluster shared disk you should use the following procedure to reconfigure the server.

1. Stop the SMS_Site_SQLBackup_<site server name> service on the cluster node on which the binaries are installed on a shared cluster disk
2. Remove the following registry key on the cluster node (make sure you make a backup first in case something goes wrong) HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\SMS\Components on a 64-bit installation or HKEY_LOCAL_MACHINE\Software\Microsoft\SMS\Components on a 32-bit installation (Note that on ConfigMgr 2012 the components are now fully 64 bit so the keys can be found under HKEY_LOCAL_MACHINE\Software\Microsoft\SMS\Components
3. Browse to the following registry key on the site server:

HKLM\Software\(Wow6432Node\)Microsoft\SMS\Components\SMS_SITE_Component_Manager\Multisite Component Servers\<cluster node name> and modify the value of path to a path on the C: drive

1. Browse to the following registry key on the site server:

HKLM\Software\Microsoft\SMS\Components\SMS_SITE_Component_Manager\Multisite Component Servers\<cluster node name>\SMS_Site_Backup and modify the value of “Installed At Least One File” to 0

1. Restart the SMS_SITE_COMPONENT_MANAGER service on the Site Server
2. Monitor the Sitecomp.log file on the site server for succesfull installation can be found in the ,<ConfigMgr installation folder>\Logs folder
3. Check the backup by starting the SMS_SITE_BACKUP service on the Site server and checking the logfile to determine if the backup succeeded.

References:

• How to Install Configuration Manager Using a Clustered SQL Server Instance- http://technet.microsoft.com/en-us/library/bb680513.aspx
• Prerequisites for Installing Configuration Manager – http://technet.microsoft.com/en-us/library/bb694113.aspx
• Setup Prerequisite Checks – http://technet.microsoft.com/en-us/library/bb680951.aspx
• Troubleshooting Duplicate or Missing SPNs for a ConfigMgr 2007 SQL Database – http://blogs.technet.com/b/configurationmgr/archive/2010/10/26/troubleshooting-duplicate-or-missing-spns-for-a-configmgr-2007-sql-database.aspx
• How to Prevent Configuration Manager From Installing Files on a Specific Drive – http://technet.microsoft.com/en-us/library/bb632890.aspx
• SCCM (Configuration Manager) Installed Component Provider Binaries on SQL Clustered Drive (or other shared clustered drive) by Mistake. – https://madvirtualizer.wordpress.com/2011/01/14/sccm-configuration-manager-installed-component-provider-binaries-on-sql-clustered-drive-or-other-shared-clustered-drive-by-mistake/
• Backup ConfigMgr Site Server Task Overview – http://technet.microsoft.com/en-us/library/bb633003.aspx