ConfigMgr for DBAs

First of all, sorry for the massive delay in posting again. I have been really busy again recently and have not had the time to post recently. In this post I will be looking at detailing some important information you can point out to your DBAs when preparing the database server for Configuration Manager.

In many large organisations teams are separated out so that each has their own responsibilities for different systems such as networks, Windows, web and DBAs.

In this post I will detail out some of the requirements for setting up and managing the SQL database in Configuration Manager 2012. Most of these rules to apply for Configuration Manager 2007 but for specific details you should get the latest information from TechNet.

Windows Authentication

While the SQL server doesn’t need to be in pure Windows authentication mode, you can get away with putting the server in mixed authentication mode however this will generate a warning in the prerequisite checker on the site installation. This isn’t a big problem as warnings are only for informational purposes. The main point here is security and hardening, we are using a domain account to authenticate to the database so why bother opening an additional authentication method.

You should according to best practice not leaving another method of attack open means a more secure system, especially as we are using a method of authentication which supports Kerberos.

Dynamic Ports

Dynamic ports are not supported in Configuration Manager. When configuring the SQL instance used by Configuration Manager you should disable dynamic ports for the instance. For further hardening (most DBAs do this already) changing the static port the server runs on is also a good idea and is fully supported on a custom port.

SQL Memory

You should configure the SQL server memory limiting to use a minimum of 8 GB of RAM for the Central Administration Site (CAS) or the primary site, or 4 GB of RAM for the secondary site. The maximum should be set to between 50% and 70% of the total RAM in the server.

This configuration if not configured again generates a warning in the setup prerequisite checker, not something that will cause prevent the installation but is recommended from a performance perspective.


On the SQL server, the computer account of the CAS or primary site should be added to the SQL server. You should also make sure the account running the installation has the sysadmin role at the server level.

These two are requirements, the first will cause a failure of the installation and the second will cause an error on the prerequisite checker.

Service Principal Name

When you run the SQL service on the server with a domain account then you should configure SPNs or Service Principal Names. The setup is pretty simple and can be done with a simple command line.

setspn.exe –A MSSQLSvc/<SQL Server computer name>:1433 <Domain\Account>

For example, if our server is called SQL01 and our service account is SQL01_Service on the CONTOSO domain, running on port 3182 then this command would look like as follows.

setspn.exe –A MSSQLSvc/SQL01:3182 CONTOSO\SQL01_Service

To verify that the SPN has been created we can issue the following command:

setspn.exe –L <domain\SQL Service Account>

Again using our example, you would issue the following command:

setspn.exe –L CONTOSO\SQL01_Service

Recovery Mode

The final one is the recovery mode of the database. It should be set to simple, yep simple you read this right. In a normal world any DBA will always leave the recovery mode to the default full. To most SQL Server administrators, no-one in their right mind would use the simple recovery model. For them, this is certainly understandable.

This is because SQL server backups cannot be used to recover a site. An Configuration Manager generated backup snapshot is required to recover a site because SQL Server backups do not back up everything required to restore a failed site.

Using the simple recovery model improves performance and saves your server hard drive space from a useless (from an SMS recovery point of view) and possibly large, transaction log file. In most cases, DBAs are responsible for having each transaction that occurs available for recovery in case of a database failure. For example, when those transactions represent revenue for a business using a SQL Server based Web application, losing a day’s worth of transactions could result in a lot of lost revenue. In our case though, there is absolutely no option other than using a Configuration Manager site backup snapshot to recover a site and the additional overhead of the full recovery model is unnecessary.


Tags: , , , , , , , ,

About Martyn

Martyn is one of the Senior Cloud Architects and DevOps Team Leader at one of the worlds leading Cloud Transformation Specialists Inframon. Martyn is responsible for the architecture of some of the largest Azure deployments in EMEA and is a advisor to a many businesses on their strategies. Martyn is a regular speaker at Microsoft events and community events on Azure and DevOps, giving his insight to a growing number of audiences.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: