skip to Main Content

Tintri Blog

Intelligent Infrastructure for the Database Administrator – Part I – SQL Integrated Storage

April 14, 2020

DBAs and Infrastructure admins have been warring for years over resource requirements. The two teams often speak different languages: one talks about transactions per second and wait stats, the other talks about LUNs and IOPS. In fact, many of the infrastructure teams don’t know why DBs need so many resources and there becomes some contention between the teams. Not knowing what resources are actually needed to run a specific workload is a common problem. DBAs will ask for the sky, so they do not have to spend time troubleshooting bottlenecks and infrastructure teams typically don’t have the ability to provide what’s been requested. Most DBA and infrastructure admins do not have the time or skills to measure and thus accurately assess the actual resources needed by a workload and fear of the unknown often leads to wasted resources.

Intelligent Infrastructure addresses this reality and is here to help with this problem. This is the first blog in a series that will cover SQL Integrated Storage by Tintri. In this first part we will focus on what SQL Integrated Storage is and how it is going to revolutionize how we look at storage and infrastructure for databases. And yes, as a long time DBA and expert consultant, I really mean it when I say “revolutionize”. Later blogs will focus on various aspects of this new concept such as cloning, performance, visibility. The subsequent blogs will feature a deeper dive into each aspect.

Tintri is already famous for delivering purpose-built storage for virtual machines: we do not make storage the traditional way. Tintri is now launching SQL Integrated Storage, storage designed for SQL Server databases. This new product will change how the DBA looks at storage while eliminating many of the limitations of traditional storage i.e., standard infrastructure.

DBAs, it is time to expect more from storage and stop thinking about it from how storage vendors have taught you to consume it and instead start thinking about how you want things to just intelligently work.

First let’s take a step back and talk through some concepts. Tintri has pioneered Intelligent Infrastructure for virtual machines; storage is automatically managed at a virtual machine level. I ask what is a virtual machine? It is a collection of files with some meta data attached to a hypervisor. Now what is a database? It is a collection files with some meta data attached to a RDBMS. So, why not use the same technology to manage database files that works so well with virtual machines? With standard infrastructure, databases are placed on drives that are virtual disks. These virtual disks are then stored on a datastore. This datastore is placed inside of LUN or volume on the storage system. The database is now buried under three layers. When storage operations need to occur, they have to pass through all these layers. Plus, all storage tasks are on a per storage unit basis, not per database. SQL Integrated Storage changes all of this: everything SQL Server needs from storage is now managed at the database level.

What is SQL Integrated Storage?

With SQL Integrated Storage, each database file is managed separately with its own paths and queues to the storage system. This enables the database to be managed differently and performance tracked at unheard of levels. Once your databases are migrated to SQL Integrated Storage the DBAs and infrastructure teams will have a single pane of glass by which to see performance numbers for every database and quickly spot performance hot spots and determine what actions need to occur.

Since storage is served on a per database basis, we can now put protection policies in place by using service groups, which allows for very granular settings for databases in terms of snapshots and replication. This is done per database, not per LUN or volume, allowing the flexibility to protect a database at whatever level you want. Plus, with the use of service groups, you can automatically apply these policies to all databases meeting criteria you define. As new databases are added, if they meet your defined criteria then the protection policies will automatically apply. You can recover from a snapshot in seconds or bring it back online to a different server. None of these new options eliminate your need for SQL Server backups, but they add many new tools to your toolbox to help you solve problems. Users of SQL Integrated Storage spend more time deciding how they want to recover from an issue than actually using the tools to affect the recovery.

With SQL Integrated Storage, cloning becomes second nature and creating management processes to prevent sprawl will be a bigger concern than the actual cloning process. Stop worrying about storage and instead focus on how you manage the big picture. Imagine being able to clone a full copy of your production database to one of your lower test/dev/Q&A environments in less than a minute. This includes the time it takes you to login to Tintri Global Center, which is where you complete this task. You can do this per database or groups of databases, to one server or to many servers. Make as many copies as you want with no additional storage consumption until you start making changes to these databases. Each developer now has a full copy of the database to work on in seconds and there’s no need to continue sharing environments – just picture the increase in productivity.

SQL Integrated Storage enables you to offload some of your daily maintenance tasks as well. Performing a full integrity check on a database nightly is highly recommended but normally it takes too many resources and impacts production. With SQL Integrated Storage you can clone the database to a different SQL Server and run CheckDB there, using the exact same set of storage blocks as production, but with no impact on production. You will be able to perform CheckDB nightly. You can offload nighttime full backups as well.

With all these new environments you will soon spin up, management can become a concern. SQL Integrated Storage has a full REST API to enable scripting or integration into the tools you already have in place e.g., full integration with whatever you use for DevOPS or CD/CI. A full, yet simple PowerShell toolkit will be available soon to extend all of these features to PowerShell. The DBAs and developers will be able to use REST or PowerShell to script the snapshots, cloning, replication, and other tasks. Talk about enabling automation.

Intelligent Infrastructure should auto-tune your environment, and SQL Integrated Storage does exactly that. Based upon usage patterns, each database receives a baseline and reserves of critical resources allocated and these resources will be available to your database when it needs them. This process is known as Auto-QoS. We have done QoS on the network for a while, but now you can set QoS for each database. SQL Integrated Storage auto-tunes based upon workload baselines and if you want to change things you can set QoS to guarantee better performance for your critical workloads, or the opposite and minimize the impact of intensive lower priority workloads as well.

Please read the next blog second part in the series which will be a deep dive into the visibility aspect SQL Integrated Storage, as we highlight what you can see and learn about performance and usage of your SQL Server databases.

Regards,

Shawn

Back To Top