MIM 2016: Setting up Delta table for SQL MA

I had an interesting question to address. I would like data to flow from FIM Service to MV but when returning back to the FIM Service to do some kind of selective filtering. If Attrib1 = X then update Attrib2 in FIM service MA. Well there is no option to write code for the FIM Service MA, so one option is to use another MA as an intermediary and then apply the selective rule in the MA. I decided to use the SQL MA as the intermediary. The object in question is the group object and it is a multi-value attribute I want to do selective attribute flow. Lets look at setting up a SQL MA for delta imports.

Carol has addressed this topic here and here and here. I want to add a couple of things because I was provisioning Group membership to SQL and it was interesting.

1/ For Group membership, you have to provision the users as well as the groups into the same SQL MA. Else what you will find is that no membership information will be provisioned to the the SQL MA. That’s standard anyway for group provisioning, the members have to be in the CS for the MA to resolve them. So I setup two outbound sync rules in the Portal, one for Group provisioning to the SQL MA and one for user. When you setup the MA its going to ask you to choose an object type, I chose Group. When I setup the User sync rule it gave me the option of only group for object type in the remote directory (SQL MA). Don’t matter, the membership will resolve fine. If you are using this for group membership you really only have to sync up an identifier attribute for the Users, not their whole attribute list. So you should have 3 sync rules

  • SQL MA Outbound
  • SQL MA Inbound Groups
  • SQL MA Inbound Users

2/ In the SQL , in addition to the base two tables (Main and Multi-value) you have to create 5 tables

  • Snapshot
  • MVA_snapshot
  • Archive
  • MVA_Archive
  • Delta

3/ For group membership, you have to create a multi-value table to store the membership information. The member attribute in the MV is a multi-value reference and needs such a table in the SQL MA. Carol covers that here.

4/ Use a unique identifying for your objects (that does not change for the life of the Object in FIM, use the ObjectID, if you use Accountname when the Group name changes, it makes it fail.

5/ For group membership, you have to do the attribute level provisioning if you want to get the membership. The normal delta scripts works if you are dealing with just one table, but when you are dealing with a main table and an MVA table then you may need the delta script for the MVA. You should remember that MVA delta involves adding an additional column to your Delta table. That extra column will have to be populated in your add/delete/modify query for the base table (you can put NULL).

6/ Truncate your snapshot table before copy the current table. Truncate your Delta table before processing. At the end of your script, truncate your Archive table and copy the current table.

7/ Setup your sql script to do create the delta table to process transactions in this order

  1. Adds
  2. Deletes
  3. Modify
  4. Attribute Modify

If your modify comes before an add to a new object then you will get a sync error and it will throw off your delta sync unless you a full Import/Sync. Something to note for me, I used a LEFT OUTER JOIN for my MVA table comparison. Carol used a FULL OUTER JOIN in the sample script given. Up to you, depends on what is your desired end state. I am not making any data change in SQL, just rejoining the same data back to the MV.

8/ How does it fit into the sync process? Prepare the Delta table query, if you are using PowerShell to run your sync jobs then you can use the DotNet SQL adapter and run the query. You can also create a simple store procedure to run the query

create procedure CreateDeltaData


Your script…..


You can run the SP from your run profile PS or if you are using batch script the MIIS resource kit run engine (which I still use till today, to me the most reliable sync run engine I have used), you can also call the SP from the command prompt.

After the Delta table is populated, run your Delta Import/Delta Sync on the SQL MA.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s