Managing SQL Server Agent Jobs in FIM 2010

Updated: July 12, 2010

Applies To: Forefront Identity Manager 2010

Forefront Identity Manager 2010 includes SQL Server agent jobs that you can use to automate key maintenance tasks. These tasks are designed to improve performance of the FIM database or ensure consistency of sets and groups. Structured Query Language (SQL) agent jobs execute scheduled administrative tasks, which contain one or more job steps. SQL Server can run an agent job on schedule, in response to a specific event, or on demand.

In FIM, you use agent jobs for two primary purposes:

  • Deleting expired system objects. (Request, WorkflowInstance, Approval, ApprovalResponse). All requests that create or modify data are captured in system objects and stored in the FIM database. They can accumulate quickly, especially in large environments. You can use the agent job to customize how frequently system objects are deleted.

  • Evaluating temporal policies. Many actions in FIM run in response to an event in time, such as expiration of a group. The agent job updates all FIM dateTime functions to the current time. It then re-evaluates all set memberships that have a filter definition containing any of these dateTime functions (temporal sets). Requests to change the set memberships are created and processed if any changes are needed. Secondary job steps also validate the current membership of all sets and groups against their filter definitions and correct any membership errors.

This document contains procedures for configuring SQL agent jobs and related processes, explains how to remove system objects from a large deployment, and addresses ways to export request data for auditing purposes.

SQL Server agent jobs

To locate agent jobs:

  1. Open SQL Server Management Studio, and then connect to your database.

  2. Click SQL Server Agent, and then click Jobs.

SQL Server includes four agent jobs in support of FIM:

  1. FIM_DeleteExpiredSystemObjectsJob. This job deletes expired system objects. It runs daily by default. It contains the following steps:

    • FIM_TruncateInstanceDataStep

    • FIM_DeleteExpiredSystemsObjectStep

  2. FIM_TemporalEventsJob.This job evaluates temporal sets and policies, validates set and group membership, and runs daily by default. It contains the following steps:

    • FIM_TriggerTemporalEventsStep. Starts the job.

    • FIM_MaintainSetsStep. Evaluates sets for correct membership.

    • FIM_MaintainGroupsStep. Evaluates groups for correct membership.

    FIM_MaintainTemporalEventsOnInstallationStep is in the list of steps but does not run. If you receive an error message in reference to this step, click Yes to ignore it.

  3. FIM_Maintain_GroupsJob. This job maintains group memberships. By default, a daily run of this job is included as a substep of the temporal events job, and it runs as part of FIM_TemporalEventsJob. This job is provided in cases when you want to add more frequent runs of the group’s maintenance task to the default daily schedule that is offered as part of the temporal job configuration. By default, it is disabled as an individual job.

  4. FIM_Maintain_SetsJob. This job maintains set membership. By default, a daily run of this job is included as a substep of the temporal events job, and it runs as part of FIM_TemporalEventsJob. This job is provided in cases when you want to add more frequent runs of the set’s maintenance task to the default daily schedule that is offered as part of the temporal job configuration. By default, it is disabled as an individual job.

Determining the number of expired requests in the system

You can find out the number of expired requests by performing a Search query in the FIM Portal for requests that are older than the system object retention period, which is 30 days by default and configurable from 1 through 365 days.

To determine the number of expired requests in the system

  1. Under Requests and Approvals, click Search Requests.

  2. Click Advanced Search.

  3. Click Add Statement, and then click Click to Select Attribute.

  4. Select Expiration Time in the drop-down list, and then select After.

  5. Enter a value for the current date/time.

  6. Click Search.

Deleting expired system objects

Among the critical areas of maintenance is the need to prevent old request objects from slowing down the FIM database. If you're operating FIM in a large environment, expired system objects will accumulate quickly and should be removed on a regular basis. FIM retains expired objects for 30 days before they are deleted by the agent job, FIM_DeleteExpiredSystemObjectsJob, which runs daily. You may have to adjust these defaults depending on your environment. Note that request objects and all associated WorkflowInstance, Approval, and ApprovalResponse objects are deleted according to the ExpirationTime attribute. The Expiration Time attribute is stamped at the time that the Request finishes processing using the currentTime + the SystemRetentionTime.

If you delete expired objects, they will be gone permanently from your system. If you want to maintain a historical record of all FIM activities for your organization, export the system objects to a separate database before they are deleted. Options to export data are described briefly below.

FIM_DeleteExpiredSystemObjectsJob is configured to delete 20,000 expired requests per run—a process that is capable of removing more than 100,000 individual system objects. The agent job finds all requests that expire before the current time, collects all of their dependent system objects (WorkflowInstance, Approval, and Approval Response), and deletes them from the permanent FIM database tables. This job agent is configured to run daily. We recommend that you keep this agent job enabled to ensure that your database does not become overloaded with old system objects.

If your system has more than 20,000 requests per day, you may have to remove objects from your system by running FIM_DeleteExpiredSystemObjectsJob more frequently. For example, you could run it multiple times per day to clean-up any “spikes” caused by your initial deployment. Be aware that running the job affects performance, slowing down normal request processing. You may want to schedule the job to run during off-peak hours.

To configure the agent job to run every 15 minutes during off-peak hours:

  1. Open SQL Server Management Studio, click SQL Server Agent, and then click Jobs.

  2. Click FIM_DeleteExpiredSystemObjectsJob.

  3. Click Schedules and then click Edit

  4. In Job Schedule Properties, under Daily frequency, click Occurs every, select 15 minutes and set it to run from 11 p.m. to 7 a.m.

  5. Configure your retention policy to allow objects to be deleted one day after they are created, as explained in the next section.

The amount of time that the agent job takes to run depends on your environment. In a standard configuration, as described in the Capacity Planning Guide, the agent job took 5 to 15 minutes to complete.

Configuring the retention policy for system objects

The default expiry period for system objects is configurable from 1 through365 days. If you are managing a large environment, you may choose to remove expired objects more frequently. Conversely, if you run a relatively small environment, you can extend this period and retain all request data for a year, although performance might be affected.

To adjust the expiry period, you create a Management Policy Rule (MPR) and then modify the attributes for Request and Workflow Instance Retention Configuration.

To create the MPR

  1. Open the FIM Portal, click Management Policy Rules, and then click New.

  2. Enter a Display Name, for example: Administration: Administrators can update system resource retention service objects.

  3. Enter a Description, for example: Allows members of the Administrators set to adjust the policy for system object retention.

  4. Ensure that Request is selected, and then click Next.

  5. On the Requestors and Operations tab, go to Specific Set of Requestors, and enter Administrators.

  6. Under Operations, select Modify single valued attribute.

  7. Under Permissions, select Grants Permission, and then click Next.

  8. On the Target Resources tab, go to Target Resource Definition Before Request, and enter All System Resource Retention Configurations.

  9. Under Target Resource Definition After Request, enter All System Resource Retention Configurations.

  10. Under Resource Attributes, ensure that All Attributes is selected, and then click Okay.

The MPR is now created, and you can modify the number of days that a system object is retained.

To modify the number of days that a system object is retained

  1. Return to the FIM Portal, and click Administration.

  2. Click All Resources, and then click System Resource Retention Configuration.

  3. Click Request and Workflow Instance Retention Configuration.

  4. Click Extended Attributes, and then enter the desired number of days, ranging from 1-365

Exporting expired objects

For auditing purposes, you may choose to export request resources to a separate database to avoid them being permanently deleted. There are various ways to approach exporting data. For example, you can:

  1. Run a simple Windows Powershell script to move all expired objects.

  2. Extend the FIM Synchronization Service to recognize expired system objects. Then, use the FIM MA to import expired objects into the Metaverse before exporting them with a SQL MA.

Managing FIM_TemporalEventsJob

You can use the FIM_TemporalEvents agent job to check all sets and groups that are associated with a timed event and ensure that all affected objects are marked for processing. This job includes FIM_Maintain_GroupsStep and FIM_Maintain_SetsStep, which run to correct processing errors that may occur on rare occasions.

Although changes are committed immediately each time that a request is processed, it is possible for parallel execution of requests operating against the same data to cause errors in final memberships of groups or sets. These steps serve as audit and correction tasks to ensure that the memberships are reviewed and corrected daily, if necessary.

The job is scheduled to run once a day. We recommend that you keep this job enabled to ensure consistency of groups and sets.

The time that this job is scheduled for represents the “tick” event that the entire system uses to evaluate time-based requests until the next time that this job runs. For example, assume that the job is scheduled for 1:00 a.m. If a Set Filter uses Today() in its logic, “Today” will resolve to 1:00 a.m. If you change the schedule, you also change the “tick”. Therefore, if you create a request at 8:00 a.m. and enter “add to set if this time is < Today()”, this would not be added to the set until the job runs. However, if it was “add to set if this time > Today()”, it will be added immediately because 8:00 a.m. is greater than 1:00 a.m.

To configure the frequency of FIM_TemporalEvents

  1. Open SQL Server Management Studio, click SQL Server Agent, and then click Jobs.

  2. Click Schedules, and then click temporaleventsjobschedule.

If you receive a warning message referring to the final step of the job, click Yes to ignore the warning.

For more information about temporal events, see Designing Business Policy Rules.