Rename and Restore a Company in SQL2000

whyaskqcn
whyaskqcn Member Posts: 22
edited 2008-10-29 in NAV/Navision Classic Client
Hi,

I am running Nav 4.0 SP1 on SQL2000 using Windows authentiation. Due to some integration work between Navision and another SQL database, we need to rename and restore company in a live database.

However, the rename and restore is gradually taking longer and longer hours. There are about 9 companies in the database. This morning, the rename took 3 hours and it is still running.

Can anyone advise how and where I can fine-tune Navision or SQL so that the rename and restore is within reasonable timing? Backing up the company takes about 30 mins.

Thank you.

Comments

  • kriki
    kriki Member, Moderator Posts: 9,121
    In Database=>Alter=>Tab Options: put recovery model to simple.
    It might also help to do a truncate log after the restore.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • whyaskqcn
    whyaskqcn Member Posts: 22
    Thanks, Kriki.

    I done a SQL restore on another server and the RESTORE performance improve by 15mins after putting recovery model to simple, and truncating logs. The performance improve by 15 mins. No much difference with running on single-user.

    However, if I restore the company to Native, and then did another backup to restore onto a new SQL database, the RESTORE and RENAME was pretty fast.

    There were few tables with Data Per Company = No. Does this contribute to the performance on the RENAME and RESTORE process.

    Is there anyway I can check out what the RENAME does?

    I did consider upgrading to SP2 to test out the performance but will it help to improve the RENAME and RESTORE process? There were already customisations done in SP1.
  • kriki
    kriki Member, Moderator Posts: 9,121
    To know what the RENAME does on SQL, you can use the Client coverage of Navision.
    For speed : the best generally is :
    -put objects in a Navision DB and remove secondary keys in the big tables.
    -backup a company in SQL
    -restore the company in local DB
    -rename in local DB
    -backup a company in local DB
    -restore the company in SQL
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • DenSter
    DenSter Member Posts: 8,307
    I have seen a company rename on a 300 GB database finish without a problem in just a few minutes. It was a very expensive and advanced server machine, but on good hardware a company rename should not be a problem. For sure it is worth your time to try it in a native database to test if it makes a difference.

    After each restore/rename do a full SQL Backup, which should clear up the TL. Shrinking the files won't do any good because those will be expanded again during the next restore/rename anyway, so I would leave those alone.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • 2tje
    2tje Member Posts: 80
    The Company Name is part of the primary key.
    Is it true that SQL is updating the keys after every record is renamed?
    Is there a way to turn this off?
  • whyaskqcn
    whyaskqcn Member Posts: 22
    Thanks to all of you for your help.

    What Kriri suggested on doing the rename using Native database and then restore into SQL indeed help to reduce the time taken significantly despite the inconvenience.

    Have conducted few rounds of testing and was surprised to find that renaming a newly created empty company in SQL takes just about as long. Can only conclude that the number of companies in the SQL database indeed contribute to the performance.

    I guess there is not much alternative at the present moment that SQL users who encounter slow performance in rename/restore will need a Native database to speed up the rename/restore process.
  • kriki
    kriki Member, Moderator Posts: 9,121
    2tje wrote:
    The Company Name is part of the primary key.
    Is it true that SQL is updating the keys after every record is renamed?
    Is there a way to turn this off?
    No, the company name is stored in the tablename and NOT INSIDE the records.
    So a company-rename just renames the tablenames.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • gregabor
    gregabor Member Posts: 8
    kriki wrote:
    To know what the RENAME does on SQL, you can use the Client coverage of Navision.
    For speed : the best generally is :
    -put objects in a Navision DB and remove secondary keys in the big tables.
    -backup a company in SQL
    -restore the company in local DB
    -rename in local DB
    -backup a company in local DB
    -restore the company in SQL
    Hi,
    yes I know i bumped an old topic, but i'm still stuck with this problem ...
    We have about 20 companies in Navi 3.10, SQL database has 100 GB. With few less companies, renaming took a couple of hours. Adding a company by copying and renaming took 15 hours. Adding yet another company - and now we are at 25 hours that take for renaming. I'm afraid we can't afford to add another company, as we have no free timeframe for renaming process.
    Restoring a company is not working anymore, too, it just closes the Navision with the company created (tables made), but no data inside?!

    So my questions:
    1. anyone have any tips for restoring a company, regarding the text above?
    2. kriki mentioned "put objects in a Navision DB" ... how to do that? And what is supposed to be "a local DB"?

    Thanks everyone :mrgreen:
  • kriki
    kriki Member, Moderator Posts: 9,121
    1. For renaming, you can best first backup a company. Restore it in a NAV-DB. Do the rename there. Make a backup and restore it back in SQL.

    Another possibility for you would be to do a very good analysis of the tables that are not used IN ANY COMPANY.
    In these tables, you can put the table-property DataPerCompany=No. In this way all companies use the SAME table (remember NO COMPANY should use them).
    An example: Let' say you don't use table 6:"Customer Price Group in ANY company. But that table is created for each company => in your case 20 tables that are doing nothing but they take a lot of time to be renamed. So if you put the table-property DataPerCompany=No, there will be ONLY 1 table. Your DB diminishes with 19 tables!
    Of course (and I repeat it again!) YOU HAVE TO BE SURE NO COMPANY USES THAT TABLE!

    And if you consider using it in the company, you will need to put the table-property DataPerCompany back to Yes.




    2. Import them into the DB :mrgreen: Local DB = NAV DB and not a SQL DB.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • bbrown
    bbrown Member Posts: 3,268
    The indexed views in 5.0 SP1 increase the time to rename companies. In particular with larger databases. We have one site where we update a testing database from live about every 6 weeks or so. The database is 175 GB (110 data) and takes about 20 minutes to backup and a bit over an hour to restore. But then the rename is another hour or so. Under 4.0 SP3 this rename was about 3 minutes.
    There are no bugs - only undocumented features.
  • gregabor
    gregabor Member Posts: 8
    kriki wrote:
    2. Import them into the DB :mrgreen: Local DB = NAV DB and not a SQL DB.
    Yes, of course if I'd knew that native db and SQL are so transparent in Navision, i'd be happy long ago. As this took me awhile, i can only say this: i really own you a beer, so see you in 196 days :)
    Thanks for the tip!

Categories