SMS Database Maintenance and Disaster Recovery

SMS Database Maintenance Tasks

The main SMS database maintenance task that you need to perform is to implement a backup strategy for the SMS site server and SMS database server. A backup is the process of archiving data and system files on a computer to a different location on a hard disk, or other media type. A backup plan or strategy should detail the data that has to be backed up, the manner in which the data should be backed up, the frequency at which the backups should occur, and the manner in which data restorations should occur. The backup should be implemented to restore system files and other valuable data.

There are different ways in which you can backup files or data. The different backup types are summarized below:

Normal backups: Here, each file that has been specified to be backed up is backed up. When compared to the other types of backups, normal backups need the most storage capacity.
Copy backups: Copy backups come in handy when you need to move data between systems. It can also be used to create an archive copy of data.
Daily backups: The modification date on the actual file determines whether a file has to be backed up.
Differential backups: Files that have been modified from the time when the last backup occurred have backup copies made. A differential backup needs more tape than that used for incremental backups. Differential backups are time consuming.
Incremental backups: This type of backup uses the minimum amount of tape, and saves time because it does not copy all the files that have not changed during each job.

The SMS maintenance tasks that you need to perform are listed here:

Define and Implement a backup strategy for the SMS site server and SMS database server (discussed above).
Configure a fault tolerant system: A fault tolerant system is a system that is set up in such a manner that it can continue to operate when certain system components experience failures. Fault tolerance pertains to the use of hardware and software to prevent data loss in the occurrence of a failure such as a system or hardware failure. Setting up a fault tolerant system becomes important when you have servers running mission critical applications. Redundant components, paths and services can be included in the network topology to steer clear of single points of failure, and to ensure a highly available topology.

A few strategies which you can employ to ensure fault tolerance in your system are listed here:
- To enable your servers to shut down properly when a power failure occurs, use an uninterruptible power supply (UPS).
- To ensure that no data is lost when a hard disk failure occurs, deploy one or multiple RAID arrays for both system and data storage. This ensures that only the failed disk needs to be replaced when a disk failure occurs. RAID essentially adds fault tolerance to file systems, and increase data integrity and availability because it creates redundant copies of your data. RAID can be used to improve disk performance as well.
- To cater for server failures where a server holds mission critical data, or runs mission critical applications, utilize clusters to provide redundancy and failover.
Define and configure Performance Monitor alerts for specific events, such as processor and memory over-utilization, and low disk space. HealthMon is another optional component in SMS that can be utilized to obtain a view of the status a Windows based computer. The HealthMon utility utilizes the objects and counters of Performance Monitor to determine status threshold levels and to collect data. With HealthMon, the threshold levels are called monitoring policies.
Configure the Status Message Viewer so that you can determine the state of the SMS site systems. Status messages show the movement of processing activity of the SMS sitesystems and clients. The status message reporting system of SMS can be used to monitor the status of the SMS site system, SMS components, and SMS packages and advertisements. The status message system of SMS can be accessed through the SMS Administrator console, by navigating to the System Status node.

A status message can have one of the following severity levels:
- Informational status messages merely inform on events such as a component starting, and a task completing.
- Warning status messages point out potential problems that can occur. This includes reporting on issues such as a file being corrupted, or a component failing to start.
- Error status messages report on issues that must be dealt with because they can result in problems with your SMS site. The issues reported through error messages include issues such as authentication failures, and the failure of a component completing a function, and so forth
The Status Message Viewer can be configured for the following:
- Configure the display interval for status messages.
- Configure programs to send alerts based on certain events.
- Define filters.
Define and configure SQL Server alerts: SQL Server Enterprise Manager can be used to perform the following tasks:
- Monitor database space usage
- Monitor connections
- Monitor user locks
If you are currently using a SNMP management system, then you can configure the Event to Trap Translator to translate selected Windows Server events into SNMP traps. These SNMP traps are then sent to the Network Management Stations (NMSs). The events which are passed to the Event to Trap Translator are transmitted as strings of data.

A few daily maintenance tasks that you should perform to secure your SMS servers are listed here:

Schedule and perform a daily site backup. In the event of a failure, you would be able to recover the state of the previous day.
Check network traffic patterns through the Network Monitor tool. With Network Monitor, you can capture frames directly from the network which you are monitoring, configure capture filters to specify the type of information which should be captured by Network Monitor, determine which user and protocol is consuming the most bandwidth, filter captured frames by creating display filters, and create triggers if you want certain actions performed when the content of a packet(s) match a predefined condition.
Check system performance through the Performance Monitor, HealthMon utility, and SMS Service Manager tools.
Check the generated status messages for warning and error messages and review the content of these messages.
Check the Windows Event logs and SQL Server Error logs for error and warning items.

A few weekly maintenance tasks that you should perform to secure your SMS servers are listed here:

Check the size and growth rate of the SMS site database.
Check the size and growth rate of the SMS software metering database.
Check how much free disk space exists on the following SMS components:
- SMS site server
- SMS database server
- SMS software metering servers
- Client access points
- Logon points
- Distribution points
Perform disk cleanup functions:
- Full virus check
- Check for old Temp files
Remove any data which is no longer valid:
- Duplicate computer records
- Bad Management Information Format (MIF) files
- Stale software inventory items.

A few monthly maintenance tasks that you should perform are listed here:

Verify that the SMS site server can be restored.
Verify that the SMS database server can be restored.
Verify that the SMS object permissions are valid and correct.
Verify the SMS site boundary configuration.
Verify your SMS component configuration.
Change SMS accounts and passwords.

Scheduling SMS Maintenance Tasks

In the Tasks node of the SMS Administrator console, there are a number of predefined database tasks that you can schedule.

These predefined database tasks are listed here:

Backup SMS Site Server; performs a full backup of the SMS Site Server, and includes the following items:
- Site database.
- Software metering database
- SMS and NAL registry keys
- SMS directory
Delete Aged Collected Files; deletes collected files that were not updated in a predefined period of time.
Delete Aged Discovery Data; deletes discovery data records that were not updated in a predefined period of time.
Delete Aged Inventory History; deletes hardware inventory that were not updated in a predefined period of time.
Delete Aged Status Messages; deletes status messages that are older than the predefined period of time.
Export Site Database; exports the site database to a specified SQL backup device.
Export Site Database Transaction Log; exports the Site Transaction log to a specified SQL backup device.
Export Software Metering Database; exports the software metering database to a specified SQL backup device.
Export Software Metering Transaction Log; exports the Software Metering Transaction log to a specified SQL backup device.
Monitor Keys And Recreate Views; monitors the integrity of primary keys utilized to identify SMS database tables.
Rebuild Indexes; rebuilds those indexes created on your database tables.
Update Statistics; updates statistics created by SQL Server on index data.

The following predefined database tasks are enabled by default:

Update Statistics
Rebuild Indexes
Monitor Keys And Recreate Views
Delete Aged Inventory History
Delete Aged Status Messages
Delete Aged Discovery Data
Delete Aged Collected Files

You can also configure and schedule SQL commands to run and create reports:

Open the SMS Administrator console.
Expand the Site Settings node.
Expand the Database Maintenance node.
The SMS console tree displays the SQL Commands node and the Tasks node.
Select the SQL Commands node.
Right-click the SQL Commands folder, and select New and then SQL Command from the shortcut menu.
The SQL Command Properties dialog box opens.
Ensure that the Enable SQL command checkbox is selected.
In the Name textbox, provide a name for the SQL command.
In the SQL Command box, provide the command name. To ensure that the command does not fail, make sure that the correct syntax is utilized.
In the Log Status To textbox, provide the path and filename of the text file where the command results should be recorded.
In the Schedule area of the SQL Command Properties dialog box, specify when the SQL command should run.
Click OK.
The new SQL command is added to the SQL Commands folder.

Overview of Backing up the SMS Site Server

To perform a full back up of the SMS site server, you have to back up the following:

Site database data
Software metering database data
SMS and NAL registry keys
SMS directory data.
Master site control file

There are a number of elements which you need to provide or modify before you can perform a back up of the site server:

The device/file to use for the backup.
The database which you want to backup.
Determine whether the following should be backed up:
- Entire database together with the transaction log.
- The transaction log
- A user table.
Determine whether the backup should be performed immediately or whether it should be scheduled for a later date.
Determine whether an expiration date should be defined for the backup.
Determine whether existing data should be overwritten.
All SMS components and services have to be stopped before the back up is performed.

How to stop a SMS component or service

Open the SMS Administrator console.
Expand the Site Settings node.
Expand the Database Maintenance node.
The SMS console tree displays the SQL Commands node and the Tasks node.
Select the Tasks node.
Right-click the Tasks node and select All Tasks and then Start SMS Service Manager from the shortcut menu.
Expand the site server.
Select the Components folder.
All currently available components are displayed.
A component is disabled by selecting the specific component and then selecting the Stop button.
To verify that the component has been disabled, select the component and then click the Query Component button.
You can start a component that has been disabled, by selecting the component, and then clicking the Start button.

How to verify that all SMS components and services have been stopped

Open Control Panel
Navigate to the Services applet.
Verify that the following SMS components and services have been stopped:
- Info Sentinel service
- Info Agent service
- Info APS service
- SMS Executive
- SMS License Server Manager
- SMS Logon Discovery Agent
- SMS Site Component Manager
- SMS SQL Monitor
- SQL Executive
- SQL Server Agent
- Windows Management

How to create a backup device in SQL Server Enterprise Manager

Open SQL Server Enterprise Manager.
Expand the server folder.
Expand the Management folder.
Select Backup.
Right-click Backup and select New Backup Device from the shortcut menu.
The Backup Device Properties dialog box opens.
In the Name textbox, provide a name for the backup device.
Specify if the backup device you are creating resides on tape or if it resides as a file.
Click OK.
The new backup device will be displayed under the Backup folder in the SQL Server Enterprise Manager.

How to configure the Export Site Database task in the SMS Administrator console

Open the SMS Administrator console.
Expand the Site Settings node.
Expand the Database Maintenance node.
Select the Tasks node.
Right-click Export Site Database and select Properties from the shortcut menu.
The Export Site Database Task Properties dialog box opens.
Select the Enable This Task checkbox.
In the Export To textbox, provide the name of the backup device. All paths should be specified as well.
In the Schedule area of the Export Site Database Task Properties dialog box, define the schedule for the backup.
Click OK.

How to configure the Backup SMS Site Server task in the SMS Administrator console

Open the SMS Administrator console.
Expand the Site Settings node.
Expand the Database Maintenance node.
Select the Tasks node.
Right-click Backup SMS Site Server and select Properties from the shortcut menu.
The Backup SMS Site Server Task Properties dialog box opens.
Check the Enable This Task checkbox.
In the Export To textbox, provide the name of the drive and directory for the backup folder.
In the Schedule area of the Backup SMS Site Server Task Properties dialog box, define the schedule for the backup.
Click OK.

Overview on Recovering an SMS site

Recovering a SMS site can be separated into the following tasks:

Recovering the site database
Recovering the site server.

There may be instances where you need to move the SMS site database and software metering database to a different SQL Server computer.

The methods which you can use to move the SMS site and software metering database databases are listed here:

Restore a backup of the SMS site and software metering databases to another SQL Server computer that is configured the same as the previous SQL Server computer. The following configurations have to be the same:
- Computer name
- Database name
- Registry settings
- SMS files
Restore a backup of the SMS site and software metering databases to a SQL Server computer that is configured differently to the previous SQL Server computer. Here, you have to run SMS Setup to configure the installation to utilize the new server.

Before you actually move the site and software metering database databases to a different SQL Server computer, there are a number of tasks that have to be performed:

Stop SMS services on the site server.
Stop SMS services on the SQL Server computer
Close SMS applications and tools.
Back up the site database from SQL Server.
If you are not using an existing SQL Server computer, then you have to install SQL Server on the new computer.
Apply all necessary operating system service packs and SQL Server service packs on the new computer.

You can use the following process as a guideline for moving the SMS database to another SQL Server computer:

Stop SMS services on the site server and on the SQL Server computer.
Close all SMS applications and tools.
Back up the SMS database in SQL Server. You can also schedule a SMS database backup through the SMS Administrator console.
Install the other SQL Server computer. Ensure that the following configuration is the same:
- Computer name
- Database name
- Registry settings
- SMS files
Create the database and log devices or files.
Move the backed up SMS database to the new SQL Server computer.
Ensure that the SMS site server accesses the correct location of the new SQL server and SMS database.

You can use SMS Setup to reset SMS to use the new database. You need to run the Modify or reset the current installation option of SMS Setup to perform this task. When you reset SMS to use the new database, the following occurs:

SMS services on the site server are stopped.
Two site configuration files are written:
- A configuration file from the current site properties.
- A configuration file from the proposed site properties.
All new configurations are checked to the previous site configuration file, and the best configuration is chosen. The site is then updated.
SMS services on the site server are restarted.