Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
Well, a current discussion is whether to go for NAV 5.0 Service Pack 1 or not. On the one hand, NAV 5.0 SP1 has lots of improvements regarding SQL performance (Indexed Views, Buffered Inserts, etc.), on the other hand there are other issues and problems (slow FlowFields, lacking SQL 2000 capability, etc.).
Once the decision was taken to upgrade – and in the following I ONLY refer to a technical upgrade, means the change of the C/SIDE programs, DLL, etc. – normally there is no way back to a previous version; except when restoring a backup which was taken before the migration. If the system with the new version was online & used for a certain time, a "point of no return" will be passed, when restoring an old backup would mean non-acceptable loss of data and time …
Restoring a FBK – a backup file created from C/SIDE client, not SQL Server – to a previous version may be possible, but with a large database this could be QUITE time consuming and may raise different issues.
Currently I'm checking out NAV 5.0 SP1 a little bit, evaluating upgrade scenarios etc. and – according to that – potential fall-back solutions. In the following I would like to describe how it is technically possible to downgrade without restoring a backup!
CAUTION! The following is really tricky business and is for sure NOT recommended – it just shows a technical possibility! This solution EXCLUSIVELY refers to SQL Server installations, NOT native C/SIDE databases. The method was developed in "laboratory" and has NEVER been tested under real life situations, so consider this as an EXPERIMENT only.
This proceeding is highly risky and may cause loss of data or corrupted database integrity if unexpected problems arise!
This BLOG is addressed to EXPERIENCED NAV/SQL "troubleshooters" who EXACTLY know what they are doing – or better not doing.
If you want to do this with your databases, you do that at OWN RISK, of course! If you screw things up DON'T BLAME ME!
A NAV Database – NAV 4.0 SP3 Build 26565 – was technically upgraded to version NAV 5.0 SP1 Build 26084. Objects/Application have not been upgraded, they are still version 4.00.
Database Server: SQL Server 2005 Standard Edition, Build 3200
Current Database: Name: Navision_Downgrade, Objects: NAV 4.0 SP3, C/SIDE: 5.0 SP1 26084
Target Database: Name: Navision_Downgrade, Objects: NAV 4.0 SP3, C/SIDE: 4.0 SP3 26565
Step 1 – Backup
In NAV 5.0 all Tables (ID < 2000000000) have to be exported into a FOB-File. This is necessary to save the current table-, key-, index- and SIFT definitions.
With SQL Server features the whole database MUST be backed up before continuing (simply to be able to rollback if everything fails).
Step 2 – Deleting NAV 5.0 System Objects
The Tables 2000000065, 2000000066, 2000000067, 2000000068 were introduced with NAV 5.0 and were added during the former technical upgrade process. Thus, before downgrading these objects have to be removed as they are not compatible with NAV 4.0.
As these tables cannot be deleted within the "Object Designer" this has to be done via "Management Studio" and TSQL:
FROM "Object" WHERE "ID" IN
(2000000065, 2000000066, 2000000067, 2000000068)
Step 3 – Dropping VSIFT
During the former technical upgrade the "SIFT Tables" from NAV 4.0 have been replaced in NAV 5.0 SP1 by "Indexed Views VSIFT". Before downgrading all VSIFT views have to be deleted. This could be done with a NAV Codeunit, executing this C/AL Code:
Key Record "Key"
Codeunit – Trigger OnRun()
Key.SETFILTER(TableNo, '<%1', 2000000000);
IF Key.FINDSET(TRUE) THEN BEGIN
c := Key.COUNT;
IF Key.MaintainSIFTIndex THEN BEGIN
i := i + 1;
Window.UPDATE(1, ROUND(10000 / c * i, 1));
UNTIL Key.NEXT = 0;
Removing the flag "MaintainSIFTIndex" will delete the corresponding VSIFT view. To be able to restore the original SIFT definitions, the FOB from Step 1 is mandatory!
Step 4 – Downgrade/Conversion
Here it is necessary to understand the version management of C/SIDE. If a "technical upgrade" is performed, NAV asks if the database should be "converted". Well, in fact it is impossible to "convert" the database – it is a SQL Server 2005 database, and will remain a SQL Server 2005 database, regardless which NAV version is using this database.
The "conversion" actually performs a re-writing of the SQL site table-triggers which run the SIFT management (aka "SIFT Trigger"); and/or completely rebuilds the SIFT or VSIFT structures.
The C/SIDE client controls which NAV Client is allowed to access the database (or not) by checking the field "databaseversionno" in the table "$ndo$dbproperty" (not available in "Object Designer", access via SQL only).
When logging on the C/SIDE client reads the table "$ndo$dbproperty" and compares this "databaseversionno" with an internal number. If there is no match, NAV raises an error (internal < versionno) or asks for "conversion" (internal > versionno).
So, by setting the "databaseversionno" to a value of an older database, the database could be accessed by older C/SIDE clients!
For example, when changing in NAV 5.0 SP1 the value from 95 to 63 the "converted" database could be accessed by a NAV 4.0 SP3 26565 client! Caution: NOT everything is possible here!
If the "databaseversionno" is set to a lower version number than the NAV client has, C/SIDE will ask for a "conversion".
Hence, the "databaseversionno" is to be set to 62 (sixty two, 63 minus 1).
Now, when opening the database with the NAV 4.0 SP3 26565 client, C/SIDE will ask to "convert the database". As we have already disabled all "SIFT Indexes" in Step 3, there is not much to convert.
Step 5 – Restoring the SIFT Tables
Finally, after successful "conversion", the Table-Backup FOB from Step 1 has to be imported and the original SIFT Structures will be restored: the SIFT tables are created from the original Table definitions, saved in the FOB. This could take a LONG time (see below)!
Have in mind that all "SIFT Levels To Maintain" are now set to "standard" and further optimization is required (reducing "Buckets" etc.).
Finished. If no error was shown, the database is back on NAV 4.0 SP3 26565, databaseversionno 63.
Once this was performed successfully (???) you have to TEST, TEST, TEST, TEST, TEST, … OF COURSE!!!
The "downgrade-conversion" does not just (re-)create the SIFT Tables, of course these tables are filled with records, too. And – depending on the database size & number of records – this can be MILLIONS of SIFT records, increasing the database size DRAMATICALLY! Thus, the conversion could be a real super-size transaction, putting HUGE load on the server. Therefore it is feasible to set the database's Recovery Model to SIMPLE, and to restrict the user access (sysadmin, db_owner only) to avoid conflicting user transactions. To reduce the load on the server, the tables from the FOB (from Step 1) should not be imported all at once, but in several smaller packages.
And again: this is really tricky business! If you screw up your database, YOU TAKE THE BLAME! No support, warranty, guarantee, or anything.
@Dean: Cool! Thanks a lot for this!