Hypothetically speaking. Lets say you are building a SQL server, and have a limited number of Physical drives (spindles) how would you ideally split them up for optimum use in a NAV environment. For example 14 is a typical number of drives in a rack, so for the purpose of this exercise lets say we have 14 physical drives:
For example.
Scenario 1:
Drive 1 : Raid 1, 1 logical drive (2 actual drives) = Windows 2003 Server + SQL 2005
Drive 4 : Raid 1, 1 Logical Drive (2 Actual drives) = Drive for backups, general work drive.
Etc.
can you please post your recommendation, and reasons for that recommendation. PS I am not suggesting the above ast he right solution, I am asking what is the right solution.
My answers would depend on a few factors. The number and size of databases to be supported, and the size of the physical drives. This is just 1 approach.
A typical approach:
Drive 1 : RAID 1 1 logical drive Windows + SQL program files. Additonal space on this drive may be used for scratch and general work space. On a smaller server this drive may also contain the paging file. If budget allows I will use larger size drives here (typically 146 GB). Another option I have used recently is to attach a large (500 GB+) USB drive to the server. Most newer servers have USB support and these drives are fairly inexpensive. It also gives you a fast and very portable backup method.
Drive 2: RAID 1 1 logical drive SQL Transaction Log. I will have a separate transaction log file for each database that is supported. Depending on the size of the database a transaction log may need either large drives or multiple sets.
Drive 3: RAID 1 1 logical drive Base SQL Databases + Navision primary data file.
Drive 4: RAID 10 1 logical drive (as many physical drives as budget/ hardware will allow)
Thanks BaBrown for the reply, sorry if I was a bit unclear in my post.
To clarify, I think most people agree that the LOG file must go on its own dedicated RAID1 and the NAV Database files should go on a dedicated RAID 10, so more my question is about where to put the rest of the files.
I am not lookign for an ideal server setup here. What I wanted to understand, is how everyone sees differently of the value of spindles in relation to the different components of a SQL install, and thus, given a fixed number of spindles, how would you split them. In this case I am saying 14 Drives, which could be split in many ways. If RAID 1 or ten, then we have 7 spindles, RAID 0 we have 14, RAID 4 we have (approx) 5 spindles etc.
So given 14 drives, how would you split them.
Anyway in this Hypothetical scenario, can I assume your suggestion is:
Drive 1 : Raid 1, 1 logical drive (2 actual drives) = Windows + SQL + Backups + general work drive.
One thing that interests me in your post, (if I understand correctly) is that you would put all the SQL DBs (which I assume to include Temp DB) on a spindle with the mdf file. What is the advantage of putting mdf on a separate spindle, rather than on the same RAID 10 set with the rest of the database?
It’s something I picked up taking the Navision SQL certification course several years ago. The mdf file contains the system tables (sometimes referred to as the catalog). Thes tables define the database structure and access to them is required to execute a transaction log backup (among other things). One of the tables (sysfiles) tells the system where the transaction log resides.
In the event of a failure of the RAID 10 array, having the mdf on a separate array allows you to backup the active transaction log. This allows recoovery back to the point of failure. Without this ability, you can only recover as far as your last backup. This adds no performance improvement but offers an extra layer of recovery.
Yes I have read that document, but it has no connection to this thread. I am trying to get people opinions on how to best use a limited number of drives.
No I don’t agree. At first glance yes, the different types of files are distributed across dedicated drives, but the sizes are not realistic in my opinion.
When you create a new database, the disc capacity needed for the initial transaction log is at least 2-3 times the file size of the data files. The NAV restore process is considered to be one giant transaction by SQL Server, and the initial TL will be roughly 2-3 times as big as the initial data file.Based on that I would say the ratio data drive : TL drive being 4:1 is only a good one if the data drives are sized appropriately. I think the question was for 14 identical drives though, so this does not look right to me. You’ll need more space for TL.
Then, when you create a backup of a SQL Server database, the backup file is the same size as the database. So unless the 1 logical drive for OS + SQL, minus the OS and SQL is big enough to hold a number of backup files the same size as the 4 logical drives intended for the data files, you’re going to be in trouble. The backups should go into their own drives, or straight to tape.
1 logical drive (2 discs, RAID 1) for OS, SQL Server, tempdb and NAV.mdf
1 logical drive (6 discs, RAID 10) for NAV.ndf, let RAID controller manage the files, SQL is too busy doing SQL stuff
1 logical drive (4 discs, RAID 1) for NAV TL
1 logical drive (2 discs, RAID 0) for backups, that are regularly backed up to tape
Kill all non NAV stuff, put that on other machines. Don’t allow ‘general work’, except for a NAV installation for admin access
I would advise against spanning the TL across logical drives. I would say it’s better to combine drives 2 and 3 into one logical drive and let the RAID controller manage the files themselves. Let SQL Server do the database type stuff and let the RAID controller do the file stuff.
I disagree. The transaction log is a seqentially written file and is better supported by RAID 1. SQL handles multiple transaction log files very well. The system does not strip across the files.
