Preparing SQL Server for SCCM
I get a lot of questions on customer sites regarding how we prepare SQL server for the installation. This usually comes from concerned DBAs around the level of access required. Let’s take a look at the setup guidelines.
First of all, let’s make an assumption that we are deploying a single Primary Site infrastructure which will support 2,000 clients. I hope this post expels some myths around the setup of SQL server to support a SCCM installation.
The only component required to host SCCM is the SQL Server Database Services. For a working instance without a Reporting Services Point you only need the database engine. In addition it doesn’t matter if the instance is default or named. The SCCM setup allows you to specify.
If you configure a domain account to run the SQL service then a SPN must be configured for the domain account in ADDS. SQL Server Windows Authentication Mode (default) because Configuration Manager site systems require Windows authentication to SQL Server hosting the site database. Because both authentication methods that can be selected when installing SQL Server support Windows authentication, either can be used.
Dictionary order, case-insensitive, for use with 1252 Character Set (default) should be selected as the SQL Server collation if possible. While using a case-sensitive SQL Server collation is supported, Microsoft recommend using a case-insensitive collation to avoid any possible future case sensitivity issues during site database operations.
Remote SQL Servers
If you are hosting SCCM on a remote SQL server rather than installing SQL locally on the SCCM server then the logged on user and the site server machine account require administrative rights to the SQL server.
Typically, performance is better if the SCCM site server and the site database are installed on the same server. However, if the intervening network connection is a high-availability, high-bandwidth network connection you might be able to install SQL Server on a remote server from the SCCM site server. You might also consider installing a second network adapter in both the site server and the computer running SQL Server and then dedicating this second card for communications between the site server and the computer running SQL Server.
When using a remote SQL Server to host the site database, the site database server must be located in the same domain as the site server and SMS Provider computer.
Cluster SQL Environment
If you plan to run the SCCM database in a clustered environment then the logged on user and the site server computer account require administrative rights on all cluster nodes.
SCCM will not create or configure the SQL cluster. The clustered SQL environment must already be configured, and functional, before it can be used to host the site database. SCCM will automatically check once an hour for SQL cluster node configuration changes. Changes in the SQL node configuration affecting installation, such as a node failover or the introduction of a new node to the SQL cluster, will be managed by SCCM with no action by the administrator necessary.
I hope this helps with your decision, I also get asked a lot of questions on backups for both the SQL server and the SCCM instance. I will post an article on this soon.