So, what’s in your wallet msdb?
If you’ve been working with Microsoft SQL Server anytime at all you can name the system databases fairly quickly – master, model, msdb, and tempdb. Almost rolls off your tongue as smoothly as Tenaha, Timpson, Bobo, and Blair (if you don’t know that reference, click here for some interesting Texas folklore). I’ve been working with SQL Server since 4.21 and the system db names have not changed from release to release. But, what did change drastically over all those releases are the contents – especially for msdb. Have you taken a look lately? I did and here is what I found.
Lots of new tables, lots of new views, and lots of new roles – well, just lots of new objects period! I always investigate what’s changed in master with every release, but it seems I’ve been delinquent in thoroughly examining msdb changes. It appears every new feature being added to the SQL Server database engine is being “managed” from the msdb database.
The objects supporting SQLAgent have always been in msdb – jobs, operators, alerts; along with the objects supporting database backup and recovery sets. Then DTS came along which later morphed into SSIS – guess where those objects are stored? Maintenance plans, log shipping, and database mirroring also join the party in msdb. And, oh yeah – SQLMail which became DatabaseMail…
Now with SQL 2008 and R2, you can add to the list – CMS (Central Management Server), Data Collector, PBM (Policy Based Management), DAC (Data Tier Applications), UCP (Utility Control Point)… but “Wait!” you say. “Don’t the Data Collector and UCP have their own databases?” Well, yes, they do – typically MDW and UMDW respectively. But, those databases only hold the collection results – the management and configuration of those features is all contained in msdb.
Here’s what the SQL Server 2008 R2 BOL says about msdb:
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
After seeing this, I had to laugh, I totally missed Service Broker in my list, but then I’ve never implemented it. On the other hand, look at all the features using msdb that BOL just lumped into “other features”! Bottom line – the BOL description really sells msdb short on its importance to so many features. AND, the default recovery mode for msdb is Simple. Before SQL 2008 I would have said that a daily full backup was typically sufficient. You didn’t usually make too many changes to jobs such that you couldn’t easily recreate a day’s work if you lost it restoring to the prior day. And, best practice said to take a backup after making modifications which impacted it anyway… but now, with so many features tied to msdb – do you really know when you are making changes to msdb? Considering all that is now dependent upon msdb – is your backup and restore strategy for msdb adequate?
However, before I go examine my msdb recovery strategy, I’m thinking of opening a Connect suggestion – rename msdb to KitchenSinkDB. Do you think I could get some votes for that? 😉