Managing Database Files for Direct Attached Storage
When managing your database files in a direct attached storage situation where the host Operating System manages the disk controller, your options are often quite restricted, and probably have a greater impact that other solutions such as a SAN or a Cloud database server.
Direct attached storage can be internal or external disks managed by one or more disk controllers installed in the database server. The point is, Windows, and the disk controllers installed in the machine are responsible for the configuration, reading and writing from the disks. There is typically no virtual layer between these features.
Your goals are still the same as always. Most likely in this situation you are not going to have a huge amount of memory. So the need for the database to read and write to the disk increases, and disk performance has a greater impact.
The goals are to separate the disk contention for tempdb data file(s), tempdb transaction log file, database data and database transaction log files. In addition, you are going to want to separate (if possible) the disk location used for virtual memory often called the swap file or files. A good practice is to place the operating system on a volume by itself.
So, if you did all of those things you already need six distinct drives. If you want any sort of RAID (drive failover) you are going to need a minimum of six more drives. Obviously, this can very quickly get out of control. So, start with common sense, and see what kind of performance you are getting. Not everyone has heavy transaction levels with huge peaks. If you are running a number of departmental databases, a simple server with few drives can often meet your operational needs.
One way to do that is to put the OS and swap file on the same drive as long as the drive is large enough (usually the case nowadays). You can partition the disk into two separate volumes if you want to keep them from becoming fragmented between them. For failover you could mirror the drive.
For tempdb you could use another drive or mirrored drive and put both the tempdb data files and transaction log files on the same drive. There will be disk contention. But, this is a smaller server, and you may have adequate performance as is. You may want to be sure you could add a disk later and split this up. Because tempdb is created every time the SQL Server service is started, you could run without a mirror. Just remember that if the tempdb drive fails, SQL Server will not be available until you re-locate tempdb.
For database data and transaction log files you have as many options as you have disk drives available, money, and performance needs. A team at Microsoft wrote about a test they did by putting four Disk Controllers in a server. They then created 32 mirrored disk pairs. They then created a stripped set without parity (raid 10) of those disk pairs. The resulting performance rivaled that of a SAN using SATA disk drives.
Most likely, since you are using direct attached storage you are not putting extremely heavy loads on the server. You could probably put together your own raid configurations based on fewer drives with less performant forms of raid such as raid 5 (striped set with parity).
I have compared two database servers where one was an older machine running at a slow clock speed, and the other was a newer machine running at a 1ghz higher clock speed. Memory and disks and controllers were identical. The older machine had 4 separate drives with no RAID. The newer machine had two mirrored sets from the four drives. The database performance of the older machine was much faster than the one with the protected drives. That performance came at the cost of no failover. Any drive could fail, and SQL Server, at a minimum, would not work.
Here are some things to consider
- In the case of direct attached disks, the configuration is even more important
- You will most likely be making some compromise due to constraints of disks available
- If you are using direct attached storage, performance is most likely not your highest priority. It is probably better to have failover (raid) storage
- A High Cache hit Ratio can make up for slower disk performance. So, get a good amount of memory for SQL Cache so that your queries may be fulfilled in cache immediately and wittten to disk as resources allow
Perhaps this will help you get some ideas for further study. Or, if you would like to fill in many of the holes in such a large topic, feel free to comment or write btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Elemental MDX: MDX Time Series Functions: ClosingPeriod(), LastPeriods() and ParallelPeriod(), Pt. 1
Part 1: Join BI Architect, SQL Server MVP and SSAS MaestroBill Pearson in the first of a three-part overview of the MDX ClosingPeriod(), LastPeriods() and ParallelPeriod() functions, specifically designed to support the analysis of data within the context of time. In this Part, we explore the ClosingPeriod() function.
Featured White Paper(s)
Putting Data to Work for Mid-Market Companies
read more)
Featured Script
admin db – find user objects where they don’t belong
It’s common for developers to inadvertantly compile stuff in master once then compile in the entended database, but they don’… (read more)