Storage Tiers What SQL Server DBAs Need to Know

Published On: 2021-01-19By:

“One Gerbil, Two Gerbils or Three Gerbils?” is a common DBA joke about server and storage performance. No matter how many gerbils power your storage, you need to know what type they are and the power that they provide. Storage is not about gerbils it is about IOPs, bandwidth, latency, and tiers.Two Gerbils, One Wheel - YouTube

As a DBA it is important for you to understand and know what kind of storage is attached to your servers and how it is handling your data. It is not important to master everything about it, but it is very advantageous to be able to talk to your storage admins or “Gerbil CoLo, LLC” provider intelligently especially when you experience performance issues.  Here is a list of things to I encourage you to know and ask.

Terminology

IOPs– IOPS stands for I/O (single read/write request) Operations Per Second. This is a performance metric that is dependent on the type of storage being used and can vary widely. It is important to understand how fast your storage can process data by knowing the expected IOPs and the actual IOPs once the array is processing workloads.

Bandwidth or Throughput – This is the measure of the size of the data in the I/O request.  You can figure out throughput by taking I/O request size multiplied by the IOPs the Measure will be in Megabytes and Gigabytes per second.

Latency– In my opinion this is the most important metric to understand. It’s the time it takes to process that I/O request. Its an indicator of a possible storage bottleneck. You measure this time from when the request is issued to when the request is completed. This determines the responsiveness of your storage.

Storage Tier & Automatic Storage Tiers–  A modern day array can be divided into tiers some of those tiers can be slower spinning disks while others can be fast flash or a hybrid of both. I think of these in terms of gerbils. You can get a small gerbil who has little legs that can run a marathon, a medium one that runs at a moderate speed 5k and a large gerbil that’s a speed racer. These together can work separately (pinned) or merged into a team like in a relay. Your data can be pass like a baton through each tier (automatic). Another words your data can be demoted or promoted between tiers of the storage device when needed for performance and compacity.

Performance Metrics

Note these apply to the Guest OS, there are metrics for the Hypervisor/Storage Stack that DBA’s do not normally have access to. The important part is that the different parts of the stack should mainly be in agreement about those numbers. If latency at the array side > latency at the Guest OS level, there is a big issue somewhere

Avg. Disk sec/Read – Shows the average read latency.

Avg. Disk sec/Write – Shows the average write latency.

Avg. Disk sec/Transfer – Shows the combined averages for both read and writes.

Disk Transfers/sec – is the rate of read and write operations on the disk.

Disk Reads/sec – is the rate of read operations on the disk.

Disk Writes/sec – is the rate of write operations on the disk.

Avg. Disk Queue Length – is the average number of both read and write requests that were queued for the selected disk during the sample interval.

Current Disk Queue Length – is the number of requests outstanding on the disk at the time the performance data is collected.

Here is a great session by my friend Argenis Fernandez (T) to better educate yourself. I highly recommend you start here.

Storage Types

RAID (redundant array of independent disks) –RAID is a solution that protects your data from a disk failure. You tend to hear administrators talk in terms of RAID 0,1,5,6 and 10. As database administrators you need to know what RAID type your data is on. For Tempdb you want it on the fastest RAID as possible RAID 1 or 10 while maintaining disk fault tolerance. This is usually old SANs and no longer a concern with modern storage arrays.  Modern arrays take a different approach with object-based storage models, which is more like the cloud.

FLASH –High speed storage based on non-volatile memory, you may see it referred to as NVMe, Non-volatile Memory Express. These are SSD, solid state drives. One thing to keep in mind is that NVMe’s are SSDs but not all SSDs are NVMe’s there are different types of SSDs.  Not matter what type of SSD it is these are really great for Tempdb workloads.

Hyper-converged– This is referred to as HCI. Both the storage, networking and compute are bundled into one. This is a newest all in one hardware that claims to save money and creates ease of use. Keep in mind that these now means the HCI processing power is now handling everything (networking, storage, IOPS, etc).

Services

Snapshots– A capture of the state of your data is taken at a point in time. These snapshots can be used as restores or backup copies. These are usually snapshot copies of your mdf and ldf files. Note: Uncommitted transactions are not captured, and snapshots are not necessarily a replacement for backups. If your sysadmin asks about doing snapshots in lieu of backups, it’s your job ask a lot of hard questions to backup or storage vendor who is doing the snapshots and test both the back and more importantly the recovery. You need to be asking about to point in time recovery and how to handle page level restores for corruption just to name a couple.

Clones– A volume copy of your data, think of this a disk drive copy. It takes the files a makes a replica from snapshots creating a database copy.

Disk Replication (sync and async) –The replication of logical disk volumes from one array to another in real time (synchronous) or asynchronous for disaster recovery and continuity.

Summary

If you can educate yourself on these it will go a long way to being able to make sure you can have intelligent conversations with your storage admins or providers. This will enable you to better ensure you can advocate for your SQL environment when you experience performance issues related to storage.  If your data is hosted elsewhere, like Gerbil Colo, LLC or even in a public cloud like Azure, make sure they can provide the above metrics to you.  If they can’t, it might be time to host your data elsewhere.

The post Storage Tiers What SQL Server DBAs Need to Know appeared first on A Shot of SQLEspresso.

Contact the Author | Contact DCAC

Azure Cloud Shell–Do You Use It?

Published On: By:

One of the challenges of being a consultant is having to work with a number of clients, and having different login credentials and accounts. In the early days of Azure, this was exceptionally painful, but over time the experience of using the portal with multiple identities and connecting to Azure tenants has gotten much easier. However, when writing PowerShell or Azure CLI code, switching accounts and contexts is slightly more painful. Also, when you are doing automation, you may be touching a lot of resources at one time, you want to be extra careful that you are in the right subscription and tenant.

Enter cloud shell.

If you click on the highlighted icon from the Azure Portal, you will launch cloud shell. This will require you to have an Azure Storage account which will consume a small amount of resources (€$£)–don’t sweat this–it’s literally going to cost pennies per month, unless you decided to upload terabytes of images to your cloud shell (don’t do this). The storage is there so you can maintain a history of commands and even store script files there.

With cloud shell you are automatically logged into the tenant associated with your login–you will still need to select the subscription. As shown below–you can see the subscriptions available to your login.

The other cool thing about cloud shell is that you also have built-in text editors including vim and code. While means you can paste code into a text editor and save it in your shell. Since you have a storage account that data is persisted. So you can have a bunch of scripts saved in your cloud shell. This is great for developing for Azure automation, or just running some ad-hoc scripts.

You can also go full screen with code–as shown above. While all of the examples I’ve shown have been PowerShell, you can also launch a bash shell running the Azure CLI.

Contact the Author | Contact DCAC

Happy New Year!

Published On: 2021-01-01By:

Happy New Year!!

Looking back at my Happy New Year! post from the start of 2020, I of course, didn’t realize the brute force of the global pandemic that was about to hit.  Even with the dumpster fire that was 2020, good things still occurred even in the midst of the world burning.  Babies were born.  Relationships started.  New doors opened with new opportunities and the list continues.  2020 is behind us now and in the review mirror.  Let’s focus on making 2021 a vast improvement from 2020.

With that said, I’m going to repeat my thoughts for the new year.  They are still valid and hold true.

Here are some thoughts for the new year:

  • Don’t wait to start that project.  Some day never comes around.
  • You are perfect the way you are.  However, if you want to get healthy, know that you aren’t alone.  You got this.
  • Tell those people close to you how you feel about them.  Tomorrow may not come for some.
  • Tell your kids (fur babies count too!) that you love them as often as you can.  My kids hear it from as often as I can.
  • Finally, be kind to each other.  The world is harsh enough as it is without us beating each other up.

See the source image

Hopefully this post comes at a time where we see the Covid vaccine rolling out to the masses.  Until then, keep wearing a mask and stay safe out there.

Peace.

© 2021 – 2020, John Morehouse. All rights reserved.

The post Happy New Year! first appeared on John Morehouse.

Contact the Author | Contact DCAC

501c3 Orgs Can Get Some Office 365 Licenses For Free

Published On: 2020-12-30By:

Yep, you read that headline correctly. Microsoft is making some Business Premium Office 365 licenses as well as some Business Basic Office 365 licenses available to US-Based 501c3 non-profit organizations, for free.

Free on a Calculator
Photo by Credit Score Blog

There’s a few steps to get these licenses.

The first step is to setup an Office 365 / Azure tenant for the nonprofit via https://azure.microsoft.com/

The second step is to register with Microsoft as a nonprofit. Once approved as a nonprofit, the licenses can be provisioned for you.

The third step is to contact DCAC (or another CSP, but we prefer if you go through DCAC) to provision your free Office 365 licenses. There will be some paperwork to sign to get the license setup, but it’s done quickly and easily through DocuSign.

That’s it, once the licenses are assigned you can setup everything that’s needed to move your email domain to Office 365.

If you are a 501c3 and are looking for an email solution, follow these three steps and you’ll be good to go.

Denny

Contact the Author | Contact DCAC
1 2 3 482

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link