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.
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 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.
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
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.