Configuring Delta Imports Using the MIM 2016 Microsoft Generic SQL Connector – Watermark Method

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

 

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 operationconsider 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.microsoft.com/en-us/microsoft-identity-manager/reference/microsoft-identity-manager-2016-connector-genericsql 

https://docs.microsoft.com/en-us/microsoft-identity-manager/reference/microsoft-identity-manager-2016-connector-genericsql-step-by-step 

https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1118 

https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/configuring-the-mim-2016-microsoft-generic-sql-connector-for/ba-p/1238412 

 

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 thWaterMark 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: 

Joe_Zinn_0-1603484476762.png

 

Sample Data: 

Joe_Zinn_1-1603484476800.png

 

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.   

 

Joe_Zinn_2-1603484476769.png

 

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. 

 

Joe_Zinn_3-1603484476771.png

 

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. 

 

Joe_Zinn_4-1603484476773.png

 

Select Global Parameters. 

Joe_Zinn_5-1603484476775.png

 

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.

 

Joe_Zinn_6-1603484476777.png

 

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 

 

Joe_Zinn_7-1603484476779.png

 

select the New Profile button and Enter the name of the run profile Ex. “Delta Import”. 

Joe_Zinn_8-1603484476781.png

 

Select Next 

Joe_Zinn_9-1603484476782.png

 

On the Configure Step pane, select Next 

Joe_Zinn_10-1603484476784.png

 

On the Management Agent Configuration pane, select Next. 

The Configure Run Step Parameters pane will be displayed.   

Joe_Zinn_11-1603484476785.png

 

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.  

 

Joe_Zinn_12-1603484476786.png

 

Using the vertical scroll bar, scroll down to the end of the Pane 

Joe_Zinn_13-1603484476787.png

 

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: 

Joe_Zinn_14-1603484476789.png

 

Perform the first Delta Import operation:  

In the Synchronization Service Manager Client right click the Management Agent 

Select RunDelta Import, and select the OK button 

Joe_Zinn_15-1603484476791.png

 

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: 

 

Joe_Zinn_16-1603484476792.png

 

Next, run the Delta Import job again by selecting RunDelta 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. 

 

Joe_Zinn_17-1603484476793.png

 

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. 

 

Joe_Zinn_18-1603484476794.png

 

Next, I perform the Delta Import and get the following Import results: 

Joe_Zinn_19-1603484476796.png

 

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.    

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.