NAV 2013 Upgrade Part IV - SQL Migration.

Hi all,

While Release of NAV 2013, we had some new things but some old things became obsolete and cannot be used with this version of Navision.

One of them that effect most of the customer is Support to NATIVE Database. So today we will discuss about Native to SQL Migration which is one of the steps during upgrade to NAV 2013.



Let's See why we cannot restore a backup of Native Database into SQL Server Database.

For Demo Purpose i have taken a demo database and intentionally created some issues which normally occurs during SQL Migration.

So i have NAV 2009 SP1 Native DB with Errors. I took NAV backup and trying to restore same on SQL Server NAV 2009 SP1 Database.

Here is the error message that i get -

The Cust. Ledger Entry table contains a field with a date value that cannot be used with SQL Server:
Record: Entry No.='1820'
Field: Posting Date='01-01-0101' (WHICH I CHANGED intentionally)

Reason -
SQL Date Starts From 01-01-1754.

*There are other errors too during restoring Native Backup to SQL.

Resolution-

We will follow the procedure given by Microsoft.

For this you will require Product DVD & Developer Licensce.

1) Navigate to Upgrade Toolkit ->SQL Migration in Product DVD.

2) you will find Migrate.fob.

3) Import Migrate.fob in Native Database(10 objects will be imported).

4) Run Codeunit 104010 Create Field Checking Code (This will create a text file in C Drive named as FIELDCHK.TXT.

5) If you are using windows 7 and the codeunit gives you an error message saying - "The operating system cannot access to the file C:\FIELDCHK.TXT .Verify that the file type and attributes are correct".

Then design the codeunit and onRun Trigger change the Path of the File Name to some other drive.

6) A message will popup which confirm that file is created.

7) Import the txt file into the database. (you require Developer Licensce to do that).

8) After importing compile Codeunit 104015 Field Check. After compilation Run Codeunit 104015 Field Check.

9) This process will take time as per the size of your database and no. of companies in your database.

10) The will will keep you updated about number of errors that have been found yet.

11) Once completed it will display a message with count of total number of errors.

12) Once clicked OK a form will get open which will contain the incorrect value and suggested values from tool.

13) You can change the New value as per discussion with client.

14) After doing all rectification just click ESC and the message will confirm that you want to update the new values in tables.

15) Delete all records from table 104010 Incorrect Data Value & 104011 Code Field Information FROM ALL COMPANIES.

16) If selected yes the new values will be applied.

17) After completion mark Codeunit 104015 and then filter based on version @*MIG* and mark all listed records.

18) Remove the filter and select all marked objects.

19) Delete all objects(Total object count should be 10).

20) Take Navision backup and restore on SQL Server Database.

Regards,
Saurav Dhyani

http://saurav-nav.blogspot.in/