The ConfigMgr Accidental DBA

Or, Be Your Own DBA!

An Administrator for Microsoft System Center Configuration Manager (ConfigMgr) either becomes the lead administrator for ConfigMgr because it was planned, or unplanned.

Once they assume control of ConfigMgr, in most cases, they are now the DBA (Database Administrators) as well.


If the ConfigMgr administrator role was planned, then fantastic! The assumption is made that with this planning the admin understands what is required to properly administer the SQL Server component. If the SQL Server component was not really considered, (I’ve seen this a few times) then this article could still help.

After all, ConfigMgr is an application (granted a complex application) that uses SQL Server as the backend to store information on clients, inventory, settings, packages, applications, etc.

If the ConfigMgr administrator role was unplanned – as in; “Your manager reports that Sam the lead ConfigMgr admin left the company to pursue making a fortune as a consultant, and you (Jane) from the help desk have been promoted to lead ConfigMgr Admin (sorry Jane ;)”. Although it could be a great opportunity for Jane. Not only is the newly minted ConfigMgr admin responsible for the CM product, they are now responsible for SQL Server.

The Accidental DBA, or Involuntary DBA, now has to administer SQL Server.

There is another consideration in writing this article, in some companies, the DBA’s administer ALL SQL Server instances. Sometimes the existing DBA’s do a great job at this, other times the DBA will start locking things down to the point where ConfigMgr no longer works, and CM admins can no longer do their jobs!

Another case reported in the community; the DBA team would take longer than 1 week to restore a CM database for Disaster Recovery. In a hierarchy, the database MUST be restored within 3-4 days. A restore greater than 1 week would be completely unacceptable (to me).

A subtitle for “The ConfigMgr Accidental DBA” could be “Be your own DBA!”. Take control of the ConfigMgr SQL Server instance and manage it yourself. If you are new to SQL Server, this article will help you.

IMO, this would be a great use case to start managing your own CM SQL infrastructure.

Now what?

The DBA (Database Administrator) role does require some unique skills. The remainder of this article is providing resources and links (which will likely be updated over time) to assist.

Some immediate suggestions:

Define very clear RTO and RPO guidelines, these will greatly help your ability to recover.

  • RTO – Recovery Time Objectives (how long will it take)
  • RPO – Recovery Point Objectives (how much data can I afford to loose)

Create a backup plan. Immediately!

Where to start?

Community articles

SQLSkils has a fantastic set of guides written for SQL Server. Writing by leading industry experts, these guides are designed to be consumed 1 day at a time for 30 days. If you are just starting out, you NEED to read and implement the SQL Server Backup section first.

Troubleshooting SQL Server: A Guide for Accidental DBAs – a free ebook is available from Redgate (some awesome tools for SQL BTW), while dated, it still contains information relevant to supporting SQL Server today.

Configuration Manager SQL Server Backup guidelines – make sure you have solid backups in place. Be sure to test restores on a regular basis.

Optimizing SQL Server for ConfigMgr – hey, it’s my blog. You’ll want to get very familiar with Ola Hallengrens’ solution. Once you have a handle on backup, you want it to run fast, right?

Microsoft SQL Server Versions List – Monitor for, and apply updates for SQL Server.

DBATools – PowerShell module. Automate SQL Server tasks…

Community support

Twitter – post your SQL related question with a #sqlhelp hashtag and you should get some assistance.

Member Note: You’re in the right place to get answers from a pretty huge community of SQL Server DBAs – that is invaluable.

Microsoft SQL Server support forums

Community suggestions

In preparing this article, I polled the twitter alias asking what the Accidental DBA needs to know. Some of these points I’ve touched on. For the thread, click here.

Notes from the Twitter alias and SQLSkills link:

You can’t do much better than starting here … -I would add that you should choose the order that suits you best (imho backups should be first!) #sqlskills #sqlhelp [Mark Broadbent @retracement]

Make sure you set your backup schedule so that you can meet your RTO and RPO. And test your restores. Your backups aren’t any use if they won’t restore. [Frank Gill @skreebydba]

For testing restores, sp_DatabaseRestore is handy, as you can do the restore, run CHECKDB, then drop the restored DB after in a few lines of tSQL. That reduces the space requirements if you perform for each DB serially. Working on a blog post to share an example. [Allen McGuire @SQLTrooper ] + next as well.

If you do it in an Agent job, you can have output files for each DB tested on top of the steps being logged to the CommandLog – part of the free Ola backup/maintenance solution (which sp_DatabaseRestore relies on anyway).

This entry was posted in DBA, SCCM, SQL Server. Bookmark the permalink.

4 Responses to The ConfigMgr Accidental DBA

  1. tplas says:

    Article has great advice for accidental DBA’s. One other very strong recommendation for tools/resources: DbaTools. Can streamline a LOT of DBA admin tasks.

  2. Hey Steve, great article for helping people to get started. Heads-up that you’ve got a T instead of a P in “Define very clear RTO and RTO guidelines” under “Now what”.

    I’d second the recommendation of Ola Hallengren, I’ve come across home-made solutions a few times now that aim to do something similar but none are as thoroughly tested and feature rich as Ola. Why reinvent the wheel when Ola is free?
    I also second the recommendation of DBATools with the proviso that with great power comes great responsibility. The documentation for DBA tools is excellent, so be sure to read fully what a command does before you run it!

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.