Migration from 2.2 To SQL 2008

Toddy_Boy · 2011-10-07

Hi

We currently have a very old 2.2 vserion of the Navision objects, nothing wrong in that, which we are moving to a SQL database. The current database size is 75000000 of which 59507360 is used.

It's a bit of a broad ask but when this is converted to SQL, what would the recommnded size of the ndf, mdf and ldf be? I've done a few test run throughs that have stuttered as the database grows. Or is it the case of let it run through once, then set the ndf, mdf and ldf to the final sizes, plus a bit for growth

Steve

Marije_Brummel · 2011-10-07

What is 2.2?

I know of 2.0, 2.01, 2.5, 2.6 etc.

It is a 60GB database? The SQL databasesize should not differ much.

During the conversion of native to sql the logfile wil grow a lot, you can shrink it afterwards.

Toddy_Boy · 2011-10-10

Hi Mark

Well spotted, it' 2.01.B

Thanks for the reply.

Steve

Marije_Brummel · 2011-10-10

2.01B should work fine in SQL2008 if you use the latest finsql.exe binaries. I have some 1.x and 2.x customers running on SQL with larger databases than 60GB and no issues that can't be solved easily.

Toddy_Boy · 2011-10-10

Hi Mark

We've done a lot of testing with the converted database, the most common problem is the error "Another user has changed this record, restart your activity", this is due to poor coding in the very bespoke part of the system.

Steve

Marije_Brummel · 2011-10-10

This error is most often caused by the change from table locking to row locking. With table locking your transactions where probably isolated before they could create this conflict.

The "easiest" way to solve this is by re-introducing the isolation by implementing a common lock on for example a setup-table or as NAV does is, using the last G/L entry.

The "toughest" way to solve it is by analysing the transaction step-by-step and try to find a way to solve the real issue.

Toddy_Boy · 2011-10-10

Hi Mark

I've been tackling this by making sure records are saved and re-read at the correct points, this is time consuming due to the amount of code I have to go through.

Can you elaborate on the "easiest" way

Steve

kriki · 2011-10-10

Mark Brummel wrote:

This error is most often caused by the change from table locking to row locking. With table locking your transactions where probably isolated before they could create this conflict.

The "easiest" way to solve this is by re-introducing the isolation by implementing a common lock on for example a setup-table or as NAV does is, using the last G/L entry.

The "toughest" way to solve it is by analysing the transaction step-by-step and try to find a way to solve the real issue.

I have done this once for a customer with lots of deadlocks on the reservation table.
I created a codeunit with these 2 lines of code:

GeneralLedgerSetup.LOCKTABLE;
GeneralLedgerSetup.GET();

Then I put in a lot of places where I had problems this code: CODEUNIT.RUN(CODEUNIT::"Your Codeunit");

In the end, I solved the problems in a cost-effective way (I hadn't time to rewrite all the code to avoid the deadlocking).
As a positive surprise, I found out that everything worked faster than before, telling me that the deadlocking problem was even worse than I suspected.

Toddy_Boy · 2011-10-10

Hi Kriki

I can see what you've done there, very clever, however my problem is when a record is changed in more than one place during the same transaction. This leads to SQL reporting the record has been amended elsewhere (as I understand it). So it's not a deadlock as such, just some bad coding.

Steve

David_Singleton · 2011-10-10

Toddy Boy wrote:

Hi Mark

We've done a lot of testing with the converted database, the most common problem is the error "Another user has changed this record, restart your activity", this is due to poor coding in the very bespoke part of the system.

Steve

Which executables are you using?

kriki · 2011-10-10

Toddy Boy wrote:

Hi Kriki

I can see what you've done there, very clever, however my problem is when a record is changed in more than one place during the same transaction. This leads to SQL reporting the record has been amended elsewhere (as I understand it). So it's not a deadlock as such, just some bad coding.

Steve

The resulting error is different, but the base problem is the same and so is the solution.

Howdy, Stranger!

Quick Links

Categories

Migration from 2.2 To SQL 2008

Comments