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.



Initial size


# Clients


DB size pr. client


# processors


# cores




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 log






Number of files





Size pr. file










# RAID 10 volumes







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


Max memory


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.


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 “Estimate SQL database” ConfigMgr-SQL-recommendations.xlsx – Downloaded 13733 times – 15 KB

By | 2017-12-18T16:11:50+00:00 August 18th, 2012|Configuration Manager (SCCM), General info|28 Comments

About the Author:

Kent Agerlund
Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.


  1. Karl Wirén August 18, 2012 at 16:19 - Reply

    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 August 28, 2012 at 20:29 - Reply

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

    • Sam Erskine August 30, 2012 at 9:41 - Reply

      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


  3. Bastian October 5, 2012 at 11:07 - Reply

    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!


    • Robert October 17, 2012 at 12:25 - Reply

      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 October 18, 2012 at 10:28 - Reply

    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 December 5, 2013 at 14:49 - Reply

    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 April 23, 2014 at 18:28 - Reply

    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?


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

  10. Amit November 24, 2014 at 20:56 - Reply

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


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

  12. David Alvarez May 29, 2015 at 23:54 - Reply

    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 December 30, 2015 at 23:27 - Reply

    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 […]

  18. Stefan Somogyi August 4, 2016 at 8:43 - Reply

    We just have setup a new SCCM CB 1602 environment with this Guide for SQL. It works as it should. Many thanks for it. Now we made a Restore Test and this fails because of a known bug during recover. Details see here:

    I’ll need to open a case bei MS to get a solution for this. If I get a solution, I’ll post it here.

  19. Stefan Somogyi August 19, 2016 at 12:19 - Reply

    I got Feedback from Premier Support. In SCCM 2012 and Current Branch is it not supported to use more the one USERDAT file. It’s not possible to restore a SCCM Backup anymore. A change request is all ready placed at the SCCM product Team and they try to change the limitation with one of the next CB releases. What do you say to this limitation, Kent?

  20. Instagram Vienna December 9, 2016 at 4:33 - Reply

    Useful information. Lucky me I found your website accidentally, and I’m
    surprised why this twist of fate didn’t took place in advance!
    I bookmarked it.

  21. Michiel August 10, 2017 at 17:59 - Reply

    As a System Center consultant I’ve seen implementation with both local and remote SQL server instances (even in SQL Availability Groups). The best option is what suits the customer (booooh…that’s lame).

    You could use these generic ‘beneficial’ arguments for all applications which require a database.

    And all could apply, on which eventually you could want all applications to have their own local SQL instance, because of
    – less complexity
    – less hassle with security
    – less network communication so better performance
    – no sensitive user information so local database

    Usually databases are consolidated on bigger SQL implementation to cut costs and have the DBA-ers take care of the database and have benefit of the high-availability and high performance data disks of a central SQL environment.

    What are going to do, if you apply these arguments to Operations Manager, Orchestrator and Service Manager? Give them each a local SQL instance?

    You’re not wrong. Just saying this sounds a bit like, for us as the person who implements SCCM, this is the easier thus better method.

    For other readers: If you think you chose wrong, you can always migrate your database to a remote or local instance. But try to aim for the best option right away.

  22. Jason September 18, 2017 at 16:33 - Reply

    The file is no longer available for download, ConfigMgr-SQL-recommendations.xlsx.

  23. Aaryan December 21, 2017 at 7:48 - Reply


    SCCM 2012 Infra for approximately 4000 clients for more that 300+ sites/Location offices connected with mostly internet and few branches are MPLS & VPN.
    Prior requirement is only for patch management and reporting.

    Major Site is India – 50 site contain 1400 clietns.
    UK- 25 Sites contains 500 clients.
    UAE – 90 sites contains 400 clients.
    China – 20 sites contain 100 clients.

    There are more than 200 Geo Site/Location where only 2-10 clients.

    Looking for your suggestion to design and implement sccm servers.

    Keeping in mind to reduce no. of servers..I am planning to place Primary in India and DP’s where more than 50+ clients with MPLS/VPN connectivity.
    For large clients connected over internet IBCM.

    Could you please suggest is this planning is good or do you think any modification ?

Leave A Comment