Sitecore SQL Migration to Azure is Blocked

Question

We have an on premises instance of Sitecore 10.1.1 and want to move the databases from SQL Server on VM to Azure SQL Database. We are leaving the Sitecore sites on VM for now.

A migration blocker in the core database has been reported by the Data Migration Analyzer because the stored procedure 'sp_helpuser' is not supported in Azure SQL Database. What needs to be done so the database can be migrated?

Issue Details

IMPACT

Azure SQL Database currently does not support certain system stored procedures that are available in SQL Server. See the "Impacted Objects" section for the specific unsupported procedures that were referenced. Objects referencing unsupported stored procedures will not work correctly after migrating to Azure SQL Database.

RECOMMENDATION

You will need to remove the references to these system stored procedures before migrating to Azure SQL Database.

OBJECT DETAILS

Type: Procedure
Name: dbo.aspnet_Setup_RemoveAllRoleMemebers

Object '[dbo].[aspnet_Setup_RemoveAllRoleMembers]' uses procedure 'sp_helpuser', which is not supported in Azure SQL Database. For more details, please see: Line 15, Column 10.

Answer 1

This error occurs because sp_helpuser stored procedure invoked in the aspnet_Setup_RemoveAllRoleMembers stored procedure is not supported with Azure SQL Database. You could try to comment out the line where it is invoked and manually uncomment it after the migration (it is not supported in Azure SQL, so it is very likely not used by the Sitecore application).

Personally, I would not use the Azure Database Migration tool to migrate the Sitecore databases from an on-prem SQL instance to Azure SQL, but instead I would:

1. Create a bacpac of the databases to migrate.
2. Upload the bacpac files to an Azure blob storage.
3. Restore the Azure SQL databases using the uploaded bacpac files from the storage.
4. Run the Create User SQL scripts (distributed within the WDP Sitecore packages of a release) to set new database users (if needed).

If you are migrating all SQL databases for a Sitecore XP instance, independently of the approach taken, you will also need to update SQL server name references stored in the Xdb.Collection.ShardMapManager database (see this other SSE question for more details).