Tuesday 29 October 2013

ConfigMgr & SQL - some tips

Back to main menu

This blog shares some tips on how to implement SQL for ConfigMgr 2012. There are some important decisions to be made regarding SQL and they will provide the foundation for a successful ConfigMgr deployment which can easily be managed.


Local V Remote

I've seen this question asked in the Config Mgr Technet Forums many times - should I install local or remote SQL server?

Microsoft don't care. They will support both. Most ConfigMgr Admins will choose local. As long as you just use the database for Config Mgr, the license is free, so there is no additional cost.


Database Management & Security
If the ConfigMgr server is suitably resourced there can be a performance gain using a local SQL installation. However this is not the main reason to choose local SQL. The main reason is control over the database. In a larger organisation dedicated Database Administrators (DBAs) manage the SQL environment. They can have very rigid rules, in particular when considering database security.

When we are installing Config Mgr 2012 with a remote SQL server it is a required that the installation account and the Config Mgr computer account are both Local Administrator and SQL Sysadmin on the remote server. If you are deploying to a remote SQL Cluster this needs to be done on both nodes. These permissions need to be maintained after the installation. ConfigMgr needs to be able to manage and manipulate the database for successful operation. DBAs often have a problem with this.

Performance
There are, of course, some obvious performance benefits with local installations

  • not sharing with resource intensive applications
  • no network latency

Collation
Config Mgr 2012 requires a specific database collation - SQL_Latin1_General_CP1_CI_AS. ConfigMgr Setup will not continue if the collation is different, which can sometimes be the case for existing SQL installations.


Random SQL tips

  • The only SQL features needed for ConfigMgr are the Database Engine and SSRS (if server is to be your reporting server also)
  • As mentioned above database collation must be SQL_Latin1_General_CP1_CI_AS
  • High Availability - ConfigMgr does not support SQL 2012 AlwaysOn
  • It is not supported to manually edit the SCCM db
  • Don't forget to configure SQL Maintenance tasks - backups and re-indexing
  • Manually configure SQL memory usage - minimum 8GB 
  • ConfigMgr 2012 shares information between sites by SQL Replication
  • A secondary site must use a locally installed SQL instance on the Secondary Site server. The default is to install SQL express on the OS partition. Just like the other site hierarchy servers, the Secondary Site server will replicate to its assigned Primary Site server, by default, on port 4022.
  • WSUS requires its own DB. The DB can be co-located on an instance that hosts the CAS or PS server.
  • The MDT DB is a small DB with light requirements. It can be co-located with other ConfigMgr DB’s like the PS and the WSUS.
  • ConfigMgr 2012 does not use a dedicated Data Warehouse. It runs its reports from the standard ConfigMgr DB.


Disk Design

Best practice implementations of SQL follow this model for presentation of disks:

Operating System, Program Files
Database
Logs 
TempDB
SQL Backup


Sizing Considerations (Disk Space, RAM etc)

There is an excellent guide here


Improved Performance Tips

Use RAID 10 configuration for high speed performance of Database and Logs drives

Consider running reports via browser rather than ConfigMgr console

  • less resources consumed on ConfigMgr server
  • reports can be run quicker
  • don't need to give unneccessary access to the ConfigMgr console

11 comments:

  1. Actually, Microsoft Recommends a Local DB for Performance, so your statement is incorrect. They will not however document how much performance degradation there is. (Ex-Microsoftee)

    ReplyDelete
    Replies
    1. Which statement do you feel is incorrect? Can you post any link to official MS documentation with this recommendation? I know for a fact the docs used to say remote was recommended but none of us ever agreed with that. :-)

      Delete
  2. hi Gerry, I must say this sit is so helpful!

    with regards to sql for sccm should I run sql as basic or full?

    ReplyDelete
    Replies
    1. I'm not sure what you mean by basic - are you talking about SQL Express? If so this is not supported. You must use a full version of SQL (Standard is the most commonly used).

      Delete
    2. sorry I meant the recovery model is it set to simple or full

      Delete
  3. How can you support Paul Keely's SQL recommendations for SC and at the same time say that the SQL DB should be hosted on the same server as SCCM?

    Paul Keely's document clearly states:

    "No matter what you decide on, make one thing clear, don’t place your System Center application on
    the same server as your SQL instance, just don’t. [...] Don’t go deploying an enterprise product or products sharing the same server with your DB instance and start moaning later that the console performance sucks, it doesn’t, your design does."

    Besides, once you get bigger designs and start taking advantage of SQL clustering for example, how are you going to do that if you have a local SQL instance? It's not impossible ofc, but all the "advantages" of keeping it local are gone and all of a sudden you have a load of disadvantages.

    ReplyDelete
    Replies
    1. I'm sorry. I don't agree. In that case Paul was specifically referring to SCOM. I have referred to Paul's document as a general SQL reference. ConfigMgr is a completely different situation. In my opinion it should stay local.

      Delete
  4. Gerry, are there any new tips for this post with the newer versions of SCCM...SCCM 2012R2 SP1 CU2 or SCCM 1511? or do all still apply?

    You mention:

    "As long as you just use the database for Config Mgr, the license is free, so there is no additional cost."

    I'm very curious to know more about the free lol...our database is external fighting resources with other dbs and If it is in fact free Id be curious to move it local and check performance. This post seems to back your comments:
    http://tinyurl.com/hh6gzkn by kurtsh

    Great Blog.

    ReplyDelete
  5. All the SQL advice still applies. You can also get a free SQL license as long as you only use it for Configuration Manager related products.

    ReplyDelete
  6. hi Gerry, super useful blog btw

    Gerry you say"Config Mgr computer account are both Local Administrator and SQL Sysadmin on the remote server. If you are deploying to a remote SQL Cluster this needs to be done on both nodes" does it need sysadmin? or is this a mistake

    https://docs.microsoft.com/en-us/sccm/core/servers/deploy/configure/use-a-sql-server-cluster-for-the-site-database

    doesnt say sysadmin

    ReplyDelete
  7. Hi, I do think this is a great web site. I stumbledupon it ;) I will revisit yet again since I saved as a favorite it.
    Money and freedom is the greatest way to change, may you be rich and continue to help others.

    ReplyDelete