SCCM 2012 SP1 and SQL Server 4

Here is an overview of the SQL Server configurations and requirements for supported SQL Server installations for ConfigMgr 2012.

Source: http://technet.microsoft.com/en-us/library/gg682077.aspx

 

Configurations for the SQL Server Site Database

Each System Center 2012 Configuration Manager site database can be installed on either the default instance or a named instance of a SQL Server installation. The SQL Server instance can be co-located with the site system server, or on a remote computer.

When you use a remote SQL Server, the instance of SQL Server used to host the site database can also be configured as a SQL Server failover cluster in a single instance cluster, or a multiple instance configuration. The site database site system role is the only System Center 2012 Configuration Manager site system role supported on an instance of a Windows Server cluster. If you use a SQL Server cluster for the site database, you must add the computer account of the site server to the Local Administrators group of each Windows Server cluster node computer.

Note
SQL Server database mirroring is not supported for the Configuration Manager site database.

 

When you install a secondary site, you can use an existing instance of SQL Server or allow Setup to install and use an instance of SQL Server Express. Whichever option that you choose, SQL Server must be located on the secondary site server. The version of SQL Server Express that Setup installs depends on the version of Configuration Manager that you use:

  • Configuration Manager without a service pack: SQL Server 2008 Express
  • Configuration Manager with SP1: SQL Server 2012 Express

The following table lists the SQL Server versions that are supported by System Center 2012 Configuration Manager.

SQL Server version SQL SP SQL CU ConfigMgr version ConfigMgr Site type
SQL Server 2008

  • Standard (1)
  • Enterprise
  • Datacenter
SP2 Min CU9
  • ConfigMgr NO SP
  • ConfigMgr SP1
  • CAS
  • Primary
  • Secondary
SP3 Min CU4
  • ConfigMgr NO SP
  • ConfigMgr SP1
  • CAS
  • Primary
  • Secondary
SQL Server 2008 R2

  • Standard (1)
  • Enterprise
  • Datacenter
SP1 Min CU6
  • ConfigMgr NO SP (2)
  • ConfigMgr SP1
  • CAS
  • Primary
  • Secondary
SP2 NO CU
  • ConfigMgr NO SP (2)
  • ConfigMgr SP1
  • CAS
  • Primary
  • Secondary
SQL Server 2012

  • Standard (1)
  • Enterprise
NO SP Min CU2
  • ConfigMgr SP1
  • CAS
  • Primary
  • Secondary
SQL Server 2008 R2 Express SP1 Min CU6
  • ConfigMgr NO SP
  • ConfigMgr SP1
  • Secondary
SP2 NO CU
  • ConfigMgr NO SP
  • ConfigMgr SP1
  • Secondary
SQL Server 2012 Express NO SP Min CU2
  • ConfigMgr SP1
  • Secondary

 

  1. When you use SQL Server Standard for the database at the central administration site, the hierarchy can only support up to 50,000 clients. For more information, see Site and Site System Role Scalability.
  2. Configuration Manager with no service pack does not support the site database on any version of a SQL Server 2008 R2 cluster. This includes any service pack version or cumulative update version of SQL Server 2008 R2. With Configuration Manager SP1, the site database is supported on a SQL Server 2008 R2 cluster.

 

SQL Server Requirements

The following are required configurations for each database server with a full SQL Server installation, and on each SQL Server Express installation that you manually configure for secondary sites. You do not have to configure SQL Server Express for a secondary site if SQL Server Express is installed by Configuration Manager.

Configuration More Information
Database collation At each site, both the instance of SQL Server that is used for the site database and the site database must use the following collation: SQL_Latin1_General_CP1_CI_AS.
SQL Server features Only the Database Engine Services feature is required for each site server.

(Configuration Manager database replication does not require the SQL Server replication feature.)

Windows Authentication Configuration Manager requires Windows authentication to validate connections to the database.
SQL Server instance You must use a dedicated instance of SQL Server for each site.
SQL Server memory When you use a database server that is co-located with the site server, limit the memory for SQL Server to 50 to 80 percent of the available addressable system memory.

When you use a dedicated SQL Server, limit the memory for SQL Server to 80 to 90 percent of the available addressable system memory.

Configuration Manager requires SQL Server to reserve a minimum of 8 gigabytes (GB) of memory in the buffer pool used by an instance of SQL Server for the central administration site and primary site and a minimum of 4 gigabytes (GB) for the secondary site. This memory is reserved by using the Minimum server memory setting under Server Memory Options and is configured by using SQL Server Management Studio. For more information about how to set a fixed amount of memory, see How to: Set a Fixed Amount of Memory (SQL Server Management Studio).

 

SQL Server Optional Configuration

The following configurations either support multiple choices or are optional on each database server with a full SQL Server installation.

Configuration More Information
SQL Server service On each database server, you can configure the SQL Server service to run by using a domain local account or the local system account of the computer that is running SQL Server.

  • Use a domain user account as a SQL Server best practice. This kind of account can be more secure than the local system account but might require you to manually register the Service Principle Name (SPN) for the account.
  • Use the local system account of the computer that is running SQL Server to simplify the configuration process. When you use the local system account, Configuration Manager automatically registers the SPN for the SQL Server service. Be aware that using the local system account for the SQL Server service is not a SQL Server best practice.

For information about SQL Server best practices, see the product documentation for the version of Microsoft SQL Server that you are using. For information about SPN configurations for Configuration Manager, see How to Manage the SPN for SQL Server Site Database Servers. For information about how to change the account that is used by the SQL Service, see How to: Change the Service Startup Account for SQL Server (SQL Server Configuration Manager).

SQL Server Reporting Services Required to install a reporting services point that lets you run reports.
SQL Server ports For communication to the SQL Server database engine, and for intersite replication, you can use the default SQL Server port configurations or specify custom ports:

  • Intersite communications use the SQL Server Service Broker, which by default uses port TCP 4022.
  • Intrasite communication between the SQL Server database engine and various Configuration Manager site system roles by default use port TCP 1433. The following site system roles communicate directly with the SQL Server database:
    • Management point
    • SMS Provider computer
    • Reporting Services point
    • Site server

When a SQL Server hosts a database from more than one site, each database must use a separate instance of SQL Server, and each instance must be configured to use a unique set of ports.

 

Configuration Manager does not support dynamic ports. Because SQL Server named instances by default use dynamic ports for connections to the database engine, when you use a named instance, you must manually configure the static port that you want to use for intrasite communication.

 

If you have a firewall enabled on the computer that is running SQL Server, make sure that it is configured to allow the ports that are being used by your deployment and at any locations on the network between computers that communicate with the SQL Server.

For an example of how to configure SQL Server to use a specific port, see How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) in the SQL Server TechNet library.

4 thoughts on “SCCM 2012 SP1 and SQL Server

  1. Pingback: SCCM 2012 SP1 and SQL Server « MS Tech BLOG

  2. Pingback: SCCM Installation procedure and prerequisite | My Blog

  3. Reply Umesh Jul 30,2014 10:24 pm

    Hi,

    Thanks for this nice article. Can you please suggest whts the max latency is supported, by Microsoft , between site server and site database server.

    Thanks
    Umesh

Leave a Reply

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