My quest to discover and teach Best Practices in Identity Management especially with Microsoft FIM / ILM -- David Lundell of Identity Managed
I wrote an article for the Insight Newsletter about two of our new offerings.
Solving identity and access management for mid-sized business
By David Lundell, Sr. Manager, Identity and Security Practice
User productivity, IT budgets, and security and compliance all suffer from ineffective identity and access management. Insight has two new packages aimed at helping mid-sized businesses confront these challenges in the age of the cloud. Read more.
Parts 1-5:
First of all the FIM Product group does not support direct modification of the data in any of the FIM databases. Do so can leave your database in a state that is entirely unsupportable.
Second, the FIM Product group doesn't support direct queries against any of the FIM databases. However, it is possible to query the FIM Synchronization Service database without causing problems and without enormous gyrations. The FIM Service database has a very *interesting* data structure and is much more difficult to query.
So how do you safely query the FIM Synchronization Service database?
1) Use no lock hint
or
2) Set the transaction isolation level to read uncommitted.
Both of these tell SQL to not obtain locks on the records you are querying. This means that other threads such as the one that is synchronizing records don't get stuck waiting on your query. However this does also mean that some of the data you read could be in the middle of transaction, a transaction that could be rolled back. For example you query could catch it at the moment when a new MV object has just been projected from the HR MA but has not yet provisioned to AD. That transaction could succeed and commit or it could get rolled back and the MV project undone.
On to the how:
To use the no lock hint you must place it after every table and view name referenced in your query like this:
SELECT MetaverseObjectCount, ConnectorSpaceObjectCount, MetaDirectoryObjectCount = MetaverseObjectCount + ConnectorSpaceObjectCount
FROM (select count(*) AS MetaverseObjectCount
FROM MicrosoftIdentityIntegrationServer.dbo.[mms_metaverse] with (nolock)) as MVC
CROSS JOIN
(select count(*) AS ConnectorSpaceObjectCount
FROM MicrosoftIdentityIntegrationServer.dbo.[mms_connectorspace] WITH (nolock) ) AS CSOC
To Set the transaction isolation level to read uncommitted you simply run that command and the keyword GO before your queries -- sometimes your reporting tools permit you to set that with a checkbox
set transaction isolation level read uncommitted
go
SELECT MetaverseObjectCount, ConnectorSpaceObjectCount, MetaDirectoryObjectCount = MetaverseObjectCount + ConnectorSpaceObjectCount
FROM (select count(*) AS MetaverseObjectCount
FROM MicrosoftIdentityIntegrationServer.dbo.[mms_metaverse] ) as MVC
CROSS JOIN
(select count(*) AS ConnectorSpaceObjectCount
FROM MicrosoftIdentityIntegrationServer.dbo.[mms_connectorspace] ) AS CSOC
One might ask: Can you do both? Yes you can and it will cause no harm. The one place you can use the set transaction isolation level command is in a view or stored procedure -- so inside of those objects you must use the WITH (nolock) hint
Parts 1-5:
Has access to the metaverse gotten faster with recent releases? Well I won't cover everything they have done but two really significant things:
1) Skinnier clustered index key for the mms_metaverse table:
2) Sequential numbering of the clustered index key
1) Skinnier clustered index key for the mms_metaverse table:
| Old | New | |
| column used as the clustered Index key: | object_id | row_key |
| DataType: | uniqueIdentifier | big_int |
| Size: | 16 bytes | 8 bytes |
The mms_metaverse table previously used the object_id column of type uniqueIdentifier (16 bytes) as the clustered index key. The product group added a new column called row_key of type big_int (8 bytes).
So how does this help?
Think of a database table like a book. The clustered index is the Table of Contents and the main text of the book, excluding the indexes in the back. There is only one order for the book to go in and that is the same with a table. In the book the key used in the table of contents is the page #. In a table we pick one or more columns to serve as the clustered index key.
The non-clustered indexes are like the indexes in the back of the book (a topical index, a place index, a person index) and they make include the clustered index key. So the topical index lists a subject and then the page(s) where it is found.
Clustered Index ~ Table of Contents
Clustered Index Key ~ page number
Non-Clustered Index ~ Topical Index at the back of the book
Non-Clustered Index Key ~ topic
Pretend we had to pad the page # so it looked like 00000000045?
Chapter 1 page 0000 0000 0000 0000 0000 0000 0001
Chapter 2 page 0000 0000 0000 0000 0000 0000 0093
Then the topical index would look like (the topical index includes the key from the table of contents -- the page number):
Beach 0000 0000 0000 0000 0000 0000 0006
Desert 0000 0000 0000 0000 0000 0000 0020
Suddenly this limits how many columns of the index I can print on a single page.
Both the table of contents and the index are much larger consuming more pages. It also takes more time to search through those pages for my topic.
By going to a skinnier clustered index key it is as though the product group changed the book to be like this:
Chapter 1 page 0001
Chapter 2 page 0093
and the index to this:
Beach 0006
Desert 0020
Suddenly the table of contents and the index take fewer pages and it is faster to leaf through them when searching. So it is with a database table reading the table and its indexes is now much faster.
2) Sequential numbering of the clustered index key
This new column has data populated automatically using the SQL Server identity feature (this feature allows new rows to be added with an incrementing counter). Previously new pages would be inserted in random order and sometimes that mean splitting pages.
So how does this help?
The use of an identity column to increment 1 by 1 as opposed to a randomly generated unique identifier is enormously helpful in writes and will lead to less fragmentation. Whereas prior to the change a new row could be inserted anywhere in the entire table, now it will be inserted at "the end."
