Artificial Intelligence
TR-4951: Backup and Recovery for Microsoft SQL Server on AWS FSx for ONTAP
Suggest changes
PDFs
Author(s): Niyaz Mohammed, Carine Ngwekwe - NetApp Solutions Engineering
This document covers the steps necessary to perform backup and recovery for Microsoft SQL Server on AWS FSx for ONTAP with SnapCenter. This includes the following information:
-
NetApp SnapCenter configuration
-
SnapCenter backup operations
-
Backup operation for an FCI database
-
Backup operation for multiple databases
-
Restore and recovery
SnapCenter Configuration
The following steps must be performed for SnapCenter configuration and the protection of Microsoft SQL Server resources. Each of the following steps is detailed in the following sections.
-
Configure sysadmin credentials for the SQL Server backup and restore user.
-
Configure storage settings. Provide Amazon Web Services (AWS) management credential to access the Amazon FSx for NetApp ONTAP storage virtual machines (SVMs) from SnapCenter.
-
Add a SQL Server host to SnapCenter. Deploy and install the required SnapCenter Plug-ins.
-
Configure policies. Define the backup operation type, retention, and optional Snapshot backup replication.
-
Configure and protect the Microsoft SQL Server database.
SnapCenter newly installed user interface
Configure credentials for SQL Server backup and restore the user with sysadmin rights.
NetApp recommends using role-based access control (RBAC) to delegate data protection and management capabilities to individual users across the SnapCenter and window hosts. The user must have access to the SQL Server hosting the database. For multiple hosts, the username and password must be the same across the various hosts. Furthermore, to enable SnapCenter to deploy the required plug-in on SQL Server hosts, you must register the domain information for SnapCenter to validate your credentials and hosts.
Expand the following sections to see the detailed instructions on how to complete each step.
Add the credentials
Go to Settings, select Credentials, and click (+).
The new user must have administrator rights on the SQL Server host.
Configure storage
To configure storage in SnapCenter, complete the following steps:
-
In the SnapCenter UI, select Storage Systems. There are two storage types, ONTAP SVM and ONTAP Cluster. By default, the storage type is ONTAP SVM.
-
Click (+) to add the storage system information.
-
Provide the FSx for ONTAP management endpoint.
-
The SVM is now configured in SnapCenter.
Add a SQL Server host to SnapCenter
To add a SQL Server host, complete the following steps:
-
From the Host tab, click (+) to add the Microsoft SQL Server host.
-
Provide the fully qualified domain name (FQDN) or IP address of the remote host.
The credentials are populated by default. -
Select the option for Microsoft windows and Microsoft SQL Server and then submit.
The SQL Server packages are installed.
-
After the installation is complete, go to the Resource tab to verify whether all FSx for ONTAP iSCSI volumes are present.
Configure log directory
To configure a host log directory, complete the following steps:
-
Click the check box. A new tab opens.
-
Click the configure log directory link.
-
Select the drive for the host log directory and the FCI instance log directory. Click Save. Repeat the same process for the second node in the cluster. Close the window.
The host is now in a running state.
-
From the Resources tab, we have all the servers and databases.
Configure a backup policy
A backup policy is a set of rules that govern how to manage, schedule, and retain backup. It helps with the backup type and frequency based on your company’s SLA.
Expand the following sections to see the detailed instructions on how to complete each step.
Configure back-up operation for an FCI database
To configure a backup policy for an FCI database, complete the following steps:
-
Go to Settings and select Policies on the top left. Then click New.
-
Enter the policy name and a description. Click Next.
-
Select Full backup as the backup type.
-
Select the schedule frequency (this is based on the company SLA). Click Next.
-
Configure the retention settings for the backup.
-
Configure the replication options.
-
Specify a run script to run before and after a backup job is run (if any).
-
Run verification based on the backup schedule.
-
The Summary page provides details of the backup policy. Any errors can be corrected here.
Configure and protect MSSQL Server database
-
Set up the starting date and expiration date of the backup policy.
-
Define the schedule for the backup. To do that, click (+) to configure a schedule. Enter the Start date and Expires on date. Set the time based on the company’s SLA.
-
Configure the verification server. From the drop- down menu, select the server.
-
Confirm the configured schedule by clicking the plus sign and confirm.
-
Provide information for email notification. Click Next.
The summary of the backup policy for SQL Server database is now configured.
SnapCenter backup operations
To create on-demand SQL Server backups, complete the following steps:
-
From the Resource view, select the resource and select Backup now.
-
In the Backup dialog box, click Backup.
-
A confirmation screen is displayed. Click Yes to confirm.
Monitor backup job
-
From the Monitor tab, click the job and select Details on the right to view the jobs.
When the backup is completed, a new entry is shown in the Topology view.
Backup operation for multiple databases
To configure a backup policy for multiple SQL Server databases, create resource group policies by completing the following steps:
-
In the Resources tab from the View menu, change to a resource group using the drop-down menu.
-
Click (+) for a new resource group.
-
Provide a name and tag. Click Next.
-
Add resources to the resource group:
-
Host. Select the server from the drop-down menu hosting the database.
-
Resource type. From the drop-down menu, select Database.
-
SQL Server instance. Select the server.
The option Auto Selects All the Resources from the Same Storage Volume* is selected by default. Clear the option and select only the databases you need to add to the resource group, Click the arrow to add and click Next.
-
-
On the policies, click (+).
-
Enter the resource group policy name.
-
Select Full backup and the schedule frequency depending on your company’s SLA.
-
Configure the retention settings.
-
Configure the replication options.
-
Configure the scripts to run before performing a backup. Click Next.
-
Confirm the verification for the following backup schedules.
-
On the Summary page, verify the information, and click Finish.
Configure and protect multiple SQL Server databases
-
Click the (+) sign to configure the start date and the expire- on date.
-
Set the time.
-
From the Verification tab, select the server, configure the schedule, and click Next.
-
Configure notifications to send an email.
The policy is now configured for backing up multiple SQL Server databases.
Trigger on-demand backup for multiple SQL Server databases
-
From the Resource tab, select view. From the drop-down menu, select Resource Group.
-
Select the resource group name.
-
Click Backup now in the upper right.
-
A new window opens. Click the Verify after backup checkbox and then click backup.
-
A confirmation message is dsiplayed. Click Yes.
Monitor multiple-database backup jobs
From the left navigation bar, click Monitor, select the backup job, and click Details to view job progress.
Click the Resource tab to see the time it takes for the backup to be completed.
Transaction log backup for multiple database backup
SnapCenter supports the full, bulked logged, and simple recovery models. The simple recovery mode does not support transactional log backup.
To perform a transaction log backup, complete the following steps:
-
From the Resources tab, change the view menu from Database to Resource group.
-
Select the resource group backup policy created.
-
Select Modify Resource Group in the upper right.
-
The Name section defaults to the backup policy name and tag. Click Next.
The Resources tab highlights the bases to which the transaction backup policy is to be configured.
-
Enter the policy name.
-
Select the SQL Server backup options.
-
Select log backup.
-
Set the schedule frequency based on your company’s RTO. Click Next.
-
Configure the log backup retention settings. Click Next.
-
(Optional) Configure the replication options.
-
(Optional) Configure any scripts to run before performing a backup job.
-
(Optional) Configure backup verfication.
-
On the Summary page, click Finish.
Configure and protect multiple MSSQL Server databases
-
Click the newly created transaction log backup policy.
-
Set the Start date and Expires on date.
-
Enter the frequency of the log backup policy depending on the SLA, RTP, and RPO. Click OK.
-
You can see both policies. Click Next.
-
Configure the verification server.
-
Configure email notification.
-
On the Summary page, click Finish.
Triggering an on-demand transaction log backup for mutiple SQL Server databases
To trigger an on- demand backup of the transactional log for multiple SQL server databases, complete the following steps:
-
On the newly created policy page, select Backup now at the upper right of the page.
-
From the pop-up on the Policy tab, select the drop-down menu, select the backup policy, and configure the transaction log backup.
-
Click Backup. A new window is displayed.
-
Click Yes to confirm the backup policy.
Monitoring
Move to the Monitoring tab and monitor the progress of the backup job.
Restore and recovery
See the following prerequisites necessary for restoring a SQL Server database in SnapCenter.
-
The target instance must be online and running before a restore job completes.
-
SnapCenter operations that are scheduled to run against the SQL Server database must be disabled, including any jobs scheduled on remote management or remote verification servers.
-
If you are restoring custom log directory backups to an alternate host, the SnapCenter server and the plugin host must have the same SnapCenter version installed.
-
You can restore the system database to an alternate host.
-
SnapCenter can restore a database in a Windows cluster without taking the SQL Server cluster group offline.
Restoring deleted tables on a SQL Server database to a point in time
To restore a SQL Server database to a point in time, complete the following steps:
-
The following screenshot shows the initial state of the SQL Server database before the deleted tables.
The screenshot shows that 20 rows were deleted from the table.
-
Log into SnapCenter Server. From the Resources tab, select the database.
-
Select the most recent backup.
-
On the right, select Restore.
-
A new window is displayed. Select the Restore option.
-
Restore the database to the same host where the backup was created. Click Next.
-
For the Recovery type, select All log backups. Click Next.
Pre- restore options:
-
Select the option Overwrite the database with same name during restore. Click Next.
Post- restore options:
-
Select the option Operational, but unavailable for restoring additional transaction logs. Click Next.
-
Provide the email settings. Click Next.
-
On the Summary page, click Finish.
Monitoring the restore progress
-
From the Monitoring tab, click the restore job details to view the progress of the restore job.
-
Restore the job details.
-
Return to SQL Server host > database > table are present.
Where to find additional information
To learn more about the information that is described in this document, review the following documents and/or websites:
-
TR-4714: Best Practices Guide for Microsoft SQL Server using NetApp SnapCenter
-
Understanding cloned database lifecycles