As I continue to write my blog series of migrating Dynamics 365/CRM on-premise to online, I was working in my lab environment putting together steps for my next post when I came across an issue that might be faced by others when working on their migration/upgrade projects.
Many production on-premise Dynamics CRM/365 systems will very likely be using SQL Server Enterprise for certain features such as High Availability. However, for the purposes of migration, the various development or “step” environments may be using SQL Standard edition.
If you attempt to restore a copy of your Dynamics CRM database (that was backed up from a SQL Enterprise system) on one of your migration environments running SQL Standard, you may get an error stating that your database has a partition function. This is actually a known issue as outlined in this support article.
One of the resolutions to this would be to configure your step or development images using SQL Enterprise instead of SQL Standard. Since I already created a library of D365/CRM server images, I would rather not have to re-do all that work.
Thankfully the support article provides a script to remove the partitioning from the CRM database. For purposes of an upgrade/migration where you are using the step environments to update the CRM customizations, you will not need this partitioning setup.
A bad idea would be to run this script on your production environment. DO NOT DO THIS! This will affect your current setup and configuration and we want to leave that 100% intact. At very least do this on a restored copy, but leave actual PROD alone.
Azure Dev Test Labs to the Rescue!
Since we don’t want to run scripts against our production environment, so in this case I configured a simple SQL Enterprise server on my Azure Dev Test Labs environment for the purposes of converting this database.
Note that we don’t need to setup an Active Directory or install CRM. We just need to provision a SQL Enterprise server. That’s it. I choose “SQL Server 2012 SP4 Enterprise on Windows Server 2012” to match the SQL version level to my CRM environments. The great thing about Azure is that a ton of base images are available to save you time installing Windows, SQL, etc. This took me about 10 minutes to configure.
Once you have the image, you can fire it up only when needed.
I then restored my CRM database to the SQL Enterprise server. We don’t need to deploy to a CRM installation or anything, just restore and run the script.
After running, you should see the following results;
Now you can back up this fixed database and restore it to your Dynamics CRM/365 “step” environment.
Most of the time spent will just be copying the backup files between environments. For large databases, you may need to consider using OneDrive or other methods to move large files.
While this isn’t a new issue, it wasn’t that long ago where you would need to spend days to setup a bunch of test servers ground up or jump through some hoops to fix your data. You might even bite the bullet and run some potentially risky procedures on a production environment. Thankfully with Azure and Dev Test Labs you can quickly spin up a system and perform tasks and fixes in a matter of minutes in a safe, secure and cost effective environment.
Nick Doelman is a Microsoft Most Valuable Professional (MVP) in Business Applications. Nick will be presenting at D365 Saturday on May 5th. Follow Nick on Twitter at @Readyxrm