Database Maintenance

[Previous] [Next]

Perhaps the most obvious way to keep your SMS site systems from experiencing failure is to keep them running in top form, much like you might develop an exercise and diet program for yourself or change the oil in your car regularly. In addition to developing a backup and restore strategy, there are several things you can do for your SMS servers on a regular basis both to keep them running well and to spot problems before they cause damage. For the most part, these maintenance tasks can be broken down into four groups: general, daily, weekly, and monthly maintenance tasks.

General Maintenance Tasks

Probably the most important general task you can perform for any Microsoft Windows NT system is to develop a backup plan for your servers. At a minimum, you want to develop a backup strategy for your SMS site server and SMS database server, as these are your key systems. Much has been written about backup strategies—full vs. differential, daily vs. weekly, and so on.

For example, one backup strategy might be that you perform a full, or complete, backup of your database once a week, say on Friday nights, while you perform a differential backup of the database Mondays through Thursdays. The differential backup only backs up data that had changed since the last full backup and will result in reduced backup time and less backup space used.

It all comes down to one ultimate question: How important is it that you recover your data, and how current must that data be? We'll look at the recommended procedure for backing up SMS in the section "Backup Process Flow" later in this chapter.

Other general maintenance tasks might more properly be called troubleshooting assistance tasks, such as configuring the Status Message Viewer, configuring the Performance Monitor and SQL Server alerts, performing a database and site backup, and monitoring the performance of the site systems. We've looked at some of these tasks in previous chapters, such as the following:

• Configure the Status Message Viewer to view status messages. Recall from Chapter 5 that the status message system is your first and often best source of information regarding the state of your SMS site systems. You can configure the display interval for status messages, set filters, have programs such as pager alerts executed based on message events, and so on. Take some time to determine how the Status Message Viewer might figure into your overall maintenance—and ultimately disaster recovery—strategy.
• Configure Performance Monitor alerts for key events. You can set up alerts for the events such as low disk space, overutilization of the processor and memory, excessive pagefile access, and so on. The HealthMon utility, described in Chapter 6, is designed to monitor and record these and other significant performance events.
• Configure SQL Server alerts. You can set up the alerts in the SQL Server Enterprise Manager to monitor database space usage, user locks, and connections. (For more information about setting up SQL Server alerts, refer to the SQL Server product documentation.)
• Configure Event to Trap Translator. If your network uses an SNMP management system, consider whether the Event to Trap Translator might be of benefit to you as a monitoring tool (see Chapter 6).
• Determine a fault tolerance strategy. If your server supports a fault tolerance method such as RAID 1 (disk mirroring) or RAID 5 (striping with parity) either through a hardware method or through Windows NT, consider configuring one of these fault tolerance methods. Maintaining data redundancy is a hallmark of disaster recovery.

You will undoubtedly think of many other troubleshooting assistance tasks to add to this list. Be as creative—and redundant—as you like. In the following sections we'll explore some specific daily, weekly, and monthly tasks you can perform as an SMS site administrator.

Daily Maintenance Tasks

As the SMS administrator, you decide when various maintenance tasks should be performed within your organization and with what frequency. No single blueprint will provide a perfect fit for every SMS site or site structure. Microsoft recommends that the following tasks be performed daily to protect your SMS servers. You can modify this list to suit your needs.

• Perform a site backup. This task ensures that you can recover to at least the previous day's state.
• Review status messages. Especially important if SMS generates a status message alert indicating a potential problem with a component. By default, the Status Message Viewer displays only messages generated since the previous midnight. If you skip a couple of days, you might miss significant status messages. Consider changing the display interval or setting up custom filters so that you will be alerted about serious events.
• Monitor the Windows NT Event logs and SQL Server Error logs. Check for errors or warnings that might be indicative of an impending failure of your SMS site. You can view the SQL Server Error logs in SQL Enterprise Manager.
• Monitor system health and performance through the Performance Monitor, HealthMon, and SMS Service Manager tools.
• Monitor network utilization using a network traffic analysis tool such as Network Monitor. This task is especially important if package distribution or intersite communication appears to be poor, to determine when and where traffic congestion is occurring.

Weekly Maintenance Tasks

Microsoft recommends that the following tasks be performed weekly. Again, you can modify this list to fit your needs.

• Monitor the size and percentage of database growth of the SMS site database, as well as the software metering database if you have implemented that feature. If you are using SQL Server 7.0, you're in luck. This version of SQL Server can be set to automatically grow to accommodate an increasing database. Of course, this does not absolve you from monitoring the database on a regular basis to determine how fast the database is growing and especially whether you might run out of disk space. However, you might not need to look at the database so frequently, and you can set a SQL Server alert to let you know when and how much the database grows.

If you are using SQL Server 6.5, a more frequent database size check will be imperative, especially with an active SMS site. In general, if the database or log device gets to be more than 80 percent full, consider expanding its size. Similarly, expand the tempdb device or log when it gets to be more than 60 percent full.

• Monitor the amount of free disk space on the SMS database server, the site server, and the site systems (CAPs, logon points, distribution points, and software metering servers). Remember, with very few exceptions, SMS components will just stop working if they run out of disk space.
• Purge data that is no longer needed or relevant. Remove bad Management Information Format (MIF) files, duplicate computer records, aged inventory records, and so on.
• Perform regular disk cleanup tasks. This cleanup would include your weekly full virus check or disk optimization routine, or monitoring for unused or old Temp files. Check the SMS directories as well for folders that have an unusually high number of files, such as a BadMIFs folder or an inbox with files that are not being processed, and cross-check these folders with status messages and logs for the specific components involved.

Monthly Maintenance Tasks

Here are some of the recommended maintenance tasks that might be performed on a monthly or an as-needed basis:

• Verify and test your ability to restore the database or the site server.
• Modify SMS accounts and passwords for those accounts you have control over. Refer to Chapter 16 for a discussion of SMS accounts.
• Review SMS object permissions.
• Review SMS site boundaries and component configuration.

You can protect your SMS site by scheduling and performing these maintenance tasks regularly. In the following section we'll look at how to schedule these tasks.