This article discusses tasks you must consider doing on data in Business Central on-premises database before you run cloud migration. By cleaning data before running cloud migration, you can help ensure that the process proceeds smoothly.
Clean up corrupted code fields in tables
Corrupted code data type fields are those fields that contain invalid characters, such as lowercase characters and white space characters. White space characters are invisible characters representing keystrokes like line feed, carriage return, tab, and space bar. These characters must be deleted before running cloud migration to ensure that any code modification, read, or delete operations on the corrupted fields in the cloud don't fail.
Tip
Learn more in IsWhiteSpace method for a list of the Unicode whitespace characters.
To delete the invalid characters, run the Invoke-NAVSanitizeField cmdlet, which is available in the Business Central Administration Shell. The cmdlet returns an object containing the number of rows and values modified, the tables impacted and a list of potentially ignored records.
General steps
You run the Invoke-NAVSanitizeField cmdlet against the on-premises database. The general steps for running the Invoke-NAVSanitizeField cmdlet are:
Open the Business Central Administration Shell as an administrator.
At the command prompt, run the
Invoke-NAVSanitizeFieldcmdlet. For example, the following command sanitizes fields in the tenant with the IDtenant1.$resultis an object containing the results of the operation execution.$result = Invoke-NAVSanitizeField -ServerInstance 'BC' -Tenant 'tenant1'
Recommended steps
The previous steps run the cmdlet on all tables in the database. We recommend that you fix the customer's production database before migrating to the cloud. To make the process most efficient, follow these steps:
Make a copy of the customer database.
Connect the database to a Business Central server instance.
Run
Invoke-NAVSanitizeFieldon all tables in the copied database. ReplaceBCwith your server instance name.$result = Invoke-NAVSanitizeField -ServerInstance 'BC'This command returns a list of the tables that include corrupted fields.
Run
Invoke-NAVSanitizeFieldon the production database, one table at a time, using the-TableIdparameter. Replace18with the table ID to sanitize:$result = Invoke-NAVSanitizeField -ServerInstance 'BC' -TableId 18
Tip
If you have set up cloud migration, you can clean a table from the Business Central client for your target online tenant. But you can only clean one table at a time using this method. It's not recommended to use this method for cleaning all tables prior to migration—use the recommended steps instead. However, this method is useful for cleaning individual tables after migration:
- Select Search (Alt+Q) in the upper-right corner, enter Cloud Migration Management, and then choose the related link.
- Select Actions > Sanitize tables.
- Set the Company name field to the company that the table belongs to.
- Set the Table Id field to the table that you want to sanitize.
- Select Sanitize table.
Clean up corrupted company names
Like code fields, fields that contain a company name can also include invalid characters. The problem in this case is that tables for companies with corrupt names aren't picked up for cloud migration.
The company names can't be fixed by using the Invoke-NAVSanitizeField cmdlet, as you can with code fields, because company name fields are text data type. Use a SQL query on the on-premises database to identify affected names. The following query only identifies names; it doesn't correct them:
SELECT * FROM Company WHERE
([Name] like '% ') or --trailing spaces
([Name] like ' %') or --leading spaces
([Name] like '%' + Char(10) + '%') or --line feed
([Name] like '%' + Char(13) + '%') --carriage return
Compress or archive data
It's beneficial to compress data and archive data that is out of date. To do this task, use page 9035 Data Administration in the client. This page includes reports for compressing and archiving data. Learn more in Data Archive extension.