Disk Advice

Moving Navision to new SAN.

I´ve got 10 drives.

If you were me, would you

(A) Put all drives in a RAID10 and then carve out LUNs

(B) Try to do some physical separation like

6 disks - Raid10 - data

2 disks - Raid1 - OS

2 disks - Raid1 - Logs

and then carving out a LUN on one of those RAIDs for the TempDB?

Backups are stored elsewhere.

Thanks very much!

Or perhaps 2 of the drives in a RAID1 for the OS, and then RAID10 the rest.

Hi koalas,

welcome to the Dynamics User Group [<:o)]

Well, sizing an SQL Server - and especially the disk-subsystem! - depends on many things, e.g. transaction volume, db size and growth, number of concurrent users, etc… When it’s up to decide if to put it on a SAN or direct RAID volumes, the exact technical specifications and capabilities of that “boxes” are crucial.

10 disks in one array I would not necessarily even call a SAN - that’s a shelf with 10 disks, no more. Even without knowing all the relevant details I dare to guess you won’t be happy with that LUN option (A); this might work for a pretty tiny system with low volume to process. So my gut feeling tends to the physical RAID volume solutions, as you suggested (B).

Here it is crucial to separate the Log from the Data - as you proposed. Especially for the Data it is important, how many spindles “share” the workload - the more workers (= spindles/HDD) you have, the faster it might work. So it highly depends on the transaction volume to determine how many “workers” you need …

If you need to process a high volume it might also be feasible to put the Log on a RAID10 with 4 HDD.

As for the “tempdb”: presuming it is a dedicated server, only running one SQL instance and nothing else; with a small to medium transaction volume; it should be OK to have the “tempdb” on the C:\ drive (RAID1). If you have a higher volume it should be considered to separate the “tempdb” …
With “tempdb” you need to adjust the initial file sizes (avoiding Auto Growth) and split it into multiple data-files, depending on the number of CPU you have; e.g. if you have 1 x QuadCore CPU then create 3 additional ndf files (totaling to 4 data files).

Again, without knowing all the details of your system this could only be a guess … an educated guess anyway [;)]

Jörg,

Thank you very much for your response. After some pushing I was able to get some more disks made available.

Also, for our environment, we have about 100 users on Navision, so it gets some decent use.

So, now I´ll have a RAID 1 for the OS, a sizable RAID 10 for the data, a separate RAID 10 for the Logs … and I´m debating a separate RAID 1 for the TempDB… or possibly using those disks in the RAID10 for the data, and then carving out a LUN for the TempDB on the OS RAID.

I do have one more question.

I know 64kb is the recommended cluster size… so if I do the setup doing the following…

  • Set the disks on the SAN to have a 64kb segment size (stripe size is not an option)

  • Align the partitions before formatting them (offset 1024 ?)

  • Then format the disks in windows with a 64kb allocation unit size

Will I have set up my disks correctly?

Thanks!

Great if you could install more spindles! Well, regarding “tempdb” maybe indeed it’s smarter to assign a RAID10 to the OS drive and leave the “tempdb” there! There’s no point in creating a logical partition, it’s always the physical volumes which count. Thus, if you have multiple spindles for that OS/tempdb drive I think this should preferable. The OS itself - on a dedicated SQL only server - does not require much HDD or I/O, thus most of the RAID10 “power” will be spent for SQL and it’s “tempdb”.

Regarding the number of HDD in the “Data” RAID10: MS suggests to have 6 to 7 spindles striped and mirrored (totaling in 12 to 14 spindles for the RAID10) for a database larger than 80GB … just to give you an idea what might be necessary … as MS states: “think spindles, not space” … then again: it really depend on what your users will do …

And last but not least: of course, we are talking about state-of-the-art disks, designed for database use, SAS/FC/ISCSI with 15krpm, etc…

Formatting with 64k blocks is benefitial; but as far as I know Windows Server 2008 takes care about the disk alignment itself … I’m not sure …

Besides the disks: your server should have plenty of RAM - the more the better. With Standard OS you could (should?!) use up to 32GB; with Enterprise even more. Plenty of RAM will reduce the pressure on the disk-subsystem, avoiding I/O problems - the server will be more “forgiving” …
And with 100+ concurrent users you should considere having two physical CPU, e.g. 2 Hex Core.

Thanks so much for the information. Here’s some more specifics in case you have any extra tips.

Database - 550GB

Logs - 200GB (split appropriately)

TempDB - stays at 15GB currently on a 20GB partition

2 SQL servers currently in an Active - Passive cluster. The setup we are moving to is the same except we will be going from 2005 to 2008 and will be using virtual servers instead of hardware servers, but each will be on it’s own separate blade.

So the main SQL server serving the database will be on a blade with 2 Xeon MP processors @ 2.4 GHz, 4 cores, 8 threads each. (I understand that Navision can only use one thread per processor though) … and we’ll have 48GB of RAM on that one server.

The SAN box we’ll be attaching to we’ll have 18 disks to use. Each has a capacity of 280GB. 10,000 RPM, SAS, and the management software lists the data rate at 6Gbps.

Now, we don’t have 100+ concurrent users, but about 100 users in total. The number of concurrent users is much less.

So, with 18 disks would you put:

4 disks - Raid10 - OS & Temp

10 disks - Raid10 - Data

4 disks - Raid10 - Logs

Or do you think some other configuration would be preferable?

Thanks again for all the advice!

Koalas,

sorry to say this, but you are heading down the path to failure.

You don’t do it by saying “here is the hardware we have, whats the best way to utilize it” you start by saying “here is the analysis of our system and the performance requirements” then you base your hardware design upon that.

Joerg has given you some great starting pointers, but this is not everything. you need to actually know what your requirement is. Navision works fine with Visualization, BUT it must be done right, your CPU setup for a start is wrong and will slow you down. The machine you are building is NOT a high end server, but 550gig and 100users is getting into the high end.

As Joerg says you really do need to get a real SAN rather than a “box of disks connected by optic fibre”, since they really are two completely different things.

Sorry to be tough like this, but I think its best you know now rather than find out in a years time.

David, Thanks for the reply. Criticism is always appreciated, however it´s not very helpful to say “that’s wrong” without providing any direction.

I understand that performance testing plays a large role in determining some factors, but you say that definitely my CPU setup is wrong.

Can you please explain what you think is wrong so that I can learn the direction I should be taking?

Also, I do believe our setup to be a SAN. We have redundant fiber channels and redundant fiber switches, 2 drive enclosures (although the sql disks only reside on one enclosure), hot spares available, and manage the connection of the logical drives to the servers via IBM’s DS Storage Manager software.

Can you please explain the difference between a ‘Real SAN’ and what we have so that I can gain that knowledge.

Thanks very much for any input. It is appreciated.

Also, for what it’s worth, our system functions reasonably well.

There was some analysis that was done previously that showed that we could improve performance by getting more/better disks.

Right now we have our Logs, Data, and TempDB all sharing a RAID10.

All we’re doing is moving to better disks, and since we have more disks to use, I figure it’s better to split up the different components on different physical disks rather than keeping them all together.

Other than this, we will be moving from SQL2005 to 2008, but making no changes to our setup. While we do this move, we will be going to physical servers instead of virtual, but not sharing the resources of the blades with anything but the SQL servers)

At this time I do not see any need to make any changes to our system, which is why my questions are primarily about the disk array.

If you have any input about the CPU setup, however, I would be interested to learn anything I can.

Thanks again.

Sorry I’m having a bad day [:D]

I get a bit upset when I see posts like this, becasue your partner should already have reviewed all this with you, and explained this to you.

As to CPUs, you need to switch off hyper threading. I can really recommend Joerg’s book as a starting point to get and idea of what you need to be focusing on.

Back to disks though. A solid hardware infrastructure is critical to having a smoothly running system, but you need to address everything. Ram can often be the magic bullet, and with 550gig and 100 users, 48gig isn’t as much as it sounds like.

The point being that Firstly there is no magic bullet, so don’t loose track of the whole picture by focusing only on one issue. Secondly don’t design your hardware till you know what you need. The fact that the system is running smooth and fine now can also be an issue. Because if there is a problem and you have spent lots of money on new and better hardware, the users (and management) are not going to be happy. And yes I have seen clients go from a smooth running system, then upgrade to a “mega server” and performance dropped becasue it just wasn’t don’t properly.

Thanks for the input. Book is purchased, however it will take a couple of weeks to get here. If only there were a kindle edition :wink:

To be clear, there is another group responsible for Navision operations, so the majority of the SQL and application operation belong to them. However our group is responsible for the hardware their system runs on, and for the part that we are responsible for, I want to make sure we are helping them get the best setup possible to work with.

With their current setup having the data, logs and tempdb sharing the same RAID10, I believe that there could be some performance improvement by splitting out these components since we have more disks to work with, instead of creating another giant RAID10. Maybe I’m wrong, but I’d like to explore if this option makes sense, and recommend it, because otherwise everything will just be put back on a single RAID10 as it is currently.

As far as RAM is concerned, the current server is only running with 13GB of RAM, so I’m hopeful that quadrupling the RAM should help some.

Back to the disks. If the tempdb didn’t exist, with 18 disks, I would split it – 2 (OS) / 12 (Data) / 4 (Logs)

Now, the question, becomes where to put the TempDB. Lump it in with the Raid1 OS? Take 2 disks from the data partition and make a 4 disk RAID 10 for the OS and TempDB? Place the TempDB on either the Data or Logs RAID10?

Now I understand that answering that question has a lot to do with current performance data, but how can I use that performance data to determine which of these might be the best option to recommend going forward? (i.e. what metrics am I focusing on, etc.)

I´ve posted some of our current performance data here:

https://docs.google.com/document/d/1vYHudzig-Y6QsnmYraTkhRTxJi8FfdqTLRN3ZdOMO0g/edit

Any insight would be appreciated.

Thanks again.

As I say, I would rather you analysis your system, and then determine your hard ware needs based on that. In your initial post you didn’t mention that the system is running fine on lesser hardware, and that has some effect. 550gig is a pretty big database, though 100 users is not huge, so one question is why is the database so big? Maybe there is a lot of static data or maybe a lot of new tables that wont affect performance so maybe the hot tables are not so big. But all that is guess work.

But if your decision is to use the hard ware you have and want to know the best way to use it, then follow Joerg’s advise, he really knows what he is talking about. I just don’t like putting the cart before the horse like this.

By the way like Joerg, I make a living from performance tuning, so I see this situation a lot, where the client already has the hardware chosen some times you can’t change it and you work with it, but always with the proviso that its not optimum. The biggest problem though is where the hardware is bumped up, and after the move the system is slower, (generally for some other reason) and everything falls back on the new hardware.

if you had a 100 gig database I would not worry, but it concerns me how you have 550gig with only 100 users, that suggests some very heavy posting.

Ok, I’d like to give an update on this now that we’re running on the new system.

Incidentally, I did buy your book Jörg, and appreciate the help both it and this forum have given in helping me grow and understand this system better.

As far as the disks, I ended up with the following configuration.

10 Disks - R10 - Data
4 Disks - R10 - Logs
4 Disks - R10 - TempDB / Master

Compared to our previous setup which was an 8 disk RAID10, which the Logs, Data, and TempDB were all sharing.

The only difference between the drives is that the previous drives were 15K 3.5 SAS, while the new drives are 10K 2.5 SAS. (43X0805 / 49Y1840)

After the change, the client did not perceive the performance to be increased on the new system, and we went about tuning the system as best as possible. Some heavy queries were tuned to lessen their impact. Un-used indexes were removed from heavily queried tables, some large tables were cleaned up, scheduled maintenance jobs were added, Read Committed Snapshot Isolation level was enabled, Fill Factor % set to 90, Max Degree of Parallelism set to 1, Optimize for Ad Hoc Workflows set to 1.

These suggestions for the system came from a compilation of information from your book, local experts, and this post:

http://blogs.msdn.com/b/nav/archive/2010/09/28/microsoft-dynamics-nav-sql-server-configuration-recommendations.aspx

As far as the hardware, the Queue Depth on the Host Bus Adapters has also been set to 64.

I have been monitoring performance both using the PAL tool. http://pal.codeplex.com/ and also a software called Ignite by Confio. Unfortunately, I did not have either of these tools before I started, and my previous performance statistics were only gathered using performance monitor.

Now, despite the tuning of the system, the customer still perceives the system to not be as fast as they would like, and the areas of Navision that they are focusing on to judge performance are Invoice Posting, Invoice Booking, and various job queues.

I’m attaching an image of some graphs that they’re compiling that they’re using to gauge system performance on their side.

I’m also attaching a PAL report that gives a good example of the system performance. Scroll to page 30 and past.

C - OS
D - Primary Database
E - Logs
F - Lesser used databases
I - TempDB
P - Pagefile

Any advice on where I need to be looking to get this system working better? Much appreciated.