Move Navision Database from SQL 2000 to SQL 2005

Business Central/NAV Technical Forum
1

All,

I have built a new SQL 2005 cluster for my Navision (v4.0) database and want to move the DB from our old SQL 2000 server to this new cluster. I’m having some problems… I have tried a couple of ways to do this restore:

  1. Restore using SQL Management tools:

If I use the SQL tools to restore the database, there appear to be some problems with the permissions on certain tables - in particular the session table. When a user logs in they get the following error:

‘The session table does not exist as the required object name of Navision_Test.dbo.Session in this database’

  1. Restore using the Navision client:

If I create a new Database from the Navision client and then attempt to restore a DB backup from the Navision client I get the follow error when trying to login:

'The session table contains a Microsoft Business Solutions-Navision field data type that is not compatible with the SQL Server data type:

Field: Wait Time (ms)

Type: Integer

SQL type: BIGINT’

Both ways restore the database just fine, so there are just some interesting problems stopping me from getting this done. Has anyone encountered this or know how to get around this?

Stevo

2

Hi Stevo,

Welcome to the Dynamics User Group [<:o)]

There are people out there that have done this more than me, and I am sure they will add more advise, but just to get the ball rolling.

Both options should work, and in fact you should be able to just detach 2000 and attach 2005.

I would say that with either detach/attach or a SQL backup, there is the issue that you will be running in 80 compatibility mode, where as a Navision restore would have you in 90 mode, but I don’t know if there is any issue running in 80 mode.

The BIGINT issue is odd, did this database start life as a 3.70 one? The issue is that you need to modify the dbo.Session view to CAST waittime as INT. BUT this is something that many people do in development environments, but not in live. As I say its odd, since I thought this issue was only when you tried to run 3.70 on SQL 2005, which is not supported.

3

Hello Stevo

Here are the steps I would take to move from 2k to 2k5

  1. Get 4.0 SP3 client, Get the updates 1 - 5 for sp3 client.

  2. Make a navision backup with 4.0 client.

  3. Create a new db in sql 2k5 with SP3 client. MAKE sure on advanced tab the security model is standard.!!!

4 Restore the backup in sql 2k5.

  1. DO performance testing etc.

  2. Once happy Uninstall 4.0 client on all computers and install 4.0 sp3 and connect to the new server.

4

Hello Rashed,

At this moment I have also serious problems with Navision. I have the same errors.

Also your solution should work, but we are working with 3.7 client.

Do you have any suggestions how to solve this problem. I’m still trying for several weeks.

Kind regards,

Annette

5

Hello Annette!

Welcome to “DynamicsUser.net” [<:o)]

Well, regarding your problems: Could you describe your issues a little more detailed?

I am assuming you run a 3.70 NAV on SQL 2000 and you want to upgrade to SQL 2005. This implicates a technical upgrade of NAV to versein 4.00 SP3 Update 6 (or higher), too.

Here it is important, that you upgrade the NAV version first, still running on SQL 2000. After “conversion” make sure to set the “Standard” Security Model. Once this was successful, then simply detach the database from SQL 2000 Server, maybe move the files to the new location, and attach it to the SQL 2005 Server. Then just switch the “Compatibility Mode” to “90” (SQL 2005).

If this does not work, where exactly do you encounter problems?

Best regards,

Jörg

6

Hello Jörg,

Our problem is as follows:

we are now working with 3.70 NAV on SQL2000

we want to work with 5.0 NAV on SQL2005

We have two servers, one with SQL2000 (3.7 NAV) and the new one with SQL2005 (NAV5.0).

We already have made the adjustment to the files/forms and reports in NAV 5.0.

Our Solution Center suggested that we should make a backup of 3.70 in a .fbk file (our database is 30GB), in order to have also our data migrated to NAV 5.0.

After that we should restore this backup (.fbk file) on our server with SQL2005.

The problem with this restore is that it takes a lot of time, I expect 60 hours.

Above that we have only 120Gb available on the server and logfiles during the backup are very hugh, also there is not enough space on the disks and the jobs crashes.

So I tried to make a restore of the backup of 3.70 (the .bak file) to the server with SQL2005.

This is no problem. When I open this database with 5.0 NAV I’m receiving the message that:

2705,“42S21”,[Microsoft][ODBC SQL Server Driever][SQL Server]Column names in each table must be unig. Column name ‘securityoption’ in table ‘dbo.$ndo$dbproperty’ is specified more than once.

SQL:

ALTER TABLE [dbo].[$ndo$dbproperty] ADD securityoption INTEGER NOT NULL DEFAULT 0

I tried to make this change in SQL2005 on the file, but the option ALTER TABLE was grey, also this was not possible.

I hope my problem is much clearer now and it would be great if you have a solution.

Kind regards,

Annette

7

Unfortunately, this error occurs quite often when “converting” a database. The problem is, that the field “securityoption” is added to the table “$ndo$dbproperty” in the beginning of a conversion. Once the upgrade fails, the transactions are not completely rolled back, the field “securityoption” still remains. Now if you tray to convert again, this error is raised, because C/SIDE tries to add this column again.

To solve this, you have to delete the field “securityoption” manually from the table “$ndo$dbproperty” via Management Studio and run the conversion again.

Does it work now?

8

The database conversion is designed to be tolerant of being re-run, in case of errors. Since there are two phases to the conversion, transaction control alone cannot achieve this, so certain steps are either explicitly undone, or errors such as duplicate column names are allowed.

I have looked at this and found that the securityoption field addition is not trapping this error, however. I will raise a bug against this problem.

9

I finally managed to finish the upgrade just before Christmas.

I used the .fbk file for the backup. It took some time (more than 5 hours). Finally the database was 25Gb and the logfile of Navision was 50Gb. So we needed a lot of free space on the server. The upgrade with the toolkit provided by Microsoft took 25 hours… Most of the time was necessary for the tables with Dimensions.

Thank you for your concern.

10

Hi !!!

I am having a similar task at hand. I need to upgrade from NAV 4.0 running on SQL 2000 to NAV 4.0 SP3 running on SQL 2005

Do i hve to upgrade to NAV 4.0 SP3 while i m still working on SQL 2000 and then move to SQL 2005 ?

wat are the steps involved ? can u explain !!

Regards

Nitin

11

Hi,

We made a move from SQL2000 to SQL2005 and an upgrade from NAV 3.7 to NAV 5.0.

We installed a new server with SQL2005 and NAV 5.0.

I have made a backup in NAV 3.7 (.fbk) and restored this file in an empty database NAV 5.0 (be sure the codeset of the database is the similar to the old one).

Afterwards I used the toolkit of Microsoft to make the upgrade to NAV 5.0.

On April 14th we started to use the new database with SQL2005.

We had large performance problems. Finally we have installed a performance tool and hired a SQL-specialist to solve our problems. The users are mostly satisfied at this moment… The work of the specialist is not finished yet, we want to make some further improvements.

I was informed afterwards that SQL2005 is quite different database as SQL2005, so be aware of the fact that you run in to some (serious) performance problems. Our problem was that the disk activity was much too high. In the test we only used two disks instead of a disk cabinet. When we installed the disk cabinet the problems were not solved.

I wish you a lot of succes with your migration to SQL2005.

Annette

12

Hey Nitin,

We need to do a similar move from Navision 4 + SQL 2000 to NAV 4 + SQL2005

Can you please let us know what were the steps you carried out.

Thanks

13

Hi.

If You are moving to SQL2005 You should really consider upgrading Your executables to 5.0 (and probably 5.0SP1 even though the SP1 is quite new). There have been several issues with NAV and SQL2005. Hopefully many of them are resolved in NAV 5.0 SP1 (but I would love to hear from real life situations about that).

If You are planning to stay on NAV 4 and upgrade SQL to 2005 You need to use the latest hotfix for NAV 4. You should also be aware of the implications of Index Hinting that is used in that version.

14

Yes I couldnt agree more. There are so many updates hotfixes etc or 4.00 SP3 tht I have lost count. I am vrey happy running 4.00 executables on 5.00. And so far tests show that SP1 is also quite nice.

15

Hi all, a customer moved from SQL2000 with NAV 4.03 into SQL2005 with NAV 5.00 SP1. We have a lot of performance problems. In particular the problems arise when users do a simple query on a single or more than one field (F7). The forms on which they apply filters have no code inside (and they do not set a specific key or filters in the properties) and are exactly the same as the previous version. Unfortunately the same old filters applied in the new system are impossible to do due to the very long time (sometimes 40 or 50 seconds for a very easy filter!).

Have someone experienced the same problem?

Marco