mibuso.com

Microsoft Business Solutions online community
It is currently Mon Nov 24, 2014 2:47 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 3:36 am 
Offline

Joined: Thu Aug 14, 2008 5:51 am
Posts: 11
Location: Brisbane
Country: Australia (au)
I am preparing for a migration from Nav 4.0sp2 to NAV 2009.

I have backed up our live database (SQL 2005) and it has created 29 fbk files total 56.6gb. Our database has 4 companies. The backup took 3 hours. I can live with that.

Destination server is a DELL R7100 - 64bit dual quad core server with 40gb ram. Server 2008 64bit OS. 1gb netcard.
(dedicated no other apps installed or running or any other databases)
Raid config: 2 controllers 3 arrays.
Controller 1 array 1 raid 1 - 2 disks 15k rpm - OS
Controller 1 array 2 raid 1 - 2 disks 15k rpm - SQL logs
Controler 2 array 3 raid 10 - 12 disks 15k rpm - SQL database files

NAV client running on a DELL R7100 - 64bit dual quad core server with 18gb ram. Server 2003r2 32bit OS. 1gb netcard.
(dedicated no other apps installed or running)
Raid config: 1 controller 2 arrays.
Controller 1 array 1 raid 1 - 2 disks 15k rpm - OS
Controller 1 array 2 raid 1 - 2 disks 15k rpm - NAV backup files

Both servers are on the same network switch.

Restore has been running for 24 hours and only up to disk 50% of disk 10, 67% total database. This is far two slow!!!!!

I have read many posts but can't seem to find any obvious problems.
CPU utilisation is 3%.
Network utilisation is 6mbps

What am I missing. Database properties are listed in code below.

Code: Select all
CREATE DATABASE [NAV40_Test] ON  PRIMARY
( NAME = N'NAV40_Test_Data', FILENAME = N'E:\Company\MSSQL\Data\NAV40_Test_Data.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [Data Filegroup 1]  DEFAULT
( NAME = N'NAV40_Test_1_Data', FILENAME = N'E:\Company\MSSQL\Data\NAV40_Test_1_Data.ndf' , SIZE = 50GB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'NAV40_Test_Log', FILENAME = N'd:\Company\MSSQL\Data\NAV40_Test_Log.ldf' , [color=#FF0000]SIZE = 100MB[/color], MAXSIZE = 2048GB , [color=#FF0000]FILEGROWTH = 10GB [/color])
GO
ALTER DATABASE [NAV40_Test] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [NAV40_Test].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [NAV40_Test] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [NAV40_Test] SET ANSI_NULLS OFF
GO
ALTER DATABASE [NAV40_Test] SET ANSI_PADDING OFF
GO
ALTER DATABASE [NAV40_Test] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [NAV40_Test] SET ARITHABORT OFF
GO
ALTER DATABASE [NAV40_Test] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [NAV40_Test] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [NAV40_Test] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [NAV40_Test] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [NAV40_Test] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [NAV40_Test] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [NAV40_Test] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [NAV40_Test] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [NAV40_Test] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [NAV40_Test] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [NAV40_Test] SET  ENABLE_BROKER
GO
ALTER DATABASE [NAV40_Test] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [NAV40_Test] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [NAV40_Test] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [NAV40_Test] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [NAV40_Test] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [NAV40_Test] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [NAV40_Test] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [NAV40_Test] SET  READ_WRITE
GO
ALTER DATABASE [NAV40_Test] SET RECOVERY FULL
GO
ALTER DATABASE [NAV40_Test] SET  MULTI_USER
GO
ALTER DATABASE [NAV40_Test] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [NAV40_Test] SET DB_CHAINING OFF
GO


Cheers

Charles ](*,)


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 4:00 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9072
Location: 3rd rock from sun
Country: United States (us)
Hello

One suggestion, run the client on the sql server on same machine and restore the database. Also have the fbk available. Put them on drive where the OS is.

Also I suggest to get the 2009 Sp1 which will be released in 3 weeks. They have added some performance features for sql 2008

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Last edited by ara3n on Wed Aug 05, 2009 4:10 am, edited 2 times in total.

Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 4:08 am 
Offline

Joined: Thu Aug 14, 2008 5:51 am
Posts: 11
Location: Brisbane
Country: Australia (au)
Thanks but can't do that. 64bit OS and can't install MDAC. I have already tried this option.


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 4:09 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9072
Location: 3rd rock from sun
Country: United States (us)
you don't need to install the client. just copy the client folder and run the finsql.exe

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 4:27 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9072
Location: 3rd rock from sun
Country: United States (us)
Also, have you installed SP1 for sql 2008?

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 5:05 am 
Offline

Joined: Thu Aug 14, 2008 5:51 am
Posts: 11
Location: Brisbane
Country: Australia (au)
Yes to SP1 for 2008.

No to local run of FINSQL.exe on 64 bit sql box. see attached error message. (image)

Attachment:
File comment: Runniing NAV 40sp2 on a 64bit server 2008.
nav_ODBC.jpg
nav_ODBC.jpg [ 35.88 KiB | Viewed 3858 times ]
.

By the way Client box is also sunning at 3% CPU utilisation.


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 5:21 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9072
Location: 3rd rock from sun
Country: United States (us)
what is the avg. disk queue length for each drive.

How much memory is sql server consuming?

where are the backup files located on the box where the client is running?

have you looked at query profiler and see anything that is taking long?

Also your log file is too small it should be set to 50 gig.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 5:29 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9072
Location: 3rd rock from sun
Country: United States (us)
Are you using 4.0 sp2 executables?
I believe you are suppose to run on sql 2008 with 4.0 sp3 with latest hotfix, or 5.0 with latest hotfixes, or 5.0 sp1, or 2009 executables.

I don't think 4.0 sp2 is supported on sql 2008.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 6:06 am 
Offline

Joined: Thu Aug 14, 2008 5:51 am
Posts: 11
Location: Brisbane
Country: Australia (au)
what is the avg. disk queue length for each drive.
os c: 0
logs d: 0.001
data e: 0

How much memory is sql server consuming?
16 gb

where are the backup files located on the box where the client is running?
local on nav client machine

have you looked at query profiler and see anything that is taking long?
nothing unusual just a bucket load of inserts and selects


Also your log file is too small it should be set to 50 gig.
yes I agree my initial log is small but my growth factor is 10gb chunks


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 7:13 am 
Offline

Joined: Thu Aug 14, 2008 5:51 am
Posts: 11
Location: Brisbane
Country: Australia (au)
Are you using 4.0 sp2 executables?
YES. Although its not supported it does complete the process. Last test took 100 hours. This time still running!!!!!! All I want to do is a restore the DB and then start the upgrade process for NAV 2009.

I believe you are suppose to run on sql 2008 with 4.0 sp3 with latest hotfix, or 5.0 with latest hotfixes, or 5.0 sp1, or 2009 executables.
I have 4.0 sp3 not sure if latest hotfix.

Will try tomorrow. Will I have to convert database?

Cheers

Charles


Top
 Profile  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 11:32 am 
Offline

Joined: Tue Jun 07, 2005 4:24 pm
Posts: 2868
Location: MA
Country: United States (us)
Be sure to create the data and log files large enough so that they do not need to auto-expand during the restore. Be sure the transaction log is on its own dedicated disk.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 12:43 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 6:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
lombie wrote:
Also your log file is too small it should be set to 50 gig.
yes I agree my initial log is small but my growth factor is 10gb chunks


](*,) ](*,) ](*,)

IMO 50gig is not enough, you need more than the size of the backup. I would set it to 100Gig.

NEVER use autogrow on the LOG file. That will have a major performance hit.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 12:45 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 6:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
lombie wrote:
Are you using 4.0 sp2 executables?
YES. Although its not supported it does complete the process. Last test took 100 hours. This time still running!!!!!! All I want to do is a restore the DB and then start the upgrade process for NAV 2009.

I believe you are suppose to run on sql 2008 with 4.0 sp3 with latest hotfix, or 5.0 with latest hotfixes, or 5.0 sp1, or 2009 executables.
I have 4.0 sp3 not sure if latest hotfix.

Will try tomorrow. Will I have to convert database?

Cheers

Charles


Why don't you restore direct into a NAV 2009 database? Sp2 is a garbage product and should NEVER be used on SQL. Worst case go to 4.00SP3 with CU8. Newer if possible.

You wont need to convert the database, since this is done during the restore process. you just import the old Navision backup into the new database.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 12:48 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 6:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
Also seriously reconsider waiting for 2009SP1.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: Restore Very Slow - SQL 2008 - NAV 4.0SP2
PostPosted: Wed Aug 05, 2009 1:28 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Wed Dec 15, 2004 5:11 pm
Posts: 9072
Location: 3rd rock from sun
Country: United States (us)
Another option is to do a sql backup and restore it on sql 2008.

_________________
Rashed.

blog: http://mibuso.com/blogs/ara3n/


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 16 posts ]  Go to page 1, 2  Next

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: