Hi All,
Let's continue from the point we left in earlier Post. If you haven't read earlier post read it here
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.
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”) there might be chances that this will result in:
• Data Loss
There are few specific cases where data is dropped in this scenario:
o The primary key is detected as being no longer unique
o Data per Company is changed from Yes to No and more than one company contains data
o One or more fields are deleted
o 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: (NEVER DO THESE)
• 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.
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.
With a lower build number, you might get into one of the following scenarios:
o 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).
o 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 Service Tier as shown Below -
Change the Value from 00:30:00 to 10:00:00 (Microsoft Suggested), I would say the value should be based on your database size. For Upgrade I set it to 24: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, Microsoft 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 “Prevent data loss from table changes” to “No”.
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 “db owner” role in SQL Server.
4. Increment the SQL Server Command Timeout parameter in 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.
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.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Regards,
Saurav Dhyani
saurav-nav.blogspot.com
Let's continue from the point we left in earlier Post. If you haven't read earlier post read it here
Microsoft Dynamics NAV 2013 R2
• 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.
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”) there might be chances that this will result in:
• Data Loss
There are few specific cases where data is dropped in this scenario:
o The primary key is detected as being no longer unique
o Data per Company is changed from Yes to No and more than one company contains data
o One or more fields are deleted
o 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: (NEVER DO THESE)
• 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.
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.
With a lower build number, you might get into one of the following scenarios:
o 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).
o 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 Service Tier as shown Below -
Change the Value from 00:30:00 to 10:00:00 (Microsoft Suggested), I would say the value should be based on your database size. For Upgrade I set it to 24: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, Microsoft 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 “Prevent data loss from table changes” to “No”.
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 “db owner” role in SQL Server.
4. Increment the SQL Server Command Timeout parameter in 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.
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.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Regards,
Saurav Dhyani
saurav-nav.blogspot.com
Comments
Post a Comment