[Oct 18, 2022] Note: This information still applies to MEMCM, or whatever the on-premises version is called these days.
As a senior consultant, long time administrator of SMS/ConfigMgr and former SQL Server MVP, I have some strong opinions on this subject. These opinions are based on years of actually designing, implementing and using the product. And, observing when things go wrong.
I’ve read different guidelines for SQL Server installed locally vs. remote. One guideline is the number of clients that are supported in your site. However, with proper server planning, memory, processor and storage configuration, these “limitations” do not exist!
My friend, Kent Agerlund, addresses this topic: System Center 2012 Configuration Manager SQL recommendations
The major advantages/benefits of a local SQL Server install:
- Less complex
- Easier to implement
- Fewer Setup / Connection / Communication issues
- – Setup fails: example here and here
- – SQL SPN (Service Principle Names) missing or invalid: example here
- – Invalid or missing Firewall rules/ports
- Fewer Authentication issues
- Higher performance – network connection is not relevant for ConfigMgr 2012 communication with SQL Server.
- WSUS – if you are using WSUS for patch management, install WSUS using a local copy of SUSDB on the same SQL Server instance. Do not use the Windows Internal database for SUSDB.
- Reporting Services – unless you have an extremely large site, install Reporting Services on the local SQL Server as well.
Suggestion:
Become your OWN DBA and manage your own ConfigMgr database(s) – many DBA’s overly complicate the setup and security access to the database. I’ve read and heard many, many stories of DBA’s doing their work so effectively, ConfigMgr 2012 no longer works!!
Additionally, for ConfigMgr 2012, I do not recommend installing or configuring SQL Server on a clustered instance. The reasons for this recommendation compliment the ones above; in that remote SQL Server overly complicate an already complex product and High Availability is not really needed for ConfigMgr 2012. Updates are far more complex in this environment as well.
If you really need higher availability, consider using remote Management Points with Management Point SQL Server replicas!
One final note, when you install SQL Server locally for ConfigMgr 2012, be sure to properly configure the amount of memory SQL Server uses.
As always, comments and questions welcome!
Steve Thompson [MVP] 
Femi Akinsola
I wish they could listen and understand, read and understand but they won’t. It always become a political issue in most organisations especially when you have Architects without real world experience. They simply don’t understand there is something called exceptions.
Editor note: comment had been flagged as spam. Completely agree!
Pingback: Installing and configuring SQL Server for Configuration Manager | Steve Thompson [MVP]
Pingback: SQL Server 2016 shininess for ConfigMgr Current Branch – Deployment Research
Pingback: ConfigMgr Site Backup and Restore - MEMFTW!