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.
ehm... all I can say is WOW
Indeed, nicely done, Jorg.
A great post! Should go directly in the FAQ list...
Great post! But are you sure you have enough disclaimers on here? I can almost guarentee some programmer somewhere is going to do this in a production database because he/she is under pressure.
[quote] I can almost guarentee some programmer somewhere is going to do this in a production database because he/she is under pressure[/quote]
One of my favorite quotes is "stupid is as stupid does", so if anybody is doing this "live" without testing before etc., this one IMHO shouldn't have a job in IT business anyway ...
Yes it a lot of work, and a great thing to share. Maybe post a thread in FAQ and link it back to this.
Oh and oddly enough I was watching Forrest Gump on TV last night.
Awesome, looks like you really spent some time investigating this, and I learnt a few things here, like how NAV knows the DB Version.
By the way, I was trying to put together a list of database version numbers. If anyone can help complete the list it would be appreciated.
The list is here : wiki.dynamicsbook.com/index.php
I'm glad to see the Key table being put to good use :-)
David, I have some more version numbers (no build info though), pasted below. The ones you have already, look very familiar! I have updated your Wiki with them.
// Database versions for: 2.50
// 1 : Beta 1 data formats
// 2 : Beta 2 data formats (change in Code field value format from space-padded prefix to hex prefix)
// 3 : Beta 3 data formats (change in Code field value format from hex prefix to no prefix or special format)
// 4 : Release data formats (SIFT trigger identifiers changed and new indexes on SIFT tables)
// Database versions for: 2.60(A->C)
// 5 : Release data formats (change in Session view and SIFT triggers to use CONVERT rather than CAST for datetime fields)
// Database versions for: 2.60(D->F)
// 6 : Changes in SIFT triggers due to UPDATE... WHERE... performance problem
// Database versions for: 3.00
// 7 : Beta 1 data formats (change in Session view for SQL Server 2000; change in chartable)
// 8 : Beta 1 data formats (change in chartable; changes in SIFT triggers due to UPDATE... WHERE... performance problem)
// 9 : Beta 1 data formats (added maintainviews dbproperty)
// 10 : Beta 2 data formats (mapped invalid characters to "_" for SQL object identifiers)
// 11 : Release data formats (added diagnostics dbproperty)
// Database versions for: 3.01, 3.01A, 3.01B
// 12 : Release data formats (added identifiers dbproperty)
// Database versions for: 3.10, 3.10A
// 13 : Release data formats (added maintainrelationships dbproperty)
// Database versions for: 3.60
// 14 : Release data formats (change in Session view for new extensions; identifer conversion dbproperties; per-database license dbproperty)
// Database versions for: 3.70
// 15 : Release data formats (change in chartable; added checkcodepage, quickfind, maintaindefaults dbproperties;
// change in Session view for new extensions and column COLLATE;
// change in Database File view for column COLLATE)
// 16 : Hotfix 5 data formats (change in Session View for removing duplicate connections based on 'sysprocesses.ecid')
// 17 : Hotfix 12 data formats (change in Session View for removing duplicate connections based on 'sysprocesses.ecid';
// the identifiers and invalididentifierchars dbproperties are updated to OEM)
// 18 : 3.70B data formats (change in Session View, removing join to syslockinfo; also removed from session count query)
// Database versions for: 4.0
// 20 : Pre-release data formats (change in Session view for new Idle Time column;
// change in permission table to include new object types)
// 30 : Security release data formats (change in Session view, removing join to syslockinfo;
// change in chartable; creation of security objects)
// Database versions for: 4.1
// 40 : Release data formats (added locktimeout, locktimeoutperiod, hardrowlock and bufferedrows dbproperties)
// Database versions for: 4.2
// 50 : Update 3 (no actual database conversion was made)
// Database versions for: 4.2 Update 4, 4.3
// 60 : Alterable security option; fix for SIFT data corruption around Closing Dates (added securityoption dbproperty)
// 61 : Rebuilding SIFT triggers in order to correct the Sum problem
// 62 : Rebuilding SIFT triggers in order to correct the delete statement
// 63 : Prevention of updating SIFT twice
// Database versions for: 5.0
// 80 : Release data formats (added system tables for office integration and record links; change in table descriptions for clustered property)
// 81 : Rebuilding SIFT triggers in order to correct the Sum problem
// 82 : Rebuilding SIFT triggers in order to correct the delete statement
// Database versions for: 5.0 SP1
// 95 : Removed SIFT tables and triggers, creating Indexed Views instead
// Database versions for: 6.0
// 120 : Release data formats (incl. CTP1) (added several new system tables;
// extended record links system table)
// 130 : Pre-release data formats (incl. CTP2) (removed SIFT tables and triggers, creating Indexed Views instead)
// 140 : Pre-release data formats (removed Assembly and Relationship system tables;
// added enabledforservice dbproperty)
@Dean: Cool! Thanks a lot for this!