Upgrading to 4.0 SP3

illug

Hi ...

How much of an operation is it to upgrade from SP2 to SP3? The security issues in SP2 are driving my nuts after just four days of running on SQL2005.

Regards,
Gulli

ara3n

It involves uninstalling the old client, and installing the new client. WHen you login the first time it will ask you if you want to convert the db. click yes.
MAKE sure you login as SA when you are logging in for the first time with Sp3 client.

kine

And the "how to switch sec. model" is described in the document "Security Synchronization in Navision 4.0 SP3.doc"

ssingla

It involves uninstalling the old client, and installing the new client. WHen you login the first time it will ask you if you want to convert the db. click yes.
MAKE sure you login as SA when you are logging in for the first time with Sp3 client.

Will it do the technical upgrade? I mean the modification/bug fixing in SP3 will be done in the SP2 database or not.

illug

Does it do any major conversion to the database? I am wondering how long it will take.

My database is 140 GB.

Mbad

Since there is no datatransformation, it should be fast, but im really no sql guru. Probably takes as long as updating indexes.

illug

I tried it on a small database and it definately does something to all the tables. Have not seen this step on other upgrades.

It took longer with larger table as expected so I am curious as to how long it will take on a 140GB database.

kine

In my opinion the SQL Triggers are recreated (SIFT optimalization), Clustered indexes are corrected...

DenSter

Clustered indexes are left alone, that is not fixed by a different executable, since it is part of the table definition.

I copied a SQL Server SIFT trigger for SP1 versus SP3 and did not find any differences either, so I don't know for sure what they are talking about when they say 'improved SIFT logic'.

kine

Hmmm, and I thought that this is the change... :-s

illug

Well, at least it will give me the Standard Security option.

Does anyone have experience with migrating from SP1/SP2 to SP3 and how long it takes?

ara3n

DenSter wrote:

Clustered indexes are left alone, that is not fixed by a different executable, since it is part of the table definition.

I copied a SQL Server SIFT trigger for SP1 versus SP3 and did not find any differences either, so I don't know for sure what they are talking about when they say 'improved SIFT logic'.

Did you take a look at SQL Triggers on the Item ledger for example?


About the clustered indexes, the new client by default makes PK clustered, eventhough it is not changed in the object.
In 5.0 they've changed all the tables.

DenSter

ara3n wrote:

DenSter wrote:

Clustered indexes are left alone, that is not fixed by a different executable, since it is part of the table definition.

I copied a SQL Server SIFT trigger for SP1 versus SP3 and did not find any differences either, so I don't know for sure what they are talking about when they say 'improved SIFT logic'.

Did you take a look at SQL Triggers on the Item ledger for example?


About the clustered indexes, the new client by default makes PK clustered, eventhough it is not changed in the object.
In 5.0 they've changed all the tables.

I thought that would happen, so I took a SP1 backup, created a SP3 database and restored the SP1 backup into it.
- None of the indexes were set to clustered.
- There were no differences compared to the SQL Server trigger code of the SP1 database, and I checked a number of tables that have sumindexfields.

Maybe this is because I only have SP3 actually installed, but still that shows that the new executables do not set the PK to clustered, that it is in fact part of the table definition.

Simply restoring into a SP3 client does not set the clustered index, in my experience.

kriki

[Topic moved from Navision forum to SQL General forum]

toor

Hello guys,

what is the fastest way to update a nav 3.60 db (SQL 2000) to nav 4.0 SP3 (SQL 2005)?

I have to update a ~500 GB db, so i think i really will run in time problems with the update sift "problem" in 4.0SP3.

Wich way have you choosen for the big dbs?

regards
tobias

Sorcerer

hi tobias,

why do you think that? have you tried to convert the database in a test system?

we converted form 4.0 sp1 to sp3 without any time difficulties (in some minutes).

but i would not run such a(ny) big database to 2005 if it has not to be (because of the BIG performance problem of sql 2005 with navision)

Marije_Brummel

If you are running on 2000 without issues, then please stay.

The best way to migrate is always via backup and restore.

BTW, I always get a little itchy here and there when people are compaining about performance while there are solutions.

toor

Hi,

thanks for your feedback.

I've updated a 3.60/sql 2000 test db by attaching the sql 2000 db to an sql 2005 and then open the db with Nav 4.0 SP3. I've watched the update process with profiler and have seen the rebuilding of the sift tables. And it took much more time then an update from 4.0 SP1 to 4.0 SP2 for example.

I've also tested with Nav 4.0 SP2 HF3, with the same result.

And i remeber the same behaviour in an scenario Nav 4.0 SP<3 -> Nav SP3.

Can someone confirm this behaviour? If in Sorcerers scenario the rebuild didn't take place, is there a way to update to 4.0 SP3, but with the old triggers?

regards
tobias

ara3n

no it's not possible to use sp3 with the old trigger.

You could copy the triggers from the existing tables before upgrade and put them back in, but I wouldn't recommend it.

Sorcerer

Mark Brummel wrote:

BTW, I always get a little itchy here and there when people are compaining about performance while there are solutions.

hi mark,
yes? i and even mbs would be very interested in hearing this solution(s).
until now there is no chance to get a performant (big) system with sql 2005.
regards

Marije_Brummel

Well, what I found out so far is that it seems to be influenced by clustered indexes that are long or start with option fields, dates in the index can also cause the issue.

What also helps is changing C/AL code with SETCURRENTEY and SourceTableView.

Most of the time, budget is the biggest issue. Playing with the C/AL code indexes, changing into more selective, changing the clustered index is very time consuming but can solve it sometimes.

Another solution can be to add a new field that get's populated somewhere in the process and use that to filter instead of a whole bunch of fields.

Sometimes I see filters with more than 10 parameters and people ask me for a selective index. :?

I must agree that it is a very very anoying issue and it's a stupid change from 2000 to 2005.

At the moment I am working on a very interesting database. When there is information to share I will try to put it into some kind of Blog.

Sorcerer

hi mark,

thanks for the update. this are the things we have seen so far.
yes it is VERY time consuming (we test since september last year).

so we will look forward what will come next.

DynamicUser

Hi,
Can anyone tell me how to upgrade from Navision SP2 to Navision SP3? I just completed upgradation from navision 3.7 to navision 4.0 SP2.

Best Regards

Marije_Brummel

Only thing you have to do is merge the objects you have modified and import them into your database.

There are no datamodel changes in servicepack, thank MS for that.

Do not forget to also upgrade your runtime after that.

Good luck.