System Center 2012 Configuration Manager SQL recommendations

 

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 Excel spreadsheet – remember to rename the file to .xls after downloading.


Comments (22):

  1. Karl Wirén says:

    Local SQL installation for sure, and the winning argument for me is ” database is not a regular database containing sensitive user information.”,

  2. Sam Erskine says:

    Hi Kent, the excel file does not download, just the resource files. Can you check the download please?

    • Sam Erskine says:

      Hi Kent,
      I was able to get the file. a little note for anybody with the same problem. windows downloads the file as a .zip. You need to rename the .zip to .xlsx

      cheers

  3. Bastian says:

    Hi Kent,

    a few questions:

    1. What do you mean with the number of Raid10 Volumes? Should i seperate the db on two Raid10? Does it mean i need 8 disks for that??
    2. In your book i understand you that the number of TempDB Logfiles should be the half number of Cores like the Database Files. Thats what our SQL Admin also said. Why do you make just 1 TempDB file? Or didn’t you mean the number of cores but rather the number of CPUs?

    Thanks a lot!

    Regards,
    Bastian

    • Robert says:

      2. I noticed this as well. I assumed it was just a typo and I changed the formula to be B6/2 rather than B5/2.

  4. SQL Mike says:

    Great info ! Ill deff keep this in mind next time working with the database!

  5. […] Tips from Kent Agurland Share this:TwitterFacebookLike this:Like Loading… […]

  6. Colin says:

    Excellent Info. Thanks!

  7. […] path F:DB and pre-created the Configuration Manager 2012 R2 database based on Kent Agerlund his Database sizing Excelsheet. During the setup you see the backslash at the end of the path like shown in the figure below, […]

  8. Al says:

    Does this solution apply to a CAS that has a SQL server separate from the actual CAS box or uses shared services model for SQL? Also in what scenario would having a SQL on the same box as the CAS not apply?

  9. kedington-community-association.org.uk

    Coretech Blog » Blog Archive » System Center 2012 Configuration Manager SQL recommendations

  10. Amit says:

    Are there any best practices for installing/configuring a SQL DB on a secondary site? or for the WSUS DB?

    Thanks

  11. […] My friend, Kent Agerlund, addresses this topic: System Center 2012 Configuration Manager SQL recommendations […]

  12. David Alvarez says:

    I’d like to point out the issue of security. In a multi-server configuration (SQL separate from the Site Server) the Site Server’s computer account MUST be in the local administrator’s group on the SQL server. This effectively causes the two computers to act as a single unit, security-wise (granted, it’s not the other way around: the SQL server’s computer account doesn’t need to be an administrator in the Site Server, but I could be wrong about this, could someone verify?) meaning if someone gained admin rights to the Site Server, they’d have full access to the SQL server anyway.

    That’s where I usually hear the argument for the separation of the two: “but if someone hacks the application they’ll get access to the data if SQL a is on-box!” In the case of SCCM, they’d have access to it on-box or off, rendering this argument irrelevant.

  13. Rob says:

    quick confirmation the number of cores field should equal the total amount of cores on the server and not per cpu?

    example: I have 2 dual core cpus assigned to the box.
    based on the above example if its total per server I would enter 2 in the processor box and 4 in the core box?

  14. […] the guide made by MVP, Kent Agerlund to estimate my DB sizing need. Visit his blog post and download the provided Excel file. Input your values in the blue cells and keep it for the next […]

  15. […] of the server build process.  If you’re not sure how to configure the baseline, refer to Kent Agerlund’s blog post about using a spreadsheet to estimate resource […]

  16. […] steps in correcting this; estimate the size of TempDB. There are some great calculators out there, Kent Agerlund did a nice job taking standard MSFT recommendations and converting it to an Excel “calculator”. […]

  17. […] years ago Kent A. and someone else put out a couple spreadsheets on database sizing and server sizing for Configmgr.  I used them off and on for a few years and […]

Leave a Reply