Many customers face challenges related to database discovery, resiliency, data archival, long-term retention, and infrastructure operational challenges. Your databases could be residing anywhere across hybrid cloud, on-premises, hyperscalers which could be hosted on Cloud Volumes ONTAP (CVO), first party service on AWS FSx for ONTAP or service provider-hosted environment, SQL Server could be installed as a standalone or setup with an availability group, protecting data across various setups could get very complex.
In the event of natural calamities, or the growing threat of ransomware, tapes remain the preferred method for backing up and restoring databases. Until a few years back, restoration from tapes was merely requested for a restoration drill to test whether tape restore is working fine. With an increasing number of ransomware attacks, organizations found that restoring from tape takes over a month, and only 20-40% of the data is typically recovered. RTO and RPO are still a concern for many businesses.
Another hurdle is lean operating management of the backup admin, where the chances of human error can lead to an incorrect restoration of data that increases the restore time. Operational challenges are mainly managing the product lifecycle of backup. Apart from managing the business infrastructure of critical applications, managing upgrades and coordinating upgrades gets complex, operation team must go through multiple levels of approval and qualifications.
All these challenges can be overcome in NetApp BlueXP Backup and recovery for Microsoft SQL Server which is now generally available. It is a single control pane to protect Microsoft SQL Server databases. The current release supports SQL Server databases mounted on storage with FC/iscsi-connected LUNs on NetApp storage. Databases running on VMware virtual disk over VMFS or NFS data store are also supported.
Onboarding SQL Server host
After the BlueXP organisation is created and connector is deployed, the first step is to grant the user "Backup & recovery super admin" role access in the "Identity & access management".
Next, go to Protection-> Backup and recovery service. Click Microsoft SQL Server-> Discover and Manage. Fill in the host & credential details to add SQL Server host and click Discover. NetApp backup and recovery service will automatically discover all the instances and database resources running on that SQL Server host.
Protecting SQL Server databases:
Protecting SQL Server database is based on policy created in NetApp Backup and recovery. User must define the frequency, schedule and configure destinations to store backups. Single policy can be used to backup multiple databases. Having minimum number of policies helps in reducing the backup policy management.
Protecting database start with creating policy. Below is the flow of creating policy.
- Enable protection at the instance level or the database level. Select an instance, and select the type of backup architecture.
- Select the frequency & retention of backup in policy
- Configure destination volume path, frequency and retention period on the secondary storage
- Configure object storage destination, frequency and retention period
-
Users can check additional options in the protection policy such as receiving any notification or enabling ransomware scan on object store.
Create policy and attach it to the database or instance resource. Backup job will run as per the schedule
Database cloning:
In case of application testing or reporting, you need a copy of the database, go to clone and select the database to create new clone. With few clicks database clone can be created. The complete orchestration to create clone on ONTAP, mount filesystem to the SQL Server host, and attaching the database to SQL Server host is done from NetApp Backup and Recovery service.
Below figures shows the workflow of creating clone. User need to provide the details of source database and target destination. Then select the snapshot, recovery scope whether database must be restored with full backup or transactional logs must be restored.
.
Database restore:
Under Databases inventory, database restoration can be performed by clicking on ellipsis 
You could restore database from just a full backup or perform point-in-time-recovery. Select the snapshot and select the source location of snapshot. If the snapshot is deleted from local or secondary storage as per the retention period, restore from object storage. Provide the destination path to restore.
Figure below shows restore option from object storage.
NetApp Backup and recovery service will restore the database securely on same SQL server host or on alternate SQL Server host.
Migrating SnapCenter plug-in for MS SQL Server to NetApp backup and recovery service:
If you are an existing NetApp SnapCenter user, then you should take advantage of the NetApp Backup and recovery. The service has import SnapCenter functionality to migrate most of the SnapCenter protected SQL Server resources to NetApp backup and recovery. All the historical snapshots and configuration metadata will be migrated to NetApp Backup and recovery. Users don’t have to reconfigure policies. There is a validation process that checks the feasibility of migration before switching over to NetApp Backup and recovery.
Conclusion:
Finally, reports are a simplified way to look at the overall protection status such as how many databases are protected, how many were successfully backed up and failed. Similar reports are available for restore and clone jobs.
With all these benefits, you can further modernize infrastructure. Get into the BlueXP management UI, don’t worry about the setup process, as it’s a managed infrastructure. You don’t have to worry about installing a separate server to host SnapCenter or any backup infrastructure. Login into NetApp BlueXP, evaluate NetApp Backup and recovery for MSSQL Server by signing up for 30-days trial.
To understand more about the NetApp Backup and recovery, refer documentation here.
