Skip to main content
NetApp Solutions

TR-4951: Backup and Recovery for Microsoft SQL Server on AWS FSx ONTAP

Contributors kevin-hoke
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 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.

  1. Configure sysadmin credentials for the SQL Server backup and restore user.

  2. Configure storage settings. Provide Amazon Web Services (AWS) management credential to access the Amazon FSx ONTAP storage virtual machines (SVMs) from SnapCenter.

  3. Add a SQL Server host to SnapCenter. Deploy and install the required SnapCenter Plug-ins.

  4. Configure policies. Define the backup operation type, retention, and optional Snapshot backup replication.

  5. 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.

Figure showing input/output dialog or representing written content

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 (+).

Figure showing input/output dialog or representing written content

The new user must have administrator rights on the SQL Server host.

Figure showing input/output dialog or representing written content

Configure storage

To configure storage in SnapCenter, complete the following steps:

  1. 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.

  2. Click (+) to add the storage system information.

    Figure showing input/output dialog or representing written content

  3. Provide the FSx ONTAP management endpoint.

    Figure showing input/output dialog or representing written content

  4. The SVM is now configured in SnapCenter.

    Figure showing input/output dialog or representing written content

Add a SQL Server host to SnapCenter

To add a SQL Server host, complete the following steps:

  1. From the Host tab, click (+) to add the Microsoft SQL Server host.

    Figure showing input/output dialog or representing written content

  2. Provide the fully qualified domain name (FQDN) or IP address of the remote host.

    Note The credentials are populated by default.

  3. Select the option for Microsoft windows and Microsoft SQL Server and then submit.

    Figure showing input/output dialog or representing written content

The SQL Server packages are installed.

Figure showing input/output dialog or representing written content

  1. After the installation is complete, go to the Resource tab to verify whether all FSx ONTAP iSCSI volumes are present.

    Figure showing input/output dialog or representing written content

Configure log directory

To configure a host log directory, complete the following steps:

  1. Click the check box. A new tab opens.

    Figure showing input/output dialog or representing written content

  2. Click the configure log directory link.

    Figure showing input/output dialog or representing written content

  3. 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.

    Figure showing input/output dialog or representing written content

The host is now in a running state.

Figure showing input/output dialog or representing written content

  1. From the Resources tab, we have all the servers and databases.

    Figure showing input/output dialog or representing written content

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:

  1. Go to Settings and select Policies on the top left. Then click New.

    Figure showing input/output dialog or representing written content

  2. Enter the policy name and a description. Click Next.

    Figure showing input/output dialog or representing written content

  3. Select Full backup as the backup type.

    Figure showing input/output dialog or representing written content

  4. Select the schedule frequency (this is based on the company SLA). Click Next.

    Figure showing input/output dialog or representing written content

  5. Configure the retention settings for the backup.

    Figure showing input/output dialog or representing written content

  6. Configure the replication options.

    Figure showing input/output dialog or representing written content

  7. Specify a run script to run before and after a backup job is run (if any).

    Figure showing input/output dialog or representing written content

  8. Run verification based on the backup schedule.

    Figure showing input/output dialog or representing written content

  9. The Summary page provides details of the backup policy. Any errors can be corrected here.

    Figure showing input/output dialog or representing written content

Configure and protect MSSQL Server database

  1. Set up the starting date and expiration date of the backup policy.

    Figure showing input/output dialog or representing written content

  2. 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.

    Figure showing input/output dialog or representing written content

  3. Configure the verification server. From the drop- down menu, select the server.

    Figure showing input/output dialog or representing written content

  4. Confirm the configured schedule by clicking the plus sign and confirm.

  5. Provide information for email notification. Click Next.

    Figure showing input/output dialog or representing written content

The summary of the backup policy for SQL Server database is now configured.

Figure showing input/output dialog or representing written content

SnapCenter backup operations

To create on-demand SQL Server backups, complete the following steps:

  1. From the Resource view, select the resource and select Backup now.

    Figure showing input/output dialog or representing written content

  2. In the Backup dialog box, click Backup.

    Figure showing input/output dialog or representing written content

  3. A confirmation screen is displayed. Click Yes to confirm.

    Figure showing input/output dialog or representing written content

Monitor backup job

  1. From the Monitor tab, click the job and select Details on the right to view the jobs.

    Figure showing input/output dialog or representing written content

    Figure showing input/output dialog or representing written content

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:

  1. In the Resources tab from the View menu, change to a resource group using the drop-down menu.

    Figure showing input/output dialog or representing written content

  2. Click (+) for a new resource group.

    Figure showing input/output dialog or representing written content

  3. Provide a name and tag. Click Next.

    Figure showing input/output dialog or representing written content

  4. 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.

      Figure showing input/output dialog or representing written content

      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.

      Figure showing input/output dialog or representing written content

  5. On the policies, click (+).

    Figure showing input/output dialog or representing written content

  6. Enter the resource group policy name.

    Figure showing input/output dialog or representing written content

  7. Select Full backup and the schedule frequency depending on your company’s SLA.

    Figure showing input/output dialog or representing written content

  8. Configure the retention settings.

    Figure showing input/output dialog or representing written content

  9. Configure the replication options.

    Figure showing input/output dialog or representing written content

  10. Configure the scripts to run before performing a backup. Click Next.

    Figure showing input/output dialog or representing written content

  11. Confirm the verification for the following backup schedules.

    Figure showing input/output dialog or representing written content

  12. On the Summary page, verify the information, and click Finish.

    Figure showing input/output dialog or representing written content

Configure and protect multiple SQL Server databases

  1. Click the (+) sign to configure the start date and the expire- on date.

    Figure showing input/output dialog or representing written content

  2. Set the time.

    Figure showing input/output dialog or representing written content

    Figure showing input/output dialog or representing written content

  3. From the Verification tab, select the server, configure the schedule, and click Next.

    Figure showing input/output dialog or representing written content

  4. Configure notifications to send an email.

    Figure showing input/output dialog or representing written content

The policy is now configured for backing up multiple SQL Server databases.

Figure showing input/output dialog or representing written content

Trigger on-demand backup for multiple SQL Server databases

  1. From the Resource tab, select view. From the drop-down menu, select Resource Group.

    Figure showing input/output dialog or representing written content

  2. Select the resource group name.

  3. Click Backup now in the upper right.

    Figure showing input/output dialog or representing written content

  4. A new window opens. Click the Verify after backup checkbox and then click backup.

    Figure showing input/output dialog or representing written content

  5. A confirmation message is dsiplayed. Click Yes.

    Figure showing input/output dialog or representing written content

Monitor multiple-database backup jobs

From the left navigation bar, click Monitor, select the backup job, and click Details to view job progress.

Figure showing input/output dialog or representing written content

Click the Resource tab to see the time it takes for the backup to be completed.

Figure showing input/output dialog or representing written content

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:

  1. From the Resources tab, change the view menu from Database to Resource group.

    Figure showing input/output dialog or representing written content

  2. Select the resource group backup policy created.

  3. Select Modify Resource Group in the upper right.

    Figure showing input/output dialog or representing written content

  4. 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.

    Figure showing input/output dialog or representing written content

  5. Enter the policy name.

    Figure showing input/output dialog or representing written content

  6. Select the SQL Server backup options.

  7. Select log backup.

  8. Set the schedule frequency based on your company’s RTO. Click Next.

    Figure showing input/output dialog or representing written content

  9. Configure the log backup retention settings. Click Next.

    Figure showing input/output dialog or representing written content

  10. (Optional) Configure the replication options.

    Figure showing input/output dialog or representing written content

  11. (Optional) Configure any scripts to run before performing a backup job.

    Figure showing input/output dialog or representing written content

  12. (Optional) Configure backup verfication.

    Figure showing input/output dialog or representing written content

  13. On the Summary page, click Finish.

    Figure showing input/output dialog or representing written content

Configure and protect multiple MSSQL Server databases

  1. Click the newly created transaction log backup policy.

    Figure showing input/output dialog or representing written content

  2. Set the Start date and Expires on date.

  3. Enter the frequency of the log backup policy depending on the SLA, RTP, and RPO. Click OK.

    Figure showing input/output dialog or representing written content

  4. You can see both policies. Click Next.

    Figure showing input/output dialog or representing written content

  5. Configure the verification server.

    Figure showing input/output dialog or representing written content

  6. Configure email notification.

    Figure showing input/output dialog or representing written content

  7. On the Summary page, click Finish.

    Figure showing input/output dialog or representing written content

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:

  1. On the newly created policy page, select Backup now at the upper right of the page.

    Figure showing input/output dialog or representing written content

  2. From the pop-up on the Policy tab, select the drop-down menu, select the backup policy, and configure the transaction log backup.

    Figure showing input/output dialog or representing written content

  3. Click Backup. A new window is displayed.

  4. Click Yes to confirm the backup policy.

    Figure showing input/output dialog or representing written content

Monitoring

Move to the Monitoring tab and monitor the progress of the backup job.

Figure showing input/output dialog or representing written content

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:

  1. The following screenshot shows the initial state of the SQL Server database before the deleted tables.

    Figure showing input/output dialog or representing written content

    The screenshot shows that 20 rows were deleted from the table.

    Figure showing input/output dialog or representing written content

  2. Log into SnapCenter Server. From the Resources tab, select the database.

    Figure showing input/output dialog or representing written content

  3. Select the most recent backup.

  4. On the right, select Restore.

    Figure showing input/output dialog or representing written content

  5. A new window is displayed. Select the Restore option.

  6. Restore the database to the same host where the backup was created. Click Next.

    Figure showing input/output dialog or representing written content

  7. For the Recovery type, select All log backups. Click Next.

    Figure showing input/output dialog or representing written content

    Figure showing input/output dialog or representing written content

Pre- restore options:

  1. Select the option Overwrite the database with same name during restore. Click Next.

    Figure showing input/output dialog or representing written content

Post- restore options:

  1. Select the option Operational, but unavailable for restoring additional transaction logs. Click Next.

    Figure showing input/output dialog or representing written content

  2. Provide the email settings. Click Next.

    Figure showing input/output dialog or representing written content

  3. On the Summary page, click Finish.

    Figure showing input/output dialog or representing written content

Monitoring the restore progress

  1. From the Monitoring tab, click the restore job details to view the progress of the restore job.

    Figure showing input/output dialog or representing written content

  2. Restore the job details.

    Figure showing input/output dialog or representing written content

  3. Return to SQL Server host > database > table are present.

    Figure showing input/output dialog or representing written content

Where to find additional information

To learn more about the information that is described in this document, review the following documents and/or websites: