This article is about a task that has been driving me crazy kept me busy for quite a while: I was supposed to move the SQL databases in Microsoft Dynamics 365 from an old Microsoft SQL Server to a newer one.
As I have only been able to find small fragments of this topic on the internet so far, I have summarised the whole process for an SQL server migration to Microsoft Dynamics 365 here for you:
Wrong approach: Import as a new organization from the new SQL server
First, I tried to copy the databases to the new SQL server and then import them into Microsoft Dynamics 365 as a new organization. This was not only complicated (you have to enter a new name for the organization, where is the Reporting Server again, which we don’t actually use, error messages for user assignment, …), but also failed in the end. The new SQL server did not appear in the Microsoft Dynamics 365 Deployment Manager, nor did Dynamics subsequently use the MSCRM_CONFIG database on the new SQL server.
Working solution
In the end, the following procedure turned out to be the solution that worked.
But first of all, I have to point out that I am not an expert on this topic and cannot guarantee that it will work just as well and without any problems for you. Please always make backups of all data and systems involved and, if in doubt, ask an expert or service provider.
Step 1: Copy databases to the new SQL server
If you have not already done so, you should deactivate all organizations in the Microsoft Dynamics 365 Deployment Manager so that no one can work in them and change data. Then back up the SQL databases (right-click on the database > “Tasks” > “Back Up …”: Select “Copy-only backup” and specify the desired path for the backup file below) and restore them on the new SQL server (right-click on “Databases” > “Restore Database…”: Select “Device” > … > Specify backup file).
My Dynamics instance had the following databases:
- “MSCRM_CONFIG”: It contains the general Microsoft Dynamics 365 configuration.
- “[ORGNAME]_MSCRM”: A database for each organization which contains the actual content.
- A database for the Reporting Service with a name that you chose when setting up Dynamics 365. Even though we don’t use Reporting Services for Dynamics knowingly, I have also transferred this database as Dynamics ask for it sometimes (e.g. when updating Dynamics). You can identify this database by the fact that it is only accessed when the Microsoft Dynamics 365 server is switched on. You can also find its name in the “Configuration Manager for Reporting Services” (Step 5).
We also had a periodic cleanup job under “SQL Server Agent”, which had probably been created by a service provider to solve a problem, that I don’t know. So, I also saved this job (right-click on the job > “Script Job as” > “CREATE To” > “File …”) and restored it on the new server (simply open the SQL file you just created in SQL Server Manager on the new SQL server and execute it).
Step 2: Add necessary login permissions to the SQL Server
Should there be any problems in the next steps, the Microsoft Dynamics 365 environment (i.e. the service/user accounts involved) might not yet have the access rights to the new SQL server. As far as I understand, the reason for this is that when restoring SQL databases (Step 1) does only restore the access rights inside the databases but not to the SQL server itself. That’s why I recommend you to check which (service) accounts have access to the databases (right-click on the database > “Properties” > “Permissions”).
Note down the accounts mentioned there and then, on the old SQL server, look in the SQL Server Manager under “Security” > “Logins” to see which of these accounts appear there and what permissions they have (right-click on the account > “Properties”). Now add these accounts in the same place on the new SQL server (right-click on “Logins” > “New Login”) and create them with the same rights and properties as on the old SQL server.
At this point, you’re through the part that you probably would have figured out without doing much research. As for the next steps, I found almost nothing on the internet, which is why I didn’t figure them out for a long time. But in fact, they can be done in just a few minutes:
Step 3: Manually replace the SQL server name in the registry
What I was not aware of: The exact location of the database “MSCRM_CONFIG” and thus the central configuration is stored in the Windows registry on the Microsoft Dynamics 365 server. Therefore, open the registry (right-click on Start > „Run“, type in “regedit” > Enter) and navigate to the key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM”. There should be an entry called “configdb” in which you simply replace the old SQL server name with the new one.
Step 4: Change SQL server name in the “MSCRM_CONFIG“ database
Next, on the new SQL server in the database “MSCRM_CONFIG” under “Tables”, right-click the table “dbo.Organization” and click “Edit Top 200 Rows”. Again, replace the old SQL server name with the new one everywhere. Do the same in the table „dbo.Server“.
Restart the Microsoft Dynamics 365 server, even though I’m not sure whether this is really necessary at this point. But in IT you can never restart too often. 😉
Step 5: Change SQL Server inside the Reporting Services
Finally, you need to change the SQL server of/inside the Reporting Services. To do this, you need to open the “Configuration Manager for Reporting Services”. In my case, this was on the Microsoft Dynamics 365 server itself, but theoretically it could be that a different server was specified when your Dynamics was installed. Simply check the servers in question and see if the “Configuration Manager for Reporting Services” is available there and if it displays the report server database name (the one you moved in Step 1) when you open it.
Now navigate to “Database” > “Change database” > “Select existing report server database”, enter the new SQL server name and click through the process. In my case, the correct values were already entered everywhere and in the end the database was simply changed without any error messages or warnings.
Step 6: Restart and a few last checks
Now restart the Microsoft Dynamics 365 server, activate your organizations and check whether your Dynamics is running properly again. I had no problems here. Don’t be surprised, it can be a little slow at first, as certain caches probably have to be recreated.
Also check whether the Dynamics Deployment Manager on the Microsoft Dynamics 365 server actually shows the right (new) SQL server.
Please also take the databases on the old SQL server offline (right-click on the database > Tasks > Take Offline). If any application or service is still using the old databases, you will see this here. In this case, you should check again whether you (or I) have forgotten anything.
And that was it. Your Microsoft Dynamics 365 should now use the your new SQL server. 😊