For Microsoft Hosted Messaging and Collaboration version 4.5, the Resource Manager database was updated. The SQL Maintenance Jobs included with this version of the solution make certain assumptions that could have adverse affects on the performance of your infrastructure. Hosters should understand exactly what these jobs do, and how to customize them (should it be necessary) prior to using them to a production environment.

The installer for the SQL maintenance jobs is a standard MSI, ResourceManagerJobs.msi, that is to be run separately by the hoster. You can find it at <solutionmedia>\Service Provisioning\MPS\ResourceManager. This should be run on a server that is running the MPS Client and be run by someone who has the proper permissions to create jobs on the server running Microsoft SQL Server 2005 that currently runs the Resource Manager databases.

The ResourceManager_DailyMaintenance job consists of a single step that is performed six days a week. The current schedule for this job is to run Monday through Saturday, beginning at 11 P.M.

The following list describes the process triggered by running the Daily Maintenance job:

1. Determine the default backup location for this instance of SQL Server.
   
2. Obtain the current date in yyyymmdd format.
   
3. Build the name of the backup by prepending "ResourceManager_backup_" to current date.
   
4. Validate whether or not the ResourceManager folder exists at the default backup location and create the full path to the backup file.
   
5. Run the backup using the differential switch and store the output to the location indicated by step 4.
   

In a typical deployment, the value for the default backup location will be similar to \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup. If you would prefer to write the Resource Manager daily database backups to another location by default, you must modify the script before you run it. You can make this change by locating the script ResourceManager_DailyMaintenance in Microsoft SQL Server Managment Studio (SSMS) under the Maintenance node. Then comment out the second line of the script.

Note:
Make sure the specified location is accessible to the account under which SQL Server is running. If it is not, you must configure the job to run under an account that has rights to the specified file store.

The ResourceManager_WeeklyMaintenance job consists of the following steps and is designed to help keep the database running smoothly. The current schedule for this job is to run Sunday evening beginning at 11 P.M.

The following list describes the process triggered by running the Weekly Maintenance job:

1. Determine the default backup location for this instance of SQL Server.
   
2. Obtain the current date in yyyymmdd format.
   
3. Build the name of the backup by prepending "ResourceManager_backup_" to current date.
   
4. Validate whether or not the ResourceManager folder exists at the default backup location and create the full path to the backup file.
   
5. Run the backup using the full switch and store the output to the location indicated by step 4.
   
6. Calculate the date 30 days ago, then delete any back up files that are older than the calculated date.
   

The script also checks and reorganizes the index, updates statistics, and checks the integrity of the database.

In a typical deployment, the value for the default backup location will be similar to \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup. If you would prefer to write the Resource Manager daily database backups to another location by default, you must modify the script before you run it. You can make this change by locating the script ResourceManager_WeeklyMaintenance in SSMS under the Maintenance node. Then comment out the second line of the script.

Note:
Make sure the specified location is accessible to the account under which SQL Server is running. If it is not, you must configure the job to run under an account that has rights to the specified file store.

One significant problem that exists when using Resource Manager in your service provisioning system is that the notifications table becomes filled with entries that are never read and never purged. This job aims to solve that problem by looping through all of the entries in the notifications table and for each entry, creating an event in the local application event log.

Once an entry has been posted to the event log, the entry is removed from the database table. This job allows the notifications table to remain at a reasonable size while also allowing monitoring applications such as Microsoft Operations Manager (MOM) to pick up the events from the event log and notify the appropriate people.

The ResourceManager_MonthlyMaintenance job consists of a single step that is performed on the last Sunday of the month. The current schedule for this job is to run Sunday evening at 11:40 P.M. to ensure that it will run after the weekly maintenance job. This script rebuilds the indexes of the Resource Management database.