Microsoft Dynamics NAV 2009 R2 Upgrade to on-premise 365 Business Central

This repository contains steps that proved to make the data upgrade process faster in production. The main goal was to be able to start testing the upgraded database as soon as possible to make a go-live or no-go-live decision before starting a working week.

If starting fresh with only open balances is an option for you, please do that by all means. Otherwise, I hope you find some useful ideas here.

The main idea of the upgrade performance improvement is moving some long running C/AL functions to T-SQL and disabling table indexes until the last moment - creating in SQL directly first while the system can be used for the final pre-go-live testing (so called 'soft launch'). We also moved the document Dimension tables out of the toolkit radar by moving the data to other tables and doing the final processing in SQL.

Disclaimer

The steps and scripts are provided as is, without guarantees, and should be referred as an example. Please ensure you have a team member that is familiar with the MS SQL Server and T-SQL if you choose to follow the steps. There probably will be some extra steps specific to your company.

Prerequisites

• Python 3 installed
• NAV 2009 R2 classic client
• NAV 2013 Dev client installed
• NAV 2015 Dev Client and service installed
• Dynamics 365 Business Central (v. 14) Dev, service and clients installed
• SQL Server installed
• SQL Management Studio installed for running the T-SQL scripts

Preparation Steps

1. Take the production NAV 2009 database offline so that no one could access it. We should be able to take it online and continue working in case upgrade doesn't go as expected.
2. Take the production database backup (or create volumes snapshots).
3. Restore the database (from the snapshots) on the upgrade environment and attach the database.
4. Ensure the log volume is twice as big as the data database file(s).
5. If the database is bigger than 1 TB, ensure there is another log file created to exceed 2TB limitation for logs.
6. Ensure the compatibility level of the NAV 2009 database is SQL Server 2008 (100).
7. Ensure recovery mode is Simple.

Upgrade 2009 to 2015 - Step 1

1. Using NAV 2009 R2 Classic client and developer license, open the company.
2. Check that all objects are not locked in the environment and force unlock all if any
3. Export tables filtered with ..2000000000 by ID into a TXT file
4. Copy the entire scripts folder, using CLI, run script to disable indexes disable_indexes.py specifying the exported text file as --original (-o).
5. Upload the result file into the database and compile tables to drop the SQL indexes on the SQL server side.
6. Export tables filtered with <50000 from the database into NAV_2009_Standard_Customized_Tables.txt (for instance, to be clear) for a step later.
7. Run upgrade6080-step1.1.sql against the database.
8. Import Upgrade601800.1.MODIFIED.fob replacing the existing tables (no schema changes, code on all triggers removed).
9. Export permissions
   1. Run Dataport 104001 - Import/Export Roles
   2. Run Dataport 104002 - Import/Export Permissions
10. In Object Designer, run table 2000000004 User Role and delete all the records with the filter <>SUPER by Role ID.
11. In Object Designer, menu Tools Security->Windows Logins, add your user and role SUPER.
12. Run upgrade6080-step1.0.sql against the database.
13. Open Form 104001 Upgrade - Old Version, press Transfer data and Delete Objects.

Upgrade 2009 to 2015 - Conversion to 2013

1. Open NAV 2009 DB with NAV 2013 Development Environment to convert the database (takes long, with indexes enabled - ages).
2. Make a backup in the fastest way (copy database files).
3. Open DB with NAV 2015 as Administrator.
4. Compile tables 2000000004…2000000130 range with the synchronize schema Later.
5. Run upgrade6080-step1.2.sql against the database.

Upgrade 2009 to 2015 - Step 2

1. Upload the development licence.
2. Make sure the user that runs the NAV 2015 service DynamicsNAV80 has the db_owner access to the DB.
3. Ensure Microsoft Dynamics NAV Server (NAV 2015) instance points to the database.
4. Ensure the NAV 2015 instance's SQLCommandTimeout is set to 23:00:00.
5. Start the NAV 2015 Service.
6. Run the Administration Shell as Administrator and execute command Sync-NavTenant –ServerInstance DynamicsNAV80 -Mode Sync to initialize database and synchronize all tables with validation.
7. Import all Microsoft Dynamics NAV 2015 objects file from the Cronus DB with Replace All and Sync Schema Later.
8. Run add_custom_fields_without_code.py to add custom fields without code to NAV 2015 tables (so that we keep the custom and regional data when tables synced) with the following parameters (the date format in objects is dd/mm/yy, might differ for your region):
   1. NAV_2009_Standard_Tables.txt as -o (--original)
   2. Exported earlier NAV_2009_Standard_Customized_Tables.txt as -m (--modified)
   3. NAV_2015_Standard_Tables.txt as -t (--target)
   4. NAV_2015_Standard_Customized_Tables.txt as -r (--result)
9. Run the index disabler disable_indexes.py specifying the result text file (NAV_2015_Standard_Customized_Tables.txt) as --original (-o).
10. Import the file generated NAV_2015_Standard_Customized_Tables_Disabled_Indexes.txt.
11. Export tables filtered with 50000..99000000 into a CustomTables.txt and use it as -o to run script to remove code remove_code.py to be able to compile them.
12. Import Upgrade601800.MODIFIED.fob with Sync Schema Now - with Validation and compile the UPG objects.
13. Compile all tables with Synchronize Schema Later.
14. Run the Administration Shell as Administrator and execute command Sync-NavTenant –ServerInstance DynamicsNAV80 -Mode Sync to initialize database and synchronize all tables with validation.
15. Review the errors if any.
16. Run Sync-NavTenant –ServerInstance DynamicsNAV80 -Mode Force (takes long).
17. Start the upgrade process by selecting from the menu: Tools-> Data Upgrade-> Start
18. Run the code moved to SQL upgrade6080-step2.0.sql against the database.
19. Drop all upgrade objects (filtered with 104000..104999&<>104087&<>104088 to update the users later) with Force.
20. Make a backup of the database or snapshot.

Upgrade 2015 to Business Central

1. Upload the license.
2. Delete all objects except tables (do NOT synchronize schema)
3. Clear Dynamics NAV Server instance and debugger breakpoint records from old database with the script 1. Run script clear-instances-and-breakpoints.sql.
4. Open Dynamics NAV Development Environment for Business Central as administrator and convert the 2015 database.
5. Import latest merged and prepared Business Central application objects filtered with ..2000000000 by ID.
6. Import upgrade toolkit objects Upgrade8001300.AU.MODIFIED.fob with Synchronization Schema Later.
7. Make sure the user that runs the BC service BC140 has the db_owner access to the DB.
8. Ensure Microsoft Dynamics NAV Server BC140 instance points to the database.
9. Ensure the BC instance's SQLCommandTimeout is set to 23:00:00.
10. Start the BC140 service, make sure it's running after a minute.
11. Export large tables (for instance, filtered by ID with 17|21|32|45|46|110|111|112|113|114|115|121|254|271|339|379|5772|5773|5802|5823|6651|6661|7313|7318|7319|7322|7323|28161|37046301) into a Large_Tables_BC.txt and use it as -o to run script disable_indexes.py and import the file generated Large_Tables_BC_Disabled_Indexes.txt.
12. Following the List of Tables and Indexes Enabled, enable ONLY the SIFT indexes on the tables in the Development Environment saving with Synchronization Schema Later and leaving the rest of the vital indexes to be created later using the script below.
13. Compile all objects in the BC DB with Synchronization Schema Later.
14. Run the Business Central Administration Shell as Administrator.
15. Execute command Sync-NavTenant –ServerInstance BC140 -Mode Sync, review errors if any.
16. Execute command Sync-NavTenant –ServerInstance BC140 -Mode Force as we should have all fields that we need in BC objects.
17. Run the first importance index creation script indexes-large-tables-first-importance.sql and without waiting for completion, proceed to the next steps while it's running in the background. The vital indexes listed in List of Tables and Indexes Enabled.
18. Then run Start-NavDataUpgrade -ServerInstance BC140.
19. Run script stored-procedure-GetDimensionSetID.sql.
20. Run script upgrade-open-document-dimensions.sql.
21. Run script upgrade-journal-line-dimensions.sql.
22. Run script upgrade-product-groups-and-open-docs.sql.
23. Run script to migrate data migrate-data.sql.
24. Restart the instances.
25. Generate symbols. Run using command prompt as administrator the following commands: cd "C:\Program Files (x86)\Microsoft Dynamics 365 Business Central\140\RoleTailored Client" finsql.exe command=generatesymbolreference, database=<BCDatabase>, serverName=<BCDatabaseSQLServerName>

Pre-Go-Live Steps

BC is functional at this point and can be tested at a certain extent. There are no history dimensions processed yet. Make sure users are aware of the situation when the test.

1. Run script stored-procedure-GetDimensionSetIDByValueIDSet.sql.
2. Run the following scripts concurrently to process Ledger Entry Dimensions. This should be not interfere with the systems setup but might decrease performance in general.
   1. upgrade-dimensions-GLEntry.sql.
   2. upgrade-dimensions-ValueEntry.sql.
   3. upgrade-dimensions-ItemLedgerEntry.sql.
   4. upgrade-dimensions-CustomerLedgerEntry.sql.
   5. upgrade-dimensions-VendorLedgerEntry.sql.
   6. upgrade-dimensions-BankAccountLedgerEntry.sql.
   7. upgrade-dimensions-PhysInventoryLedgerEntry.sql.
   8. upgrade-dimensions-FALedgerEntry.sql.
3. Run the indexes-large-tables-second-importance.sql script to build secondary importance indexes for Entry tables and leave it running until done.

User Groups and Profiles

After having database running, import prepared Permission, Permission Sets, User Group, User Group Permission Set, Profile, Profile Metadata.

After Go-Live Steps

1. Run script upgrade-product-groups-historical-data.sql.
2. Run script upgrade-posted-document-dimensions.sql.
3. Enable indexes on the tables from the List of Tables and Indexes Enabled gradually (estimate by testing table by table first) by planning and deploying BC table objects with indexes and syncing them.

Helpful Resources

Upgrading from Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2015

Converting a Database to Business Central - Technical Upgrade

Waldo's blog - What impact does my C/AL have on SQL?