Restoring backup on SQL 2K5

ara3n
ara3n Member Posts: 9,258
edited 2007-03-17 in SQL General
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?
Ahmed Rashed Amini
Independent Consultant/Developer


blog: https://dynamicsuser.net/nav/b/ara3n

Comments

  • nunomaia
    nunomaia Member Posts: 1,153
    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
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ara3n
    ara3n Member Posts: 9,258
    They are on 4.0 sp3. So I can't upgrade to any latest hotfixes.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • DenSter
    DenSter Member Posts: 8,307
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • ara3n
    ara3n Member Posts: 9,258
    They use GL granuale. No addon. 3-4 hours to just delete the tables is way too long.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • nunomaia
    nunomaia Member Posts: 1,153
    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.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ara3n
    ara3n Member Posts: 9,258
    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?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • DenSter
    DenSter Member Posts: 8,307
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • nunomaia
    nunomaia Member Posts: 1,153
    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 :-)
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • ara3n
    ara3n Member Posts: 9,258
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Waldo
    Waldo Member Posts: 3,412
    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 :wink:

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog

Categories