The launch of the new AADSync Beta 3 has announced that the release of AADSync to RTM might be around the corner. One of the pieces of Best Practice advise that should be taken into consideration is the recommendation that the ADSync database is installed on a separate SQL server. This allows for
- much better management of the database
- increased availability from redundancy in SQL Server
- increased performance from larger more optimized SQL Server environments
- easier upgrades and disaster recovery
The AADSync deployment documentation currently states that in order to install to a separate SQL Server the following procedure is followed:
Open a command prompt, and then go to C:\Program Files\Microsoft Azure AD Connection Tool.
Start the wizard again with the following parameters:DirectorySyncTool.exe /sqlserver localhost /sqlserverinstance InstanceName /serviceAccountDomain AADSync /serviceAccountName AADSyncSvc /serviceAccountPassword VerySecretP@ssw0rd
During various scenarios in my lab it found that I could not get this to work, which made me think if normal ILM / FIM Sync engine principles could help out here. So, could I get to the database and could it be successfully migrated.
How to Migrate the AADSync Database
To give you the short answer – Yes, it can be done. In order to do it you will do:
- Backup of the database from the localDB
- Restore the ADSync database to your selected SQL Server
- Create a SQL Alias for the SQL server (Optional but recommended for easy database portability in future)
- Stop the Microsoft Azure Sync Service
- update the ADSync registry location of the ADSync database
- Start the Microsoft Azure Sync Service
Most of the steps above are straight forward, the tricky ones are explained below:
- Where do I find the ADSync database?
- Azure AD Sync utilizes the SQL 2014 Express engine to store the database. It also creates a separate instance of SQL just for that purpose. The instance can be found at (localDB)\.\ADSync
Make sure you connect with the same accounts that installed the service in order to get to the database.
- Azure AD Sync utilizes the SQL 2014 Express engine to store the database. It also creates a separate instance of SQL just for that purpose. The instance can be found at (localDB)\.\ADSync
- What permissions are required for the ADSync database on the new SQL server?
- In order to grant access to the ADSync database make sure you give the account that runs the “Microsoft Azure AD Sync” the db_owner role mapping. In my case this was the user <domain>\AAD_42dbe4a2a688
- Where do I change the registry to point to the new SQL Server of SQL Alias?
- In a weird sentence… HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ADSync\Parameters
- How do I create a SQL Alias?
- By running the c:\windows\system32\cliconfg.exe utility and selecting Aliases.