Menu
Modern Workplace Blog
  • Home
  • About: Kenneth van Surksum
  • Cookie Policy
Modern Workplace Blog
November 27, 2013November 27, 2013

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.

clip_image001

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”

clip_image002

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
Tweet
Follow me
Tweet #WPNinjasNL

3 thoughts on “Some things to consider before installing the ConfigMgr database on a SQL cluster”

  1. Glen Grady says:
    May 8, 2014 at 2:13 am

    Thanks very much, this has really helped me understand why the SQL component of our SCCM backup wasn’t working consistently and finally sort out the underlying issues in having it on a separate SQL Cluster; most appreciated.

    Reply
  2. Pingback: System center configuration manager 2012 R2 (Part1)installation | Walid Hegazy
  3. GS says:
    September 24, 2015 at 3:16 pm

    I am installing CAS site and remote SQL is cluster node. We have dedicated instance created for SCCM using 1435. For some reason when SCCM installation reaches to select database path selection area, I am not able to browse location for database or neither able to manually type the path.

    Do you know what is going wrong?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Founding member of:

Recent Posts

  • Azure AD Conditional Access authentication context now also available for Azure AD Privileged Identity Management
  • December 2022 update of the conditional access demystified whitepaper and workflow cheat sheet.
  • Conditional Access public preview functionality reviewed (22H2) – Part 3: Granular control for external user types
  • Conditional Access public preview functionality reviewed (22H2) – Part 2: Conditional Access filters for Apps and Workload Identities
  • Conditional Access public preview functionality reviewed (22H2) – Part 1: Authentication Strength

Books

System Center 2012 Service Manager Unleashed
Amazon
System Center 2012 R2 Configuration Manager Unleashed: Supplement to System Center 2012 Configuration Manager
Amazon
System Center Configuration Manager Current Branch Unleashed
Amazon
Mastering Windows 7 Deployment
Amazon
System Center 2012 Configuration Manager (SCCM) Unleashed
Amazon

Archives

  • February 2023
  • December 2022
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • May 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • November 2019
  • October 2019
  • August 2019
  • July 2019
  • November 2016
  • November 2015
  • June 2015
  • May 2015
  • November 2014
  • July 2014
  • April 2014
  • March 2014
  • February 2014
  • January 2014
  • November 2013
  • August 2013
  • April 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • August 2012
  • July 2012
  • June 2012

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Categories

  • ABM (3)
  • Advanced Threat Protection (4)
  • Announcement (42)
  • Azure (3)
  • AzureAD (65)
  • Certification (2)
  • Cloud App Security (3)
  • Conditional Access (50)
  • Configuration Manager (24)
  • Events (11)
  • Exchange Online (7)
  • Identity Protection (3)
  • Intune (17)
  • Licensing (2)
  • Microsoft Endpoint Manager (35)
  • Mobile Application Management (1)
  • Modern Workplace (65)
  • Office 365 (10)
  • Overview (10)
  • Power Platform (1)
  • PowerShell (2)
  • Presentations (7)
  • Privileged Identity Management (5)
  • Role Based Access Control (2)
  • Security (50)
  • Service Manager (4)
  • Speaking (22)
  • Troubleshooting (4)
  • Uncategorized (11)
  • Windows 10 (14)
  • Windows 11 (4)
  • Windows Update for Business (3)
  • WMUG.nl (16)
  • WPNinjasNL (30)

Tags

#AzureAD #community #conditionalaccess #ConfigMgr #IAM #Intune #m365 #MEM #MEMCM #microsoft365 #modernworkplace #office365 #security #webinar #wmug_nl ATP AzureAD Branding Community Conditional Access ConfigMgr ConfigMgr 2012 Configuration Manager Email EXO Identity Intune Licensing M365 MCAS Modern Workplace Office 365 OSD PIM Policy Sets Presentation RBAC roles Security Service Manager SSP System Center troubleshooting webinar Windows 10

Recent Comments

  • Kenneth on December 2022 update of the conditional access demystified whitepaper and workflow cheat sheet.
  • John Barnes on December 2022 update of the conditional access demystified whitepaper and workflow cheat sheet.
  • Intune Newsletter - 24th February 2023 - Andrew Taylor on Azure AD Conditional Access authentication context now also available for Azure AD Privileged Identity Management
  • Azure AD Conditional Access authentication context now also available for Azure AD Privileged Identity Management | Modern Workplace Blog on A first look at Azure AD Conditional Access authentication context
  • Kenneth on Intune: Choosing whether to assign to User or Device Groups

This information is provided “AS IS” with no warranties, confers no rights and is not supported by the author.

Copyright © 2021 by Kenneth van Surksum. All rights reserved. No part of the information on this web site may be reproduced or posted in any form or by any means without the prior written permission of the publisher.

Shorthand: Don’t pass off my work as yours, it’s not nice.

©2023 Modern Workplace Blog | Powered by WordPress and Superb Themes!
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT