The Realm of the Verbal Processor

Shared SQL with Configuration Manager?

Over time I have talked with numerous people about where the SQL database should be for the Configuration Manager database. Where this conversation typically comes up is when a company has a DBA team that is demanding that all SQL databases be hosted on dedicated (and super powerful) database servers. These servers predominantly will host numerous SQL databases for a variety of applications. The reasoning typically falls into the following arguments:

1. Licensing – We don’t want to have to pay for another SQL license, so all DBs will be on our dedicated SQL servers.
2. Performance – Our crazy powerful DB servers will give better performance than what you would install locally.
3. Security – We need to maintain control over the content of the DB, and the DB integrity in general. Having them on a dedicated SQL server allows us to do that in the best way.

Sounds like some good arguments right? Well…not so much. Let’s take a look at each of the three.

1. Licensing – Not an issue at all. Configuration Manager 2012 licensing includes the ability to install SQL Standard…at no additional charge.
2. Performance – There have been arguments for years about whether Configuration Manager performed better with remote or on-box SQL. I’ve seen people give great arguments both ways…but haven’t really seen anything definitive either direction. With Configuration Manager 2012, the recommendation from Microsoft is that SQL be local unless you hit certain size limitations. Unless you are over 50,000 clients, then on-box SQL Standard will work just fine for you. If more than 50,000 clients, then a remote SQL Standard will take you to 100,000 clients. SQL Enterprise is only necessary on a Central Administration Site supporting more than 50,000 clients. (For more info.)
3. Security – THIS IS THE BIG ONE! It generally takes about a three minute conversation with a DBA before they run away from this argument. Consider the following facts and implications in a remote SQL scenario:
   1. The Configuration Manager site server must be a member of the local administrators group on the remote SQL server. (See the Configuration Manager documentation.)
   2. Several people who are not SQL admins will be administrators on the Configuration Manager site server.
   3. It is trivial for an admin on the Configuration manager site server to run any application (such as a CMD prompt or SQL Server Management Studio) as Local System. (See this post.)
   4. Since the Configuration Manager server (Local System) has admin rights on the remote SQL server…the non SQL Admin can VERY easily obtain admin rights on the SQL server.
   5. The DBA has now started sweating, twitching and begging you to keep your weird database away from his/her server. :-)

So, really the only reason to consider doing remote SQL at all is a performance issue…but you have to be a pretty big organization for that one to come into play. And even if you do need to do remote SQL…it should be a SQL server that is dedicated to Configuration Manager.

Note (12/4/2012): I was talking with a friend late in the day yesterday about this blog post. He reminded me that I had already posted about this issue last April. Thanks Phil…I’m a little scatterbrained sometimes! I’m leaving this post up anyway because it is better than the original in my opinion.

December 3, 2012 - Posted by Jarvis | ConfigMgr, ConfigMgr 2012, Security, SQL