Migrate On-Premises Data to Business Central Online

Organizations that run their workloads on-premises but are looking to move to the cloud can easily migrate to Business Central online. By moving to the cloud, users get the benefits of cloud scenarios such as Machine Learning, Power BI, Power Automate, and others to drive suggested actions.

If you're not already familiar with Business Central online, take a look here.

Supported migration paths

You can use the built-in migration tools to migrate data to Business Central online from specific product versions. The on-premises solution must use SQL Server 2016 or a later version, and the database must have compatibility level 130 or higher. Currently, you can migrate to Business Central online from the following products:

• Dynamics GP 2015 and later versions

• Business Central on-premises

  Currently, you can migrate to Business Central online from the earlier versions 14 through 20. For an indication of compatibility across minor versions, see here. You can also switch to online from the current version (21).

Tip

If you are currently on a version of Dynamics NAV, you must upgrade to Business Central on-premises, and then switch to Business Central online. For more information, see Upgrading from Dynamics NAV to Business Central online.

If a customer is coming from another product, you can use configuration packages and templates to migrate their data to Business Central on-premises, and then use the cloud migration tool to switch to Business Central online. For more information, see Set Up Company Configuration Packages.

Tip

Customers coming from QuickBooks can use apps from Microsoft to move to Business Central online. For more information, see Changing from a QuickBooks App to Dynamics 365 Business Central.

Prerequisites

• The customer must have a Business Central online tenant

• The person who runs the migration must be signed in as an administrator of the Microsoft 365 tenant and Business Central online

• The on-premises solution must be on the list of supported migration paths

  • If the solution is based on an older version of the on-premises product, upgrade to the latest supported version

• The on-premises database must be ready

  • The on-premises solution must use SQL Server 2016 SP1 or later

  • The database must have compatibility level 130 or higher

  • Update statistics and reorganize indexes on all tables on the source database

    This will ensure that the migration runs as fast as possible. For more information, see the documentation for sp_updatestats (Transact-SQL) and Resolve index fragmentation by reorganizing or rebuilding indexes.

• At least one user has SUPER permissions in the target company in Business Central online.

  This is the only user who can make changes in Business Central online. All users that don't have SUPER permissions will be automatically reassigned to the intelligent cloud user group. This will limit them to read-only access to data in Business Central online. For more information, se the Business Central permission sets section.

• Consider reducing the amount of data that you migrate.

  You can migrate the data that you want to take with you to the cloud. If you need more storage than the default 80 GB, you can buy additional environments or additional storage capacity. We recommend that you consider reducing the amount of data that you migrate so that it is less than 30 GB in each migration run. For example, reduce the number of companies that you are migrating data for, or delete outdated data in tables that contain log entries and archived records. Also, review how you can manage database capacity in a Business Central online environment.

  You can specify which companies to include in the migration in the assisted setup guide, and you can view the migration status of each company in the Cloud Migration Management page.

  If you want to add more companies after the first selection of companies, you can add more companies in the Cloud Migration Management page in Business Central online. For more information, see Run the tool multiple times. But use the Capacity section of the Business Central administration center to keep track of how much data you migrate.

  Tip

  On page 9035 Data Administration, you can find reports that are used to compress or cleanup the data. In earlier Business Central versions, the page may not be present. However, most reports can be found using Search .

  In certain cases, the customer wants to migrate large amounts of data. For large source databases, we recommend deploying the source database to an Azure SQL Database, and then setting up cloud migration from Azure SQL source instead of the on-premise SQL Server. This eliminates the need to install and maintain self-hosted integration runtime on-premise, and ensures much faster data replication.

  Deploying to Azure SQL can be an easy and quick process if done in SQL Server Management Studio connected to the on-premise database. Follow the Deploy Database to Microsoft Azure SQL Database wizard, which you find in the Tasks context menu on the database. When prompted to choose a service tier for the new Azure SQL database, remember that the lowest configurations may not be adequate for migrating large amounts of data. Consider the right balance between performance and price that would be preferable in your case. The database service tier can be tuned later in Azure Portal.

  We continually work on improving and optimizing the migration tool for larger database sizes. For example, customers can buy more environments, and they can buy extra storage. For more information, see Managing Capacity. If more assistance is required, contact support as described in Escalating support issues to Microsoft.

  There are no firm limits on the size of the database, the number of daily transactions, or the number of users that can migrate from on-premises to Business Central online.

  To help you test the migration, you can migrate data to the target production environment, and then create a sandbox environment based on this production environment. For Business Central on-premises, first use the data replication step in the production environment, and then run the data upgrade step in the sandbox environment for safe testing before you run the data upgrade step in the production environment.

  Important

  Only run the data migration into a single environment. Don't reuse the same on-premises database for multiple data migrations—restore a fresh backup of the database. You have the option of migrating to a sandbox, copying to another sandbox, and testing upgrade there. If successful, you can then copy the upgraded sandbox to the production environment and delete all other environments.

• Plan the switch to use Business Central online for production carefully to not start until migration is complete

  Important

  Do not set up cloud migration for a production environment that is already in use for business. You risk that the migration process overwrites data that is needed to run the business. Even if your migration targets a different company in that environment, you risk that the upgrade overwrites data that is shared across companies in the target environment.

• Schedule the migration to not conflict with an update of Business Central online

  If you set up cloud migration for an environment, the environment cannot 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 remove 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.

• Install the migration apps in Business Central

  In the Business Central administration center, open the environment that you intend to migrate your data to, and then choose the Apps action. Make sure that these apps have the latest updates installed:

  • Intelligent Cloud Base
  • Business Central Intelligent Cloud

  Important

  In Business Central online, install, publish, or upgrade the Intelligent Cloud Base extension first, and then the product-specific extension or extensions.

  If you're migrating from an earlier supported version of Business Central, you must also make sure that the following apps are updated:

  • Business Central Cloud Migration – Previous Release
  • Business Central Cloud Migration – Previous Release [code for your country-specific version]

  Also, at the end of the upgrade, you must make sure that the applicationVersion field in the ndo$tenantdatabaseproperty table is set to the right version. If the field is blank, or if it's set to an older version than the migration tool supports, the migration can't run. For more information, see Post-upgrade tasks.

  If you're migrating from Dynamics GP, make sure that the following apps are installed and updated:

  • Dynamics GP Intelligent Cloud
  • Dynamics GP History SmartLists

  Important

  Migrating from Dynamics GP using the Cloud Migration Setup assisted setup guide is currently only supported for the following markets:

  • United States
  • Canada
  • United Kingdom
  • Australia

  If you want to migrate from another product, check the marketplace for an app to help.

• Test extensions

  It's highly recommended that you test the impact of any extension in a sandbox environment before having it installed in your production environment in Business Central online to help avoid any data failures or untended consequences.

End-to-end process

You manage cloud migration from Business Central online. But the on-premises solution remains the operative environment until you complete the migration. Do not set up cloud migration for a production environment that is already in use for business. You risk that the migration process overwrites data that is needed to run the business. Even if your migration targets a different company in that environment, you risk that the upgrade overwrites data that is shared across companies in the target environment.

Any existing data in Business Central online will be overwritten with data from your on-premises solution, or source, once the data migration process is run.

If you don't want data in Business Central online to be overwritten, don't configure the connection. The only exception is when you migrate from Business Central on-premises current version because you can run the migration tool multiple times in that specific scenario.

If your data source is Business Central on-premises, several stored procedures will be added to the SQL Server instance that you define. These stored procedures are required to migrate data from your SQL Server database to the Azure SQL server associated with your Business Central tenant.

The main steps in a migration process are:

1. Have a target environment with a paid subscription

2. Determine which data to migrate

   • For Business Central on-premises, determine which companies to migrate and the number of migration runs to use to migrate the required data
   • For Dynamics GP, you can use the migration process to move historical data to Azure Data Lake
   • For Dynamics NAV, upgrade to Business Central on-premises first, and then migrate

3. Migrate data, using the Cloud Migration Setup assisted setup guide in Business Central online

4. Test the result of the migration

5. Set up users, permissions, and other configurations in Business Central online

6. Stop using the on-premises solution, switch off the migration, and tell users to start using Business Central online for daily business

Tip

Meanwhile, users can train themselves in how to work in Business Central using a sandbox environment and the free training modules on Microsoft Learn.

Migrate data

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 will 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 will take 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 in order to ensure all data was moved to the cloud before you start transacting in Business Central online.

Important

Do not set up cloud migration for a production environment that is already in use for business. You risk that the migration process overwrites data that is needed to run the business. Even if your migration targets a different company in that environment, you risk that the upgrade overwrites data that is shared across companies in the target environment.

Run the assisted setup guide

To migrate data, in the target company in Business Central online, run the Cloud Migration Setup assisted setup guide.

Important

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 in order to run the cloud migration. In this case, the Data Migration Setup guide will display an extra step, where the delegated administrator can copy the auto-generated 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 choosing the same approval link that was shared by the delegated administrator, or from the Cloud Migration Approval page.

Tip

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 current version because you can run the migration tool multiple times.

Important

Do not set up cloud migration for a production environment that is already in use for business. You risk that the migration process overwrites data that is needed to run the business. Even if your migration targets a different company in that environment, you risk that the upgrade overwrites data that is shared across companies in the target environment.

Once the setup guide is complete and data migration is activated, the initial data migration ready to be run from the Cloud Migration Management page whenever you want. Go to Manage the Migration.

Tip

You can migrate the data that you want to take with you to the cloud. If you need more storage than the default 80 GB, you can buy additional environments or additional storage capacity. We recommend that you consider reducing the amount of data that you migrate so that it is less than 30 GB in each migration run. For example, reduce the number of companies that you are migrating data for, or delete outdated data in tables that contain log entries and archived records. Also, review how you can manage database capacity in a Business Central online environment.

Specify the SQL connection

If the product you selected requires a SQL connection, this page will be presented. Other source applications may require different information to connect to them. 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've 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.

  Important

  Azure SQL Managed Instance is not supported.

• 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=BC180;UID=MySQLAccount;PWD=MyPassWord;, if you're migrating from Business Central on-premises, version 18. For more information, see the SQL Server blog.

  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};

  Important

  In the second connection string format, don't forget to add the space in User Id parameter as it's mandatory and can throw an error if it's missing.

  The SQL connection string is passed to Azure Data Factory (ADF), where it's 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 choose 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. If you want to test your SQL string, open the Microsoft Integration Runtime Configuration Manager, and then choose the Diagnostics menu option. From there, you can test to see if the connection is good.

Manage the migration

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.

The Cloud Migration Management page provides information about your data migration runs and the ability to manage your migration services, for example.

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. The Migration Information tiles show the number of migrated tables and the number of tables that didn't migrate due to warnings or errors. Choose a tile to drill into more details and guidance to correct any errors.

There's 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 following table describes the actions that you can run from the page:

Action	Description
Run Migration Now	Choose this action to start the data migration manually. 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	Choose 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 choose to refresh 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'll 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 choose 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've 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, we'll publish the new service. This action will check to see if a new service has been published. The check will display the version of the service you're currently running and then also display the latest service published. Then, you can choose to update your solution. We recommend 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 run a migration for. For example, you're migrating a large database with multiple companies, so you break down the migration in several runs by including one or a few companies in each migration run. You can see the estimated size of each company
Define User Mappings	This option is available when you sign 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're logged in as in Business Central. Map users only once for each migration. If you run the mapping twice or more, you might run into conflicts.
Setup Checklist	When you're ready to use your Business Central online tenant as your main system, the tables that weren't 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 available if the Business Central online tenant is connected to Dynamics GP. For more information, see Migrate Dynamics GP to Azure Data Lake.

Run the tool multiple times

There are some scenarios where it will be necessary for you to run the cloud migration setup guide more than once.

Tip

We recommend that you take a backup of the target environment so that you can easily restore the environment to a specific state and time, should you want to.

The following list highlights a few examples:

• Multiple companies in Business Central on-premises

  One example is if you want to add more companies to the migration, or if you want to change the companies to migrate, run the assisted setup guide again. A more efficient option, though, is to use the Select Companies to Migrate action from 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. For more information, see Create and configure a self-hosted integration runtime in the Azure docs.

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 the Microsoft Integration Runtime Service Manager and enter the runtime name in the field in the wizard; you won't be allowed to copy/paste. The runtime service will identify that you're 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. Regenerating an Azure Data Factory (ADF) key may be done using the Cloud Migration Management page in your Business Central online. For more information, see the Run the assisted setup guide section.

Tip

If you are using Business Central on-premises, the same setup guide is also available in your on-premises solution. You will automatically be redirected to your Business Central online to continue the configuration process.

Caution

If you have mapped users in the first run of the cloud migration setup guide, then do not choose the Define User Mappings action again in subsequent runs.

Business Central permission sets

Specifically for migration from Business Central on-premises, we limit 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, we created the Intelligent Cloud user group and the Intelligent Cloud permission set. 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.

Note

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.

Warning

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 rerun a full migration to correct this.

Company initialization

When a company is created in Business Central, it must be initialized to ensure it's accessible and functional for everyone who needs to use it. If you're familiar with Dynamics NAV, then you're used to this step happening automatically during the upgrade process, for example. But it's not quite the same with Business Central online. When a migration run completes, you're prompted to view a list of non-initialized companies so that you can start the initialization. You can choose 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.

Note

When you schedule an initialization in the Hybrid Companies list, we recommend not using the company until its initialized. The setup data could be missing, which might cause problems.

End the migration

Once you've 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.

Note

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.

Important

If you set up cloud migration for an environment, the environment cannot 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 remove 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.

See also

FAQ about Migrating to the Cloud from On-Premises Solutions
Migrate to Business Central Online from Business Central On-premises
Migrate Dynamics GP Data to the Cloud
Upgrading from Dynamics NAV to Business Central Online
Managing Capacity