Navision Native database to SQL Server Conversion

anilkumar

hi!

Planning change database from Navision navtive to SQL Server. What will the procedure to convert native database to Sql Server - ?

Can anyone help me.

Thanks!

ara3n

The simplest thing to do is. do A Navision backup, and create a new database on sql and restore the navision .fbk backup.

If you get Date errors, you need to import sql migration fob. and fix the dates. Then do a backup and restore it on sql.

bbrown

I suggest running the Migrate process as the first step. It saves a lot of aggravation and wasted time. I have seen very few databases convert into SQL without data errors.

Also don't overlook hardware and database tuning issues. Yiu will find lots of post on those subjects

anilkumar

Thanks a lot! I will try.....

David_Singleton

Theoretically the conversion to SQL is just backup native, and restore to SQL, BUT in the real world it takes a bit more than just that.

There are many issues that can arise, but in summary, these are the big ones:

Dates:
Navision handles dates from Jan 1st, 0000. SQl can only handle from the years 1700 or there abouts. This normally looks pretty trivial, but, the problems arise when users accidentally enter dates in that range. You will often find dates entered as things like 1089 instead of 1980 and similar typos. Both options mentioned here by Ahmed and bbrown, make sense. If there are just a few errors, and its a small database, then its easier just to import and manually fix the errors. Other wise run the migrate tool that will find the dates. Now if the dates are for example Posting dates, then the job to fix them could be a big one, and you will not be able to fix this without the help of your NSC/ Dynamics Partner.
Code Field Sorting:
Navision uses a type of sorting for Code fields that makes great sense in an ERP environment. It separates numbers and text. Also if a number contains text, then it is considers that the whole field is text. But SQL uses so called "Telephone book sorting" for code fields. For most cases, all this means is that some lists will be sorted differently, and you have the option with SQL of selecting (in most cases) which type of sorting you want.

Where is is a major issue though, is if you haven't followed proper styles, and have created document that use Navision Flow Fields, and the sorting will affect them. The place you are most likely to see this is in the Account Schedules and Analysis views, (though VAT can also be an issue). In these cases you need to fix those by redesign.

Maintenance
For many years, the key thing that has set Navision's Native C/SIDE server apart from most other servers (besides its unquestioned superior performance) is it ease of maintenance. The reason that Navision's Server is fast, AND maintenance free, is simple. The server was designed to do one thing, and one thing only, i.e. to run Navision. SQL on the other hand is a general purpose solution to millions of different solutions, and needs to be specifically tweaked and tuned for the particular implementation. When Navision makes a request to get a record set from the server, the server already knows the optimum way to do this, so it just does it. SQL on the on the other hand needs to maintain and update statistics so it can decide the best solution for getting the data. What this means is that if you are moving to SQL, then you either need to learn about SQL, and make sure that someone in house is performing WEEKLY maintenance on the system, or agree with your Partner to have someone come in each week to do this for you. You should be ready to calculate this into your ERP system costs.

Server Hardware
If you have a properly built Navision C/SIDE server, then there is no chance it can be used for SQL. The requirements of the two servers are extremely different, so its is almost certain that you will need a new server. The key issue, is that all of Navision C/SIDE server performance is derived through the correct use of drives. SQL on the other hand has a huge system overhead to determine how to gt data, that CPU and RAM play an important role.

Navision C/SIDE Server
If you have say a 50 user system, with a 50 Gig database, then you Navision server will be something like:
* CPU P2 500Mhz.
* RAM 1.5 Gig
* Disks:
o C: - RAID 1 2x10GiG - Windows 2000 + Navision Server.
o Drive K thru P (6 sets) - RAID 1 2x10Gig - 10 Gig DB parts 1-6 Total 60 GIG DB 10 GIG per Array

SQL Server
In the case of SQL the server will be more like:
* CPU 4 x 64 bit CPU 2-3 GHz
* RAM 8 Gig room to expand to 16
* Disks
o Drive 1 : Raid 1, 1 logical drive (2 actual drives) = Windows 2003 Server + SQL 2005
o Drive 2 : Raid 1, 2 Logical Drives (4 actual Drives) = SQL transaction Log files
o Drive 3 : RAID 10 7 logical drives (14 actual drives) = SQL Database files
o Drive 4 : Raid 1, 1 Logical Drive (2 Actual drives) = SQL/Windows Temp db file

Windows 2003 Server 2003 CALs - 50 SQL 2005 CALs (50) - (SQL to support 4 CPUs)

System tuning
For most clients (large ones at least) the biggest part of converting to SQL, is tuning the system for SQL optimization. The issue come down to a number of issues.

SIFT and Flow Field Maintenance
C/SIDE was built around Flow Fields. In fact the whole design of the applicaiton relies on them. To move a Navision application over to SQL, means that we need to emulate Flow Field functionality in SQL. Since SQL is a "CPU" oriented server, and C/SIDE is a "DISK" oriented server, this translation is not always optimal. SQL does not like lots of keys the way Navision does, and in fact it does not need them. Functions to calculate FlowFields that Navision does using Keys (indexes), SQL is capable of doing with the CPU and RAM. Thus you need too carefully consider if it makes sense to maintain keys in SQL that are used only to calculate flow fields. So on the move to SQL, you need to look at each individual major flow field,. and decide on the optimum level of indexing required. IN C/SIDE this is all automatic.

Key Maintenance
As mentioned above, keys/indexes are totally different concepts in Navision and SQL. And this must be taken into account. For example, in Navision ALL the data from a record is store in the primary key. But the Secondary keys only store the information about sorting, plus the fields of the primary key, but no other data. So in Navision if you request data from a secondary key, then you expect that you will only find the record, and then you will do a FIND on that record to get the actual data. We all know that in Navision, it is critical to select the correct key before filtering. But in SQL, on smaller record sets, its actually faster to sort by primary key, and then filter with incorrect sorting. Since SQL will return not only the result, but also the whole record, and thus there will be less disk activity.

This means that you need to review key bottle necks to find areas where keys can be switched off in SQL.

Security
In version 4.00 of Navision a new level of SQL security was introduced. Although this adds better security to your system, it is recommended that if converting frm Native database, go to Version 4.00 SP3 which will allow you to use the older version which is similar to Native security. Otherwise you will have a lot of issues getting a lot of things to work.


Summary
The move to SQL at first glance looks like a no brainier, it does look extremely simple. But in reality there is a lot more behind it. In reality though, SQL is the future. We are not expecting any new upgrades to the Navision Native database. And in fact, the current version of the C/SIDE database is virtually identical to the DOS version.

If you want to take advantage of any new features that will come in Dynamics NAV, then now is the time to move to SQL.

Waldo

Hm, very complete, David =D> .
How is anyone going to be able to add something to that ... :-k .

May be a small detail:
first date in SQL Server: 01/01/1753

cunny

Thanks David! You really did a greate job!

cunny

lzr

Very nice article David, thank you!

Anyone know about a tool to search for dates that are outside of the permitted range?

kine

lzr wrote:

Very nice article David, thank you!

Anyone know about a tool to search for dates that are outside of the permitted range?

Migrate.fob ... :-)

David_Singleton

gee thanks :P

lzr

Should have checked before asking :oops:

Thanks!

kriki

[Topic moved from Navision forum to SQL General forum]