Navision 3.70 - SQL DB Restore Problem - Need Advice

Nusky

Hi all

We are planning to migrate to SQL our native database and are doing a few tests of performance, and are having a lot of problems. The biggest one we've seen is in restoring. When we try to restore a Company to an empty Navision SQL Database, it lasts for about 2 minutes. That's ok. But when there are already more companies, the time increases a lot. For example, when we restore the same company but with ten in the database, the restore time goes to 5 minutes, and when we have 50 companies, time goes to ours. As we have about 600 companies in our production database, this can be a great problem.

Anyone knows anything about obtaining a better performance, or what to do to obtain good results? Maybe we have something wrongly configured, but don't know what to look at, everything looks ok and the SQL Server works smoothly.

Thanks for your help.

Waldo

How many tables were there in navision? About 700? Multiply this by 600 companies ... I think even SQL Server has got problems when handling that number of tables in one database ... . I suggest you use a decent server for it.

May be you can try to divide it into seperate databases?

Nusky

Thanks for your quick answer. I know maybe 600 companies can be a lot of tables for SQL, but even with 50 companies the performance goes down a lot. I think we may have something badly configured, as I can't believe that Navision behaves so badly with SQL.

We will probably end up dividing the database, but even in that case the performance needs to be better.

Our SQL server has 4GB RAM, 4x 2GHZ processors and tons of disk space, so I don't think this can be the problem.

facade

SQL demands all the power it can get so - I think you should have at least four processors with 1 Mb cache/per each minimum - the main issue in your case is IO thruput - enormous amount of read unpack write transactions.

Are you using SQL backup or Navision backup?

Nusky

I'm using Navision backups, sql backups work normally.

radek.bb

Hi
May I ask why you have 600 companies?
Sorry for asking - I am just curiois about it

R.

Nusky

I would answer if I could, but I really don't know it. I'm the one that has to make it work, that knows what we have, but don't know why. Just have this and have to make it work smoothly

But it's really driving me crazy!

fb

Ok, I'm going to go way out on a limb, and take a wild stab at what is happening here...

(Careful, guessing follows...)

When the C/SIDE restore function restores a table in SQL, it first disables all indexes except the primary key, so that the table restore occurs at maximum speed. Then, when the table is restored, it re-enables the secondary keys, rebuilding them in the process. You can observe this behavior by following the progress dialogs during restore...

(Be even more careful... really wild guessing occurring now...)

It seems, however, that it is not just the indexes for the 'current' company that are being dropped/re-created. Instead, C/SIDE is doing the equivalent of a 'table re-design', which involves disabling and then re-enabling/rebuilding the alternate indexes for ALL companies... This explains (?) why restoring many companies one-at-a-time exhibits exponential runtime.

If this wild guess is correct, then there are two possible workarounds:

1) Don't restore one-at-a-time. It may be that restoring all companies at once will only do the alternate index rebuild once for all companies.

2) If you must restore one-at-a-time, then try the following:

• Restore the first company.
• Disable all secondary keys in all 'substantial' tables.
• Restore each of the remaining companies.
• Re-enable the disabled keys.

Nusky

Hi, my guessing was in the same line as yours, fb, and I have been looking at different approaches about the system tables in SQL and things like, but haven't discovered anything at the moment.

We don´t plan to do the migration with one company at a time, the problem comes when the database is in production. Users usually backup companies, work with them locally, and then restore them in the server, thats why we need this to work ok.

If your guess is right I think we have a problem :?

I'll try something similar to your second workaround and see what I can do.

Thanks!

fb

Our SQL server has 4GB RAM, 4x 2GHZ processors and tons of disk space, so I don't think this can be the problem.

Just a few follow-on queries re: your SQL configuration...

• You are using RAID 1 or 1+0 over many, smaller disks, right?
• You have configured your db space in multiple files, right?
  (see http://blogs.msdn.com/mat_stephen/archive/2005/02/02/365325.aspx)
• You are not relying on auto-grow, right?
  (see http://blogs.msdn.com/Mat_Stephen/archive/2005/02/02/365370.aspx)

[edit -- fixed busted links -- close-paren was incorrectly included in auto-link]

Waldo

When working with one database and a whole bunche of companies, you have the disadvantage that you have one big database file (which you can seperate, of couse ...).

May you can think of dividing that one big database into seperate databases?

Companies that are not connecten to one way or another ... shouldn't be a problem ... .

Nusky

Our disks are in a SAN, so info is in a lot of disks.

We have only one datafile and we have auto-grow, so maybe this can be on of the problems. I'm going to do some more tests changing this.

Thanks again for your help!

Nusky

Well, have tried different settings, tried spliting the database different files, tried reducing the amount of log writting, tried some memory optimization, tried to make a trace and optimize, but nothing seems to works, restoring times continue the same.... :?

Thanks to all for your help and advice anyway!

Wiechardt

Sorry Trusky, I wanted to suggest setting the "Recovery model" on the Navision SQL database to "Simple" instead of "Full", but then I read your last post and realised that you've already played around with the amount of log writing...

:-#

fufikk

You also have the Enterprise Edition, right?
Standard Edition will use max. 2GB of RAM no matter how much you actually have installed. And there are other performance limits put on non-Enterprise Edition.

ara3n

this might help.
http://www.mibuso.com/forum/viewtopic.php?t=9228&highlight=