Properly configure SQL Server ConfigMgr database

Ideally, this will take place before you install ConfigMgr!

First, estimate size(s) of the databases:

How? Use the  SCCM DB Sizing estimator!

Shown is an example of the Database sizing worksheet, based on estimated client counts:

image

Anthony has documented this approach as follows:

http://configmgr.com/configmgr-sql-server-sizing-and-optimization/

Get the sizing estimator here.

Using the estimator to calculate the number and size of the data files, pre-create the SQL Server database for ConfigMgr. Key take away, will be the total estimate sizes.

Multiple data files may be appropriate for a couple of reasons. SQL Server is multi-threaded, which means you may get performance gains by having multiple data files. Further, these data files could be located on different logical drives to further distribute load, and increase IOPS. on As a general rule of thumb, use the following as a guideline:

Total Estimate Client Count

Number of CM data files

< 10K

1

10K to 25K

2

>25K to 50K

4

>50K

8

Other factors to consider:

  • Drives dedicated for SQL data and logs should be formatted with NTFS in 64K block size.
  • Always place TempDB data and logs on a dedicated logical drive
  • DB files should be equal size (hint: divide total estimated size / # data files)
  • Create data files to be an even GB
  • Always pre-create the db files prior to installing CM – auto-growth is bad.
  • If you enable auto-growth:
    • Do not use percent (%) growth
    • Set auto-growth to be evenly divisible into a whole GB; (i.e. 512 or 1024MB)
  • Monitor available free space
  • Always use a single LOG file
  • If possible, place LOG file on separate logical drive

It is possible to migrate a single data file to multiple data files. Use this reference to get you started. It is an older article, the screen shots are missing, however the technique is valid.

Note: Be cautious if you attempt to implement in a multi-site hierarchy! Table partitioning may present an issue.

Final thoughts: As always, there is never a right or wrong way to do this, this is intended be used a guideline only. Test in a lab environment first. No cute furry animals were harmed with this process and never stick your fingers inside the cage.

Advertisements
This entry was posted in ConfigMgr, SQL, SQL Server. Bookmark the permalink.

6 Responses to Properly configure SQL Server ConfigMgr database

  1. This is the first time I’ve ever seen a recommendation for data files based on client count. All previous references I’ve seen are based on SQL Server CPU count. What changed?

    • ConfigMgrMVP says:

      I’ve seen various subjective references to NUMA settings and affect on IO. You did see my Final thoughts section “As always, there is never a right or wrong way to do this, this is intended be used a guideline only” comment? If you have references that show the proper number of data files for CM, based on CPU – please post them?

  2. John Nelson Number2 says:

    Correction: I think there IS a wrong way to do it…one DB file on a 3-disk raid 5 volume 🙂

  3. John says:

    Thank you for a good post.
    Quick question: should the TempDB drive also be formated in 64K?

    • ConfigMgrMVP says:

      Yes – see first point “Drives dedicated for SQL data and logs should be formatted with NTFS in 64K block size.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s