Restoring backup on SQL 2K5

ara3n

Hello
Client has 50 companies on sql. Restoring a company from Nav backup (12K) goes through the process and at the end after it deletes the tables in million range, it hangs for several hours and then finishes. Anybody has seen this or knows why this happens?

nunomaia

The only problem common to you, was restoring a database in SQL 2005 and hanging and the end of process.

Upgrading the client with the lasted hotfixes solved that random issue

ara3n

They are on 4.0 sp3. So I can't upgrade to any latest hotfixes.

DenSter

I can only guess that it just takes a long time to get rid of that many tables. With at least around 1000 tables per company (plus hundreds per company more if they have one of the verticals, or heaven forbid Eship or Lanham HM) that's at least 50 thousand tables to get rid of.

ara3n

They use GL granuale. No addon. 3-4 hours to just delete the tables is way too long.

nunomaia

Don't forget that there is several ways to deleted tables, in worst case all table data is copied to transaction log and then is really deleted.

ara3n

hmm they could temporarily change to simple recovery model.

During the restoration, does navision create 1K tables for every company?

So If you restore 10 companies, you'll have 10K tables created and deleted?

David_Singleton

ara3n wrote:

hmm they could temporarily change to simple recovery model.

During the restoration, does navision create 1K tables for every company?

So If you restore 10 companies, you'll have 10K tables created and deleted?

Yep.... Sad isn't it. How about a solution where there is one database that stores Objects, and all "Common to all companies" tables, then store each company in a seperate SQL database. Then during restore, process and "commit" each company individually?

PS I did suggest that to the NAV team, but they really could not see any reason for it. Why not try again next week when you are there. The more times they hear it the more likely they are to listen.

DenSter

ara3n wrote:

They use GL granuale. No addon. 3-4 hours to just delete the tables is way too long.

Yeah that is way too long. I'd say there must be something fishy about the computer that is used, of the server is busy with something, maybe users in other companies doing some batch processing. A wireless network connection somewhere.

nunomaia

David Singleton solution it's a good way to increase performance restore in SQL Server when we have huge databases. I doubt it that Microsoft will change the restore method, but pressuring MS it's a good start :-)

ara3n

The backup tables could also have company name as pk that way you would have only 1K created and deleted.

with multiple db for each company it can make reporting a lot easier across multiple companies. You would just write one SQL report and it could run on every company.

I'm sure that the enhanced Security model will take faster to sync. Navision will sync it for one company and then copy that role across all the other db's.

On SQL you could backup each company separately.

You could keep company independent tables in the Object database. Or you would have to write to multiple database if you keep the tables in each company.

You could separate the db in separate disk arrays so performance can be improved.


What would be the downsides?

Also how does axapta handle multiple companies.

Waldo

ara3n wrote:

Also how does axapta handle multiple companies.

Axapta has in every table some kind of company ID.
I heared an SQL guy at convergence complaining about this ... saying that seperate tables was much better... . I guess the backup/restore is one downside, but the fact that transactions/read spead is generally faster makes up for it