Restore Very Slow - SQL 2008 - NAV 4.0SP2

lombie

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.

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 ](*,)

ara3n

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

lombie

Thanks but can't do that. 64bit OS and can't install MDAC. I have already tried this option.

ara3n

you don't need to install the client. just copy the client folder and run the finsql.exe

ara3n

Also, have you installed SP1 for sql 2008?

lombie

Yes to SP1 for 2008.

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

.

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

ara3n

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.

ara3n

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.

lombie

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

lombie

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

bbrown

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.

David_Singleton

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

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

Also seriously reconsider waiting for 2009SP1.

ara3n

Another option is to do a sql backup and restore it on sql 2008.

ara3n

David Singleton wrote:

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.

I thought the original db was 50 gig, and yes, the size of the created db should be the same as the original db.