I've already explained that no, it doesn't make your database server more reliable - and in fact, it's the exact opposite. But what about performance? The answer is going to depend on your hardware and workload, but let's work through an example. I'll take the first lab workload from the Mastering Server Tuning class and…
You're a DBA responsible for making sure SQL Server databases are online, backed up, corruption-free, and fast. Your databases have gradually grown in size over time, and you're starting to hit new size issues you haven't encountered before.
Nightly maintenance windows are getting smaller, you're not able to refresh your development environments quickly enough, and you're not able to run DBCC CHECKDB as often as you'd like.
You've heard these terms thrown around about storage, and you're not sure what they mean, and how they relate to each other. Let's make it simpler by using an analogy. Say you want to measure a shipping company. You're trying to decide whether to use DHL, FedEx, UPS, or your local postal service. You could…
Storage sizing isn't just about space: it's about latency and throughput.
When you move to new hardware or the cloud, and when you try to consolidate SQL Servers, you can't just add up the database sizes and call it a day. Storage sizing isn't just about space: it's about latency and throughput. So how can we look at an existing SQL Server and translate that into requirements?
Your users are complaining that their queries are slow. You've tried changing SQL Server settings, tuning indexes, but the problem just won't go away. You've got a hunch that it's a storage problem, but you need real, empirical proof. You need numbers that no one can argue, and you need to know acceptable targets for those numbers.
I bet you wanna know whether your storage is hot or not, and there's no quicker, easier way to get a rough idea of your storage's capabilities than to fire up CrystalDiskMark. This month, CrystalDiskMark released an all-new version 7, and it makes for even better testing.
Start by downloading it and installing it - sadly, there's no zip file version anymore that you can stick on a network share and run remotely.
Enter SANdman
When people buy SANs, it's often quite a large investment. Whether it's all SSD, all flash, or there are tiers of storage that different types of data live on, those disks aren't cheap. When people visualize their SAN, it's usually just the server and the pool of drives.
But there's some important stuff in between the Server and the SAN -- SAN doesn't stand for Storage Abstraction Nerglefwomp.
During Dell DBA Days 2016, we got to hang out with Doug Bernhardt of Dell's storage team. He's the guy who publishes Dell's SQL Server documentation, builds their Fast Track reference architectures, and gets to play with all kinds of cool toys. Doug emailed us and asked: Is there any solutions type of info (or any…
Let's start with definitions
Many SANs have caching built in. What kind of cache is important, because if you're dealing with non-SSD storage underneath, you could be waiting for a really long time for it to respond.
Let's start with some definitions of the most popular caching mechanisms available for SANs. I'm not going to say 'only', because some vendor out there might have some proprietary stuff going on that I haven't heard of.
We've all had to move a database. For databases above a small size, this is a painful process. While there are a number of ways that you can move a database without downtime, they all involve a lot of extra work. What if there were something easier? What's the Real Problem? For most people, moving…
We've got a client that does big batch jobs every day, loading hundreds of gigabytes of data or more in short bursts. They were frustrated with slow performance on the batch jobs, and after we performed our SQL Critical Care® with 'em, it was really clear that their hardware was the bottleneck. They were using…
Everyone wants to make sure they're getting the best performance out of their solid state storage. If you're like a lot of people, you want to make sure you're getting what you paid for, but how do you know for sure that the drive is performing well?Watch that AverageThe first way to monitor performance it…
I’m a bit of a researcher. When I want to know about a topic, I start off by doing a lot of reading. After I take a lot in, I want to make sure I understand the details. At that point, I try to think like a scientist. I come up with ways to test what I’m learning and see it in action. My process isn’t revolutionary, but it goes something like this:
Wanna get your storage learn on? VMware has a well-laid-out explanation of the pros and cons of different ways to connect to shared storage. The guide covers the four storage protocols, but let's get you a quick background primer first. iSCSI, NFS, FC, and FCoE Basics iSCSI means you map your storage over TCPIP. You…
Buying SAN gear is hard work. You have to compare vendors based on performance, capacity, reliability, ease of implementation, quality of support, and of course, price. When I'm working with a client buying new storage, here's the questions I have them ask each prospective storage vendor. Q: If we buy SSD, SAS, and SATA drives,…
Let's oversimplify the bejeezus out of this complex problem. Suspend your disbelief for a second and work with me:
We have a database server hosting just one 100GB table. Sure, in reality, we've got lots of databases and lots of tables, but we're going to keep this simple. We've got a simple sales table that stores a row for each sale we've ever had. We don't have any indexes: this is just 100GB of raw data in our clustered index.
I've written about how to test your SAN's performance with SQLIO, but I'll be honest with you: that's the hard way. It takes knowledge and time, and you only have one of those. (I'll be charitable and not tell you which one.) Instead, let's get seat-of-the-pants numbers for your storage. Go to the CrystalDiskMark download…
Geeks love duct tape. Sure, we pride ourselves on building a rock-solid solution the right way, but when the brown stuff hits the moving metal stuff, we love to show off our ingenuity. We carry our money in duct tape wallets, wear duct tape shirts, practice ductigami, and hang our duct tape from custom metal brackets.
Database administrators want just one kind of storage: fast.
Companies want just one kind of storage: cheap.
I wanted to love this monster from the moment I read the spec sheets: Network attached storage device with 4 or 6 hot-swappable drive bays Available empty (so you can bring your own drives, including SSDs) iSCSI server with 2 network ports, jumbo frame support, VLANs Official VMware ESX/ESXi, Hyper-V, Windows DFS compatibility RAID 0/1/5/10…
