Run the Cloud Migration tool
Data migration is the process of securely migrating data from an on-premises SQL Server instance (or Azure SQL) to Business Central online. The process uses the Azure Data Factory (ADF) to migrate the data between databases directly, meaning it doesn't look at any permissions within the applications you're transferring data between, only SQL permissions.
Data is migrated table by table, and success and failures are tracked for each table. If a table fails to migrate, the error will be captured, and the migration moves on to the next table until completed. Tables fail to migrate if they can't be found, or if the schema doesn't match between the cloud and the on-premises tables.
The initial data migration time can vary depending on factors such as the amount of data to migrate, your SQL Server configuration, and your connection speeds. The initial migration takes the longest amount of time to complete because all data is migrating. After the initial migration, only changes in data will be migrated, so each iteration runs more quickly. You don't need to run the migration process more than once if you don't want to. However, if you're running the migration while users are still using the on-premises system, you must run at least one more migration to ensure all data was moved to the cloud before you start transacting in Business Central online.
Run the assisted setup guide
To migrate data, in the target company in Business Central online, run the Cloud Migration Setup assisted setup wizard.
You must be signed in as an administrator of the Microsoft 365 tenant as well as Business Central online.
If the user running this flow is a delegated administrator, they must receive approval from a licensed user with either the Essentials or the Premium license and SUPER permissions to run the cloud migration. In this case, the Data Migration Setup guide will display an additional step, where the delegated administrator can copy the autogenerated approval link and send it to the licensed user for approval. Once the licensed user has approved the request, the delegated administrator can continue with the setup of the cloud migration and perform all other steps required to complete that process. The licensed user can always revoke the permission to run the migration by selecting the same approval link that was shared by the delegated administrator, or from the Cloud Migration Approval page.
We recommend that you start the migration by running the assisted setup from a company other than the company that you are migrating data to. For example, sign into the demonstration company, CRONUS, and start the process there. This way, you can make sure that all users are logged out of the original company and the target company. This is especially important when you migrate from Business Central on-premises because you can run the migration tool multiple times.
Once the wizard is complete and data migration is activated, an initial data migration will happen at the scheduled time. Alternatively, you can trigger the data migration process manually.
Select the product that you want to migrate data from:
If the product you selected requires a SQL connection, this page is presented:
This page will display the connection information based on the product that you specified in the previous page. This is defined from the installed extensions for the product you have selected. The following list provides more details about the fields in this part of the assisted setup guide.
SQL Connection - Specify SQL Server for a locally installed SQL Server instance, or Azure SQL.
SQL Connection string - You must specify the connection string to your SQL Server, including the name of the server that SQL Server is running on, and the name of the instance, the database, and the relevant user account.
For example,
Server=MyServer\BCDEMO;Database=BC170;UID=MySQLAccount;PWD=MyPassWord;, if you're migrating from Business Central on-premises, version 17.The following snippets illustrate a couple of connection strings with different formats:
Server={Server Name\Instance Name};Initial Catalog={Database Name};UserID={SQL Authenticated UserName};Password={SQL Authenticated Password};
Server={Server Name\Instance Name};Database={Database Name};User Id={SQL Server Authenticated UserName};Password={SQL Server Authenticated Password};
The SQL connection string is passed to Azure Data Factory (ADF), where it is encrypted and delivered to your Self-Hosted Integration Runtime and used to communicate with your SQL Server instance during the data migration process.
Integration runtime name - If your SQL connection is SQL Server, you must specify the runtime service that will be used to replicate the data from the defined source to Business Central online. The integration runtime must be running on the machine that holds the SQL Server database. If you don't already have a runtime service, leave the field empty, and then select the Next button.
If you leave the Integration runtime name field empty, a new page appears from where you can download the self-hosted integration runtime that you must install. Follow the instructions on the page. Hover over a field to read a short description.
Once you choose Next, a new pipeline will be created in the Azure service. This takes less than a minute to complete, in most cases.
Next, install, if required, the integration runtime service on your Business Central on premises database server.
If you want to test your SQL string, launch the Microsoft Integration Runtime Configuration Manager, and then select the Diagnostics menu option. From there, you can test to see if the connection is good.
The Diagnostics menu option displays diagnostic tools and information and has a Test button at the bottom of the screen.
Once you have set up this configuration, you can manage your cloud environment and data migration from the Cloud Migration Management page in Business Central online. First, select a company or companies to migrate.
Select Next and you'll move to the Cloud Migration Management page.
The page provides a view of the status of all migration runs. You can view the time the migration ran and the status of each migration. If you have set up a schedule, you can also see when the next migration is scheduled to run. The Migration Information tiles show the number of migrated tables and the number of tables that didn't migrate due to warnings or errors. Select a tile to drill into more details and guidance to correct any errors.
There is also a tile that shows tables that aren't migrated due to problems with the data. For example, tables with permissions aren't migrated from on-premises solutions because permissions work differently between online and on-premises.
The actions that you can run from the Cloud Migration Management page are:
Manage Schedule - Opens a page where you can set the migration schedule without having to run the assisted setup wizard again.
Run Migration Now - Select this action to start the data migration manually. A manual run can be helpful if you received errors in the scheduled data migration, you corrected the errors, and now want to push updated data to the cloud outside of a normally scheduled run. The migration can also be used for subsequent runs after the initial migration. On subsequent runs, the migration tool will only migrate changes that have happened since the previous migration was run. Change tracking is used to identify what data should be moved in those subsequent runs. However, the migration tool can't run if the target environment is being upgraded. In that case, you must disable cloud migration, upgrade, and then set up cloud migration again.
Run Data Upgrade Now - Select this action to upgrade data, such as if you're migrating data from an earlier version to the latest version of Business Central.
Refresh Status - If a migration run is in progress, you can select to refresh the status to update the page. If the run is complete, the status will update using the refresh status action without having to close the window and reopen it.
Reset Cloud Data - You may run into instances where you need to reset your cloud data. This option will clear all data in your cloud tenant and enable you to start over with data migration. Only run this process if you want to start the migration process all over from the beginning. If you need to clear data in your cloud tenant, and you have connectivity issues that persist for more than 7 days, you must contact customer support. They will create a ticket to have your tenant data cleared. Only run this process if you want to start the data migration all over and bring all data from on-premises to your cloud tenant.
Get Runtime Service Key - Returns the existing runtime key.
Reset Runtime Service Key - If at any time you suspect that your Self-Hosted Integration Runtime Key is no longer secure, you can select this option to regenerate a new key. A new key will be generated for you and automatically be updated in the Self-Host Integration Runtime service.
Disable Cloud Migration - Opens a guide that helps you through a checklist of instructions to disable the cloud migration configuration. Use the guide when you have migrated the data that you want to migrate, or when you want to upgrade the target environment. Once the steps in this process are complete, you can use your Business Central online tenant as your primary solution, or you can upgrade the environment.
Check for Update - If there have been changes to the migration service, the new service will be published. This action will check to see if a new service has been published. The check will display the version of the service you are currently running and then also display the latest service published. Then, you can select to update your solution. It's recommended that you update the solution if a newer version has been published.
Select Companies to Migrate - If your database contains more than one company, use this action to specify which company or companies to schedule a migration run for. For example, you might use this when you're migrating a very large database with multiple companies, so you might break down the migration into several runs by including one or a few companies in each migration run. You can also see the estimated size of each company.
Define User Mappings - This option is available when you log in to a particular company that has been migrated. This action should be done in one of the companies you've migrated. This action gives you a list of the users that were in your on-premises environment, and then gives you a list of your Microsoft 365 users, so that you can map the two together. This process renames the Name field on the User Card to match the user name in your on-premises solution. It isn't a required step, but if you use some of the processes in Business Central that work with the user name, such as time sheets, you may want to map users. Time sheets are visible based on the user name you are logged in like Business Central.
Setup Checklist - When you're ready to use your Business Central online tenant as your main system, the tables that were not migrated must be set up or defined as needed. The checklist page shows recommended steps to complete your migration to the cloud.
Azure Data Lake - This option is only available if the Business Central online tenant is connected to Dynamics GP.
There are some scenarios where it will be necessary for you to run the cloud migration setup wizard more than once.
The following list highlights a few examples:
Multiple companies in Business Central on-premises - One example is if you want to add additional companies to the migration, or if you want to change the companies to migrate, run the assisted setup wizard again. Alternatively, choose the additional companies in the Cloud Migration Management page.
Add tenants to an existing runtime service - If you're a hosting partner, you may have multiple tenants running on the same integration runtime service. Each tenant will be isolated in their own data pipeline. To add tenants to an existing integration runtime service, enter the name of the existing integration runtime service into this field. The integration runtime name can be found in the Microsoft Integration Runtime Manager.
In both examples, you'll be making updates to an existing runtime service. When you get to the point of the wizard where you can specify an existing runtime services name, open Microsoft Integration Runtime Service Manager and enter the runtime name in the field in the wizard; you won't be allowed to copy or paste. The runtime service will identify that you are making updates to an existing service and won't create a new one.
Complete the steps in the wizard to update the runtime service. If the change was related to adding tenants to an existing service, a new data pipeline will be created for that tenant. Changing your migration schedule or regenerating an Azure Data Factory (ADF) key may be done using the Cloud Migration Management page in Business Central.
Business Central permission sets
Specifically for migration from Business Central on-premises, there is a limit on the amount of data that you can enter in Business Central online to data that isn't migrated. Otherwise, any data that was written to the tenant database would be continuously overwritten during the migration process.
To make setting up this read-only tenant more efficient, the Intelligent Cloud user group and the Intelligent Cloud permission set were created. Once the cloud migration environment is configured, all users without SUPER permissions will be automatically assigned to the Intelligent Cloud user group. Only users with SUPER permissions will be allowed to make modifications to the system at this point.
Before you configure a connection from on-premises to Business Central online, make sure that at least one user in each company is assigned SUPER permissions.
Users that are reassigned to the Intelligent Cloud user group will have access to read ALL data by default. If you need to further restrict what data a user should be able to read, the SUPER user may create new user groups and permissions sets and assign users accordingly. It's highly recommended to create any new permissions sets from a copy of the Intelligent Cloud permission set and then take away permissions you don't want users to have.
If you grant insert, modify or delete permissions to any resource in the application that was set to read-only, it could have a negative impact on the data in Business Central online. If this occurs, you may have to clear all your data, and then redo a full migration to correct this.
Company initialization
When a company is created in Business Central, no one can access it until it has been initialized. If you are familiar with Dynamics NAV, then you are used to this step happening automatically during the upgrade process, for example. But it's not quite the same with Business Central. When a migration run completes, you will be prompted to view a list of non-initialized companies so that you can start the initialization.
You can select to mark a company as already initialized, such as if it was initialized in an earlier migration run. Technically, the initialization runs as a scheduled task in the job queue, and the status is automatically updated in the list of companies when a task completes.
When you schedule an initialization in the Hybrid Companies List, then you can't make any modifications to the company until the initialization task completes.
End the migration
Once you have migrated the data that you want to migrate to Business Central online, you end the migration by disabling cloud migration in the Cloud Migration Setup page. This is an important step, because each time someone runs the migration, outstanding documents for vendors and customers, general ledger account numbers, inventory items, and any other changes made in the target company in Business Central online are overwritten.
The amount of time the migration will take to complete depends on the amount of data, your SQL configuration, and your connection speed. Subsequent migrations will complete more quickly because only changed data is migrating.
If you set up cloud migration for an environment, the environment can't be upgraded. If you want to upgrade the environment, you must disable cloud migration. If you want to move more companies, set up cloud migration again once the upgrade is complete. By separating upgrade from cloud migration, we removed the risk of potentially corrupting data if the upgrade touches tables with records in both migrated and non-migrated companies. Upgrade the target environment first, then migrate.
Include or exclude tables from a cloud migration
In 2023 release wave 2 or later, it's easier to customize your data migration to the cloud to fit your needs. You can select which tables to include or exclude from the cloud migration, as long as the table is accessible by AL code and not marked as internal or on-premises only. This feature is useful for customers who have specific requirements for their data migration, such as:
You want to migrate only a subset of your data to the cloud to reduce the size or complexity of your cloud environment.
You want to include additional data that's not part of the default cloud migration, such as change log data.
You want to overwrite an entire table or only use a delta sync to replicate only new and changed data.
To use this feature, go to the Cloud Migration Setup page. In the Actions menu of the page, you'll find an action called Include/Exclude Tables. This action opens a new page where you can view all the tables that are available for cloud migration. For each table, you can select whether to include or exclude it from the cloud migration. You can also specify whether to preserve or replace the existing data in the cloud for each table. This feature has the following limitations and restrictions:
You can't change the behavior on system tables, like Object Metadata, Session Event, or User Personalization.
You can't include tables that are marked with the on-premises scope, such as Company Information or User Setup.
You can only include tables on which it's possible to write a per-tenant extension targeting a cloud environment.