Migration from 4.0 to 5 client fails

Stef-B

We have a Database (SQL on 2005 server) with >120 Companies.
Migration to 5.0 SP1 client fails after several hours with SQL Error stating that there is not enough system memory.

Is there a way to perform the migraton process on sql-server only?
Does anyone have a tsql script, so i can split the process into several transactions?

What does the Migration process do anyway?

Marije_Brummel

To SP1 it does a lot!

It converts all SIFT tables to indexed views.

With 120 db's it can be a total nightmare!

Since the restore still behaves like native it will create a copytable for all companies.

For 120 companies you will have 120* 700 * 2= 172000 tables.

Try the following:

Make all empty tables data per company false and restore on a x64 server with a lot of disks and RAM.

I have a customer with 2000 companies and they used XML import/export for that :?

David_Singleton

Mark Brummel wrote:

...

I have a customer with 2000 companies and they used XML import/export for that :?

:shock: wow that must have been fun. sounds like a big job.

kine

And what about making NAV backup on old version, and restore it into target database on new version in more steps (e.g. 10 companies in one step...)?

Marije_Brummel

kine wrote:

And what about making NAV backup on old version, and restore it into target database on new version in more steps (e.g. 10 companies in one step...)?

The first 10 will go fine, then the next 10 will start to itch a little, slowly but surely it will come unmanageable...

Sharing tables really is the best approach. Make sure all SIFT is disabled, indexes etc.

bbrown

Is the error referring to lock memory?

Place the database in "Single User" mode and run the convert. "Single User" mode does not place locks on resources and therefore does not run out of memory. I ran into this recently on an upgrade. The server was 64 bit with 24 GB of memory. Single user mode resolved the issue.

ara3n

Mark Brummel wrote:

kine wrote:

And what about making NAV backup on old version, and restore it into target database on new version in more steps (e.g. 10 companies in one step...)?

The first 10 will go fine, then the next 10 will start to itch a little, slowly but surely it will come unmanageable...

Sharing tables really is the best approach. Make sure all SIFT is disabled, indexes etc.

I think this should a standard feature.
Basically based on your license NAV should create the tables SIFT/Views perCompany only based on license.

davmac1

There is a fix/method available for restoring a Navision database into SQL Server where there are a huge number of companies. This is especially a problem on 32 bit SQL Server 2005.
Since I got it directly from Microsoft, I suggest you contact Navision support and request it.
It does require some SQL knowledge and you have to be careful.

bbrown

davmac1 wrote:

There is a fix/method available for restoring a Navision database into SQL Server where there are a huge number of companies. This is especially a problem on 32 bit SQL Server 2005.
Since I got it directly from Microsoft, I suggest you contact Navision support and request it.
It does require some SQL knowledge and you have to be careful.

Could you provide some additional information about this fix/method? Do you have the article number?

The fix for 32 bit SQL? Install 64 bit.

Stef-B

Thank you all for the suggestions. We're going to migrate the LIVE Version of the DB soon. Let's hope this works smoother.

kriki

[Topic moved from Navision forum to SQL General forum]