While adding a new attribute to our FIM Portal Schema I seemed to have stumbled upon a new issue. I could create the attribute and the binding just fine. The logical next step would be to alter the Synchronization Engine MPR regarding its access to the new attribute.  However my new attribute was nowhere to be found. Back to the schema administration then! I searched it and indeed couldn’t find it. Tried navigating to where it alphabetically was supposed to be and it was there.. Well that’s odd… I tried searching an other attribute I created weeks ago and that worked out just fine…

When we help a customer with an issue like this the first question would be: “What did you change?” Then they’d say “Nothing”. However I have to admit, “Nothing” is definitely not the case here… I do recall moving the FIM databases to another server myself… yesterday... In fact we moved from a standalone SQL server to the newly designed SQL failover cluster.

Having a SQL expert around I figured I’d get a head start moving everything swiftly by asking some advise. I’m aware that moving databases to a new server also includes pre-creating the associated logins and the FIM installed SQL jobs had to be transferred somehow as well! I got pointed to “SQL Server Business Intelligence Development Studio” and got some guidance. My migration solution looked like this:

Now that looks like a sexy solution! Just drag and drop, fill in some source/target information, check some checkboxes and we were good to go. This mini-project would first transfer the logins, then copy the selected databases, leaving the old one in place and finally migrated all Jobs. All I had to do was enable the right jobs and all was fine.

Obviously we stopped all FIM related services during the transition. After moving the database to it’s new location we updated the following registry key:

• key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\FIMService\
• Value: DatabaseServer

And started the FIMService again. The portal seemed to act just fine. Users were in it, I could provision new users, search users,… All in all this seemed a smooth transition! Time for a coffee.

Because our portal behaved nicely, and in fact we didn’t got any FIM errors, besides no search results for the new attributes, we started thinking SQL might be the culprit. Using SQL profiler we captured the query used by FIM to search an attribute. We searched twice: once for a findable attribute, once for a new one. The query was approximately 700! lines… Now that’s a giant haystack…

We pointed our FIM Portal to the “old” database and tried to reproduce our issue. No problem there, so we definitely started blaming our new shiny SQL. Perhaps a recent SQL update or so… A colleague of mine, who knows SQL a lot better than me, suggested me to compare the standalone versus the failover cluster SQL server settings. He provided me the following commands:

• sp_configure 'show advanced options',1
• reconfigure
• sp_configure

The first one will make sure the advanced options are shown also, the second command will configure this option while the sp_configure will finally provide an overview of all settings. We then exported the results to excel and compared both SQL server settings. All seemed to be equally apart from some new options on the cluster.

After comparing the SQL server settings we went on by comparing the database settings of the FIMService database both on the working SQL and on the non working SQL (cluster). We issued the following commands:

• select * from sys.databases where name = 'FIMservice'

Again we go a lot of values and exported this information to Excel, using some Excel Foo (If(Column1=Column2),True,False)) we finally found a difference! The is_fulltext_enabled value was 1 on the working SQL but 0 on our cluster. To display this value:

• select is_fulltext_enabled,name from sys.databases

The command on our standalone SQL showed the following:

Versus the SQL cluster:

Now everywhere we looked online it said that since SQL 2008 the Full-Text Indexing was enabled by default and didn’t need to be altered (Technet: How to: Enable a Database for Full-Text Indexing (SQL Server Management Studio). Well our databases hadn’t! Creating a new database on the cluster indeed showed up with Full-Text Indexing enabled. We then tried to move the FIM Service database using a SQL backup restore from the standalone to the cluster and that showed up as Full-Text Indexing enabled too! Damn my swift my migration tool! So we attached our “faulty” database again and searched the Full Text Catalogs folder. It was there, and it actually had stuff in there which related to the query we gathered at the very beginning. This started looking good!

Right-clicking the database and viewing the Files section it showed us that Full-Text Indexing was enabled for the database. It was checked and grayed out…

So back to the command line stuff to enable the Full-Text Indexing:

• use FIMService
• exec sp_fulltext_database 'enable'

And a screenshot:

 

 

And finally our attributes get found again. Now this was a fun day!
Thanks Jeroen for your SQL knowledge and courage troubleshooting this with me :)

 

Thomas ‘I’ll never move a database again’ Vuylsteke