Initial SQL Configuration For Small Site?

I'm setting up a new SCCM site from scratch.  It will be 2012 R2 SP1 CU1 with SQL Server 2014 SP1.

I followed online documentation on how to install it and I successfully installed it in a lab, however, I need to know what to change from defaults in the SQL setup for production.  The server will be Server 2012 R2 running SQL and ConfigMan 2012 R2 SP1 CUI on a single box with 32GB RAM and SSD drives. 

The site will have less than 500 clients.

I plan to set the minimum SQL memory at 8GB and maximum at 28GB.  That setting is the only required setting for SCCM to successfully install, but what about other settings for optimization?

What settings should be set to optimize SQL performance manage the database and log size to prevent the drive from filling to capacity? 

What are SQL configuration recommendations for a 500 client site and around 500GB disk space?

There will not be 500 clients starting out.  I will likely start with less than 200 clients and grow to up to 500 over several months.

This is what is set by default in the lab install.  Is this all that needs to changed or are there other settings?

September 13th, 2015 9:49pm

8GB is the minimum required for the SQL memory as documented by Microsoft, however I would suggest that 28GB is way to much for the maximum, SQL will more than likely use it, but that's just due to the functionality of SQL.

16GB should be sufficient for a maximum for the client size you're referring to.

With regards to the SQL file configuration, the following table offers a good guide:

*Initial Size and Maximum Size refer to the percentage of total free disk space

Data File

Initial Size *

Autogrowth

Max Size *

Site Database .mdf File

50%

1%

90%

Site Database .ldf File

50%

2%

90%


Temp Database Files (.mdf and .ldf)

Default

Default

Default

This table is taken from the following sizing guide:

http://blogs.msdn.com/b/scstr/archive/2012/05/31/configuration_2d00_manager_2d00_2012_2d00_sizing_2d00_considerations.as

September 13th, 2015 11:10pm

I don't understand what you posted.  It doesn't match what I see.

I don't know what physical sizes to select.  My screenshot shows maximum size as in MB, not percentage.  I don't know what max size 90% means.

Should I change the initial size from the 5120MB automatically created?

September 14th, 2015 12:44am

More information can be found in the article I linked.

However, the table was based on creating the database before installing ConfigMgr.

As you have already installed ConfigMgr, you should NOT alter the initial sizes.

The only value you need to focus on the from the table is the Autogrowth setting.

Note: The percentages in the other columns are of the total available disk

Free Windows Admin Tool Kit Click here and download it now
September 14th, 2015 12:56am

The screenshot is from the lab test.  I could do it differently when I deploy the the production system.

I still don't see how to configure SQL to control all those separate files.  My screenshot in the first post only shows 2 files, but the example in your link explains setting different settings for a bunch of separate mdf and ldf files and lists Site Database .mdf File twice. 

Where is that set?

September 14th, 2015 1:02am

I've updated the table for clarification and added note regarding the percentages.

For installing in production, I'd recommend taking the following approach:

  • Set minimum SQL memory to 8GB
  • Set maximum SQL memory to 16GB
  • Install ConfigMgr and let the ConfigMgr installation create the databases
  • Configure the autogrowth to the values outlined in the table
    1% for the mdf and 2% for the ldf
  • Set the maximum file sizes to 90% of the total available disk space

You should also use best practice for configuring the disk subsystem on your SQL server, using dedicated volumes for your database files and logfiles.

Another Note: Microsoft recommendation for ConfigMgr 2012 is to co-locate the SQL server on the same server as the primary site server.

Free Windows Admin Tool Kit Click here and download it now
September 14th, 2015 1:16am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics