Database migration 3.7 to 4.0

anuragatish

hi friends,
one of our client is using nav 3.7 . the database size is around 55GB and have 150 concurrent users across 42 locations.

we face repeatedly locking problem in the system during month end. uesrs either end up in deadlock or wait for more than 10 min to post.

Microsoft suggested to go for 4.0 as the locking mechanism is changed drastically there.

my doubt is ,

1. If we just open the 3.7 database in 4.0 client , we the locking mechanism of 4.0 take affect. as we are not going to use any new features of 4.0. the only reason for migration is locking problem.

2. what does that automatic database conversion means when we open the 3.7 database in 4.0.

3. The locking is driven by C/SIDE code or some inbuilt system code.

thanks and regards
Anurag
Bluestar Infotech
India

kriki

First : are you on SQL- or Navision-DB? In case of a Navision DB, it won't change anything.

1)
To avoid lockings, there are different strategies to follow:
A) diminish the locking time of the processes. This can be done by increasing hardware, changing the programs, using better indexes, using fewer indexes, ... How to proceed depends largely on which DB you are using.

in case A) is not enough, you have to check which processes go in deadlock. These processes you have to change to avoid deadlocks. An easy trick is to let them lock the same record before doing anything else.

2) it means that Navision does some changes in the DB. After that it is not possible anymore to open the DB with the old version. Remember to make a backup before doing this.

3) Locking depends on both.
-C/AL : it depends where a LOCKTABLE is put.
-inbuild : this is more for SQL. It depends if Navision does record-locking or page-locking. Record-locking is slower because each record must be locked separatly by SQL. But sometimes it is better to avoid deadlocks because page-locks sometimes locks records that are not part of the current process.

anuragatish

we are using sql 2000.

please clarify this one doubt....

if we convert the 3.7 db by opening in through 4.0 client , whose locking mechanism will take effect , 3.7 or 4.0 ??

as locking mechanism of 3.7 is table level and 4.0 is record level , it will help in reducing the deadlock significantly..

Waldo

It's always better to run on the new client. If going to 4.0 ... be sure to go to 4.0SP3, else you're in a whole lot of mess regarding security as well: be sure to use the "standard" security model.

Anyway ...

4.0 indeed produces "other SQL statements" ... better for SQL Server. That's one thing. BUT ... if this is going to be better for your locking, that's something I can't confirm.
This is what I can confirm, and that might be interesting for you:
- from 4.0 SP1: there are new instructions (FINDSET, FINDFIRST, FINDLAST) to use SQL Server more efficiently. Using these instructions in the right way can case a significant performance gain. You have to implement this in your code, so only converting the database will not benefit by these instructions.
- from 4.0 Sp1: you can "alter your database" and set "Always Rowlock". From help: This setting allows you to specify that Navision always places row-level locks instead of page- and table-level locks. It already solved me some locking problems.

Furthermore my own recommendation:
To solve locking, I always use the SQLPerform Tools (www.sqlperform.com). They have tools (and a methodology) to monitor lockings and blockings. Thanks to this, you can have a clear picture on where your problems are, and you might be able to solve the lockings in your database.

diptish.naskar

my doubt is ,

1. If we just open the 3.7 database in 4.0 client , we the locking mechanism of 4.0 take affect. as we are not going to use any new features of 4.0. the only reason for migration is locking problem.

2. what does that automatic database conversion means when we open the 3.7 database in 4.0.

3. The locking is driven by C/SIDE code or some inbuilt system code.

Hi,

If you are facing the locking issue, only converting(rather opening the 3.7 db in 4.0) the db directly won't help you, the automatic database conversion is, it just updates the executables and the build for the same.

If the locking problem in your case is happening through the code then I would suggest you to use the SQL server resource kit, this will help you in indentifying the various locking problems. You can also use the sqlperform.com as stated by waldo....well i have not tried that but wish to do so in future.