Synchronizing Table Schemas
When you design a table in Dynamics NAV, you define the metadata for the table, such as the name and object ID, and the fields and their data types. This table definition describes the table that must be created in the SQL Server database to store business data. You create and modify the table definition by using the Microsoft Dynamics NAV Development Environment. When you make changes to the table definition in the development environment, or when you upgrade a Dynamics NAV database to a newer version, you must synchronize the business database table schema with the new or changed table definition so that the two are the same. Table schema synchronization is performed by the Microsoft Dynamics NAV Server instance that connects to the business database.
Depending on the changes to a table definition, you might have to consider how to handle the existing data in the business data table when synchronizing the schema. For example, you must decide whether to keep or delete the data. Some table definition changes, such as adding and renaming a field, adding a new table, or modifying C/AL code to a table, do not affect data in the database table and are considered to be non-destructive changes. With non-destructive changes, you can synchronize the schema without any special data handling considerations. However, if you make destructive changes to the table definition, such as removing a field, then you will be warned by the development environment when you try to save the changes, and you must specify how to handle the data when synchronizing the schema. For more information about destructive table changes, see Handling Destructive Changes With Table Schema Synchronization.
Synchronizing Table Schemas with SQL Server
You can synchronize table schemas from the Microsoft Dynamics NAV Development Environment or by using the Sync-NAVTenant cmdlet in the Microsoft Dynamics NAV Administration Shell.
You typically use the development environment to synchronize the table schema during development while you are making changes to a specific table. In the development environment, you can synchronize table schema changes for a specific table or for all tables. During development, you synchronize the table definitions with a development database that has no active users. This means that you can make destructive changes and create upgrade codeunits to migrate the business data to the new database schema.
You typically use the Sync-NAVTenant cmdlet when you are upgrading the live database from an earlier version of Dynamics NAV. For example, the Sync-NAVTenant cmdlet can be used by system administrators who automate the process of applying application updates and upgrades in a multitenant environment. When you apply a new database schema to a business database, you must make sure that existing data is migrated to new version. For example, during development, you have added two new fields to a table, and you have created upgrade codeunits to migrate data from another field in that table to the two new fields. So when you want to apply those changes to an existing business database, you upgrade the database. For more information about synchronizing table schemas by using the Sync-NAVTenant cmdlet, see Sync-NavTenant.
Important
Depending on the size of the tables that you modify and the nature of changes, the schema synchronization process can take anywhere from several seconds to several hours (for example. this can occur when you add multiple fields or keys to a large table). Therefore, it is important that you control when schema synchronization is run.
When synchronizing large tables, you should increase the SQL Command Timeout setting for the Microsoft Dynamics NAV Server instance that connects to the database to avoid timeouts during schema synchronization. The default setting is 30 minutes. For more information, see Configuring Microsoft Dynamics NAV Server.
Synchronizing the Table Schema for Specific Tables From the Development Environment
In the development environment, you can synchronize the table schema for a specific table or for all tables. You have the option to synchronize a table schema when you perform one of the following operations on a table from the development environment:
Save
Save As
Compile (This also pertains when you select multiple tables for compiling from Object Designer).
Delete (This also pertains when you select multiple tables for deleting from Object Designer).
Important
Before you can perform table schema synchronization from the development environment, you must make sure that that you have a working connection to a Microsoft Dynamics NAV Server instance. You can see the currently connected Microsoft Dynamics NAV Server instance and change as required in the Database Information window. For more information, see Database Information or How to: Change the Microsoft Dynamics NAV Server Instance.
Schema Synchronization Options
When you perform one of the operations on a table, you can choose from the following schema synchronization options:
| Option | Description |
|---|---|
| Now - with validation | Before applying changes to the business data table, Microsoft Dynamics NAV Server validates the changes to the table definitions to check whether they are destructive changes. This includes changes that will delete data in the fields of the business data table which are affected by the changes. * If there are no destructive changes to the table, then the schema changes are applied to the business data table immediately. * If there are destructive changes, Microsoft Dynamics NAV Server checks that there are table synchronization instructions in an upgrade codeunit. If there are instructions, then the schema changes are applied to the business database table according to the instructions. If there are no instructions, then an error message appears. The table definition changes are not saved and the schema changes are not applied. For more information about how Microsoft Dynamics NAV Server validates changes, see How Microsoft Dynamics NAV Server Validates Table Schema Changes. |
| Later | Table definition changes are saved and compiled in the application but the changes are not validated or applied to the business data table. You synchronize the table schema later by doing one of the following: * Save or compile the table from Object Designer in development environment and choose either the Now - with validation or Force synchronization option. * Use the Sync. Schema for All Tables option on the Tools menu. * Run the Sync-NAVTenant cmdlet from the Microsoft Dynamics NAV Administration Shell. |
| Force | Table definition changes are applied to the business data table schema without validation. For destructive changes, data in columns of the business data table that are affected by changes will be deleted. This option ignores any table synchronization instructions for the table in upgrade codeunits. |
Synchronizing All Table Schemas From the Development Environment
From the development environment, you can synchronize table schema changes for all tables in the database. This is useful when you upgrade from an earlier version of Dynamics NAV or when you made changes to a table or multiple tables previously and chose to synchronize later.
To synchronize schema changes for all tables, on the Tools menu, under Sync. Schema For All Tables, you can choose one of the following options:
| Option | Description |
|---|---|
| Check Only | Microsoft Dynamics NAV Server validates the table definition changes but does not apply the schema changes to the business database. * If there are no destructive changes to any of the tables, no errors occur. * If there are destructive changes, Microsoft Dynamics NAV Server checks that there are table synchronization instructions in an upgrade codeunit. If there are instructions, then no errors occur. If there are no instructions, an error occurs. For more information about how Microsoft Dynamics NAV Server validates changes, see the How Microsoft Dynamics NAV Server Validates Table Schema Changes section. |
| With Validation | Before applying changes to the business data table, Microsoft Dynamics NAV Server validates the table definition changes to check for destructive changes to the table. * If there are no destructive changes to the table, then the schema changes are applied to the business data table immediately. * If there are destructive changes, Microsoft Dynamics NAV Server checks that there are table synchronization instructions in an upgrade codeunit. If there are instructions, then the schema changes are applied to the business database table according to the instructions. If there are no instructions, then an error message appears. Table definition changes are not saved and the schema changes are not applied. For more information, see How Microsoft Dynamics NAV Server Validates Table Schema Changes. |
| Force | Table definition changes are applied to the business data table schema without validation. For destructive changes, data in columns of the business data table that are affected by changes will be deleted. This option ignores any table synchronization instructions for the table in upgrade codeunits. You should use this option only when you are sure that there is no risk of unwanted data loss. |
Note
Microsoft Dynamics NAV Server will only synchronize schemas for new tables and tables with changed table definitions.
When you synchronize schemas for all tables, the Synchronize Schema Changes window appears. In this window, you can see the state of the database. For example, whether it is operational or whether schema synchronization is pending or produced errors. If errors occur when you use the Check Only or With Validation synchronization options, the actual error messages are not displayed in the Detailed State field on the Synchronize Schema Changes window. This is because of text display limitations of the development environment. To see the full list of the detected errors you can run the Sync-NAVTenant cmdlet from the Microsoft Dynamics NAV Administration Shell with –Mode parameter set to CheckOnly.
Synchronizing Table Schemas When Importing FOB Files
You can import a FOB file, which contains Dynamics NAV objects, into a database by using the development environment or the Import-NAVApplicationObjects cmdlet that is available in the Microsoft Dynamics NAV Development Shell.
When you import a FOB file that contains tables, you are prompted to choose a schema synchronization option to perform on tables after the import is finished. We recommend that you back up the application objects before importing the FOB file, and then choose the Now – with validation synchronization option on the confirmation dialog box. You can back up the application objects by exporting all objects to a FOB file or making a full SQL backup of the database.
If you choose to synchronize the schema with validation, after the import is finished, the Microsoft Dynamics NAV Server compares the table definitions that were imported from the FOB file with the table definitions that are currently stored in the database.
If Microsoft Dynamics NAV Server detects destructive changes to tables, then it will check whether there is an upgrade codeunit in the application that handles the destructive changes.
If an upgrade codeunit is not found in the application, then an error occurs.
If there is an upgrade codeunit to handle destructive table changes, then the changes are applied.
When you import objects in FOB files by using the Import-NAVApplicationObjects cmdlet, table schema synchronization is controlled by the SynchronizeSchemaChanges parameter of this cmdlet. By default, the SynchronizeSchemaChanges parameter is set to Yes. This means that during the import process, each table will be checked for destructive changes. If destructive changes are detected, the FOB file import will be canceled, even if an upgrade codeunit for handling the changes is included the FOB file. Therefore, if you want to import a FOB file that contains tables that have destructive changes and upgrade codeunits to handle the changes, you must set SynchronizeSchemaChanges parameter to No, and then call Sync-NAVTenant cmdlet that is available in the Microsoft Dynamics NAV Administration Shell to synchronize the changes.
Handling Destructive Changes With Table Schema Synchronization
Destructive changes to a table definition are changes that can potentially affect the existing data in the business data table. The following table definition changes are considered destructive changes.
- Deleting a table.
- Deleting a field.
- Changing a field's data type.
- Changing a field's class.
- Changing the SQL data type of a field.
- Decreasing the length of data in a field.
- Changing the primary key of a table, such as removing a field from the key.
- Changing the ID of a table field.
To synchronize the business data table with destructive changes to table definition, you must do one of following:
Create or import an upgrade codeunit that includes synchronization instructions for the table. For more information, see Upgrade Codeunits.
Use the Force option to synchronize the schema. This will apply the table definition changes regardless of existing data the data. Data in fields that are affected by the table definition changes will be deleted.
Tip
When you synchronize destructive changes to a table by using the Now – with validation schema synchronization option, an error occurs and will not let you save the changes. The validation process for destructive change does not consider whether there is data in the fields that are affected by the changes. The validation process determines whether the change is destructive by analyzing actual metadata changes. This implementation helps prevent you from overlooking a destructive change and neglecting to create data upgrade codeunits to handle the changes when you work with development or test databases that do not contain data.
How Microsoft Dynamics NAV Server Validates Table Schema Changes
When you synchronize a table schema and choose a synchronization option that validates the changes, Microsoft Dynamics NAV Server verifies whether the changes can be applied to the business data table without affecting the data in changed columns that are affected by the changes.
The following describes the Microsoft Dynamics NAV Server process for validating the table schema changes:
Compares the content of the Object Metadata and Object Metadata Snapshot system tables to identify differences in the database schemas.
Lists tables with schema changes in the Table Synch. Setup system table.
Identifies the upgrade codeunits with instructions for handling changes and adds the instructions to the Table Synch. Setup table.
Applies the relevant changes according to the upgrade instructions.
During the schema synchronization, each change is applied based on the synchronization mode that was specified in the upgrade codeunits. For more information about the synchronization modes, see TableSyncSetup Modes.
Note
If the change is not a destructive change and has not been handled with an upgrade instruction in an upgrade codeunit, then the default mode is Check.
Monitoring the Business (Tenant) Database State During and After Schema Synchronization
When you synchronize a table schema, the business database passes through various operational states, depending on the outcome of the synchronization. You can use the Get-NAVTenant cmdlet from the Microsoft Dynamics NAV Administration Shell to view the current state of the database during and after synchronization. For more information, see Get-NAVTenant cmdlet.
You can also view the state of the database from the development environment in the Synchronize Schema Changes window that appears when you choose Sync. Schema For All Tables option on the Tools menu.
The following table describes the states of the database during and after table synchronization.
| State | Description |
|---|---|
| Mounted | Indicates that the business (tenant) database is mounted on the Microsoft Dynamics NAV Server instance but is not yet operational. This can occur when Microsoft Dynamics NAV Server is started or when the Mount-NavTenant cmdlet is run from the Microsoft Dynamics NAV Administration Shell. You should consider the Mounted state as an intermediate state that only exists right after mount operation is executed. The purpose of this state is to indicate that the Microsoft Dynamics NAV Server knows about this database but has not yet brought the database into an operational state, which includes verifying whether table schemas are synchronized. With the exception running the Get-NavTenant and Dismount-NavTenant cmdlet, all other operations that access the database will initiate the process of bringing the database into the operational state. This means that the first operation on the database can take some time to complete while the database is validated. The result of the validation will be that the database will change state to Operational, OperationalWithSyncPending, or OperationalWithSyncFailure. This design enables the Microsoft Dynamics NAV Server to start with multiple tenants. However, if there are only few tenants on the Microsoft Dynamics NAV Server, then you should consider including the Get-NavTenant | Sync-NavTenant command into the script that mounts the database to make sure all the databases reach the operational state at the same time. |
| Operational | Indicates that the database is fully operational and the table schemas are up to date. This is the normal operating state of a database. |
| OperationalWithSyncPending | Indicates that the database is operational but schema synchronization is pending on one or more tables. If a table that is pending schema synchronization is accessed by Microsoft Dynamics NAV Server (for example, when user tries to access the table from the Microsoft Dynamics NAV Windows client) an error will be displayed. The error informs the user that the metadata for the table has not been synchronized. To synchronize table schemas, you can run the Sync-NAVTenant cmdlet from the Microsoft Dynamics NAV Administration Shell or use the development environment, for example, by saving a table and choosing the Now-with validation synchronize schema option. Either of these operations will initiate the schema synchronization process. If the process is successful, the state will change to Operational. If the process is not successful, the state will change to OperationWithSyncFailure. In the development environment, the Detailed State field on the Synchronize Schema Changes window will contain the errors that occurred during synchronization. The Sync-NAVTenant cmdlet provides you a more detailed list of any synchronization errors. |
| OperationalWithSyncInProgress | Indicates that table schemas in database are currently being synchronized. You can use the Get-NAVTenant from the Microsoft Dynamics NAV Administration Shell to track progress. Users can continue to work with the application from the Dynamics NAV clients. However, if they try to access a table whose schema has not been synchronized, an error will occur. While the synchronization process is running on a Microsoft Dynamics NAV Server instance, the following restrictions apply:
|
| OperationalWithSyncFailure | Indicates that the database is operational but the last attempt to synchronize the table schemas failed. If Microsoft Dynamics NAV Server tries to access a table that has not been synchronized, an error occurs. For example, this can occur when destructive changes have been made to a table and there is no upgrade codeunit available to handle the changes. You can run the Get-NAVTenant cmdlet to retrieve a list of all the destructive changes that were detected during the schema synchronization process. The changes are listed in the Detailed State field.To get the database back to Operational state, you can do one of the following:
|
For more information about the Get-NAVTenant cmdlet and how to use the Microsoft Dynamics NAV Administration Shell, see Get-NAVTenant and Microsoft Dynamics NAV Windows PowerShell Cmdlets.
See Also
Upgrade Codeunits
Upgrading Data
Tables
Development
Administration
Microsoft Dynamics NAV Windows PowerShell Cmdlets
Sync-NAVTenant cmdlet