Database storage has evolved in many ways in recent years. Besides dramatic increases in performance from NVMe interfaces, and ultra-fast SSDs, different approaches to storing data have provided increased flexibility. For example, Azure SQL Database stores its data and log files as HTTP targets in Azure Storage. This concept is known as object-based storage, whereby the storage is aware of all of the files on the system, and can store additional metadata about those files. Tintri has been at the forefront of this object-based approach for years with their VMstore systems, which greatly reduced the effort to deploy and configure storage for VMware and Hyper-V virtual machines. In addition to ease of deployment, storing files as objects allows for better data collection and integration features like the ability to snapshot your files.
Tintri has recently introduced database awareness into their VMstore systems with SQL Integrated Storage. This means that instead of storing your data and log files on say a volume you created within your VM, you create the database files as SMB shares, where they are stored in the file system of the storage. This approach provides a number of benefits over storing your databases in the file system of your VM. The first being that the storage system can capture metrics around latency detail and IOPs in real-time.
It also means you can truly control the IOPs for data or log files—this is particularly useful for problematic databases associated with operational applications (think SCOM or McAfee EPO) that are impacting your storage and the performance of mission-critical databases. Another performance benefit is that each database can have its own I/O channel, which avoids queuing in Windows – something that can occur when a device becomes overwhelmed with I/O.
Beyond capturing metrics and providing automated quality of service, you can back up your databases using crash-consistent snapshots. Snapshots can be a controversial topic amongst DBAs—clumsy solutions from some vendors have broken availability groups and, in the worst cases, even caused corruption. Since the Tintri VMstore contains the file system where the database file lives, it is not using the traditional VSS framework that requires freezing (or stunning) SQL Server’s I/O. This means you can realize all of the benefits of snapshots without disruption when performing tasks like moving production databases to lower tier environments and creating new copies for availability group replicas – in addition to traditional backup and restore operations.
The process for cloning database files can be executed either via the Tintri Global Center user interface for the VMstore system, or by using REST API calls. Tintri is releasing a set of PowerShell cmdlets in the near future, so you will be able to integrate your automation workflows for snapshots and volume management.
As mentioned earlier, by storing the files directly on the VMstore system, Tintri SQL Integrated Storage is able to collect a great deal of metadata about the operations on your data files. This capability provides real-time insight into the number of IOPs your databases are performing as well as information on throughput, deduplication, and utilization. The closest thing to that kind of information and insight is the dynamic management function sys.dm_io_virtual_file_stats, which has good information, but is reset every time the instance restarts. As a result it can be harder to analyze trends over time. Additionally, it can be harder to identify spikes in demand because a database can be extremely busy, say during a batch process, and idle the rest of the time. Tintri SQL Integrated Storage includes an intelligent dashboard which helps you identify your busiest volumes and individual database. Having a storage solution that is fully aware of your databases and their I/O activity is another tool in the arsenal for a DBA. This enables DBAs to both better understand and control I/O performance and take advantage of features like snapshots to provide much better service to your organization.
You can learn more about Tintri SQL Integrated Storage at https://tintri.com/sql.
Note: DCAC was compensated by Tintri for this post
