Hyper-V Server vs. Physical Server

couberpu

Good Morning,

I just started a new position at Sunrise, FL and I need help to clear several things:

1. The company is running NAV 2009 R2 classic with SQL option
We had a NAV VAR and an in-house solution provider!!!!
I am just an IT manager with some NAV background.

2. I was told that MS would not suggest to run under RAID-10 with SQL option
3. I was told that MS suggest to use simple recovery mode with SQL option
4. I was told that Hyper-V is a NO-NO for NAV 2009 R2 classic SQL option
5. I was told that SQL 2009 R2 enterprise is the only option to run 150+ GB database, with 55 users
6. I was told that RAID-1 is the only way to get best performance.
7. I was told ...

Can anyone help me understand if MS actual suggest any of these?
My personal view on any of these are :oops: :oops: :oops:

Please advise.

CouberPu

bbrown

I have been supporting NAV since 1999. Besides being a developer, one of my primary roles is to assist customers with selecting their hardware for supporting NAV. I also have 20+ years of hardware integration experience. I find the answers above to be rather misleading.

1. This is an incorrect statement. RAID 10 is the preferred configuration for the drive holding SQL data files. This should also the a dedicated array. RAID 1 (or 10) is recommended for the SQL transaction log. These should be physically separate disk.

2. There is no preferred recovery method. Although I would say that full is the most common. The appropriate recovery method is the one that supports your recovery plan.

3. Hyper-V (or other virtualization) is a very valid solution for NAV. You just have to configure it right. The one thing that often gets overlooked is that the underlying hardware, especially the disk systems, still remain as a critical design point. While dedicated physical hardware is almost always faster, I have seen many successful virtualized installs. But I've also seen disasters. I will also generally not recommend virtualization to a client that has no experience with it.

4. We have plenty of customer running systems of this size of larger on SQL Standard. NAV does not need SQL Enterprise. The only reason to use SQL Enterprise is that you find a need for 1 or more of its exclusive features and can justify the additional cost to get those features.

5. This is a false statement. RAID 10 is the preferred setup as stated above. But this does not mean the server is just a single RAID 10. The RAID 10 is only the dedicated array to hold the SQL data files.

Hope this is helpful.

couberpu

Thanks. BBrown.

The "in-houise" solution supporter told me that he started with NAV during 1980s and he knew inside out of every function of NAV. The list of 'what I been told' was from him and system was setup based on those.

I have more questions need help with.

1. Do we need to purchase sql tuning program made just for NAV for size of 150+ GB database or we can just use built in sql maintenance plan? (tables and views)
2. I find out that there is a lot of if else statement instead of case of statement in custom code dealing with dates. One example would be finding which month and update total of sales, which can be done via SIFT but ..., and the custom code would use if month = 1 then ... else if month = 2 then ...... else if month = 12 then ...., etc. Would it be quicker to use case of statement? I found if else code almost everywhere. Would this cause performance issue?

Thanks,
Couber

bbrown

I suspect that he is basing those recommendations on the native database and not considering the different requirements for SQL. RAID 1 was the recommended configuration for the old native database.

bbrown

In response to your other questions:

1. Do you absolutely need one of those "made for NAV" programs? That answer is no. Could they be helpful? Possibly. I don't have enough experience with any "made for NAV" programs to give you any up or down recommendations on them. Perhaps others with more direct experience can comment. I tend to lean toward the more traditional methods. However I do not recommend using the built in maintenance plans for index maintenance. They do too much work. There's a standard SQL script I've used for years and has worked well. Should be in Books Online (I think). It updates only what needs to be.

2. The choice of IF..ELSE versus CASE is more about code readability than performance.

couberpu

Thanks.

Do you know the name of the script or which chapter in Books Online?

bbrown

couberpu wrote:

Thanks.

Do you know the name of the script or which chapter in Books Online?

Sorry I don't. I've had it as a saved SQL script for years that I just copy between systems. I'm not totally sure of the exact original source. It was Microsoft but may have been MSDN or TechNet. I'll dig it out and post it here later.

davmac1

NAV has grown a lot over the years in features, functionality, and technology.
A person who has been around since the very beginning will know a lot of the original features, but unless they have dedicated themselves to keeping up with all the new features, they will fall behind.
Keep learning and finding out for yourself.
There are a number of knowledgeable people contributing to Mibuso, including bbrown who has given you some great feedback - which I fully agree with.

couberpu

I am grateful for all the help I got over the years.

I would say that I agree and I have to be careful dealing with someone been around forever.

Here is the thing:

1. People at work think that they got someone who knew everything about NAV.
2. The system had performance issue when convert to SQL and suggested solution is to use SQL 2008 R2 Enterprise with 128GB of RAM. ](*,)
3. We still have performance issue and suggested solution was to add another 128GB of RAM. ](*,)
4. My experience with NAV SQL option started at end of 2003 and I think we can do better without SQL Enterprise and lots of RAM.
5. I am not a NAV developer but I can read and write code in NAV.
5. I am testing the database with a VM, using Windows 2008 Server,SQL 2008 R2 standard with 8 Cores and 64GB of RAM. I am seeking suggestions that would help me complete the test with acceptable performance by user.

Thanks,
CouberPu

bbrown

My first advice is don't guess your way thru performance problems. You need to "get your hands dirty" and sort out where the bottlenecks are. Then address those issues. My general rule is "it's a problem until you prove it is not". That included NAV code, SQL database, hardware, and maintenance.

Performance is also rarely a single problem.

bbrown

The native DB and SQL will interact with NAV code quite differently. It is not uncommon to see performance issues after a migration. Especially with a larger database. This is especially true in the areas of indexes. It can be dangerous to assume a NAV system will just migrate and everything will be wonderful. It's always better to plan for some tuning.

couberpu

Thanks & agree.

Planning on getting my hand dirty and already started.
Still, appreciated for some advise on SQL index vs. NAV key / 2nd key. I saw lots of keys created for sub ledgers to maintain same SIFT calculation. Example would be item ledger table has 17 keys created to maintain SIFT for Quantity, and they all have both maintainSQL and SIFT index checked. Any suggestion? :oops:

bbrown

http://www.stryk.info/english/fieldguide.html

I recommend investing in a copy of this book. It will help to answer a lot of questions. The author is also one of the frequent posters to this forum on the subject of performance.

couberpu

10-4.

Any chance to get the sql script?

bbrown

Here's the script the I use. It is actually 2 scripts which I will setup as 2 steps in a SQL Agent job. Then schedule the job to run daily at a low usage time.

Step 1 handles the index updates. It will selectively process indexes based on the level of fragmentation. Indexes with less than 10% fragmentation are skipped.

-- Ensure a USE <databasename> statement has been executed first.
USE YOURDATABASENAME
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @partitions2 bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0 and avg_fragmentation_in_percent > 10;


-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
 
    END;


-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Step 2 handles the statistics

-- Ensure a USE <databasename> statement has been executed first.
USE YOURDATABASENAME
GO
EXEC sp_updatestats