Migrating from C-Side to SQL Server on Navision 3.6

Charger

Hello everyone,

Been lurking in the forum all morning soaking up as much information I can on migrating the native database to SQL server as possible.

Is there some document, or some resource that can provide me a detailed explanation of the process?

While there are several advantages, our main reason for moving to SQL Server is that we feel we'll solve a plaguing performance issue that we currently have with the native database. We feel that the combination of the Hardware we're using, coupled with SQL's ability to use it will be the solution we've long been looking for ... [-o<

What happens is that our disk queue length shoots up to 100% for long periods of time, and the users experience slow periods of system access...

Database is 25gig, here are approx. 40 concurrent users accessing it
Server is a Gen3 dual Xeon 2.0 DL380, 4 gig ram, 15k SCSI Raid 1 ( 2 disks)

I've also tried on a BL20p Dual 3.2Ghz Xeon, 2 gig of RAM, connected to a Raid 1 SAN w/ 10,000 RPM SATA drives.......it was even slower, but even if you have 1 user in i, the disk qeue shoots up to the top...

I'll be trying a new DL380 Gen4 Dual Xeon with 4 (Raid 1+0) 15k SCSI drives and 6 gig of RAM and we'll see what happens in Native, but we think SQL Server will perform better...

Savatage

I don't know if you came across these during your searches

http://www.mibuso.com/forum/viewtopic.php?t=11444
http://www.mibuso.com/forum/viewtopic.php?t=12076

they have helpful info to, such as:

Split database from 1 to 2 disks Performance Increase additional %100
Split database from 2 to 3 disks Performance Increase additional %52
Split database from 3 to 4 disks Performance Increase additional %30.6
Split database from 4 to 5 disks Performance Increase additional %21
Split database from 5 to 6 disks Performance Increase additional %10.8

so I guess if you compare a 1 disk to a 6 disk system there is a %214.4 speed increase.

There are literally 100's of posts regarding server speed, I can see where it can be a hard thing to search for.

Perhaps a Navision Hardware Only forum would help?

Savatage

Charger wrote:

Is there some document, or some resource that can provide me a detailed explanation of the process?

http://www.mibuso.com/forum/viewtopic.php?t=7482

bbrown

If you are expecting to simply convert to Navision SQL on your existing server and solve all your performance issues, be prepared to be disappointed.

You should take the time to get a better understanding of the performance issues. Then identify how a move to SQL would address these issues, or how you could improve your Native system. Don't make the generic assumption that changing to SQL will solve all your problems. A 25 GB, 40 user system (if properly configured) is well within the capabilities of the native Navision database.

Making assumption about SQL could result in a lot of wasted time, effort and money.

What happens is that our disk queue length shoots up to 100% for long periods of time

This statement makes no sense. Where did you get this number? Disk Queue Length is expressed as the number of pending request and not as a percentage.

DenSter

Changing hardware will only 'solve' about 20% of your performance issues. The reason why a dual processor doesn't improve performance is that the Native database server only knows how to use one processor. You can throw terabytes of RAM at it, but it will only utilize up to 1GB of it. In fact, I've seen situations in which the cache was set to higher than 1GB and that actually slowed down the system because it gets confused. Set your cache to about 850MB and you should already see an improvement.

Most of your performance issues can be solved by tweaks to the application, such as optimizing keys and improving code. I know this is hard to believe for a hardware person, but it is true.

Simply switching over to SQL Server will not solve your performance problem. I would even say that initially, if you don't also look at how the app is used, it will even make things worse.

bbrown

There are 3 areas that must be considered when building systems. No one of these is more important than any other, and ignoring any one can lead to dissappointment, or even disaster.

Database/Application:

You must have a properly designed and built database and client application. Don't expect faster hardware to solve performance issues caused by poor or improper use of keys or bad code.

Systems

This not only involves the server that is running the database, but the entire network including user's computers and wiring. It is important to have a properly designed and built system. This doesn't mean just go out and buy the biggest of everything. You should be able to justify everything you buy. The system must also be built correctly.

Maintenance

Everything must be maintained.

kriki

Probably you performance-issues is more a program-problem.
You can check this:
Create a form on table Session (a virtual table).
Check the field "Sum Intervals". If for 1 of the sessions, you have a value that is 10's or 100's or 1000's or even more higher than the rest, it means that the user is using programs that use bad indexes.
So check with the user wich programs he is using, and check those programs on index-use. (Also a possibility is that the user is putting filters WITHOUT first using the correct index).

The RAM and CPU of the Navision-server is not an issue. Just make sure that your DB-cache stays in memory and is not swapped to disk. So 1.5 GB should be enough if you don't use the server for other purposes. The Navision-DB is almost not using the CPU. Check this out on your server: if the disks are running like hell, your CPU should still be enoying itself at around 10% (or even less).
You need to put the DB-cache at 700-800MB and enabling the COMMIT-cache.
use the fastest disks you can find and put them as 1 RAID10 or several RAID1 dividing the DB in 1 file per RAID1-pair.

Charger

This statement makes no sense. Where did you get this number? Disk Queue Length is expressed as the number of pending request and not as a percentage.

My error, I was actually referring to % Disk Read Time, what I was thinking and what I was typing weren't matching.......

My systems are healthy, and my network is nowhere near congested

I realize that Microsoft SQL isn't the answer to all my problems and I'm not expecting it to solve everything. In my opinion I agree that probably all the issues we're having will ultimately be resolved with better optimization of the code.....Nevertheless, we're ready to dump a copy of the live database, migrate, and run it in a test environment to see what happens. I've read all the posts regarding what to expect or not expect when I move over. At the end of the day however, there's no better way of predicting what will happen than by trying it.

An update to my performance issue, I had been running my HP 5302 Disk Controller with 128MB cache at 50% allocation for read ahead and 50% write back.....As a test, I moved to 100% of my cache to read ahead, 0 for write back......My % Disk read team actually started to look reasonable, but my % Disk Write has only slightly increased...My total % Disk Time stays pegged from time to time. While I realize it sounds absurd having to use 256MB cache in this environment, it looks to me that if I had more cache on the controller, it would probably be a lot happier.

I still agree that the code needs to be optimized, and by throwing hardware at it I'm just partially masking the problem, but we're spreading our eggs around and trying everything concurrently...I will let you know what more cache does....


Thanks
Robert

themave

Charger wrote:

.... Database is 25gig, here are approx. 40 concurrent users accessing it
Server is a Gen3 dual Xeon 2.0 DL380, 4 gig ram, 15k SCSI Raid 1 ( 2 disks)

I've also tried on a BL20p Dual 3.2Ghz Xeon, 2 gig of RAM, connected to a Raid 1 SAN w/ 10,000 RPM SATA drives.......it was even slower, but even if you have 1 user in i, the disk qeue shoots up to the top...

I'll be trying a new DL380 Gen4 Dual Xeon with 4 (Raid 1+0) 15k SCSI drives and 6 gig of RAM and we'll see what happens in Native, but we think SQL Server will perform better...

Splitting the database works best when the database parts are on different drives, so on your first configuration with a raid 1 mirror, splitting database will not really do much.

If you go to sql, you will definitely need to switch to the san or raid 1+0 machine, because sql needs to have several items on different physical disk, such as the log file. But your raid 1+0 only has four drives total, so you really won't be putting different parts of the sql database on different physical drives. your raid 1+0 with 4 drives will not be enough.

here is the quote from the sql install guide "The minimum server configuration should consist of at least four disks. You should use one disk for storing the primary data file for each of your databases. You should the second disk for storing the operating system software and any installed applications. You should use the third disk for storing the secondary data files (which contain the Navision data) if secondary data files have been created. You should use the fourth disk for storing the transaction log files."

so you need at lease 4 physical disks, and then if you are going to mirror you need to double that, and then if are going to mirror and strip then you need more, none of your configurations have this many disk, so you will be limiting the potential gain of switching to sql before you even install sql.