Site servers share with a single SQL server or Cluster

[Kevin Dai]

Each System Center 2012 Configuration Manager site database can be installed on either the default instance or a named instance of a SQL Serverinstallation. The SQL Server instance can be co-located with the site system server, or on a remote computer. In a hierarchy with multiple sites, each site can use a different version of SQL Server to host the site database so long as that version of SQL Server is supported by the version of Configuration Manager that you use. For example, if your hierarchy runs Configuration Manager SP1, it is supported to use SQL Server 2008 R2 with SP1 and cumulative update 6 at the central administration site, and to use SQL Server 2012 with no service pack and cumulative update 2 at a child primary site, or vice versa.

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. SQL Server cluster configurations that have multiple active nodes are supported for hosting the site database. 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

A SQL Server cluster in a Network Load Balancing (NLB) cluster configuration is not supported. Additionally, SQL Server database mirroring technology and peer-to-peer replication are not supported.

 

 

 

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.

Warning

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

 

 

 

 

 

With the introduction of the Central Administration site (CAS) option, customers will likely implement the CAS and first local Primary site in the same datacenter. The typical thought is the CAS will be used for central administration, while the Primary site will service clients at each location. Since the CAS and local primary site likely are in the same data center, it might additionally be a desire to combine both site server databases on the same SQL server.

This may yield undesirable performance depending on many factors; but is a supported configuration. Below are some pointers to assist you through setting up this model:

o Make sure you’re using a supported version of SQL server.

o Use a domain account to run as the SQL service.

o Ensure the SQL service account has been configured with the appropriate SPN(s).

o While installing each of the two instances, ensure they use completely different database directory locations. (this is key because of the MS_AgentSigningCertificate file)

o Ensure the “SQL Server Browser” service is installed, set to “Automatic” startup and is running. This will be a requirement for the site servers to find their respective SQL instance.

o After each instance is installed, disable dynamic ports and define a specific port for each instance using the SQL Server Configuration Manager.

1. Expand “SQL Server Network Configuration”

2. Select the “Protocols for x” (x=instance name)

3. Double-click “TCP/IP”

4. Select the “IP Addresses” tab

5. For every “TCP Dynamic Ports” field, clear all data (field should be completely empty)

6. For every “TCP Port” field, enter “5033”

7. Repeat steps 1-6 for your second SQL instance, this time using port 5034

You should now be ready to install your CAS and first primary ConfigMgr 2012 sites using their respective and dedicated SQL instances on the same SQL server/cluster.

[rasheedah]

Thank you for this is there an official MS document in regards to SCCM Hierarchy and sql configurations? So there will be one CAS/3 Primary Sites. It looks like Multiple Instance configuration is the way to go on a SQL Cluster.

They will also be in one Datacenter FYI

[rasheedah]

I found my answer thanks!

[GarthMJ]

19 hours ago, rasheedah said:

Thank you for this is there an official MS document in regards to SCCM Hierarchy and sql configurations? So there will be one CAS/3 Primary Sites. It looks like Multiple Instance configuration is the way to go on a SQL Cluster.

They will also be in one Datacenter FYI

I hate to say it but have one SQL server for you cas and three primary site doesn't make any sense.the whole point of a cas and primary is for work load. if you are combining them into one SQL server them you are suggesting that you don't have a lot of load.

[rasheedah]

Hi Garth,

I was looking at a best practice document by MS from what i saw the CAS has a local sql and the Americas are using remote sql.. my now thinking is to go this route with primaries on the cluster using mulitple instance ...i really have not seen anything via technet other than MI's are supported ....btw client is on sccm 2012 r2 160k the focus right now is U.S.A. then address global sites later... correction there is one CAS/2 primaries 44k in one site and 32 in the other.

I also saw active/active cluster not sure if this will work for a Hierarchy. I wished they kept local sql on all primaries believe me the discussion in regards to the advantage of this was beaten but the org wants HA of the site database...

 

 

[GarthMJ]

Having all the cas and primary sites all using the same SQL servers is clearly not best practice. why splits the primaries with 44k and 30k?

[rasheedah]

I have a ticket open with MS thanks

[Kevin Dai]

13 hours ago, rasheedah said:

Hi Garth,

I was looking at a best practice document by MS from what i saw the CAS has a local sql and the Americas are using remote sql.. my now thinking is to go this route with primaries on the cluster using mulitple instance ...i really have not seen anything via technet other than MI's are supported ....btw client is on sccm 2012 r2 160k the focus right now is U.S.A. then address global sites later... correction there is one CAS/2 primaries 44k in one site and 32 in the other.

I also saw active/active cluster not sure if this will work for a Hierarchy. I wished they kept local sql on all primaries believe me the discussion in regards to the advantage of this was beaten but the org wants HA of the site database...

 

 

Hi Rasheedah,

 

I wrote this article refer to some others engineers' when do some testing just like you want to install CAS and Primary sites on SQL cluster.

I remembered that I had completed the testing following this article.

Just one thing, the reporting services point configuration had not completed. Maybe there are some problem I have not found.

Expect you could succeed.

The web site from microsoft  FYI.

https://docs.microsoft.com/en-us/sccm/core/servers/deploy/configure/use-a-sql-server-cluster-for-the-site-database

[rasheedah]

Thanks Kevin looks like your article is aligned to what a few SCCM MVPs said in an old forum discussion 

 https://social.technet.microsoft.com/Forums/en-US/6bb8b527-b607-4c00-9ed0-69a94d7e6f59/sccm-cas-primary-site-server-same-sql-database-and-reporting-instance?forum=configmanagergeneral

Just a matter of making sure SQL failover cluster server is optimized where there is a best practice documents

http://www.hasmug.com/wp-content/uploads/2012/10/07-201210-Oct-SQL-Server-Optimization-and-Best-Practices-for-System-Center-Administrators-Kevin-Holman.pdf

 

[GarthMJ]

From a performance stand point it makes no sense to share a SQL cluster with a CAS and primary site(s).