sql server on sans
DESCRIPTION
How to configure and test SQL Server on storage area networks (SANs) with tools like multipathing and SQLIO.TRANSCRIPT
![Page 1: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/1.jpg)
© 2009 Quest Software, Inc. ALL RIGHTS RESERVED
For audio, please call the phone number in your invitation email. Audio will not come through your speakers.
SQL Server onStorage Area Networks
![Page 2: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/2.jpg)
2
Brent Ozar• SQL Server MVP
for Quest Software• Managed SQL, VM,
80tb SAN for $7b co• Published author• Made lots of
expensive mistakes• www.BrentOzar.com• @BrentO on Twitter
![Page 3: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/3.jpg)
3
BrentOzar.com/go/insidetheblackbox
![Page 4: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/4.jpg)
4
The Agenda• Inside the SAN• DBA Pros & Cons• Starting From The
Beginning• How to Use SQLIO• Multipathing
![Page 5: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/5.jpg)
5
In The Beginning…
![Page 6: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/6.jpg)
6
Next: External Storage
![Page 7: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/7.jpg)
7
Storage Area Networks• Independent• Expandable• Well-Protected• Blazing Fast• Complex• Expensive
![Page 8: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/8.jpg)
8
![Page 9: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/9.jpg)
9
Photo Licensed with Creative Commons from http://www.flickr.com/photos/tophost/2247031208/
![Page 10: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/10.jpg)
10
Photo Licensed with Creative Commons from http://www.flickr.com/photos/tophost/2246209421/
![Page 11: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/11.jpg)
11
Photo Source http://www.flickr.com/photos/tophost/2246209421/
![Page 12: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/12.jpg)
12
SAN Components• Drive enclosures• Controllers (& Cache)• Switch networks• Host Bus Adapters• HBA Drivers• Servers
![Page 13: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/13.jpg)
13
Drive Allocation MethodsShared Dedicated
Grow Volumes In…
Tiny Increments
Whole Drives
Drives Idle… Rarely Often
Performance Management
Easy Hard
Overall Speed Fast Enough Really Fast - Maybe
![Page 14: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/14.jpg)
14
Enterprise Midrange Entry-Level
Uptime 100% >99% >99%
Features Stable Growing Exploding
Interface Fiber & iSCSI
Fiber or iSCSI
iSCSI
Entry Cost $1,000,000 $200,000 $20,000
Vendors EMC HitachiHP EVAIBM
CompellentEMCIBM NetApp
Dell (Equallogic)LeftHand
![Page 15: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/15.jpg)
15
Good Things• Clustering• Table partitioning• Faster builds• Easier growth• Caching speed
Photo Source http://www.flickr.com/photos/pcka/3240903696/
![Page 16: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/16.jpg)
16
Not-So-Good Things• Black box• Caching
may not help• Compatibility lists• More drivers• Tough to grow• SAN admins
may not help
Photo Source http://www.flickr.com/photos/stewf/95709873/
![Page 17: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/17.jpg)
17
“How many gigs do you need?”
![Page 18: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/18.jpg)
18
“We’ll keep an eye on it for ya.”• Perfmon changes• Neighbors change• SAN changes• SAN monitoring
software is tough
Photo Source http://www.flickr.com/photos/mrjoro/39470992/
![Page 19: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/19.jpg)
19
What’s Your Bottleneck?• Not Enough Drives• Drives Too Slow• Shared Drives• RAID Level• SAN Controllers• Partition Alignment• Drivers• Multipathing
![Page 20: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/20.jpg)
20
Things to Monitor• Hard drives• Controllers (CPU, caching)• Controller connectivity to
the network• Network switch throughput• Server connectivity to the
SAN (the HBA) • Server drivers
![Page 21: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/21.jpg)
21
The X Factor: Redundancy• Hard drives – hundreds• Controllers – 2+• Controller connectivity to network – 4+• Network – separate networks, switches• Server connectivity to SAN – 2+• Network fabrics – iSCSI, Fiber, WAN
![Page 22: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/22.jpg)
22
SAN Monitoring• Strict HCLs, SCLs• Cross-platform
means JAVA• No “full” picture• Very, very, very
expensive
Photo Source: http://flickr.com/photos/earthandeden/865393933/
![Page 23: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/23.jpg)
23
Unpredictable, Unexplainable
![Page 24: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/24.jpg)
24
Start From The Beginning• Get block sizes right• Align partitions• Design for pathing• Test with SQLIO• Install SQL Server• Monitor sec/read, write• Save history
![Page 25: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/25.jpg)
25
Simple SAN Pathing
![Page 26: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/26.jpg)
26
Simple SAN Pathing
![Page 27: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/27.jpg)
27
Simple SAN Pathing
![Page 28: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/28.jpg)
28
Simple SAN Pathing
![Page 29: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/29.jpg)
29
SIMPLE?!?1#%&@!?
![Page 30: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/30.jpg)
30
Photo Licensed with Creative Commons from http://www.flickr.com/photos/creatista/204265480/
![Page 31: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/31.jpg)
31
Goals of Multipathing
1. Protection
2. Performance
![Page 32: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/32.jpg)
32
Active/Active Can Mean
![Page 33: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/33.jpg)
33
Active/Active Gotchas• One path per LUN/volume• Sending vs receiving• Post-failover stickiness• Network design
![Page 34: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/34.jpg)
34
Network Bottleneck Example
![Page 35: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/35.jpg)
35
Pathing Really Matters Now
![Page 36: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/36.jpg)
36
Photo Licensed with Creative Commons from http://www.flickr.com/photos/thatguyfromcchs08/2300190277/
![Page 37: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/37.jpg)
37
Photo Licensed with Creative Commons from http://www.flickr.com/photos/atomdocs/3127663764/
Why Can’t We Go Faster?
![Page 38: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/38.jpg)
38
How to Run SQLIO
sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
![Page 39: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/39.jpg)
39
Write This Down. It’s Important.
sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o16 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o32 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o64 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o128 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t4 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t4 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t4 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t4 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
![Page 40: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/40.jpg)
40
We Did the Hard Work
![Page 41: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/41.jpg)
41
The OutputE:\Program Files (x86)\SQLIO>sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat sqlio v1.5.SGusing system counter for latency timings, -1361967296 counts per second2 threads writing for 120 secs to file M:Testfile.dat
using 64KB random IOsenabling multiple I/Os per thread with 1 outstandingbuffering set to use hardware disk cache (but not file cache)
using current size: 24576 MB for file: M:Testfile.datinitialization doneCUMULATIVE DATA:throughput metrics:IOs/sec: 1539.50MBs/sec: 96.21latency metrics:Min_Latency(ms): 0Avg_Latency(ms): 0Max_Latency(ms): 572histogram:ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+%: 66 32 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
![Page 42: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/42.jpg)
42
Common Bottlenecks• 1 gig = ~125 MB/sec• 2 gig = ~250 MB/sec• 4 gig = ~500 MB/sec• 10 gig = ~1250 MB/sec
![Page 43: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/43.jpg)
43
Working Around It• Data/logs/tempdb
on different volumes• Separate
groups of DBs• Separate DB
into filegroups• TempDB, backups
Photo Source http://www.flickr.com/photos/doodlemonger/2667917978/
![Page 44: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/44.jpg)
44
Wrapping It Up• Test before
deployment• Learn multipathing• Design DB files
around SAN limits• Monitor forever• Get vendor help
Photo Source http://www.flickr.com/photos/doodlemonger/2667917978/
![Page 45: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/45.jpg)
45
Advanced Topics• Table partitioning• Filegroups & files• Snapshot backups• Storage virtualization• Storage VMotion• SAN administration
Photo Source http://www.flickr.com/photos/thomasthomas/504369245/
![Page 46: SQL Server On SANs](https://reader033.vdocuments.us/reader033/viewer/2022061214/549a0b69b479591e4d8b462e/html5/thumbnails/46.jpg)
46
BrentOzar.com/go/insidetheblackbox