In the server world we have three different kinds of storage available. Today, only two of these can be used with your SQL Server (as long as you want to keep the SQL Server in a supportable configuration). Your three options are Direct Attached Storage (DAS), Network Attached Storage (NAS) and Storage Area Network (SAN).
Network Attached Storage is the configuration that you shouldn’t be using with your SQL Server. NAS can be used with SQL Server if you drop in a trace flag and run your server in an unsupported configuration. However if there is a problem with the SQL Server Microsoft PSS (CSS, whatever they are called this month) may not be willing to help you as officially SQL Server does not support Network Attached Storage. NAS devices are specialized devices, typically running some flavor of Linux which present network shares which Windows can recognize to the network so that people or services can access the storage over the network. NAS devices can also be running Windows sort of like a traditional file server where you can access the files over the IP network.
Direct Attached Storage also called JBOD (Just a Bunch Of Disks) or local storage is storage which is directly attached to the back of the server. There will probably be a couple of disks which sit within the server, and when these are outgrown you’ll get an external shelf of storage which has more disks in it. These will be connected via SCSI, SAS or fibre channel cable to a card within the server. For SAS or fibre channel DAS units the controller which does the RAID will probably be within the shelf which holds the disks. For older SCSI units the card which handles the RAID will probably be within the server (there are SCSI shelves which have the controller within the shelf). Direct Attached Storage is usually faster than SAN storage as the disks within the DAS units are dedicated to the servers. What you gain in speed you loose in flexibility, and manageability.
Storage Area Network storage (SAN) is very flexible storage which has a big management overhead. But that management overhead gives you lots of options, and makes it very easy to reconfigure the storage on the fly without any changes to the servers. You can easily extend the storage, reduce the storage (if you have Windows 2008 and a storage array which supports making the volumes smaller), move the volume to faster storage, or slower storage all with no outage to the server. You don’t even need to tell the server. Because there is a lot of management involved with storage arrays (the device which actually holds the disks, also called arrays among other things) correctly configuring storage arrays for maximum performance is quite difficult and usually isn’t done to maximize the performance of the array. Storage arrays don’t ship in the best possible config, then need to be tweaked and tuned to fit the workload that your specific environment will be putting on them. All to often people that are managing the storage array don’t understand what all the nobs within the array management software do, so they don’t touch them (which is probably good) or the tweak them incorrectly (which is very, very bad).
I’ve talked to people that have deployed storage array’s from EMC that have gotten less than 1/2 the performance than I’ve been able to get from similar storage arrays. What makes me think that this is a storage array configuration problem is that they were using high end 15k RPM disks, while I was using 10k RPM disks. Obviously the workloads were different, but their workload was better suited to high speed storage than mine was. They were doing sequential reads (where the blocks which are being read and right next to each other on the spindles) on a RAID 10 array while I was doing VERY random reads and writes (where the block which are being read and written are all over the array) against a RAID 5 array.
Hopefully this helps shed the light on some of the terms which you may hear flying around your office.
In a perfect work, you would use RAID 10 for everything. However we don’t work in the perfect world, we have budgets to deal with. And these budgets mean that we have to make sacrifices at times so we don’t get what we want.
RAID 10 is very expensive to implement, much more so per gig than RAID 5 or RAID 6, especially if there are a lot of disks in the RAID array. If the database doesn’t specifically need more performance than the RAID 5 array can provide, then using a RAID 10 array is just a waste of money. And that money is money that could be used for other projects that the company is trying to complete.
In the real world that we all work in (or at least most of us) performance comes at a cost, and those costs have to be controlled. If you need RAID 10, and you actually need it, and you’ve got the budget for it, then use it. Otherwise use something less expensive. If you aren’t sure if you need RAID 10 or not, start with a lower level such as RAID 5 or RAID 6, and if needed switch up to RAID 10.
Welcome to the first day of storage week of SQL University. Over the next week, we’ll be covering some different topics, at different levels, but hopefully the information is useful to everyone.
One of the big reasons that companies pay for storage arrays is that when it comes to expanding storage (LUNs) you can do it quickly and easily with no downtime. This begs the question, how do you expand your storage when you are using local disk, with little to no downtime?
So assume that you’ve got 4 72 Gig spindles in your server, and you want to upgrade these to 400 Gig spindles, you’ve got two options. The first requires a lot of downtime, the second requires basically none.
With a bit outage
If your RAID card doesn’t support online expansion (the documentation for the RAID card will tell you) then you’ll have to take a decent outage to do this. You’ll need to connect another hard drive like a large USB drive, down all the services, and copy all the data off of the disk onto the USB drive (backing everything up to tape will work as well). Then down the server, remove the old drives, put in the new drives and create a new RAID array. Boot Windows back up, create the partition (don’t forget to align the partition) copy the data back to it and you are up and running.
With a really small outage
If you have a RAID card that supports online expansion (the documentation for the RAID card will tell you) then you can do this with little to no downtime. First replace each disk one at a time, leaving enough time to rebuild the RAID array so that you don’t loose any data. Once all 4 disks have been replaced (this will take 2-3 days to get to this point) either open the RAID cards management tools, or reboot into the RAID card’s BIOS and have the RAID card expand the virtual disk (or what ever the manufacture calls it). Once that process is done bring Windows back up if you had to reboot into the BIOS, and open diskpart and use diskpart to extend the volume.
This is done by opening diskpart and typeing in the following:
select disk n
You use “list disk” to find the disk number, then use “select disk n” to select the correct disk where n is the number of the list that shows from the output of list disk. The “extend” command extends the volume to fill the disk.
Something which was talked about at the last SoCal Code Camp was running a bus from SoCal to Phoenix to help our two communities get to know each other better, and to give speakers and attendees easier access to more events.
So along those lines a bus has been setup taking people from SoCal to the next Desert Code Camp and registration is open to get a seat on the bus. Now if you plan on taking the bus don’t wait until its to late to sign up. If enough people don’t sign up for the bus, it’ll be canceled.
If you plan on attending be sure to get signed up soon, and we’ll see you in Phoenix.
Microsoft is putting on a great free three day online class for VMware professionals that need to learn more about Hyper-V.
Day 1 will focus on “Platform” (Hyper-V, virtualization architecture, high availability & clustering)
Day 2 will focus on “Management” (System Center Suite, SCVMM 2012 Beta, Opalis, Private Cloud solutions)
Day 3 will focus on “VDI” (VDI Infrastructure/architecture, v-Alliance, application delivery via VDI)
The class is being taught by two top notch presenters Microsoft Technical Evangelist Symon Perriman (a good friend of mine, who knows his stuff) and leading Hyper-V, VMware, and XEN infrastructure consultant, Corey Hynes.
Each of the days is a separate event so you need to sign up for each one separately. I’ve made each of the days a separate link to make your life easier. Just click through each link and register. The only downside to the amazing training opportunity is that it is happening during the Dev Connections conference, so if you are attending Dev Connections in April, 2011 you won’t be able to take advantage of this amazing FREE (did I mention FREE) training event. As I’ll be at Dev Connections I won’t be able to make it, which is something which I really bummed about.
In case you missed all the talk about SQL Excursions yesterday, I’ll recap here since a lot of people read blogs but aren’t on Twitter.
SQL Excursions is designed to provide fun, educational SQL Server training events. The excursions will be held in beautiful locations across the US (and eventually world wide). Each excursion will also have social events for the session attendees as well as spouses, partners, guests, etc. so that they can come to the excursion and not be bored by the technical sessions.
Each excursion will have top notch speakers, typically Microsoft MVPs and/or Microsoft Certified Master recipients. As each excursion is announced the speaker and session outline will be posted as well. What will make this training different from other kinds of training is that while a basic outline will be posted with what the speakers are planning on speaking about, the exact content will be voted on by you, the session attendees. This will ensure that the you receive the sessions that will be of the most use to you in your day to day work life.
If you are on Facebook I’ve got a page there, and if you are on Twitter there’s the official SQL Excursions account (@SQLExcusions). As the excursion is ready to be announced (there’s a bunch of legal stuff which I need to get out of the way) it’ll be announced on Facebook, Twitter as well as the SQL Excursions web page. There’s a news letter which you can sign up for on the home page of the SQL Excursions web page so that the new information will be sent directly to your inbox.
If you were planning on attending the SSWUG virtual conference “DB Tech Con” which is happening April 20-22, 2011; and you’d like to save $30.00 of the cost of the conference, have I got a deal for you. When you sign up using the discount code SP11DBTechDC you’ll be given a $30 discount off of the current price.
You can sign up on the normal registration page and enter the code SP11DBTechDC into the VIP Code box then press the “Update Registration” button.
Join me Wednesday March 9th at 8am Pacific (11am Eastern) as I join Marathon Technologies as I present a webcast titled “Controlling SQL Server Sprawl: The Consolidation Conundrum and Availability Imperative“. During this session I’ll be talking about some of the benefits and risks consolidating SQL Server databases and instances.
If you have a SAN RAID 5 LUN for your data and a SAN RAID 10 LUN for your logs, and say a local RAID 1 array for the C drive in the server itself, where do you usually install the SQL Server binaries to?
In this sort of configuration, which is pretty normal actually, I would but the SQL Server binaries on the C drive.
The last two weekends I’ve presented at two different SQL Saturday events, so as I’m pretty lazy I’m posting the decks for both weekends in a single blog post.
SQL Saturday 47 – Phoenix
- Back to Basics; Getting Back to the Basics of SQL Server
- SQL Server Clustering 101
- Where should I be encrypting my data
SQL Saturday 65 – Vancouver, BC
Now the slide decks for my encryption deck are the same, just different templates, but I wanted to upload what people actually saw.