Protect your SQL Server workloads using NetApp SnapCenter with Amazon FSx for NetApp ONTAP

Update (February 23, 2023): No additional licensing is required to use SnapCenter software with FSx for NetApp ONTAP. See instructions on how to sign up for a NetApp account in our updated FSx for ONTAP user guide.

SQL Server deployments need application-consistent backups and the ability to quickly restore or clone databases for development, testing, and disaster recovery requirements. Amazon FSx for NetApp ONTAP (FSx for ONTAP) is a fully managed service that combines the features of NetApp’s popular ONTAP file system including capabilities with NetApp SnapCenter to protect, clone, and replicate your data using snapshots.

Snapshots with FSx for ONTAP are always crash-consistent; however they require you to quiesce (or pause the I/O of) your database in order to create an application-consistent snapshot. Using NetApp SnapCenter (an orchestration tool with plug-ins for specific applications, including SQL Server) with FSx for ONTAP, allows you to create application-consistent snapshots to protect, replicate, and clone your databases at no additional cost.

In this post, we walk through SnapCenter best practices with SQL Server databases to illustrate how you can protect and quickly restore or clone your SQL Server databases. We demonstrate SnapCenter capabilities using a SQL Server Failover Cluster Instance (FCI) with FSx for ONTAP logical unit numbers (LUNs) that are presented to Amazon Elastic Compute Cloud (EC2) hosts via the iSCSI protocol.

NetApp SnapCenter overview

SnapCenter is a unified platform for application-consistent data protection. SnapCenter refers to snapshots as backups, and we’ll do the same for the rest of this post. SnapCenter provides a single pane of glass for managing application-consistent backups, restores, and clones. You add a SnapCenter plug-in for your specific database application to create application-consistent backups. The SnapCenter plug-in for SQL Server provides the following functionality that simplifies your data protection workflow.

• Backup and restore options with granularity for full and log backups
• Automated discovery of databases
• In-place restore and restore to an alternate location
• Ability to clone a backup to any SQL Server host within SnapCenter
• Refresh clones with production data on a regular cadence
• Disaster recovery with replication of backups via SnapMirror
• Wizard-driven migration of SQL Server database to FSx for ONTAP LUNs

Before deploying SnapCenter, you should consider the following best practices:

• Snapshots created automatically by each volume’s snapshot policy aren’t application-consistent. Therefore, disable automatic snapshots by setting the volume’s snapshot policy to “none”.
• Separate your database workloads across different volumes or file systems, depending on their performance requirements.
• Configure a per-host, or FCI level, host log backup directory on a dedicated volume in which SnapCenter copies transaction log backups.
• Perform a backup following any change to database configuration, such as changing the database recovery model to full recovery.
• When cloning databases, make sure to have at least 0.5% of volume space available for the clone metadata.

For additional best practices and consideration, see Best practices for Microsoft SQL Server using NetApp SnapCenter.

Solution overview

For this post, we cover two main areas:

• Deploying and configuring SnapCenter
• Backup, restore, and cloning of SQL Server databases with SnapCenter

LUNs are located within volumes in FSx for ONTAP. Once the LUNs are configured on the file system based on the best practices, you’ll present these LUNs to the Amazon EC2 instances running SQL Server. Then, you’ll install and configure SnapCenter for SQL Server backups.

Prerequisites

Complete the following prerequisites:

1. Deploy and configure a SQL Server Failover Cluster Instance with FSx for ONTAP and Amazon EC2 as documented in the SQL Server High Availability Deployments Using Amazon FSx for NetApp ONTAP blog. Note: for SQL Server workloads running on FSx for ONTAP volumes, automatic snapshots should be set to None.
2. Review the SnapCenter Server Installation workflow. Launch an Amazon EC2 Windows 2019 instance (c5.xlarge with 50 GB gp3). We use this to run SnapCenter. For instructions on Amazon EC2 deployment, refer to Launch an instance using the new launch instance wizard.

Deploy and configure SnapCenter

The steps to download and install NetApp SnapCenter are documented in Install the SnapCenter Server.

In order to download the SnapCenter software, you’ll need to sign up for a NetApp account. The instructions can be found in our FSx for ONTAP Managing Resources user guide.

Launch SnapCenter

1. Launch the SnapCenter software and log in using a domain user with local administrator rights.
2. Within SnapCenter, choose Settings in the navigation pane.
3. On the Credential tab, choose New.
4. Enter the following information:

a. For Credential Name, enter AdminCredential.

b. For Username, enter <Your Domain>\admin.

c. For Password, enter your account’s password.

5. Choose OK.

Add storage system to SnapCenter

1. In the Amazon FSx console, navigate to the storage virtual machines (SVMs) that you created that are hosting your LUNs mounted on your EC2 instances, and identify the value for Management IP address.

Figure 1 – Navigating to the FSx for NetApp ONTAP storage virtual machine

2. Within SnapCenter, choose Storage Systems in the navigation pane.
3. Choose New.
4. Enter the following information:

a. For Storage System, enter the management IP that you collected.

b. For Username, enter vsadmin, as this is the SVM-specific user. The SVM user doesn’t need to be the vsadmin user. Typically, a user is configured on the SVM and assigned the required permissions to execute backup and restore operations.

c. For Password, enter the password for the user.

5. Choose Submit.

Figure 2 – Adding the FSx for NetApp ONTAP storage system

Add host to SnapCenter

1. Obtain the Host Name for the Amazon EC2 Failover Cluster from Microsoft Failover Cluster Manager. In our case, it’s TEST-WSFC.octank.corp.
2. Within SnapCenter, choose Hosts in the navigation pane.
3. On the Managed Hosts tab, choose Add.
4. Enter the following information:

a. For Host Name, enter the fully qualified (name.domain) machine name.

b. For Credentials, choose the ‘Run-as’ credentials AdminCredentialas described in Set up credentials for SnapCenter Customer Plug-ins.

c. For Select Plug-ins to Install, select Microsoft Windows and Microsoft SQL Server.

When adding an SQL Server host, SnapCenter deploys the required plug-ins on the database host and executes auto-discovery operations.

5. Choose Submit.

Figure 3 – Adding the Windows host

Configure log directory

1. When the host status shows Configure log directory, choose Configure log directory.

Figure 4 – Configuring the log directory

Here you identify the disk location where the database transaction log backup files will be placed. The log backup directory should be placed on a dedicated volume.

2. Under Configure FCI instance log directory choose Browse to refresh the storage display.
3. Choose the path, hit Apply and choose Save.

The host Overall Status should now be displaying Running.

Figure 5 – Reviewing the host status

Configure backups with SnapCenter

To configure database and log backups with SnapCenter, complete the following steps:

Configure backup policies

1. Within SnapCenter, choose Resources in the navigation pane.
2. Make sure that Microsoft SQL Server is chosen on the drop-down menu and choose the database that you would like to backup. In this case, we use database db1.
3. Choose Next.

Figure 6 – Selecting a database to backup

4. Select the plus sign to create a new backup policy.

Figure 7 – Creating a new backup policy

5. For Policy name, enter SQL Full Backups, and choose Next.
6. For Choose backup type, select Full backup.
7. For Schedule frequency, select Daily, and choose Next.

Figure 8 – Configuring the backup type

Finalize backup policies

1. For Retention, use the defaults and choose Next. The retention settings can be changed to meet the overall SLA requirements and to suit specific needs.
2. For Replication, accept the defaults and choose Next. To replicate a backup via an existing SnapMirror relationship with another FSx for ONTAP file system, select that file system which is added as a secondary backup location in SnapCenter.
3. For Script, we don’t specify any optional scripts, so accept the defaults and choose Next.
4. For Verification, accept the defaults and choose Next.
5. Review the Summary section, and choose Finish.

Figure 9 – Finishing the backup policy configuration

Add schedule to backup policy

1. After the policy is created, choose the plus sign to add a schedule.

Figure 10 – Adding a backup schedule

2. Edit Start date to reflect the desired time, and set Repeat every to one day.
3. Choose OK.

Figure 11 – Configuring the backup schedule

4. Choose the plus sign again to add an additional policy for log backups.
5. Repeat the previous steps with the following settings:
   1. Policy name – SQL Log Backups
   2. Backup type – Log backup
   3. Schedule frequency – Hourly
   4. Repeat every – 15 minutes
6. Now that all of the backup policies are created and the schedules have been set, choose Next.

Complete backup configuration

1. For Verification, the verification server is where we stage the CheckDB validation process if needed. However, we don’t configure it for this walkthrough, so accept the defaults and choose Next.

Figure 12 – Selecting verification options

2. For Notification, this lets you configure email alerts to be sent for protection issues such as backup job failures. We recommend this be configured in all environments but for this example, accept the defaults and choose Next.
3. Review the Summary section and choose Finish.

Figure 13 – Finishing the backup configuration

Test backups

1. To test the backups within SnapCenter, choose Resources in the navigation pane.
2. Choose the database you want to backup. In this case, we choose db1 and select Back up Now (on-demand option).

Figure 14 – Initiating a new backup

3. For Policy, choose SQL Full Backups.
4. Choose Backup.
5. Choose Yes to the confirmation message.
6. Repeat these steps for the policy SQL Log Backups.

The activity view shows the successful completion of backup jobs. This information can also be found on the Dashboard, Monitor, and Reports sections.

Figure 15 – Verifying the backup completed

You can now add these policies to any other user databases (full and log backups) and system databases (full backups only).

Restore the database with SnapCenter

One of the major benefits of using FSx for NetApp ONTAP with SQL Server on AWS, is its ability to perform fast and granular restores at the database level. Complete the following steps to restore the database:

1. Within SnapCenter, choose Resources in the navigation pane and the database db1.
2. Choose the backup to be restored and choose Restore.

Figure 16 – Initiating a new restore

3. For this demonstration, we do a simple in-place restore, so accept the defaults for Restore scope and choose Next.

Figure 17 – Configuring the restore scope

4. For Recovery Type no logs will be restored here, so select None and choose Next.
5. For Pre Ops, select Overwrite the database with the same name during restore, deselect Create transaction log backup before restore and choose Next.
6. For Post Ops, accept the defaults and choose Next.

Figure 18 – Configuring the post restore options

7. For Notification we won’t be sending notifications, so accept the defaults and choose Next.
8. Review the Summary section and choose Finish.

Figure 19 – Finishing the restore configuration

When the restore is complete, the activity view shows its completion.

Figure 20 – Verifying the restore completed

Clone databases with SnapCenter

To restore a database onto another location on a dev or test environment, or to create a copy for business analysis purposes, it’s best practice to use the cloning methodology to create a copy of the database on the same instance or an alternate instance. The cloning of databases that are 1 TB on an iSCSI disk hosted on an FSx for NetApp ONTAP environment typically takes about 5 minutes or less. After this you, can perform all of the required read/write operations on a database.

You can clone the database via two methods: create a clone from the latest backup, or use clone lifecycle management through which the latest copy can be made available on the secondary instance.

SnapCenter lets you mount the clone copy on the required disk to maintain the format of the folder structure on the secondary instance and continue to schedule backup jobs.

1. Within SnapCenter, choose Resources in the navigation pane and choose the database db1.
2. Choose the backup that you want to clone and choose Clone.

Figure 21 – Initiating a new clone

3. For Clone Options under Clone settings, choose your Clone server and Clone instance based on the active Amazon EC2 SQL Server instance to which you would like to clone.
4. For Clone name, enter a unique database name within the SQL Server instance.
5. For Choose mount option, select Auto assign volume mount point under path, and specify an empty directory on a FSx ONTAP iSCSI disk. In this case, we’ll use the D:\clone\dbl-clone . This directory will be used as the root location to mount the new volumes during the clone creation.
6. Choose Next.

Figure 22 – Configuring the clone options

7. For Logs, no logs will be restored here, so under Choose logs select None and choose Next.
8. For Script, no optional scripts are needed, so accept the defaults and choose Next.
9. For Notification, we won’t be sending notifications, so accept the defaults and choose Next.
10. Review the Summary section and choose Finish.

Figure 23 – Finishing the clone configuration

An activity status message appears when the clone request is complete.

Figure 24 – Verifying the clone completed

In the following screenshot, we can confirm that the db1-clone database has been restored to the D:\clone\db1-clone\ path specified earlier.

Figure 25 – Confirming the database clone has been restored to the appropriate path

To clone across Amazon EC2 SQL Server instances, SnapCenter should have the additional host added with its associated SVM. The steps are the same as before but when choosing the clone server, you have multiple servers from which to choose.

Figure 26 – Selecting optional clone servers

Cleaning up

When you’re finished using the resources in this post, clean up the AWS resources to avoid incurring unwanted charges. Specifically, delete the following:

• EC2 instances used for SQL Server FCI instance and the SnapCenter install – For instructions, refer to How do I delete or terminate my Amazon EC2 resources?
• FSx for NetApp ONTAP file system – For instructions, refer to Managing FSx for ONTAP volumes and Clean up resources.

Conclusion

In this post, we showed you how to use NetApp SnapCenter to manage backup, restore, and clone functionality with your EC2 instances for SQL Server using FSx for NetApp ONTAP. This solution enables improved data protection and scalability for your database workloads. Using the SnapCenter functionality with your FSx for ONTAP file system, you no longer need to manually script your backup and restore process or clone snapshots with CLI commands for your database workloads.

Thanks for reading this blog post, if you have any comments or questions, leave them in the comments section.