<h1>SAN Storage Best Practices for SQL Server</h1>
<h3>How Storage Works</h3>Before we dive into how SQL Server works with storage, you might want to learn how storage works in general. This isnâАЩt required, but having a good foundation will help as we get into the more advanced stuff.
Fundamentals of Storage Systems by Wes Brown âАУ this monster 13-part series explains the basics of spinning disks, disk controllers, RAID, solid state drives, and more.
SQL Server 2008 Internals and Troubleshooting âАУ I think the storage chapter in this book is absolutely outstanding, but I might be biased because I wrote that one.
<h3>How SQL Server Connects to Storage</h3>The term SAN gets misused a lot because it really means Storage Area Network âАУ the communication pipelines between your server and a magic black box called a SAN controller. That controller is the configurable hardware that manages RAID levels, caching, and more. Here, weâАЩre talking about how we plug your SQL Server into the network (SAN) itself, and how it gets there is called pathing.
SAN Multipathing Part 1 âАУ What Are Paths? - I start by explaining a little history of storage and how we got to the messy black boxes we have today.
SAN Multipathing Part 2 âАУ What Multipathing Does - IâАЩll give you a hint: active/active multipathing isnâАЩt as cool as you think.
Finding Your SAN Bottlenecks with SQLIO - the name SQLIO is so misleading, because this free utility from Microsoft helps you troubleshoot bad performance no matter what servers youâАЩre using.
TomâАЩs Hardware Hard Drive Charts âАУ when youâАЩve got your storage speed test results, compare them to how fast a single hard drive goes.
<h3>How SQL Server Uses Storage</h3>The relationship between memory (cache), data files, log files, and TempDB is like your Facebook status âАУ itâАЩs complicated.
SQL University Part 1: The Basics of Storage - the basics of data files, log files, and cache.
SQL University Part 2: Where We Store Stuff - RAID levels, magnetic drives versus solid state, and where to put your data and log files.
SQL University Part 3: Scary Storage Scenarios - why DBCC is so important in SANs and virtualization.
Analyzing What SQL Server Caches by Jonathan Allen âАУ this query looks at the contents of your serverâАЩs memory and shows what database is being cached.
Analyzing What SQL Server Accesses by David Pless âАУ this DMV query checks the read and write latency by database file.
SQL Server Setup Checklist - Now that youâАЩve learned about the basics of SQL Server storage, itâАЩs time to put together a SQL Server. WeâАЩre not just going by best practices here âАУ weâАЩre going for real-world maintainable performance. I tell you what changes to make before & after the install, and some of these have a big payoff in storage performance. Make sure to enable Instant File Initialization, for example, and I explain how in the checklist.
<h3>Vendor-Specific SAN Best Practices for SQL Server</h3>SAN gear is notoriously difficult to configure, but thankfully, storage vendors put a lot of work into writing good documentation on how to set up their gear for SQL Server. Read this stuff, and believe me, thereâАЩs some fantastic tips in here that can make all the difference between good performance and bad.
<h3>HereâАЩs the most common vendor document repositories:</h3>
Dell EqualLogic with SQL Server âАУ mix of a marketing and tech specs whitepaper, but good data.
EMC âАУ EMC hides some of their documentation behind a paywall. You have to prove you have current EMC maintenance before you can access their docs. Boo, EMC. YouâАЩll need to work with your SAN guy to get access to those, but the good news is that thereâАЩs still a lot of useful info at that link.
Hitachi âАУ I havenâАЩt worked with these myself, but clients have found these docs helpful.
HP LeftHand Documentation for SQL Server âАУ unfortunately, HP doesnâАЩt have the best navigation for their support docs, so this link goes straight to Google, which makes searching HPâАЩs documentation easier.
IBM SAN Storage Redbooks for SQL Server âАУ IBM calls their best technical documents Redbooks.
IBM XIV with SQL Server âАУ IBMâАЩs XIV is an unusual kind of SAN.
NetApp SQL Server Resource Guide âАУ those are NetAppâАЩs favorite documents. For a complete list, check out the NetApp Technical Library. For the basics, check out PDF TR-3696 on SQL Server with NetApp.
