We typically focus our CRM attention to the CRM application. But underlying the application is the SQL databases that house our CRM data. The two main databases are <organization_Name>_MSCRM and <organization_Name>_METABASE. And of course there's the ReportServer database that SQL Reporting Services uses. These databases deserve a little of our Tender Loving Care as well.
One of the first things I do when I set up any SQL database that has data I might be fond of is to set up a Database Maintenance Plan. This is really simple to do. Open up SQL Enterprise Manager, drill down into the SQL server that hosts your CRM databases, open the Management folder in the Navigation Pane and you will see a selection for Database Maintenance Plans. Clicking on this will show you a list of existing Database Maintenance Plans. If you see some, then you likely already know how to do this. If not, right-click on Database Maintenance Plans in the Navigation Pane and select New Maintenance Plan. This starts a wizard that will walk you through the process so I won't do so here. What you get is a job that automatically runs at the intervals you specify that will check over your database, remove unused space, reindex it and create a backup of your data.
To me, the backups are very important. I know, you backup up your server every night. But I've found the SQL data backups to be very handy. I generally set it to maintain the last 7 days of backups. That gives me a way to go back, and with relatively little effort, restore my data back in time. I've had to do this over the years more than once when a database such as my old GoldMine data, would get messed up because I did something stupid. Now YOU would never do anything stupid with your data, but what about your users? In the days of using tape backup, restoring from an on-line SQL backup was a heck of a lot easier too. Of course all those SQL backups get backed to your system backup on an daily basis as well which gives a little more depth to your backup protection.
Once you've set up your Database Maintenance Plan, be sure to check on it every once in a while to be sure it's working properly. This article was prompted by my discovering my Database Maintenance Plan needed a little TLC. You can check on the status of your Database Maintenance Plan in a number of ways. First, you can configure the plan with notifications that will send you an e-mail if there is a problem. You can also browse your Event Viewer logs periodically. Or you can be a bit more proactive and look at the status in Enterprise Manager. To do so, go back to your Management folder on your CRM SQL server and open SQL Server Agent. Here you will see 3 additional items. Jobs is the one you're interested in. Opening this you will see the various SQL jobs that the Database Maintenance Plan created for you. You will also see a few that CRM creates. We can discuss those a bit later. What you want to see if a bunch of small square icons with blue circles in them indicating all is well. What you don't want to see is a bunch of red circles with Xs in them indicating you have a problem. If you'll excuse me now, I see a couple of new red circles in mine that need a little more TLC.