SAN or NAS (Network Attached Storage) for SQL

bonehead

We are going to implement SQL on out HO database and we are looking to see what platform is better for Navision, SAN or NAS (of course to add to confusion this is not the NAS we all know and love but rather Network Attached Storage )

Anyway it appears that SAN and NAS are very similar - has anybody out there got SQL implemented on these?

I have gotten from an article that :

SANs feature block-level transfers instead of NAS file-level transfers

Link : http://www.comnews.com/stories/articles ... rstory.htm

Anyway - what does Navision use, Block or file, and what is the difference??

Thanks,

Dave

kriki

I haven't implemented but in a course about SQL-performance on Navision, the instructor said a few things about SAN and NAS:
-SAN is better than NAS.
-SAN is more costly but is more free in configuration-possibilities
-NAS is only fast with fibreoptics
-Never pt more than 8 disks per channel

The fact you are asking questions about this:
-About which size are you talking for the DB?
-Is in only for a Navision-DB or also others and also file-servers?

bonehead

Hi Kirki,


We are on a Native DB at the min and it;s 226147744 (226 GB)at the min we got our licenece extended to 512 but performance is a big problem at the min.

We are looking to just put the Navision DB on it first - but I want the option to put all File servers and so on , on to it again - It appears that SAN's are only really cost efficive if all our servers are on it.

Dave

DenSter

I heard the same in a Navision-SQL class. NAS is meant for static file storage, not for a database application, so that is not recommended.

The misconception with Navision on a SQL Server database is that by throwing hardware at it, the problem will just go away. Most of the performance gain is achieved by streamlining the keys and indexes within the Navision application design. On 225GB database you should get ENORMOUS gains by streamlining your keys, provided of course that this has not been done yet. Then after that there are things you can do to streamline the code to make better use of the SQL Server option.

bonehead

Hi Denster,

We are not on SQL yet so I am looking to do some major streamlinging of the keys once we move.

I also want to start data warehousing info so that I can remove further keys from the production database.

As for the NAS - I have not heard this before , I have heard about it hammering the network and so on .

Thanks for the info

dave

DenSter

I need to put a disclaimer here . I am in no way shape or form a networking specialist, all I am doing is reproducing what someone said to me. I do not have the slightest idea what NAS exactly is, but in this class the instructor said that for Navision this is not the preferred way to go.

By the way, you should really consider going to SQL Server. With a database size that big it is much more scaleable.

By the way 2, you shoud consider what Kriki said in another post and analyze the use of many keys in ledger tables. I've observed an 8GB database shrink by 50% just by turning off like 5 keys. Many keys are created to support one or two reports, and are part of the cause of system performance. By writing to temp tables, the report slows down just a little bit, but by redesigning the key you get a big performance gain.

kriki

DenSter wrote:

I've observed an 8GB database shrink by 50% just by turning off like 5 keys.

Other example:
Table 32 with 1.6M records in it. (exact size I don't remember).
A collegue wanted to delete most of the records for having a small DB for testing. He wrote a program to delete records and after a day he had only deleted some 200.000 records.
I disabled all secondary keys and the total size of the table was ONLY 1/3!

Did I already tell that for deleting the other 1.4M records, Navision needed only 20 minutes!

And with SQL, you can disable maintainenance of a lot of keys in the table without needing to rewrite a letter of code in the programs!

bonehead

Thanks Guys

There is no doubt - we are going SQL 2005 anyway !

The only remaining factor is whether to implement on a SAN or NAS and I am leaning to the SAN solution more and more as I find out new information.

As for the Data Warehouse - a SQL option with OLAP is the way forward considering we grow at 8 GB a day !!! (and that is of pure data, no keys growth included !)

Looks like I'll have to learn SQL and fast ! Any suggestions as to On line tutorials ?

Dave

DenSter

If you go with SQL Server 2005, you will have to upgrade Navision executables to 4.0 SP1 as well. All other versions are not supported on SQL Server 2005.

Browse around on the Microsoft SQL2005 site, there's lots of information about online resources there. You can also ask google.

You may not realize it, but I bet most of that 8GB a day is key data.

bonehead

Hi Denstar

Ya we are testing 4.01 at the min.

As for the data growth, the actual figures are
Data: 6,969,517 KB
Keys: 2,077,835 KB

Total : 9,047,352 KB

This is the heavest recorded by us for one day's traiding so far !!! We have taken off as many secondary keys as possible at this stage !!! And streamlined the ones used in reports to cour business needs.

Anyway thanks for the tips

Dave

kriki

For performance, you might start thinking about a second DB where you put all data in that is not immediately needed in the working DB.
In the secondary DB, you can run the statistics-reports.
This keeps the working db smaller with better performance and the reporting does not take performance from the working DB.

bbrown

When working with database applications, there are 4 basic areas that need to be considered. With high transaction volumes, these become more important. These are listed in no special order. Each one needs to be addressed.

1. A properly designed application and database implementation.

You will find lots of advice on this site concerning this subject. Bad code and improper use of keys can quickly kill performance.

2. A properly designed server and associated network.

Again lots of advice on this site. On the question about NAS vs. SAN, unless you have a pressing reason for wanting the database on shared storage, I would use direct-attached storage. A desire to support clustering would be a reason for shared storage.

If you do use shared storage avoid overloading the database drives with other i/o request. Dedicated drives would be best. Even in a SAN/NAS it will come down to how many spindles/heads.


A properly installed network

A bad install can quickly ruin even the best application and hardware.

Maintenance

Yiou must maintain the system.

kriki

bbrown wrote:

1. A properly designed application and database implementation.

Most performance problems, come in this category.

bonehead

Thanks guys

All this info helps a great deal.

Kriki - we are going to split the DB into a warehouse and a processing DB, this will help the performance issues.
As for the

bbrown wrote:
1. A properly designed application and database implementation.

This is an ongoing battle for the best database possible but we will be attacking this fresh in the SQL implementation.

bbrown - you said

2. A properly designed server and associated network.

Again lots of advice on this site. On the question about NAS vs. SAN, unless you have a pressing reason for wanting the database on shared storage, I would use direct-attached storage. A desire to support clustering would be a reason for shared storage.

If you do use shared storage avoid overloading the database drives with other i/o request. Dedicated drives would be best. Even in a SAN/NAS it will come down to how many spindles/heads.

We are currently running 2 external raid cabinets each with 14 disks raided. Each disk has two file partitions so currently our database is spread over 28 file partitions.

I am not sure how to configure it for SQL but I will stay chipping away at it. The only disadvantage of this that I can see the that the disks are not FC and if we need more physical drives it is an issues to add them. The current setup is DAS and has been performing so far - but we are still on Native Database at the min so I do not know how this will be when we move to SQL untill we start our testing.

Thanks

Dave

kine

There are good new things in SP1 about optimizing keys for SQL - you can define index fields for each Navision key - it means, each key is defined for Navision and can be different for SQL (you needed 2 different keys in previous versions). And you can change clustered index on SQL! Using SP1 with SQL 2K5 is something about 50% performance gain in critical business rutines (posting) compared with 4.00 on SQL 2K.

ara3n

I hope Navision SP2 will have the objects restructured/changed so that new features for SQL are used and optimized.

bbrown

I hope Navision SP2 will have the objects restructured/changed so that new features for SQL are used and optimized.

I understand that will be coming in Navision 5

ara3n

I think it will be also available in 4. SP2. Otherwise why create the features and not use them in standard objects? Especialy the new function such as findfirst,findlast,findset.

I believe in one of those webpresentations the presenter mentioned that SP2 will have them.

bbrown

What I got from Microsoft is that they would roll out the changes as they released updates/service packs for 4.0 and hoped to have it implemented throughout the product by version 5.

amcmwu

Hello everybody
seems like you ave done what we planned. A sinple question from my side
Is there anything we should know when we plan to switch with 120 Users
from NAV 5.0 on C/Side ( Databse is on 12 SCSI-HD ) to NAV 5.0 SQL on a HP EVA8100? :?:

Thanks and regards

ara3n

expect to do sql optimization.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]