Hello, my name is Joe Zinn and I am a Senior Customer Engineer for Microsoft supporting Azure, Identity, AD Connect, FIM and MIM solutions in the US. The following document describes how to configure the Microsoft Generic SQL Connector to use the Watermark method to achieve Delta Imports.
Overview:
When configuring the Microsoft Generic SQL Connector for Delta Imports there are several methods that that can be used including Watermark, Trigger, Snap Shot, and Change Tracking. Each of these methods are briefly addressed in the reference links provided below.
This document covers the step by step configuration of the Watermark Delta Import method. Please note that the Watermark method manages Adds and Update operations only. It does not allow for deletion operations.
To import deletions when using the watermark method, a Full Import must be performed. If you require deletions to be imported in your delta operation, consider using either the trigger, SnapShot, or Change Tracking method.
The watermark method will perform an initial full Import, then set a Watermark date value to be used on subsequent Delta Import Operations. Please be aware that your server times for the SQL Database and MIM Synchronization Server must be in sync to achieve accurate delta import results. The WaterMark date time is obtained from the SQL server, while the sync engine itself reports run history using its date time settings. For consistency the times between the two servers should be in sync.
Management Agent Installation and Configuration:
Install and configure the Microsoft Generic SQL Connector following Microsoft’s step-by-step guide (link provided below). This configuration is specific to a Microsoft SQL Server implementation.
For Oracle and other databases see my blog post covering date time configurations.
Microsoft Identity Manager 2016 Generic SQL Connector Reference Documents
https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1118
SQL Database Table Preparation:
The SQL Table we are using contains an EmployeeID attribute that is unique to each user. This attribute is used as the Anchor value in the MA Configuration.
Two attributes (LastUpdate and ChangeType) are added to the SQL table to support the WaterMark delta option. Note: The attribute names can differ from the names I use in this example. The attribute names are self-defined in the configuration of the run profile later in this document.
For simplicity, this document adds these two attributes to the primary data table. LastUpdate is a datetime value and ChangeType is a varchar(50) in the sample connected data source. Below is a snippet of the HR_DATA_CSV table schema and sample data that we will use for this documentation:
Schema:
Sample Data:
LastUpdate Default Value:
The LastUpdate attribute has a default value populated on create using the GetDate() function. The Default Value or Binding value is set to (getdate()). Note: The GetDate() Function is specific to Microsoft SQL. Similar functions such as “Select Now()” in MySQL are available to obtain the current date in other forms SQL.
ChangeType Default Value:
The ChangeType attribute can have 2 values Add when new, and Update when modified. On create the default value is set by setting the Default Value or Binding value to (N’Add’) as shown below.
Note: You may find for single valued attributes that MIM will automatically determine whether the record is an Add or Update. However, this is not the case for multi-value attributes. Multi-valued attributes require the ChangeType to be present in order to process adds or updates to the attribute.
Updating the LastUpdate and ChangeType Values:
When a record is modified in the table, the LastUpdate and ChangeType attributes should be automatically updated to reflect the date and time of the change and that the ChangeType is Update. This is done using a trigger.
The following SQL command can be used to create this trigger:
CREATE TRIGGER [dbo].[trg_ChangeType]
ON [dbo].[HR_DATA_CSV]
AFTER UPDATE
AS
UPDATE dbo.HR_DATA_CSV
SET LastUpdate = GETDATE()
,ChangeType = N'Update'
WHERE EmployeeID IN (SELECT DISTINCT EmployeeID FROM Inserted)
SQL Table Preparation Summary:
In the preparation of the SQL table we added two attributes, LastUpdate and ChangeType. We set default values for both attributes to the current date time, and Add respectively. We then setup a trigger to update the LastUpdate and ChangeType when the record is modified. The trigger uses our unique identifier (EmployeeID) and sets the values to current date and time, and Update respectively.
Configuring MIM for Watermark Delta Import operations.
There are several steps involved in configuring the Microsoft Generic SQL Management agent for the Watermark Delta Import operation. First, we configure the management agent, then we create a Delta Import Run Profile, and finally we perform two Delta Import operations. The first Delta Import will actually be a full import and will establish the watermark for the second delta import where we will see only changes.
Setting up the Generic SQL Management agent for Watermark Delta Import method.
Launch the MIM Synchronization Server Manager Client.
Right Click the Management Agent and Select Properties.
Select Global Parameters.
Change the Delta Strategy to WaterMark
Set the Water Mark Query using the SQL command of the database provider. MIM will send this command to the SQL server to be executed. Therefore, test this command in your database first to validate its functionality.
While GetDate() works in Microsoft SQL, it does not work in MySQL. The proper command for MySQL is Select NOW(). Other SQL database providers may have different commands to obtain the date time values. Please reference the database providers documentation for proper syntax.
Set the Data Source Time Zone and the date time format for the WaterMark.
Select Ok
Setup The Delta Import Run Profile:
The next step is to setup the Delta Import run profile.
Right Click the Management Agent
select Configure Run Profiles
select the New Profile button and Enter the name of the run profile Ex. “Delta Import”.
Select Next
On the Configure Step pane, select Next
On the Management Agent Configuration pane, select Next.
The Configure Run Step Parameters pane will be displayed.
This is where we will configure the WaterMark method for Delta Import. Note the vertical scroll bar on the right side of the pane that will be used to navigate through the various settings.
Set the Operation Method as Table
Enter the Table in the Table/View/SP field.
Using the vertical scroll bar, scroll down to the end of the Pane
In the Delta Operation Column Name enter ChangeType
In the Water Mark Column Name enter LastUpdate
In the Define Change Type Attribute Add field enter Add
In the Define Change Type Attribute Update field enter Update
Note: The delete function will not be used in the WaterMark method
Select Finish, Ok
The run profile configuration should look similar to the following:
Perform the first Delta Import operation:
In the Synchronization Service Manager Client right click the Management Agent
Select Run, Delta Import, and select the OK button
The Management Agent will perform an import of all the records. The initial Delta Import is truly a Full Import since the WaterMark date has not yet been established. My HR Database contains 180 user records and produces 180 updates:
Next, run the Delta Import job again by selecting Run, Delta Import, and select the OK button
Now that the WaterMark Date has been established and no updates have been made, there are 0 Adds and 0 Updates in the second Delta Import results.
I will now create two new users in the table (EmployeeID 10000177 and 10000178), and update one new user in the table (EmployeeID 10000177), and perform an update on an existing user (EmployeeID 10000176) in the table. The modified records appear as follows for these test users.
Next, I perform the Delta Import and get the following Import results:
Note that both new users appear as an Add from the data source and only the three modified records are processed.
Summary:
The WaterMark Method requires the data source to contain two attributes such as LastUpdate and ChangeType. The Management Agent must be configured for the WaterMark Delta Option and a Delta Import run profile must be created with specific configuration settings. The initial Delta Import is essentially a Full Import, as the Watermark is not yet established. Tertiary Delta Imports should only reflect new Adds or Updates.
I hope that this information has helped clarify the configuration of the WaterMark Delta Import method for the Generic SQL MA.
Updated Oct 23, 2020
Version 3.0
Joe_Zinn
Microsoft
Microsoft