Clean data

Article
08/07/2023

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

For a list of the Unicode characters, see IsWhiteSpace method.

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 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-NAVSanitizeField cmdlet. For example, the following command sanitizes fields in tenant with the ID tenant1. $result is an object containing the results of the operation execution.
```
$result = Invoke-NAVSanitizeField -ServerInstance <Business Central Server instance name> -Tenant <tenant ID>
```

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 the Invoke-NAVSanitizeField on all tables in the copied database.
```
$result = Invoke-NAVSanitizeField -ServerInstance <Business Central Server instance name>  
```
This command returns a list of the tables that include corrupted fields.

Run the Invoke-NAVSanitizeField on the production database, one table at a time, using the -TableId parameter:

$result = Invoke-NAVSanitizeField -ServerInstance <Business Central Server instance name> -TableID <table name>

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:

Open the Cloud Migration Management page.
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 ant to sanitize.
Select Sanitize.

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 don't even be 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. Instead, you have to use an SQL query on the on-premises database. For example:

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 Archive in the client. This page includes reports for compressing and archiving data. For more information, go to Data Archive extension.

Next steps

Set up cloud migration