1 / 44

SAN Basics for DBAs

SAN Basics for DBAs. Joey D’Antoni SQL Saturday #106 San Juan, PR. Complete the Evaluation Form to Win !. Pick up your evaluation form: In each presentation room Drop off your completed form: Near the exit of each presentation room. www.facebook.com/prpass.

duncan
Download Presentation

SAN Basics for DBAs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SAN Basics for DBAs Joey D’Antoni SQL Saturday #106 San Juan, PR

  2. Complete the Evaluation Form to Win! • Pick up your evaluation form: • In each presentation room • Drop off your completed form: • Near the exit of each presentation room www.facebook.com/prpass Win a book – just for submitting your completed form. Each session evaluation form represents a chance to win .

  3. About Me Principal Architecture SQL Server at Comcast Blog: joedantoni.wordpress.com Twitter: @jdanton Email: jdanton1@yahoo.com

  4. Storage Understanding Storage Types A little bit about SSDs RAID Levels Components of a SAN SAN Benefits SANs and DR Summary

  5. Storage

  6. Different Kind of Storage

  7. Hard Drive Components

  8. Caching Almost all hard drives (SAN, standalone) have some form of caching (RAM in front of storage) For SQL Servers—make sure you have a battery backed cache—if you don’t and power fails—you will lose data!

  9. Why is storage the bottleneck?

  10. Disk Drives Hard Drives can only spin at 15,000 RPM. Hard Drive Performance has improved approximately 50x Have grown in capacity During the performance improvement CPU speed increase 5521x

  11. Latency “Disk latency is around 13ms, but it depends on the quality and rotational speed of the hard drive. RAM latency is around 83 nanoseconds. How big is the difference? If RAM was an F-18 Hornet with a max speed of 1,190 mph (more than 1.5x the speed of sound), disk access speed is a banana slug with a top speed of 0.007 mph.” --credit Christian Paredes Blue Box Group

  12. CPU and Disks As CPUs have gotten faster they have the ability to drive more IOPs. Modern CPUs are so powerful they can saturate a 10 GB connection with I/O requests—your disks can’t possibly keep up

  13. SSDs (Solid State Drives)

  14. SSDs are Fast • Much faster on random reads and writes • At least 5x better performance, often much more • Up to 350x faster on seeks • Not nearly as much of difference on sequential reads and writes

  15. SSDs are Expensive • List Prices From Fusion-IO 160GB SLC ioDrive - $8495 320GB SLC ioDrive - $15495 320GB MLC ioDrive - $7495 640GB MLC ioDrive - $11495 320GB SLC ioDrive Duo – 16,990 640GB SLC ioDrive Duo - $30,990 640GB MLC ioDrive Duo - $14,990 1.28TB MLC ioDrive Duo - $22,990 • These are really great for TempDB

  16. RAID RAID—Redundant Array of Independent Disks Hard Drives Will Fail, RAID is what gives you protection from that

  17. RAID 0

  18. RAID 0, Don’t Do This From a major virtualization vendor benchmark

  19. RAID 0 No data protection at all Best performance If you lose one disk, you lose it all As you add disks risk increases

  20. RAID 1--Mirroring Mirroring No increase in write performance Read performance is increased 50% Capacity Loss

  21. RAID 5—Striping (What you SAN Admin Wants) Maximum Capacity Big Write Penalty—gets worse as more disks are added Not good for highly transaction databases BAARF

  22. RAID 1+0 (10) Mirrored Striping Best performance Requires 4 or more drives Only 50% of actual capacity is used

  23. Summary of RAID Levels Ask for RAID 10 for Everything (you won’t get it) Make sure your TempDB and Logs are on RAID 10 NEVER USE RAID 0!!!

  24. Storage Area Network

  25. What is a SAN? Basically a specialized computer for storage Computer, Switches and Hard Drives Not a performance device Can be used for redundancy and DR purposes Will serve many servers—so critical piece of your infrastructure

  26. SAN Components HBA (Fibre) Card—Connects your server to SAN via Fiber Optic cable iSCSI Card—Ethernet Card connecting server to SAN Switch—Either fibre or ethernet switch connecting server to SAN SAN head unit—Controls processing, RAID levels Disk Array—The physical array behind your SAN (a bunch of hard drives)

  27. iSCSIvsFibre Channel iSCSI is cheaper, and in smaller shops your network admin can manage the switches Over 10G Ethernet iSCSI is faster, but that is still uncommon Fiber is more susceptible to breakage, but currently more common Fibre is faster, generally Note—all SAN components must be the same speed, or network traffic reverts to slowest in chain

  28. Multi-Pathing How your SAN admin sleeps at night! Make sure your databases servers are multi-pathed

  29. SAN Throughput

  30. What are the Benefits of a SAN Expand Capacity easily and on the fly High availability Disaster Recovery

  31. SAN Terms LUN—Logical Unit Number, but in practical terms, what your SAN admin will call a disk that he presents to your server Fibre Channel—Fiber Optic connection to SAN HBA Card—Card that plugs into your server to connect it to the SAN IOPs—I/O Operations Per Second—the way your SAN admin measures performance

  32. SANs and SQL Server—What to ask for TempDB absolutely needs its own disk (and you should have multiple TempDB files) Logs should be on a separate disk from data files Ideally separate system and user DBs If shared instance, put split high utilization DBs onto separate disk devices

  33. SANs and DR—Block WARNING!—Don’t try this unless you have a real budget and a good SAN admin Most SANs vendors have as an option SAN replication Allows for multi-site failover Multi-site clustering fully supported in SQL Server 2012 Expensive—requires fiber connection between sites and expensive software

  34. SAN Replication

  35. Shared Environment vs Dedicated Environment

  36. SANs are Multi-Tenant You don’t want to share disks with the Exchange server File servers are a decent partner for database servers But….

  37. If you have are on a new SAN Most of the previous slides, don’t matter This technology on applies to really high end, enterprise-class storage arrays IBM, EMC, Hitachi

  38. EMC VMax Fast DP Allows for dynamic moves of disk from faster to slower disks (Fibre channel to SATA), or slower to faster (FC to SSD) Totally dynamic, and seamless to servers Moves data in 768k extents Your SAN admin doesn’t know where your data is

  39. What does this mean for the DBA? Overall—don’t worry too much about—the technology is solid Make sure your transaction logs are in a group that can not go to SSD. A lot of what we did in the past, changes with this technology

  40. Test Your SAN Good free tools available like SQLIO Never run this on a production server (and warn your SAN admin)

  41. Summary RAID 0 is bad Hard Drives will always be the bottleneck Be nice to your SAN admin—ask for RAID 10 Split your SQL files across many disks SSDs are fast, but pricey

  42. Questions?

  43. Platinum Sponsors: Gold Sponsors: Silver Sponsors: Bronze Sponsors: Swag Sponsors:

  44. Contact Info Twitter: @jdanton Email: jdanton1@yahoo.com Blog (slides): joedantoni.wordpress.com

More Related