Restore Very Slow - SQL 2008 - NAV 4.0SP2
                
                    lombie                
                
                    Member Posts: 15                
            
                        
            
                    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.
Cheers
Charles ](*,)
                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 ](*,)
0                
            Comments
- 
            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 20080 - 
            Thanks but can't do that. 64bit OS and can't install MDAC. I have already tried this option.0
 - 
            you don't need to install the client. just copy the client folder and run the finsql.exe0
 - 
            Also, have you installed SP1 for sql 2008?0
 - 
            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.0 - 
            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.0 - 
            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.0 - 
            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 chunks0 - 
            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
Charles0 - 
            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.0
 - 
            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 Singleton0 - 
            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 Singleton0 - 
            Also seriously reconsider waiting for 2009SP1.David Singleton0
 - 
            Another option is to do a sql backup and restore it on sql 2008.0
 - 
            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.0 
Categories
- All Categories
 - 73 General
 - 73 Announcements
 - 66.6K Microsoft Dynamics NAV
 - 18.7K NAV Three Tier
 - 38.4K NAV/Navision Classic Client
 - 3.6K Navision Attain
 - 2.4K Navision Financials
 - 116 Navision DOS
 - 851 Navision e-Commerce
 - 1K NAV Tips & Tricks
 - 772 NAV Dutch speaking only
 - 617 NAV Courses, Exams & Certification
 - 2K Microsoft Dynamics-Other
 - 1.5K Dynamics AX
 - 323 Dynamics CRM
 - 111 Dynamics GP
 - 10 Dynamics SL
 - 1.5K Other
 - 990 SQL General
 - 383 SQL Performance
 - 34 SQL Tips & Tricks
 - 35 Design Patterns (General & Best Practices)
 - 1 Architectural Patterns
 - 10 Design Patterns
 - 5 Implementation Patterns
 - 53 3rd Party Products, Services & Events
 - 1.6K General
 - 1.1K General Chat
 - 1.6K Website
 - 83 Testing
 - 1.2K Download section
 - 23 How Tos section
 - 252 Feedback
 - 12 NAV TechDays 2013 Sessions
 - 13 NAV TechDays 2012 Sessions
 

