Database migration 3.7 to 4.0

anuragatish
anuragatish Member Posts: 48
edited 2007-06-04 in NAV/Navision Classic Client
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




Anurag atish
Navision Technical Consultant
Bangalore
anurag.atish@yahoo.com

Comments

  • kriki
    kriki Member, Moderator Posts: 9,121
    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.

    B) 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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • anuragatish
    anuragatish Member Posts: 48
    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..
    Anurag atish
    Navision Technical Consultant
    Bangalore
    anurag.atish@yahoo.com
  • Waldo
    Waldo Member Posts: 3,412
    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.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • diptish.naskar
    diptish.naskar Member Posts: 360
    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.
    Diptish Naskar
    For any queries you can also visit my blog site: http://msnavarena.blogspot.com/

Categories