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

By:   |   Updated: 2021-05-17   |   Comments   |   Related: More > Backup


Problem

With constantly growing Microsoft SQL Server databases in the terabytes (TB) and petabytes (PB) range, DBAs managing hundreds of instances, interwoven dependencies between numerous applications, partners and more, how can we minimize our downtime? Unfortunately, outages of even a few minutes impact our clients, employees, partners and business reputation. Worse yet, outages make the news and this is not the publicity our company needs or wants.

As a SQL Server DBA, how can we get our SQL Server databases online as quickly as possible when an issue occurs with one database or all if we are hit with ransomware? If an issue occurs, we do not 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.

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 multi-terabyte databases to less than 10 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 simultaneous 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.

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 B-Tree, giving us as 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.

Further, the SQL Server BACKUP and RESTORE commands are familiar. In this example from Pure Storage, backups are striped across 8 different files. The only parameters specified are Compression, MaxTransferSize and BufferCount. 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:

SQL Server Backup Code
SQL Server Restore Code

In terms of the time and throughput, check out the following results from FlashBlade:

FlashBlade Performance Summary – BACKUP Database and RESTORE Database
Test Full Backup Time Throughput Full Restore Time Throughput
8 Databases @ 2 TB each with simultaneous operations 13 minutes and 54 seconds 2578 MB per second 24 minutes and 20 seconds 1480 MB per second
1 x 2 TB Database 7 minutes and 57 seconds 5511 MB per second 6 minutes and 4 seconds 3650 MB per second

FlashBlade’s value is the speed at which data can be written to (i.e. BACKUP) and read from (i.e. RESTORE) the storage system. In the scenario above, 1 TB or more of data is backed up per minute with backups striped across 8 VIPS and 8 files, which helps to load balance across the FlashBlade. This translates to more than 60 TB per hour for the SQL Server backup process and 44 TB per hour for SQL Server restores for 8 x 2 TB databases running in parallel. Further, the backup and restore times will improve with more Flash Arrays and testing various MaxTransferSize and BufferCount settings. The FlashBlade performance results in significantly faster backups, restores and slashing downtime with the familiar backup and restore process that SQL Server DBAs know and trust.

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.

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.

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


Last Updated: 2021-05-17


get scripts

next tip button



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
Related Resources



Comments For This Article