Dynamics 365 Business Central: Cloud Migration FAQ (Directions EMEA version)

At Directions EMEA 2024 in Vienna I talked a bit about the Cloud Migration tool during the Microsoft Regional Session Italy and during the Q&A moment (to be honest, at the end of the official Q&A slot as usual ) I’ve received some interesting questions that I think are valuable to be shared to everyone outside of that room.

What’s the minimum SQL Server supported version for migration?

To migrate to Business Central online, the on-premises Business Central instance must have SQL Server 2016 (or later) version, and the database must have compatibility level 130 or higher. 

Compatibility level in SQL Server refers to a database property that determines the syntax and behavior of the database, allowing it to be compatible with earlier versions of SQL Server

SQL Server 2016 (13.x) has compatibility level 130 by default but it also supports compatibility levels 120, 110, 100. The compatibility level for a database is usually lower when the Microsoft SQL Server was upgraded and/or database backups from older Microsoft SQL Server were restored, so I alkways suggest to check your on-premises database.

You can use sys.databases dynamic management view to check the compatibility level of any SQL database. The column named compatibility_level shows the current compatibility level of the database. You can use the following T-SQL query to check the compatibility level of your on-premises database:

  USE master
  GO
  SELECT database_id [Database ID] ,name [Database Name], 
  compatibility_level [Database Compatibility Level],
  state_desc [Database State] 
  FROM sys.databases

Alternatively, you can view the compatibility level of your database by using SQL Server Management Studio. Here go to the Database Properties and select the Options page.

To change the compatibility of your Dynamics 365 Business Central on-premises database (if needed) you can use the following T-SQL query:

ALTER DATABASE [YOUR_DATABASE_NAME] SET COMPATIBILITY_LEVEL = [compatibility_level];

Can I migrate an on-premises database and exceeding the storage space?

Yes. Cloud Migration tool works at the SQL level and you can migrate your on-premises data to the cloud also by exceeding the storage space that you have. Please only remember that if a tenant exceeds the storage limit, Microsoft restricts administrative actions on the environment. Exceeding the storage limit will not interrupt transaction processing within the existing environments.

If I have 3 on-premises database that must be migrated to 3 different tenants, can I use the same Self-Hosted Runtime?

Yes. You can use a single Self-Hosted Runtime instance to migrate N databases to N tenants because each added tenant has a dedicated data pipeline created. If you’re on this scenario, just check that migration processes are not overlapped.

Can I estimate the size of the migrated database before cloud migration?

Dynamics 365 Business Central online uses data compression on Azure SQL, so your on-premises database size is reduced when migrated to the cloud platform.

To have an estimation of the data compression before cloud migration (remember, just an estimation!) you can use the sp_estimate_data_compression_savings system stored procedure.

Here is an example of usage to estimate row compression for a database table:

EXEC sys.sp_estimate_data_compression_savings
     'DATABASENAME', 'TABLENAME', NULL, NULL, 'ROW';
GO

A script to execute an estimation of the data compression applied on the entire Business Central on-premsie database tables is available here.

Must change tracking be enabled on every database tables?

Cloud Migration uses change tracking to identify what data should be moved between subsequent runs and it’s automatically enabled.

SQL change tracking must be enabled at the database level, with the retention period set to 2 days minimum (longer periods are recommended if you expect to do several replication runs and want them to be incremental). To do that (if you have errors like Change tracking is not enabled…), you can use the following T-SQL:

ALTER DATABASE [YOUR DATABASE] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

You don’t have to enable change tracking on every SQL table, because this will be done automatically during replication.

Are Tenant Media migrated?

Yes, Cloud Migration works also with Tenant Media table, but here is where I suggest to check things before the migration.

Tenant Media content larger that 25 Mb could have problems on migration, expecially if you start the migtation from SQL on-premise. As said during the session, Cloud Migration should be always started from Azure SQL. Said that, I suggest to check your Tenant Media table’s content size with this query before migration:

SELECT ID, DATALENGTH(Content) / 1048576.0 AS SizeInMB
FROM [Tenant Media]
ORDER BY SizeMB DESC

This query helps on identifying possible problems with files.

You said that starting Cloud Migration from Azure SQL is the recommended path. But how to move a large on-prem SQL database to Azure SQL?

The easiest way to deploy a local database to Azure SQL can be done via SSMS by right-clicking on the database, choosing Tasks and then Deploy Database to Microsoft Azure SQL. But I honestly don’t recommend this approach if you have a quite large database.

With a large database, it’s better to upload the bacpac file to Azure Blob Storage and then restore the bacpac to a new Azure SQL database from here:

az sql db import --admin-password $password --admin-user $login --storage-key $key --storage-key-type StorageAccessKey --storage-uri https://$storage.blob.core.windows.net/$container/$bacpac --name $database --resource-group $resourceGroup --server $server

For very large databases, you can also use a Microsoft tool called SQLPackage for importing the bacpac file starting from an Azure VM:

sqlpackage.exe /a:import /tcs:"Data Source=[ServerName].database.windows.net;Initial Catalog=[DatabaseName];User Id=[UserName];Password=[Password]" /sf:E:BacPacDirBacPacFile.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P11

where DatabaseEdition and DatabaseServiceObjective are parameters related to your Azure SQL database instance.

I want to close this special FAQ post with what I’ve emphasized during the session in Vienna (tatoo that in your mind):

On Business Central version 26 Microsoft will clean up ALL schemas that have been marked as 𝘖𝘣𝘴𝘰𝘭𝘦𝘵𝘦𝘚𝘵𝘢𝘵𝘦 = 𝘙𝘦𝘮𝘰𝘷𝘦𝘥 since v14. This means that data upgrade or cloud migration to version 26 or higher will have to be done via an intermediate step to version 25 (previous versions will result in an incompatible schema).

Dynamics 365 Business Central version 25 is a milestone for cloud migration and you cannot skip it. All 𝘖𝘣𝘴𝘰𝘭𝘦𝘵𝘦𝘚𝘵𝘢𝘵𝘦 = 𝘙𝘦𝘮𝘰𝘷𝘦𝘥 Microsoft’s fields will then be regularly cleaned up with a cadence of once every 5 major releases.

Original Post https://demiliani.com/2024/11/12/dynamics-365-business-central-cloud-migration-faq-directions-emea-version/