Confidently Slash Downtime with Fast, Simple SQL Server Backup and Restores with FlashBlade®

By:   |   Updated: 2022-08-29   |   Comments   |   Related: More > Backup


Problem

With constantly growing Microsoft SQL Server databases in the terabytes (TB) and petabytes (PB) range, if a database issue occurs, we don’t have time for a debate on what to do because every minute of downtime is costly to the organization. Our DBAs need fast database backups and restores using a simple plan to bring our databases online rapidly.

We need to:

  • Shrink database backup windows to provide more time for maintenance and business processes
  • Improve database backup concurrency to no longer stagger processes
  • Reduce downtime with fast restores as the “Go To” recovery process
  • Get database backups offsite to protect the organization
  • Continue to use familiar SQL Server BACKUP and RESTORE commands
Solution

The tangible and intangible costs related to downtime are staggering across all industries and many of the solutions to reduce downtime are very complex to implement, require additional licensing, numerous database copies and are time consuming to continuously support. As SQL Server DBAs, we rely on database backups as a trusted means to recover a specific database, instance or an entire environment. The first commands many DBAs learn are related to backups and restores and many new DBAs at organizations start managing backups as they move up the ranks.

Backup times, compression and encryption are valuable. But it is actually the time required to restore a database that is extremely critical when downtime occurs, whether it is related to a specific database or the organization has been hit with ransomware.

Unfortunately, every incident is different. Typically, in these situations significant time is spent:

  • Trying to decipher user tickets
  • Assembling the right team members
  • Analyzing the situation
  • Determining the root cause of the issue
  • Figuring out a road to recovery

This process often extends the downtime, increases the stress for the entire team and pushes critical news up to management very quickly.

What if we as SQL Server DBAs could continue to rely on the backup and restore solutions that we have implemented, but significantly reduce our restore times for multiple terabytes of databases to a matter of minutes? Would that time frame enable you to meet your Service Level Agreements (SLAs) with the SQL Server backup and restore solution that you have implemented and are comfortable with today?

This can all be accomplished with Pure’s FlashBlade, a modern, massively parallel and scale out Fast File and Object Store with NFS, SMB and S3 protocol support enabling a backup and restore solution that can easily be integrated into your SQL Server environment to:

  • Drastically reduce backup database times for large databases.
  • Enable shorter backup windows with concurrent database backups across all instances. There is no longer a need to stagger backups which enables opportunities to complete more off-hours tasks and/or database maintenance.
  • Make database restores your first choice for disaster recovery due to ease of use and familiarity for SQL Server DBAs.
  • Integrate native and third-party backup\restore technologies, with all of the native automation, data protection, compression and encryption benefits.
  • Support point in time recovery with transaction log backups to minimize data loss.
  • Get database backups offsite quickly as a disaster recovery best practice.
  • No or minimal code changes to existing SQL Server BACKUP and RESTORE scripts.

The value of FlashBlade is the speed at which data is written to and read from the storage solution this consists of three components comprising the chassis: Blade for processing and flash storage, elastic fabric and Purity operating system. The read and write operations are tremendously fast enabling backup and restore reduction to slash downtime. There is no de-duplication or delta detection under the covers with the FlashBlade system. When the SQL Server BACKUP command is issued, it is a complete backup from the start to the end of the database, giving DBAs the confidence we need to be able to successfully restore the database.

FlashBlade Performance in Action

FlashBlade performance demonstrations by Pure Storage are very impressive with backup and restore scenarios with TB sized databases. FlashBlade integrates with your existing database (*.mdf and *.ndf) and transaction log file (*.ldf) storage platform, so no changes are necessary on your current server. Backups and restore operations are from the Pure Storage FlashBlade, which is where the magic happens to drastically reduce backup and restore windows to minimize downtime impacting the organization. Let’s walk through three examples to understand the value of FlashBlade.

Example 1 - No BACKUP and RESTORE Code Changes with 4x Performance Reduction

Our first example, is a client with conventional hardware who simply swapped the underlying hardware for three-backup servers shared across their environment to now point to FlashBlade. With no code changes in scripts, SQL Server Agent Jobs, Maintenance Plans, etc., this client was able to achieve a 4x performance reduction for their backup execution times. This is an unprecedented improvement with no code refactoring by the DBAs in order to achieve a significant performance gain.

Example 2 - Optimized FlashBlade BACKUP and RESTORE

 In another example, backups are striped across 8 different files with a corresponding number of CPU cores, volumes and threads enabling high performance backup and restore operations. The code changes are minimal with eight backup files specified and the addition of the Compression, MaxTransferSize and BufferCount parameters. It is possible to natively encrypt backups as well, but generally more CPU usage occurs on the SQL Server instance with this parameter.

Here is the sample SQL Server database BACKUP and RESTORE T-SQL code run from SQL Server Management Studio (SSMS) where you can see the full database backups are striped across numerous *.bak files to take advantage of numerous threads to improve backup and restore performance:

SQL Server Backup Code
SQL Server Restore Code

In this example, the single 2 TB database backup using FlashBlade completes in less than 8 minutes with the restore completing in about 6 minutes. This level of performance enables DBAs to shrink backup windows and rapidly restore databases if an issue occurs with familiar native SQL Server commands.

Example 3 - Shrink Backup Windows and Concurrent Database Restores

FlashBlade enables massive parallel processing for SQL Server database backup and restore operations across numerous databases, instances or even your entire estate. This enables DBAs to shrink backup windows, stop staggering backups and reduce downtime with the ability to scale to 150+ FlashBlades to support the most demanding environments.

An example with eight 2 TB databases, backup operations across all eight databases completes in less than 14 minutes and restores in less than 25 minutes using FlashBlade. With FlashBlade even the most stringent backup windows can be met to support maintenance and business needs. If a ransomware issue occurs, FlashBlade will enable your business to get back online with rapid restore.

Pure Storage SMB Performance Acceleration

SMB (Server Message Block) is the protocol most used in Microsoft Windows networks for communication among devices. SMB was first introduced in the 1980’s and Microsoft has improved the protocol with each release. Pure Storage recognizes the global usage of Windows and the SMB protocol. This performance enhancement in FlashBlade instantly improves backup and restore times without any code or network configuration changes for a single backup or restore operation for concurrent operations across numerous databases and servers.

In a recent Pure Storage white paper on FlashBlade they demonstrate backup and restore scenarios with a 4 TB SQL Server database. Interesting results include:

  • Backup rate for 1 to 8 servers running in parallel is between 19.65 to 21.96 TB per hour
  • Multi-Server metrics
    • Backup throughput is between 42 TB per hour and 77 TB per hour
    • Restore throughput is between 35 TB per hour and 37 TB per hour
  • Important SQL Server Configurations
    • Target backup files \ VIPS (virtual IP addresses)'
    • MaxTransferSize
    • BufferCount
    • Compression

Road to Recovery with FlashBlade

So, the question is: how fast can you bring your SQL Server databases online when faced with downtime? FlashBlade can simplify your standard operating procedures for how to recover from a SQL Server database down time event. If there is an issue, you could follow these simple steps:

  1. Set the original database to read-only
  2. Rename the original database name to not overwrite the database files
  3. Restore to a new database in minutes from Pure Storage FlashBlade
  4. Apply Transaction Logs backups to the point before the issue occurred

This is a fast and familiar process for even the most Junior DBAs to confidently support the organization. This eliminates the need to coordinate between DBAs, Storage Admins, VMWare Admins, etc. DBAs can manage, secure and test their backups as well as confidently restore a database, if an issue arises.

Disaster Recovery Best Practice - Offsite Backup Replication

As SQL Server DBAs working to protect the organization, we need to get database backups off site as quickly as possible to support disaster recover best practices. Pure Storage recognizes this need and provides DBAs with fast backup and restore locally to FlashBlade and the ability to copy database backup files offsite. With a single checkbox enabled in FlashBlade, once a database backup is completed it will then be copied offsite to SMB or S3 storage. This simple feature extends the disaster recovery capabilities for the organization to ensure backups are in multiple locations.

FlashBlade Integration for SQL Server Backup and Restore

FlashBlade seamlessly integrates with your existing MS SQL Server storage, products and processes to meet your Service Level Agreements (SLAs). This includes integration with the following backup related products:

  • Native SQL Server BACKUP and RESTORE Transact-SQL commands
  • Catalogic
  • Cohesity
  • Commvault
  • Idera SQL Safe Backup
  • Quest LiteSpeed for SQL Server
  • Redgate’s SQL Backup
  • Rubrik
  • Veeam
  • Veritas

As you can see, your existing products and processes you have implemented and are comfortable with today can still be utilized, while reaping all of the performance and uptime benefits from FlashBlade.

FlashBlade Ransomware Protection

As an added level of security, the FlashBlade SafeMode™ snapshots will protect backups against ransomware with no additional licensing costs. SafeMode can protect hourly or daily snapshots from ransomware as well as the prevention of inadvertently deleting a backup file giving your organization the resiliency it needs.

Beyond the backup, restore, scalability and disaster recovery performance features in FlashBlade, SafeMode also delivers protection from the latest data threats that can cripple an organization.

SQL Server 2022 S3 Integration

SQL Server 2022 with many new features to benefit DBAs, Developers and Business Intelligence Professionals. One area where Microsoft is helping DBAs responsible for backups, restores and uptime is with S3 integration delivering new levels of scalability, availability and durability via a REST API. The SQL Server 2022 S3 integration is expected to be available directly in the T-SQL language resulting in a very low learning curve for DBAs and minimal code changes, but a whole new set of capabilities to access unstructured data.

FlashBlade has been supporting the S3 protocol since 2017 and with the new capabilities of SQL Server 2022, will enable reliable and fast backup and restore operations for SQL Server DBAs.  This type of performance will shrink backup windows and enable concurrent backups.  Further, FlashBlade offers data replication between Flash Blades and from FlashBlade to cloud via S3 or SMB protocols to follow best practices of getting database backups offsite.

To learn more about the SQL Server 2022 Data virtualization and S3 compatible storage systems, check out this demo centric webcast.

Additional Pure Storage Flash Blade Use Cases

Multi-Platform Database Support

Are you responsible for data management of SQL Server, Oracle or other relational database engines? FlashBlade provides multi-platform support for both SQL Server and Oracle. For Oracle databases, RMAN backups are supported. Numerous Pure Storage customers are also using FlashBlade for large scale Data Warehousing projects.

Business Intelligence

FlashBlade was originally engineered for unstructured data storage common for Artificial Intelligence (AI) and Machine Learning (ML) projects. FlashBlade implementations also support real-time AI\ML projects as well as high performance backup and restore to minimize downtime.

Managed Service Providers

For Managed Service Providers and Hosting Providers, FlashBlade enables your organizations to improve backup time, offer new restore options and simplify disaster recovery for your clients with the existing backup infrastructure implemented.

Cloud Support

Check out this article on ServiceNow’s adoption of Pure Storage as the first all-flash data center. ServiceNow was experiencing slow backup and restore processes with significant limitations. But with FlashBlade, the IT Team is able to scale to the business needs of 571 TB of backups, which equates to 2.4 million backups and 5,000 database restores monthly.

State Governments

The Mississippi Department of Revenue knows Pure Storage’s value first hand after moving two major state applications to FlashStack (Pure Storage and Cisco Converged Infrastructure for high performance applications to scale compute, servers and storage independently). The Department is now saving 39.5 hours of staff time daily. But the Department needed more help with backups, and turned to Pure Storage once again for FlashBlade. Now backups are an afterthought, not even a blip on the radar.

Summary

As you reflect on this article, ask yourself:

  • How fast can you restore your SQL Server databases?
  • What does a SQL Server database restore look like for you?
  • Is your disaster recovery plan consistent, predictable and fast for your entire environment?
  • Are you able to meet your current uptime SLAs?
  • Can all the DBAs on your team get your databases up and running quickly?

If you are not satisfied with the answers, then check out the next section to learn how to get started with FlashBlade from Pure Storage to give you the confidence you need to support your organization.

How do I get started with FlashBlade?

The best way to get started with FlashBlade is with Test Drive. This enables you at no cost to test FlashBlade and see the performance and simplicity firsthand. Registration is required.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Pure Storage, makers of FlashBlade.



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips


Article Last Updated: 2022-08-29

Comments For This Article