Tweaking the AD and CM Connectors in Service Manager 2012

  • Article

IMPORTANT: Always perform a FULL Backup of the database before doing anything to it !!!

ALSO: It is advised that you open a case at Microsoft before doing this - directly editing the database is not supported and you may find yourself in an unsupported state if anything goes wrong.

 

Some Connectors (like the Active Directory and Configuration Manager Connectors) are based on the Linking Framework (LFX). All connectors that are based on LFX use 2 sets of templates:

Data Provider:
The Data Provider section provides information, such as which data to import, that you must have when you are importing data from source into the staging tables of Linking Framework. The Data Provider section includes the queries that run against the source (LDAP queries for AD Connector and SQL queries for CM Connector), directives for staging table creation, custom SQL scripts and information that is relevant for incremental synchronization, such as watermarking and batching.

 

Data Consumer: The Data Consumer section provides information about reading the data from staging tables and writing it to the CMDB database’s instances space, such as Entities or Relationships. The Data Consumer section includes queries that run on the staging tables, mapping to the Service Manager type system, custom SQL scripts, and information that is relevant for incremental synchronization, such as watermarking and batching.

 

For *each* Class which will get imported we will see these events in the Operations Manager event log on the SM Workflow Management Server:

  1. Data Provider:
    1. 3332 (Lfx Service) – Connector Sync Begin
    2. 3333 (Lfx Service) – Connector Name starting sync for class X
    3. 34011 (Data Connectors) – Connector Name imported n rows – includes create/update
    4. 3339 (Lfx Service) – Connector Name finished sync for class X
  2. Data Consumer:
    1. 3333 (Lfx Service) – Connector Name starting sync for class X
    2. 34012 (Data Connectors) – Connector Name imported n rows of class X from cache table CLASS_CACHE_TABLE
    3. 3339 (Lfx Service) – Connector Name finished sync for class X

 

 

The AD /  CM Connector syncs usually take longer in the Data Consumer portion of the Connector and the performance impact is seen when this portion of the connector is running.

There are batch size settings for both the Provider and Consumer portion of the Connector. These batch size settings are set for pretty low numbers for the Data Consumer portions and have room for optimization in some environments where one of such a Connector will sync a lot of data. Unfortunately these settings are set during the installation of the product and are part of the Library Management Packs, so they cannot be changed using the Console. The only way to change them is by updating the CMDB database.

 

We can tweak the DefaultBatchSize values for your environment. These values are environment specific and depend on many factors like the hardware configuration and amount of data being pulled in. We will have to tweak these settings and test it to find the optimal values for your environment.

To begin with we can set the DefaultBatchSize to from 500 to 1000 for some type of data for example. Let the Connectors run for a couple of weeks and measure the run times for each run using the event id's above from the OM event log. If these values reduce the time we can bump it up further and see if we see further decrease in time for the connector. If we don’t we leave it at 1000. Be warned though, there is a good reason why this is done in batches - so be careful and don't bump up the values too much or you can put more pressure on your SQL Server.

 

First of all, always get a backup of the CMDB before doing this and *also* note down the default values to make sure that if you need to revert, you can do so easily:

  SELECT DataName, DefaultBatchSize<br> FROM LFX.DataTable

 

Now, you can use this SQL Query on the CMDB to check which parts of which Connectors import the most amounts of data and which may or usually do need some bigger batch size values:

  DECLARE @TableName NVARCHAR(255), @Query NVARCHAR(MAX)<br> DECLARE TableCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR<br>    SELECT name<br>    FROM sys.views<br>    WHERE schema_id = (<br>       SELECT schema_id<br>       FROM sys.schemas<br>       WHERE name = 'LFXSTG'<br>    ) UNION SELECT name<br>    FROM sys.tables<br>    WHERE schema_id = (<br>       SELECT schema_id<br>       FROM sys.schemas<br>       WHERE name = 'LFXSTG'<br>    )<br> OPEN TableCursor<br> FETCH NEXT FROM TableCursor INTO @TableName SET @Query = N'SELECT ''' + @TableName + ''', COUNT(1) FROM [LFXSTG].[' + @TableName + ']'<br> FETCH NEXT FROM TableCursor INTO @TableName<br> WHILE @@FETCH_STATUS = 0 BEGIN<br>    SET @Query += N' UNION SELECT ''' + @TableName + ''', COUNT(1) FROM [LFXSTG].[' + @TableName + ']'<br>    FETCH NEXT FROM TableCursor<br>    INTO @TableName<br> END<br> CLOSE TableCursor<br> DEALLOCATE TableCursor<br> SET @Query += ' ORDER BY 2 DESC'<br> EXEC(@Query)

 

 

Let's just take an example here and say that from this result, the AD_User_Out staging table has a very big number of entries - this would mean that we sync a lot of users - so we could see that the batch size for this is 500 and we will tweak this to 1000 for example by using this SQL Query (this would be true for others which would seem to need tweaking):

  UPDATE LFX.DataTable<br> SET DefaultBatchSize = 1000<br> WHERE DataName = 'AD_User_Out'

 

 

This should do the trick and speed up your Connector import times! :D