Share via

FIM/ILM: How to move the backend SQL Server Synchronization Service Database

  • Article

Applies To

  • Microsoft Identity Integration Server 2003
  • Microsoft Identity Integration Feature Pack
  • Microsoft Identity Lifecycle Manager 2007 Feature Pack 1
  • Microsoft Forefront Identity Manager 2010  

Overview

A need may arise to move the backend SQL Server database for the FIM Synchronization Service.  One good example would be the need to split the FIM backend databases from one SQL Server instance to two separate SQL Server instances, or simply just moving the FIM Synchronization Service Database to another SQL Server.

Previously, we were not able to do this without uninstalling and reinstalling the Synchronization Service.  Now we can simply update a registry key and ensure that the Synchronization Service Account has permissions. 

Goal

Our goal is to be able to move the backend FIM Synchronization Service Database and be able to start the Forefront Identity Manager Synchronization Service, as well as launch the Synchronization Service Manager Console to be able to start working again. 

Steps to move the backend database

  1. Stop any scheduled tasks that are supposed to run in task scheduler

    • If you have automated your run profiles to have your synchronization cycles run based on a schedule in task scheduler, ensure that they have been disabled.  If they are not, you will cause errors, and possibly cause data corruption.

  2. Ensure that you have no runs running

    • Ensure that all Management Agents have a status of Idle, and that on the Operations tab that there is nothing running currently.

  3. Execute a Full Backup of the current FIM Synchronization Service Database

    • This is done via SQL Server Management Studio

  4. Stop the Forefront Identity Manager Synchronization Service

    • Open the Services Management Console under Administrative Tools
    • Locate the Forefront Identity Manager Synchronization Service
    • Double click to open the Properties
    • Click the Stop button to stop the service

  5. Stop the FIM Service

    • Go to the machine that the FIM Service is installed.
    • Open the Services Management Console under Administrative Tools
    • Locate the Forefront Identity Manager Service
    • Double click to open the Properties
    • Click the Stop button to stop the service

  6. Copy the FIM Synchronization Service Database to the new SQL Server

  7. Ensure that the SQL Server version of the new SQL Server is the same as the old SQL Server

    • Open SQL Server Management Studio and connect to the SQL Server
    • Click the New Query button in the upper left
    • Type Select @@version
    • Click the Execute Button
    • Results will be shown at the bottom

  8. Restore the FIM Synchronization Service Database on the new SQL Server

    • This is done via SQL Server Management Studio

  9. Create a SQL Server Login for the FIM Synchronization Service

    ***NOTE: Here is a **Microsoft Knowledge Base Article that uses a SQL Script to move the account and passwords*

  10. SQL Management Studio

    • Open SQL Server Management Studio and login to the SQL Server that is hosting the FIM Synchronization Service Database

    • Select Logins and right mouse click

    • Select New Login from the Context Menu

       

    • Enter the domain name \ synchronization service account name in the Login Name text box, and choose Windows Authentication

       

    • Select Server Roles, and ensure that Public is checked

    • Select User Mapping, and ensure that the FIMSynchronizationService Database is selected and that the FIM Synchronization Service Account has public and db_owner permissions checked.

       

    • Select Status

      1. Permissions to connect to database engine should be Grant
      2. Permissions to Login should be Enabled
      3. Click the Ok button

  11. Open the Windows Registry

    • Click the Start Button and then Run and type regedit and then click the Ok button
    • Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\FIMSynchronizationService\Parameters
      1. Server Property should equal the name of the new SQL Server
        1. Should be the MACHINE Name of the SQL Server that is hosting the FIM Synchronization Service backend database
        2. SQLInstance Property should equal the name of the instance

    *NOTE* if it is the default instance, then it should be blank

  12. Start the FIM Services

    • Follow the steps outlined in Step 4 and 5.  Replace stop with start.

  13. Open the FIM Synchronization Service Manager Console, and ensure that you see your Run History on the Operations Tab, and the Management Agents appear on the Management Agents.  

SEE ALSO