What’s the problem with backups? Throughput.
Microsoft SQL Server is one of the most widely used relational database management systems by volume of installations. Its global footprint across all customers is likely in the range of exabytes (thousands of petabytes). Or perhaps even zettabytes (thousands to millions of exabytes). That amount of data needs to be protected, and when it needs to be restored it needs to be done quickly.
With this large, ever-increasing global footprint of data (data gravity), database administrators continually face the challenge of accommodating backups of these databases during a set timeframe. Computationally, this becomes purely a storage throughput (bandwidth) problem.
For a backup, you need throughput on your source storage device to read your database files into memory buffers that will then be written to backup files on the target device. Conversely, for a restore operation, you need throughput on your source backup device to read your backup files. Then you need throughput on your primary storage device that holds your database files to write them at acceptable speeds. In short, it’s all about the throughput.
What is considered an acceptable backup/restore speed these days? You likely have a phone in your pocket that can handle at least a gigabyte per second of throughput. So why should you accept anything less for something as important as production database backups?
Test Your SQL Server Backup Speed
If you don’t know what kind of throughput you’re achieving while backing up your SQL Server databases, you can run a simple query on any instance that will provide throughput stats for your backups. This query is similar to one on SQLBackupRestore.com. (The site is a great resource if you want a deep dive on how SQL Server backups work.)
|
1 2 3 4 5 6 7 |
SELECT database_name, backup_start_date, CAST(CAST((backup_size / (DATEDIFF(ss, backup_start_date, backup_finish_date))) / (1024 * 1024) AS numeric(12, 3)) AS varchar(16)) + ‘ MB/sec’ speed FROM msdb..backupset ORDER BY database_name, backup_start_date |
If your backup speed is measured in less than a 1,000 megabytes per second, your backups are slow by today’s standards.
Make Your SQL Server Backups Screaming Fast
If you need better throughput for your backups and restores so they complete faster, look no further than Pure Storage® FlashBlade® unified fast file and object (UFFO) storage. FlashBlade is a highly parallel, massive throughput device. It can handle entire farms of SQL Server instances backing up to it at the same time. And it breaks through a fundamental metric of backup performance. Data backed up with FlashBlade is measured in terabytes per minute, not terabytes per hour.
So how does FlashBlade achieve that kind of throughput? With threads. Tons of threads.
In the simplest scenario, a SQL Server database sitting on a single logical disk (as seen by Windows) on FlashArray™ backing up against a single .BAK file on FlashBlade will generate throughput, over a GB/sec.
You can only increase parallelism for backup/restore (more threads) on the SQL Server side by placing your database on multiple logical disks on Windows. It doesn’t matter if these are virtual disks sitting within the same VMFS datastore on VMware vSphere or the same CSV on Microsoft Hyper-V. All that matters is that the Windows volumes where your database files sit are different. A backup operation will have one reader thread per logical disk. A restore operation will have one writer thread per logical disk.
When performing a backup operation, SQL Server will generate one writer thread per .BAK file in the backup set. If your target backup device benefits from parallelism and multiple threads, then striping your backup across multiple .BAK files will yield faster performance.
On FlashBlade, you can turn up the volume all the way to 11 by creating multiple SMB shares and enjoy even more parallelism from the system.
Follow the recipe above for your backups across multiple instances and watch how you can achieve an aggregate throughput of over a terabyte per minute.
There are tons of other knobs to consider for backup performance. Fine-tuning them can help speed up your backups even more. Two important ones to consider are:
- MAXTRANSFERSIZE: Specifies the IO request size to be used for your database and backup files.
- BUFFERCOUNT: Establishes how many memory buffers will be available to the backup or restore operation. These buffers will be equal in size to MAXTRANSFERSIZE, which defaults at 1MB.
Hacker’s Guide to Ransomware Mitigation and Recovery
Latest SQL Server Advancements for Backup and Restore Performance
SQL Server has introduced new features that enhance backup and restore performance, improving efficiency and reducing downtime for mission-critical databases. Here are the latest advancements relevant to fast SQL Server backup and recovery:
1. T-SQL Snapshot Backup for Instant Restore
- SQL Server 2022 introduced T-SQL Snapshot Backup, enabling near-instantaneous backups using storage snapshots instead of traditional file-based methods.
- This feature minimizes I/O impact and significantly speeds up restores, especially for large databases.
- When combined with Pure Storage FlashBlade® and SafeMode™ Snapshots, organizations can achieve even faster recovery times with added ransomware protection.
- Command Example: sqlCopyEdit
BACKUP DATABASE [MyDatabase] TO VIRTUAL_DEVICE = 'PureStorage_Snapshot' WITH SNAPSHOT;
2. Accelerated Backup Compression Enhancements
- SQL Server 2022 and later versions provide improved backup compression algorithms, reducing backup file sizes and processing times.
- When used alongside FlashBlade® high-throughput storage, organizations can achieve faster backup speeds with lower storage consumption.
3. Direct Integration with S3-Compatible Object Storage
- SQL Server 2022 now allows direct backup to Amazon S3 and S3-compatible storage, including Pure Storage FlashBlade Object Storage.
- This feature eliminates the need for intermediate storage layers, reducing latency and enhancing disaster recovery strategies.
- Command Example: sqlCopyEdit
BACKUP DATABASE [MyDatabase] TO URL = 's3://mybackupbucket/MyDatabase.bak' WITH CREDENTIAL = 'MyS3Credential', FORMAT;
Why These Updates Matter
By leveraging the latest SQL Server features in conjunction with Pure Storage FlashBlade’s high-speed backup and restore capabilities, organizations can:
- Reduce backup and restore times significantly
- Improve data availability and minimize downtime
- Enhance ransomware protection with immutable SafeMode™ snapshots Optimize cloud and hybrid storage strategies
Will Restores Also Be as Fast?
You bet. FlashBlade isn’t like other backup appliances that ingest data quickly and then slowly drip bytes back to your database for a restore. What’s the point of fast backups if you can’t restore equally as fast?
Just as we adjust our expectations with backups we need to with restores. We should frame the expectation of performance for restores in the following way: FlashBlade will restore your databases so fast that you’ll be reaching the ceiling of your primary database storage write ingest limit.
In other words, you’ll be bound by how fast your storage device can write data to it. Recovering from a data event should never be slowed by the speed of the storage. It is imperative that business continuity happens as quickly as possible. What use are fast backups if restores are slow.
Turn Up the Volume
Get more technical information about how to configure your backups and restores for maximum performance against a FlashBlade device.
