ConfigMgr & SQL - some tips

ConfigMgr & SQL - some tips

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