NAV Team: The New Table Synchronization Paradigm in Microsoft Dynamics NAV 2013 R2

[Blog bot]

Microsoft Dynamics NAV 2013 R2 was dispatched with a brand new feature that introduces big challenges to all of the Microsoft Dynamics NAV channel: Multitenancy. In simple words, multitenancy allows partners to deal with hosting scenarios on-premises and in cloud services in an easier and more flexible way than in the past.

Before Microsoft Dynamics NAV 2013 R2, partners and customers could only use a single-tenant scenario (here also called Legacy mode).

Below a short explanation how table synchronization used to work in earlier versions.


Microsoft Dynamics NAV 2009 / Microsoft Dynamics NAV 2013

1. At object import/compile, C/SIDE checks the Object Metadata version in working memory and compares it to the version in Object Metadata table to decide if and what kind of change is made.
2. Schema changes are DIRECTLY APPLIED to the SQL Server database by C/SIDE if there is no breaking schema change, otherwise an error will be thrown by C/SIDE depending on SQL Server error catch.
3. Object Change Listener is checking for changes in metadata, then updating Microsoft Dynamics NAV Server Cache with data from Object Metadata table if the change was detected.

A synchronization failure would typically be reported with an error like “The Object Metadata does not exist. Identification and values … “ when running the Microsoft Dynamics NAV Windows client.

The multitenancy feature has also changed the design how Microsoft Dynamics NAV developers has to deal with object changes, overall related to table objects. Multitenancy implies that the table structure definition has to be stored in the application database and this needs to be applied on one or more separate storage databases called Tenants. From a development perspective, this means that any modification that are made to a table object in C/SIDE are NOT DIRECTLY applied to the SQL Server structure but there is a need of performing a secondary action to apply and made persistent these modification at SQL Server side: this process is called Synchronization. Microsoft Dynamics NAV 2013 R2, then, comes with a time decoupling between table metadata creation (C/SIDE) and data structure changes (SQL Server).

In order to simplify the current design, the Microsoft Dynamics NAV development team decided to handle single- and multitenant scenarios in the same way (roughly speaking a single-tenant / Legacy mode is handled as a multitenant scenario with a single tenant database constantly mounted against an application database).

Below a short explanation how this is working in practice.


Microsoft Dynamics NAV 2013 R2

SCENARIO 1:

• Single-tenancy / Legacy mode
• “Prevent data loss from table changes” = Yes (default):

1. At object import/compile, C/SIDE checks the Object Metadata version in working memory and compares it to the version in Object Metadata table to decide if and what kind of change is made. (Same as in Microsoft Dynamics NAV 2009 and Microsoft Dynamics NAV 2013)
2. C/SIDE then CALLS THE Microsoft Dynamics NAV Server to check for breaking schema changes in SQL Server structure.
   If C/SIDE is unable to call the Microsoft Dynamics NAV Server or if a breaking schema change is attempted (action that cannot performed due to the current SQL Server structure such as deleting a field containing data): a C/SIDE error is reported accordingly and changes to Object Metadata table will not be committed.
   If it is evaluated as not attempting a breaking schema change in SQL Server then metadata from C/SIDE working memory is saved and committed to Object Metadata table.
   PLEASE NOTE: at this stage NO CHANGES ARE MADE TO THE SQL SERVER DATA STRUCTURE.
3. When prompting for SYNCHRONIZATION, Microsoft Dynamics NAV Server then compares Object Metadata table with Object Metadata Snapshot table content. Any difference in the value for the “Hash” field is a flag to Microsoft Dynamics NAV Server that a change exists and should be subsequently applied physically SQL Server side as structural changes.

Prompting for Synchronization happens when

- Performing ANY Microsoft Dynamics NAV client action.

For example, if a user opens a Microsoft Dynamics NAV Windows client, then Microsoft Dynamics NAV Server is starting applying the relevant structure changes to SQL Server, and the Microsoft Dynamics NAV Windows client is not shown until all the changes are done on SQL Server side.

OR

- Running the Sync-NAVTenant Windows PowerShell cmdlet.


SCENARIO 2 (DEPRECATED):

• Single-tenancy / Legacy mode
• “Prevent data loss from table changes” = No (Manually opted, not persistent)

IMPORTANT NOTICE:

Setting the “Prevent data loss from table changes” C/SIDE switch to “No” has been intended to be used as last resource in a pure multitenancy scenario and in Test or Staging environments when partners does not have any business data database mounted against the application database. All other usages that deviate from this statement might lead to unpredictable results and even undesired data loss scenarios in upgrades or, even worse, production environments.

Never change for any reason this parameter to “No” when developing against a single-tenant / Legacy mode database.

1. At object import/compile: C/SIDE checks the Object Metadata version in working memory and compares it to the version in Object Metadata table to decide if and what kind of change is made. (Same as in Microsoft Dynamics NAV 2009 and Microsoft Dynamics NAV 2013)
2. C/SIDE DOES NOT CHECK FOR ANY BREAKING SCHEMA CHANGES IN SQL SERVER but simply FORCES COMMIT of metadata from C/SIDE cache TO the Object Metadata table.
3. When prompting for SYNCHRONIZATION, Microsoft Dynamics NAV Server then compares Object Metadata table with Object Metadata Snapshot table content. Any difference in the value for the “Hash” field is a flag to Microsoft Dynamics NAV Server that a change exists and should be subsequently applied physically SQL Server side as structural changes.

Since no validation is made against SQL Server (“Prevent data loss from table changes” was set to “No&rdquo there might be chances that this will result in:

• Data Loss
  There are few specific cases where data is dropped in this scenario:

• • The primary key is detected as being no longer unique
  • Data per Company is changed from Yes to No and more than one company contains data
  • One or more fields are deleted
  • One or more field data type is/are changed
• Missing Synchronization
  Activities cannot be completed since SQL Server prevents these actions that would break the data structure and therefore no Microsoft Dynamics NAV Windows client or Web client can connect to the database. The partner or customer has to resolve these missing synchronization issues before moving forward or fall back to a backup where these issues does no longer exists

SCENARIO 3:

• Multitenancy
• “Prevent data loss from table changes” = Yes (default):

Same as Scenario 1 for point 1. and point 2.

When prompting for SYNCHRONIZATION, changes will be triggered and applied to the SQL Server data structure.

Prompting for synchronization in a pure multitenant deployment happens when

- Performing ANY Microsoft Dynamics NAV client action

OR

- Running the Sync-NAVTenant Windows PowerShell cmdlet

OR

- Mounting a tenant database



Based on the scenario depicted above, there might be risks of data loss and/or missing synchronization issues if handling C/SIDE development (namely dealing with Table objects) in a way that deviate by the prospected paradigm.

Data loss issues:

These might arise typically in one of the following scenarios:

• Direct removal of rows from the Object Metadata table in SQL Server
• Stretched / Borderline scenarios that implement platform files with a Build No. lower than 36281 KB 2934571 as described in this blog post.

Synchronization issues:

These might arise typically in one of the following scenarios:

• The Microsoft Dynamics NAV Server service account has insufficient permissions
  The service account must be added to “db owner” SQL Server role for the Microsoft Dynamics NAV tenant Database.
• Stretched / Borderline scenarios that implement platform files with a Build No. lower than 36281 KB 2934571 as described in this blog post.
  With a lower build number, you might get into one of the following scenarios:

• • When several developers commit changes at the same time in the same database / tenant while synchronization is running, this might lead to metadata corruption. (Object Metadata table now is locked for committing changes).
  • Doing actions like FOB Import > Replace > SaveAs and then Import again the saved FOB was causing a metadata corruption.
• SQL Connection Timeout meanwhile performing an operation, such as when SQL Server schema changes require drop and build of indexes on large tables.
  To resolve this issue it is necessary to increment the following parameter in the Microsoft Dynamics NAV Server CustomSettings.config file
  <add key="SqlCommandTimeout" value="10:00:00" />

Development Environment best practice
thinking about potential data loss and synchronization issues is a brand new big challenge in the development environment, and so some consideration and following best practice might be advisable. These applies to developing solutions for both single- and multitenant deployments.

1. Do not use Build No. lower than than 36310 KB 2934572
   As a partner, you take this as the "RTM Build No." starting point for NAV 2013 R2 and deploy this platform hotfix in the future projects while you also convert existing installations.
   NOTE: As per common best practice, we recommend that you download / request / test and deploy the latest platform hotfix for Microsoft Dynamics NAV 2013 R2. This will contain correction for minor issues not directly or just slightly related to synchronization scenarios.
2. Never-ever change &ldquo;Prevent data loss from table changes&rdquo; to &ldquo;No&rdquo;.
   This have been noticed as one of the major source of potential data loss and missing synchronization for NAV 2013 R2 databases.
3. Make sure that the Microsoft Dynamics NAV Server service account has been granted the &ldquo;db owner&rdquo; role in SQL Server.
4. Increment the SQL Server Command Timeout parameterin the Microsoft Dynamics NAV Server configuration file that you use in development to a very high value (such as 10:00:00)
5. For large Microsoft Dynamics NAV objects OR a high number of table modifications, do NOT use a Microsoft Dynamics NAV client action to prompt for synchronization but it is warmly preferable to use the Sync-NAVTenant Windows PowerShell cmdlet. (This is a typical scenario related to upgrades).
6. For big batch of FOB files that are making a high number of table modifications, be sure to have this tested on a safe staging environment and import, where possible, the Table Objects in smaller chunks and synchronize them after importing every single chunk of Microsoft Dynamics NAV objects.
7. For important changes in several table structures, such as when upgrading from previous version, it would be good to run a SQL Server Profiler trace after prompting for synchronization to check what is running on the SQL Server side and keep the synchronization monitored until it ends.

Recommended Events:

• SP:StmtCompleted
• SQL:StmtCompleted

Recommended Column Filters:

• DatabaseName Like <DatabaseName>
• TextData Not Like SELECT %

Bottom line. Worth mentioning that if a Microsoft Dynamics NAV Client hang / disconnect happens due to a missing synchronization issue or there were a synchronization transaction running behind the transaction rollback SQL Server side will take a higher amount of time in comparison with the same committed transaction, depending on the type of changes, resources available, etc.

Just in case you fall back in this situation, it is warmly advisable to do not stop nor restart Microsoft Dynamics NAV Server and check through a SQL Server Profiler trace and/or via SQL Server Management Studio if the transaction has successfully rollback.

Another blog post will follow this one, related to synchronization challenges and best practice while upgrading to Microsoft Dynamics NAV 2013 R2 from previous versions.



These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.



Gerard Conroy - Microsoft Dynamics UK

Abdelrahman Erlebach - Microsoft Dynamics Germany

Duilio Tacconi - Microsoft Dynamics Italy

Jasminka Thunes - Microsoft Dynamics Norway

Microsoft Customer Service and Support (CSS) EMEA



A special thanks to Jorge Alberto Torres & Jesper Falkebo from the Microsoft Dynamics NAV development team



Источник: http://feedproxy.google.com/~r/Micro...v-2013-r2.aspx