How to Import CRM Database to SQL Azure for Analysis Purposes
by Toby MacAlister, Senior Platform Engineer //
This article details the steps required to import a Dynamics CRM on-prem organisation database to SQL Azure. Essentially SQL Azure is a different product to On-Premises SQL Server. A database BAK backup file cannot be directly restored to SQL Azure (currently – this caveat is required as the features in Azure are constantly evolving). Instead SQL Azure supports importing of bacpac files as a new database instance.
The example I’m working with is a Dynamics CRM 2016 on-prem instance with an out of the box configuration running on SQL 2014 SP2 (12.0.5000.0).
To preserve my initial CRM database, the first step is to create a second copy of the database that is not being used by the CRM so we can make changes required to the database without affecting the actual CRM used version.
Above is a copied version of the original CRM database for testorg. This can be done using copy database or doing a backup and restore.
Before going any further you’ll need to make sure you have an Azure SQL Server instance available to attempt to import to.
Using the new copied database you could attempt to use the task action Deploy Database to Windows Azure SQL Database directly(shown below)
Ensure that the service tier selected is sufficient to allow the required database size and provides enough DTU’s to perform the unpacking operation efficiently (note this can be resized easily after the import is complete if required to reduce costs).
But you’ll find you may encounter the following errors:
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71562: Error validating element [dbo].[p_GetDbSize]: Procedure: [dbo].[p_GetDbSize] has an unresolved reference to object [master].[dbo].[spt_values]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[p_GetDbSize]: Procedure: [dbo].[p_GetDbSize] has an unresolved reference to object [master].[dbo].[spt_values].[low]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[p_GetDbSize]: Procedure: [dbo].[p_GetDbSize] has an unresolved reference to object [master].[dbo].[spt_values].[type]. External references are not supported when creating a package from this platform.
Error SQL71562: Error validating element [dbo].[p_GetDbSize]: Procedure: [dbo].[p_GetDbSize] has an unresolved reference to object [master].[dbo].[spt_values].[number]. External references are not supported when creating a package from this platform.
Error SQL71564: The element User: [TM\SQLAccessGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element User: [TM\ReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element User: [TM\PrivReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element User: [NT AUTHORITY\NETWORK SERVICE] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Login: [NT AUTHORITY\NETWORK SERVICE] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Login: [TM\SQLAccessGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Login: [TM\PrivReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Login: [TM\ReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Full-text Catalog: [CRMFullTextCatalog] is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Full-text Index on [dbo].[DocumentIndex] is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Full-text Index on [dbo].[BusinessDataLocalizedLabelBase] is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element SqlFullTextIndexColumnSpecifier: <unnamed> has property LanguageId set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element SqlFullTextIndexColumnSpecifier: <unnamed> has property LanguageId set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element SqlFullTextIndexColumnSpecifier: <unnamed> has property LanguageId set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element SqlFullTextIndexColumnSpecifier: <unnamed> has property LanguageId set to a value that is not supported in Microsoft Azure SQL Database v12.
These errors are due to features on the CRM database that are unsupported in bacpac files and SQL Azure databases. This serves to highlight the fact the SQL Azure is a different product to SQL Server on-prem.
To overcome these errors you need to drop incompatible objects from the SQL Server database by running the commands below against the copy of the CRM database [ensure you do not run these commands against the actual CRM database]:
Please note that the GUIDs for your user and schema objects may be different and the commands should be updated accordingly.
drop procedure p_getdbsize
drop schema [NT AUTHORITY\NETWORK SERVICE]
drop schema [TM\PrivReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop schema [TM\SQLAccessGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop schema [TM\ReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [TM\PrivReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [TM\ReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [TM\SQLAccessGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [NT AUTHORITY\NETWORK SERVICE]
drop fulltext index on documentindex
drop fulltext index on businessdatalocalizedlabelbase
drop fulltext catalog CRMFullTextCatalog
Further errors are then encountered and in addition the following objects need to be dropped:
drop function fn_ConvertUtcToSpecificTimeCLR
drop assembly MSCRMSqlClr
drop procedure p_ReindexAll
Once the incompatible objects have been removed from the copied database, you can retry the Deploy Database to Windows Azure SQL Database operation, but if you do this with SQL Server Management Studio 2014, you will find the bacpac file gets created in the temporary location successfully, but there is the following errors trying to import the bacpac file as a SQL Azure database.
This occurs when testing using SQL Server Management studio 2014. To overcome this issue, install a later version of SQL Server Management Studio (I used v17.9.1).
This time the Deploy Database to Windows Azure SQL Database operation completes successfully.
SOLUTION
In a nutshell (step by step) Solution:
Use a restored copy of the on-Premise CRM database that you can make changes to.
Use a later version of SQL Server Management Studio (e.g. 17.9.1) to connect to the database, note the database can still be an earlier version of SQL.
Run the following statements against the database to drop incompatible objects(replace [domain] with actual object domain):
drop procedure p_getdbsize
drop schema [NT AUTHORITY\NETWORK SERVICE]
drop schema [[domain]\PrivReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop schema [[domain]\SQLAccessGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop schema [[domain]\ReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [[domain]\PrivReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [[domain]\ReportingGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [[domain]\SQLAccessGroup {004834e2-9217-433c-bce2-73ac7f02e3d1}]
drop user [NT AUTHORITY\NETWORK SERVICE]
drop fulltext index on documentindex
drop fulltext index on businessdatalocalizedlabelbase
drop fulltext catalog CRMFullTextCatalog
drop function fn_ConvertUtcToSpecificTimeCLR
drop assembly MSCRMSqlClr
drop procedure p_ReindexAll
From the database, right click and expand tasks, then select Deploy Database to Windows Azure SQL Database.
In the wizard, connect to the SQL Azure Server you wish to import the database to and ensure that the tier and size of the database are sufficient to deploy to database to and ensure that the location specified to create the temporary bacpac file has enough space:
Click on Finish and wait for the deployment to complete (hopefully with success).