SCCM - SQL Server Configuration

Requirements for SQL server configuration

The following are required by all installations of SQL Server you use for a site database, (including SQL Server Express). When Configuration Manager installs SQL Server Express as part of as secondary site install, these configurations are automatically made for you.
SQL Server architecture version:
Configuration Manager requires a 64-bit version of SQL Server to host the site database.

Database collation:
At each site, both the instance of SQL Server that is used for the site and the site database must use the following collation: SQL_Latin1_General_CP1_CI_AS.
Configuration Manager supports two exceptions to this collation to meet standards that are defined in GB18030 for use in China. For more information, see International support in System Center Configuration Manager.

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. However, this SQL Server configuration is required if you will use Database replicas for management points for System Center Configuration Manager.

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. This can be a named instance or the default instance.

SQL Server memory:
Reserve memory for SQL Server by using SQL Server Management Studio and setting the Minimum server memory setting under Server Memory Options. 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).
·         Database server that is installed on the same computer as the site server: - Limit the memory for SQL Server to 50 to 80 percent of the available addressable system memory.
·         Dedicated database server (remote from the site server): - Limit the memory for SQL Server to 80 to 90 percent of the available addressable system memory.
·         Memory reserve for the buffer pool of each SQL Server instance in use:
o    Central administration site: minimum of 8 gigabytes (GB)
o    Primary site: minimum of 8 gigabytes (GB)
o    Secondary site: minimum of 4 gigabytes (GB)

SQL nested triggers:
SQL nested triggers must be enabled.

SQL Server CLR integration
The site database requires SQL Server common language runtime (CLR) to be enabled. This is enabled automatically when Configuration Manager installs. For more information about CLR, see Introduction to SQL Server CLR Integration

The following configurations are optional for each database that uses a full SQL Server installation.
SQL Server service:
You can configure the SQL Server service to run using:
·         Domain local user account:
o    This is a best practice and might require you to manually register the Service Principle Name (SPN) for the account.
·         Local system account of the computer that runs SQL Server:
o    Use the local system account to simplify the configuration process.
o    When you use the local system account, Configuration Manager automatically registers the SPN for the SQL Server service.
o    Be aware that using the local system account for the SQL Server service is not a SQL Server best practice.
For information about SPN configurations for Configuration Manager, see Manage the SPN for the site database server. 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:
o    Management point
o    SMS Provider computer
o    Reporting Services point
o    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.

SQL Server on a Cluster

Configure the Site Database to Use a SQL Server Cluster

System Center 2012 Configuration Manager supports the use of a virtual Microsoft SQL Server cluster instance to host the Configuration Manager site database. For a list of supported SQL Server versions and supported configurations for the SQL Server cluster, see the Configurations for the SQL Server Site Database section in the Supported Configurations for Configuration Manager topic.
Configuration Manager Setup does not create or configure the SQL Server cluster. The clustered SQL Server environment must be configured before it can be used to host the site database. When you use a SQL Server cluster, Configuration Manager automatically checks each hour for changes to the SQL Server cluster node. Changes in the configuration of the SQL Server node that affect Configuration Manager component installation, such as a node failover or the introduction of a new node to the SQL Server cluster, are automatically managed by Configuration Manager.
When you use a clustered SQL Server instance to host the site database, the TCP/IP network communication protocol must be enabled for each SQL Server cluster node network connection. This is required to support Kerberos authentication. The named pipes communication protocol is not required, but can be used to troubleshoot Kerberos authentication issues. The network protocol settings are configured in SQL Server Configuration Manager under SQL Server Network Configuration.

Performance Considerations

Clustered SQL Server environments allow for failover support for the virtual SQL Server, and provide greater reliability for the site database. However, a site database on a clustered SQL Server configured for failover support does not provide additional processing or load balancing benefits and in fact, degradation in performance can occur. This is because the site server must find the active node of the SQL Server cluster before it connects to the site database.

SMS Provider Considerations

When you use a clustered SQL Server database to host the site database, install the SMS Provider on the site server or on a separate computer that does not host a SQL Server cluster node. It is not supported to install an instance of the SMS Provider on a SQL Server cluster or a computer that runs as a clustered SQL Server node.

How to Install Configuration Manager Using a Clustered SQL Server Instance

Use the following procedures to install the Configuration Manager site database for a central administration site or primary site, using a clustered virtual SQL Server instance during setup.
During Configuration Manager Setup, the Volume Shadow Copy Service (VSS) writer will install on each physical computer node of the Microsoft Windows Server cluster to support the Backup Site Server maintenance task.
To install Configuration Manager using a clustered SQL Server instance

  1. Create the virtual SQL Server cluster to host the site database on an existing Windows Server cluster environment. For specific steps to install and configure a SQL Server cluster, see the documentation specific to your version of SQL Server. For example, if you are using SQL Server 2008 R2, see Installing a SQL Server 2008 R2 Failover Cluster.
  2. On each computer in the SQL Server cluster you can place a file with the name NO_SMS_ON_DRIVE.SMS in the root folder of each drive where you do not want Configuration Manager to install site components. By default, Configuration Manager installs some components on each physical node to support operations such as backup.
  3. Add the computer account of the site server to the Local Administrators group of each Windows Server cluster node computer.
  4. In the virtual SQL Server instance, assign the sysadmin SQL Server role to the user account that runs Configuration Manager Setup.
  5. Use Configuration Manager Setup to install the site using one of the procedures from the topic Install Sites and Create a Hierarchy for Configuration Manager, with the following alteration:
    1. On the Database Information page, specify the name of the clustered virtual SQL Server instance that will host the site database, in place of the name of the computer that runs SQL Server.
During setup, you must enter the name of the virtual SQL Server cluster instance, and not the virtual Windows Server name created by the Windows Server cluster. Installing the site database using the Windows Server cluster virtual instance name will result in the site database being installed on the local hard drive of the active Windows Server cluster node, and it will prevent successful failover if that node fails.
  1. Complete the remainder of the Setup Wizard normally, to install Configuration Manager using a clustered SQL Server instance.

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.
    Blogger Comment


Post a Comment

Note: Only a member of this blog may post a comment.