Share via

FIM 2010 SQL Synchronization Service Database Tables

  • Article

Applies to:

FIM 2010 SQL Database tables - FIM 2010 R2 SP1

Research into the tables created in FIMSynchronizationService Database

Introduction

As I start to progress through FIM, I decided a create an article on what is stored in the various tables found

in the FIMSynchronizationService database

This is a work in progress at the moment so details will changed and be added to, including information on the other tables:

Table names

Dbo.mms_run_history

 

Every time a profile run is executed a row is added to this table

It contains information you would see displayed in the operations window after clicking the operations button

as per example screenshot

The table contains additional information columns with uniquely identifying ID numbers

Additional columns for the information displayed in the Operations panel are not necessarily an exact name match

 (e.g. run_result column = status column)

Columns

The table contains the following columns  (to be filled in )

run_history_id A GUID uniquely identifying this run instance
ma_id A GUID uniquely identifying the management agent instance for this run instance.

This MUST match a management agent record in the mms_management_agent table

at the time the run started
run_profile_id A GUID uniquely identifying the run profile. This value MUST match a run profile record

in the mms_run_profile table
run_number A number uniquely identifying this instance of the run profile execution
user_name The domain and user name that triggered the execution of the run profile.

This MUST be in the form DOMAIN\UserName.
is_run_complete  A bit where a value of 1 indicates that the run is complete
run_result A string indicating the run result.

This is the message shown in the status column of the operations view

The possible values and descriptions are show below 

Value in quotes followed by description

  • "in-progress" The run profile execution is still in progress.
  • "success" The run profile completed successfully.
  • "no-start-credentials" The credentials for the management agent are invalid.
  • "no-start-connection" The management agent was unable to establish a connection to the data source.
  • "no-start-file-not-found" The input file could not be found for an import staging profile.
  • "no-start-file-accessdenied" The management agent does not have permissions to read or write the staging file or drop file.
  • "no-start-file-sharing-violation The file system reported a sharing violation when the management agent tried to access the staging file or drop file.
  • "no-start-file-open" A generic file create/write error.

During the creation of a staging file, this will be reported for any file access error other than:

  • § no-start-file-access-denied
  • § no-start-file-sharing-violation
  • § stopped-disk-full

 

Specific details of the failure will be written to the application event log.

  • "no-start-databasepermission" The management agent does not have the permissions needed to access the data source database.
  • "no-start-database-table" The management agent could not read or write the database table.
  • "no-start-databaseschema-mismatch" The database schema does not match the schema defined for the management agent.
  • "no-start-ma-workingdirectory" The management agent working directory could not be accessed.
  • "no-start-full-importrequired" The management agent does not have a valid watermark for the data source server it is accessing. A full import MUST be performed in order to get all changes and bring the watermark current.
  • "no-start-file-containsincorrect-step-type" The step-type of drop file does not match the step-type of run profile step.
  • "no-start-bad-maconfiguration" The management agent has incorrect configuration in the XML definition.
  • "no-start-partition-notconfigured" The management agent does not have a valid partition configured.
  • "missing-partition-forrun-step" The partition defined for this run step does not exist.
  • "no-start-no-domaincontroller" The management agent could not locate a domain controller.
  • "no-start-partitionrename" The management agent detected a partition rename in the data source.
  • "no-start-partitiondelete" The data source partition has been deleted.
  • "no-start-change-log-notenabled" The data source change log has not been enabled.
  • "stopped-change-log-outof-order" The data source change log is out of order.
  • "no-start-delta-steptype-not-configured"The delta step type is not configured.
  • "no-start-file-code-page" The code page configured for the management agent does not match the code page detected in the import file.
  • "stopped-parsing-errors" The run step stopped because there were parsing errors while reading the data.
  • "no-start-server" The management agent failed to start because of an unspecified server error.
  • "no-start-ma" The management agent failed to start because of an unspecified management agent error.
  • "no-start-no-steps-inprofile" There are not steps defined in the run profile. Define at least one run step.
  • "no-start-notes-api-notavailible" Reserved.
  • "no-start-notes-clientinit-failure" Reserved.
  • "no-start-header-rowmismatch" The header row in the input file and the header row configured in the file management agent do not match.
  • "stopped-connectivity" The run step stopped because of connectivity errors.
  • "stopped-usertermination-from-wmi-orui" The user stopped the run.
  • "stopped-usertermination-fromextension" The extension terminated the run.
  • "stopped-serviceshutdown" The run stopped because the service is shutting down.
  • "stopped-object-limit" The run step stopped when it reached the maximum number of objects per run.
  • "stopped-deletion-limit" The run step stopped because the number of deletions exceeded the defined limit.
  • "stopped-error-limit" The run step stopped when it exceeded the specified maximum number of errors.
  • "stopped-import-read" The run step stopped when it encountered an error reading from the import file
  • "stopped-export-write" The run step stopped when it encountered an error writing to the export file.
  • "stopped-bad-ma-configuration" The run stopped because the management agent configuration is invalid.
  • "stopped-file-embedded-nulls" The run step stopped because the import file contains embedded nulls.
  • "stopped-deadlocked" The run step failed because of a database deadlock.
  • "stopped-code-page-conversion" The run step stopped because the management agent was unable to convert data to or from the specified codepage.
  • "stopped-server" The run step stopped because the server encountered an unexpected error.
  • "stopped-ma" The run step stopped because the management agent encountered unexpected errors.
  • "stopped-extension-dll-not-configured-for-mv" The run step stopped because the metaverse extension has not been configured.
  • "stopped-extension-dl-lnot-configured-for-ma" The run step stopped because the management agent rules extension
  • "stopped-extension-dll-file-not-found" The run step stopped because the extension DLL could not be found in the extensions folder.
  • "stopped-error-in-end-connection" point. The run step stopped because the extensible management agent returned an EndConnectionException exception while calling the end connection entry point.
  • "stopped-ma-extension-error-in-end-connection" The run step stopped because the extensible management agent returned an unexpected exception while calling the end connection entry point.
  • "stopped-ma-extension-timeout" The run step stopped because the extensible management agent timed out while performing an operation.
  • "stopped-password-extension-timeout" The run step stopped because the password extension timed out while processing a password.
  • "stopped-password-extension-not-configured" The run step stopped because the password 

 
current_step_number The current run step that is being executed starting with step 1. When the run completes, this MUST be set to the total step number
total_steps The total count of run steps in this run profile
start_date The UTC time that this run execution started
end_date The UTC time that this run execution completed. This MUST be NULL if the run

is still in progress
run_profile_name The name of the run profile at the time the run was created. This is used for displaying history even if the run profile is subsequently deleted from the system
mms_timestamp A sequential integer identifier derived from the mms_timestamp field in the mms_server_configuration table
operation_bitmask Flags that indicate what operation(s) were performed during the run, and what statistics have been collected in the run history. The possible values and descriptions are show below 

Bit mask value followed by description

  • 0x000000000000000 No statistics.
  • 0x000000000000001 Staging unchanged.
  • 0x000000000000002 Staging add.
  • 0x000000000000004 Staging update.
  • 0x000000000000008 Staging rename.
  • 0x000000000000010 Staging delete.
  • 0x000000000000020 Staging delete-add.
  • 0x000000000000040 Staging failure.
  • 0x000000000000080 Disconnector filtered.
  • 0x000000000000100 Disconnector joined, no flow.
  • 0x000000000000200 Disconnector joined with flow. PY
  • 0x000000000000400 Disconnector joined, remove metaverse object.
  • 0x000000000000800 Disconnector projected, no flow.
  • 0x000000000001000 Disconnector projected with flow.
  • 0x000000000002000 Disconnector remains a disconnector.
  • 0x000000000004000 Connector filtered, remove metaverse object.
  • 0x000000000008000 Connector filtered, leave metaverse object.
  • 0x000000000010000 Connector with flow.
  • 0x000000000020000 Connector with flow, remove metaverse object.
  • 0x000000000040000 Connector, no flow.
  • 0x000000000080000 Connector delete, remove metaverse object.
  • 0x000000000100000 Connector delete, leave metaverse object.
  • 0x000000000200000 Connector delete-add processed.
  • 0x000000000400000 Flow failure.
  • 0x000000000800000 Export add.
  • 0x000000001000000 Export update.
  • 0x000000002000000 Export rename.
  • 0x000000004000000 Export delete.

Selecting the clear all runs operation from the Actions menu option will clear this table

This table will continue to grow unless you regularly clear the runs 

If the table is not clear it could eventually consume all the disk on which FIM sync database is located 

 

Dbo.mms_run_profile 

Stores information about each run profile which has been created

The table contains the following columns  (to be filled in )

creation_date

run_profile_name

configuration_xml

run_profile_id A GUID uniquely identifying the run profile
ma_id A GUID uniquely identifying the management agent associated with this

run profile. This value MUST match a management agent record in the mms_management_agent table.
version_number The version of the last change made to this run profile. This MUST start at 1. The synchronization engine will increment the version, allowing conflict detection for simultaneous edits
The UTC time that this run profile was created
modification_date The UTC time that this run profile was last modified
The name of this run profile. name of this run profile.
An XML fragment containing the run profile configuration. This must be formatted using this XML schema

<xs:element name="configuration">

  <xs:complexType>

   <xs:sequence>

    <xs:element minOccurs="1"

  maxOccurs="unbounded"

  ref="step" />

  </xs:sequence>

 </xs:complexType>

</xs:element>
   

 

 

 

Dbo.mms_step_history 

Every time a  run profile is executed a row is added to this table for each step in the run

Selecting the clear all runs operation from the Actions menu option will clear this table

The information stored in this table is the information you see when you select a step that has run as part of a

run profile

When you select a step which has been executed  you can see  various information about the activities of the

step listed in the synchronisation statistics panel  and synchronization errors / status panel 

as per example screenshot

 

The table contains the following columns  (to be filled in )

Step_history_id A GUID uniquely identifying this run step history object
run_history_id A GUID uniquely identifying the run history object associated with this run step. This value MUST match a run history record in the mms_run_history table
step_number An integer identifying what step this was in the overall run profile definition
step_result A Unicode string identifying the overall result of the run step. This MUST be a value specified in section
start_date The UTC time that this run step started
end_date The UTC time that this run step stopped
stage_no_change Number of objects which remain unchanged in the MA connector space after an import stage is run

Shows as “Unchanged” in the Synchronization Statistics Pane

Technet Description :

The object referred to by the change was looked up by the anchor and found in the connector space with the same distinguished name (also known as DN). By comparing attributes and values, it was determined that its hologram in the connector space matches the incoming change, which will result in no change.
stage_add Number of objects added to the MA connector space after an import stage is run

Shows as “Adds” in the Synchronization Statistics Pane

The objects will show as Modification type : - add in the MA connector space

The object state will be: Normal Disconnector (i.e. the object is not connected to an object in the Metaverse

Technet Description :

The object referred to by the change was looked up by the anchor and not found in the connector space. A new connector space object was created with the information specified in this change. If there is a connector space object with a different anchor and the same distinguished name (also known as DN), the existing connector space object will be moved into the transient state. This transient move is not accounted for in the statistics.
stage_update Number of objects in the MA space which have been changed during a staged import stage

Shows as “Updates” in the Synchronization Statistics Pane

Technet description :

The object referred to by the change was looked up by the anchor and found in the connector space with the same distinguished name (also known as DN). By comparing attributes and values, it was determined that its hologram in the connector space is different, implying that there is an update.
stage_rename Number of objects in the MA space which have been renamed – i.e. the DN value of the object has changed

during a staged import stage

Shows as “Renames” in the Synchronization Statistics Pane

Technet description :

The object referred to by the change was looked up by the anchor and found in the connector space, but the distinguished name (also known as DN) is different. Staging a rename will cause the distinguished name in the connector space to change and that can trigger a move of an existing connector space object to a transient state. This transient move is not accounted for in the statistics.
stage_delete Number of objects which are marked for deletion during a staged import

Shows as “Deletes” in the Synchronization Statistics Pane

Technet description :

For delta import runs, the incoming change was a delete and the object being deleted was found in the connector space. For full import runs, either an incoming delete was imported and found in the connector space or the object was obsolete. There is no difference in the accounting of the two cases, but generally for the management agents for Active Directory and Active Directory Application Mode (ADAM) staging this counter indicates the number of imported tombstones that corresponded to objects in the connector space.
stage_deleteadd  The number of staging objects processed resulting in the delete-add of a connector space object 
stage_failure  The number of staging objects processed resulting in a failure 
disconnector_filtered The number of disconnector objects that were run through the connector filter and marked as filtered disconnectors.

Shows as “Filtered Disconnectors in Synchronization Statistic Panel
disconnector_joined_no_flow The number of disconnectors objects that were successfully joined for which there was no actual attribute changes flowed to the Metaverse.
disconnector_joined_flow The number of disconnectors objects that were successfully joined for which actual attribute changes flowed to the Metaverse.
disconnector_joined_remove_mv The number of disconnectors objects that joined to metaverse objects in the first part of the synchronization process, but ended up removing that metaverse object because the provisioning extension programmatically disconnected its connectors. 
disconnector_projected_no_flow Number of disconnectors objects that were projected for which there was no actual import attribute flow to the Metaverse.

shows as “Connectors without Flow Updates” in the Synchronization Statistics Pane
disconnector_projected_flow The number of disconnectors objects that were projected for which actual attribute changes flowed to the metaverse.

Shows as “Connectors with Flow Updates” in the Synchronization Statistics Pane
disconnector_projected_remove_mv The number of disconnectors objects that tried to project to the Metaverse, but in the process the provisioning extension programmatically disconnected connectors leading to the removal of the Metaverse object it was trying to create.
disconnector_remains The number of disconnector objects that successfully passed the connector filter test, but which there were no rules calling for them to project or join and now remain as normal disconnectors in the connector space.
connector_filtered_remove_mv The number of existing connector objects which were disconnected by the connector filter on this pass causing the metaverse object to be removed.
connector_filtered_leave_mv The number of existing connectors objects which were disconnected by the connector filter on this pass but the metaverse object was left in place.
connector_flow The number of existing connector objects that passed the connector filter test and remained connectors. When import attribute flow was applied, new or changed values were flowed to the Metaverse
connector_flow_remove_mv The number of existing connector objecs that passed the connector filter test and had import attribute flow applied to them, but had their metaverse object removed because the provisioning extension programmatically disconnected the connectors joined to that metaverse object
connector_no_flow Number of connected objects in the MA which have had no updates to the attributes
connector_delete_remove_mv Number of objects removed from the Metaverse (Deleted Connectors)
connector_delete_leave_mv The number of existing connectors which were deleted on this pass for which the metaverse object was left in place.
connector_delete_add_processed The number of connector space objects with an existing connection to a metaverse object that had a delete-add operation processed 
flow_failure The number if objects for attributes failed to update
export_add The numnber of new object creations that the management agent has processed successfully or which have been written to the drop file.
export_update The number of object updates, which do not involve renames of the object, that the management agent has processed
export_rename The number of object updates, which do include renames of the object, that the management agent has processed successfully or which have been written to the drop file
export_delete The number of object deletes that the management agent has processed successfully or which have been written to the drop file.
export_deleteadd The number of modifications, which involve deleting the existing object and adding a new object with the same distinguished name (also known as DN), that the management agent has processed successfully or which have been written to the drop file.
export_failure The number of objects which failed to export from an MA to a destination identity store
current_export_batch_number The current export batch number at the time this export step began. 
last_successful_export_batch_number The last successful export batch number at the time this export step began
step_file_name  The file name of the defined drop file for this step
ma_connection_information_xml Stored in the database as XML

Shows information about instance of an MA connection to a identity store

e.g. (AD MA connector )

(this shows all the tags used with example AD MA)

<connection-result>

success

</connection-result>

<server>

FIM2012-02.FIMR2ENV.COM.LOCAL:389

</server>

<connection-log>

<incident>

<connection-result>

success

</connection-result>

<date>

2015-03-27 14:51:16.504

</date>

<server>

FIM2012-02.FIMR2ENV.COM.LOCAL:389

</server>

</incident>

</connection-log>
ma_discovery_errors_xml An XML fragment containing management agent discovery errors for this run step. This MUST be formatted as specified in section 

 
ma_counters_xml XML fragment containing management agent counters for this run step. This MUST be formatted as specified in sections
sync_errors_xml Details of synchronization errors stored in XML format

These are the details shown in the synchronization errors

pane
step_xml Configuration information about the step executed within a run profile

Information is stored in an XML format

This is configuration details you see when you click on a configured step within the Configure Run Profiles editor

Every option configured for the step will show up as a Tag with the XML

e.g.

<step-type type="full-import">

<import-subtype>to-file</import-subtype>

<import-subtype>resume-from-file</import-subtype>

<import-subtype>to-cs</import-subtype>

</step-type>

<dropfile-name>output_test</dropfile-name>

<partition>DC=FIMR2ENV,DC=COM,DC=LOCAL</partition>

<custom-data>

<adma-step-data><batch-size>100</batch-size><page-size>500</page-size><time-limit>120</time-limit></adma-step-data>

</custom-data>
mv_retry_errors_xml An XML fragment describing the metaverse retry errors that occurred during synchronization. This MUST be NULL if no errors occurred
flow_counters_xml This contains XML fragments specifying the inbound and outbound flow counters. 

 -for  inbound-flow-counters the XML is formatted as

per this schema

<xs:element name="inbound-flow-counters">

<xs:complexType>

<xs:sequence>

<xs:element name="disconnector-filtered" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-joined-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-joined-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-joined-remove-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-projected-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-projected-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-projected-remove-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="disconnector-remains" type="counterDetailFalseType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-filtered-remove-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-filtered-leave-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-flow-remove-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-delete-remove-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-delete-leave-mv" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="connector-delete-add-processed" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

<xs:element name="flow-failure" type="counterDetailTrueType" maxOccurs="1" minOccurs="1" />

</xs:sequence>

</xs:complexType>

</xs:element>

 -for  outboud-flow-counters the XML is formatted as

per this schema

<xs:element name="outbound-flow-counters">

<xs:complexType>

<xs:sequence>

<xs:element name="provisioned-add-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="provisioned-add-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="provisioned-rename-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="provisioned-rename-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="provisioned-disconnect" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="connector-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="connector-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="provisioned-delete-add-no-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

<xs:element name="provisioned-delete-add-flow" type="counterDetailTrueType" maxOccurs="1" minOccurs="0" />

</xs:sequence>

<xs:attribute name="ma" type="xs:string" use="required" />

<xs:attribute name="ma-id" type="guidType" use="required" />

</xs:complexType>

</xs:element>

 

 

Dbo.²mms_step_object_details 

 

Every time a step within a profile is run, a row is added to this table for each object 

(e.g . user, group  object  e.t.c.) which has had a successful action performed against

it during the step execution

An action can be a projection, join e.t.c

Selecting the clear all runs operation from the Actions menu option will clear most of the rows

in this table , some rows remain which I want to identify

step_history_id GUID uniquely identifying this record in the table
statistics_type  A GUID uniquely identifying the associated run step in the mms_step_history table.
ma_statistics_type A number indicating which management agent statistic values

were incremented while processing this object
update_count The number of times the statistics were updated after they were initially set during the run
ma_id A GUID uniquely identifying the management agent associated

with this step object details record. This value MUST match an

ma_id value in the mms_management_agent table.
cs_object_id  A GUID uniquely identifying the connector space object

associated with this step object details record. This value

MUST match an object_id value in the mms_connectorspace

table
cs_dn The distinguished name of the connector space object at the

time of the step object details event