I have two SQL related question that comes up in all of my ConfigMgr 2012 projects. Those are:

 

· Where are we going to install the SQL server?

· How are we going to configure SQL?

Where are we going to install the SQL server

Answering the first question often ends in a political discussion between the ConfigMgr. administrators on one side and the SQL DBA’s on the other side. Most of the ConfigMgr. administrators that I know, strongly believe in having a local SQL installation. I’m also a true believer of that for several reasons.

  • Remote SQL installations often do not perform much better.
  • Security often becomes an issue and time factor in the project. We need SQL permissions to:
    Perform the installation
    When installing Cumulative Updates and Service Packs
    Finally – we just need access to the database for all sorts of purposes.
  • The ConfigMgr. database is not a regular database containing sensitive user information.
  • With a remote database you are introducing an extra server and adding extra complexity into the project.

How are we going to configure SQL

Configuring SQL involves disk subsystems, database files, log files and memory. The configuration you choose is often based on the Economy in the project and performance SLA’s. I created an Excel spreadsheet that I use to calculate the estimated size of the database, the size of each the database files and the number files that I recommend.

I start by entering the facts, like number of clients, server specs (# of CPU’s, Cores and Memory). The initial size of the database and the DB size pr. Client is based on my assumptions and experiences from previous projects.

 

Value

Initial size

5120

# Clients

15000

DB size pr. client

5

# processors

2

# cores

16

Memory

24576

Once I have the facts I use the numbers to calculate the estimated sizes and number of files. The estimates are based on my previous experiences, recommendations from the ConfigMgr. team along with general SQL recommendations.

 

ConfigMgr DB

ConfigMgr Log

TempDB DB

TempDB log

Size

80.120

26.440

24.036

12.018

Number of files

16

1

1

1

Size pr. file

5.008

26.440

24.036

12.018

Autogrowth

1.652

512

512

512

# RAID 10 volumes

2

1

1

1

 

image

I recommend creating the database files using the estimated size from the beginning. This prevents database from expanding too frequently, which can affect performance. I do not recommend creating more than 8 database files even if the official documents are saying one file per core. If the total number of cores exceeds 8, please modify the number off files manually in the spreadsheet.

 

For my memory configuration I follow this simple calculation 2 GB reserved for the Operating System + 2 GB reserved for ConfigMgr. and the rest for SQL.

Min memory

8192

Max memory

20384

Cap log files

Another important step is to cap/limit the maximum size of the log files.

Other databases

You most likely have other databases on the site server like the Reporting database, WSUS and MDT. for the Reporting database configure the recovery mode to Simple.

Conclusion

Above numbers are just recommendations and sometimes I’m not getting away with those due to political reasons and budget constraints. It is important to emphasize that ConfigMgr. will run with a remote SQL, a SQL server with less memory and another disk layout. Performance might just be degraded and the question is; can you live with that?

[download id=”11052″]