Around the Storage Block

Data protection for Microsoft SQL databases on HPE Nimble Storage


SQL DBA/Storage Admins love deploying SQL Server databases on HPE Nimble Storage systems. Learn how HPE StoreOnce backup systems and HPE Cloud Bank Storage can help with data protection both on and off premises.

data protection_SQL server_cloud_blog.jpgIf you ask a group of SQL data base administrators (DBAs) if backups are an easy topic, the answers might be divided halfway between yes and no. What matters more than the answers is if they have factored in “recovery” as a part of how they have devised their backup and data protection strategy. The reality is, SQL backups can be a tricky topic for many DBAs.

First of all, DBAs have to find about where to dump the SQL backups. A lot of the DBAs I have talked to end up putting the backups on the same array as the production DB on a different tier of disks. But is this a good idea? (I’ll discuss later why it is not.) The DBAs also has to select an optimal backup plan among various options available: weekly Fulls + daily differential (or) weekly Fulls + daily Incremental (or) daily Fulls—to satisfy the strict backup window SLAs.

Secondly, DBAs have to experiment with a zillion backup parameters: number of backup stripes; whether or not to enable backup compression at the cost of performance; balancing the PROD DB performance so that it doesn’t drop while the SQL backups are running; storage capacity planning for hosting the backups; and schedule and retention planning for optimally using the storage.

Once the DBAs determine all of this information for an optimal backup path, they need to “verify” the backups on an ongoing basis by: executing drills; thinking about how to create another “offsite” copy on top of backup for DR purpose; and, most important of all, determining how quickly a restore can get the data back and the the latest data can be retrieved from the backups. (Or in acronym terms: RTO and RPO.)

To put it simply: The backup process may have been streamlined but was “recovery” a key part of the decision-making process? Phew! Tiring right? Don’t worry. It can get easier!

So what can DBAs conclude from this discussion?

Time for the key takeaways so far:

1. Admins should not put all their eggs in the same basket, that is, don't put your prod DB storage as well as backup on the same hardware.

Nimble array resilient architecture provides six 9’s availability and also supports on-array snapshot capabilities that can be used for instantaneous recovery purposes/active-backup use-cases (i.e. spin up a SQL database immediately from a snapshot or cloning a database from a snapshot for test-dev/QA/Reporting purposes). It is true that one can go on creating hundreds of snapshots on the Nimble array.

In spite of the array’s resilient architecture, is it a good idea to use yet another LUN from the same array as your production database and mount it as a NTFS/ReFS volume for your SQL maintenance plans’ backup destination? No—bad idea! It is analogous to insuring yourself with a billion dollars and adding only yourself as the beneficiary. J A better approach to data protection would be to practice the proven 3-2-1 rule. It works and will save you in times of need. Guaranteed.

To meet this need: Introducing HPE StoreOnce backup systems as a backup target for HPE Nimble Storage systems.Data Protection 3 2 1 Rule.jpg2. SQL backups need to be the least “operational time spent” for a DBA on a daily basis.

As a DBA, your time is best spent on qualitative tasks, such as database design or enabling dev teams—rather than on operational tasks such as backup. Unfortunately, most DBAs get sucked into “transactional” activities like backup instead. The last thing you need to learn is a new backup tool for doing his duties. That’s why it’s very important that as a DBA you don’t step outside of your “home pitch” to go learn something new just for backing up your environment. Hence a simple integration into SQL Server Management Studio (SSMS) is the ideal solution.

To meet this need: Introducing HPE StoreOnce Catalyst plug-in for SQL Server. Check out this link for details on this plug-in.)

And let me summarize the advantages (compelling enough to bring any DBA on board).

  • Integrates right into the SQL Server Management Studio (SSMS) using extended Stored Procedures integration. CLR integration is on the roadmap.
  • Requests no external backup software. Backups goes straight from SSMS into StoreOnce backup appliance on-premises. The backup protocol can either be Ethernet or Fiber Channel
  • Offers StoreOnce Intelligent SQL Deduplication persona. That is, StoreOnce changes the deduplication persona from an “agnostic” approach to a SQL specific approach for delivering much better backup performance, and deduplication ratios and hence smaller storage footprint compared to traditional SQL maintenance plan backups.
  • Offers the power of source-side deduplication, automatic SQL backup striping for optimal performance and deduplication and seamless disaster recovery capability by offering on-premises StoreOnce to off-premises StoreOnce deduplicated replication (OR) on-premises StoreOnce to Public Cloud deduplicated replication capabilities entirely orchestrated from within the SSMS by the DBA.SQL Server on Nimble Storage.jpg

3. The StoreOnce Catalyst plug-in for SQL Server is built with “easy recovery” as the central theme.

We already discussed “recovery” in times of need is the key criteria one should evaluate a backup methodology on. The recovery of the SQL Server—whether from an on-premises StoreOnce appliance, from an off-premises StoreOnce appliances at the DR site or from a public cloud—is designed in a way that the SQL DBA would still not need to step outside the SQL Server Management studio, irrespective of the origination of the recovery objects into the recover site. Also, in case of recovery, there is also a StoreOnce Catalyst plug-in GUI available for easy identification of the objects required to restore with a single click.

Check out this demo video so see everything discussed here working together.

I encourage you to see this demo recording of this data protection solution as presented as part of a recent BrightTalk webinar on HPE Storage Solutions for SQL Server. The SQL Server on Nimble section starts from 44m:14s if you want to get right to it.SQL Server on Nimble copy data management.jpgLearn more about Cloud Bank Storage for Microsoft SQL Server.

Featured articles:

Bharath Ram Ramanathan_HPE Storage.jpgMeet Around the Storage Block blogger Bharath Ram Ramanathan, Senior Technical Marketing Engineer, HPE Storage. Bharath is responsible for bringing a solution/application context into HPE Storage’s portfolio spanning the Primary, Secondary and Cloud Storage products in the areas of Copy Data Management, Business Continuity, Dev-Ops and Data Protection. He works closely with Presales, Sales and Channel Partners to ensure the success of HPE Storage products through training delivery, tech talks, best practice sharing, whitepaper and collateral creation, direct deal support and industry event presence. He has been with Hewlett Packard Enterprise for ~9 years.


0 Kudos
About the Author


Our team of Hewlett Packard Enterprise storage experts helps you to dive deep into relevant infrastructure topics.