Querying the MicrosoftIdentityIntegrationServer database

Many organisations and customers often express their requirement to effectively report on  information contained within the ILM database (MicrosoftIdentityIntegrationServer). Although I would never recommend that direct database access is used for this there are some cases in which information must be pulled from the ILM database itself.

However, since ILM assumes that the MIISServer.exe process has got exclusive access to the database; locking issues could cause you problems. (ILM will generate exceptions when locking occurs.)

In an effort to overcome this, one solution is to read ‘dirty’ data from the SQL database. In this way you run the risk of not having completely accurate information, but you mitigate your risk of causing locking issues.

NOLOCK:Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.

Within the MIIS/ILM database, the NOLOCK hint is not used for performance reasons but rather for safe MIIS/ILM operations. It is recommended that the NOLOCK hint is used so that your queries does not lock any data that MIIS/ILM might need during processing thus causing MIIS/ILM to fail or generate errors. Keep in mind that ILM assumes that it has total database control.

So where do we use it…

    SELECT        object_id
    FROM            mms_metaverse WITH (nolock)
    WHERE        (employeeID = @EMPID)