Amazon FSx for ONTAP: SQL Server Snapshot-Based Backup Automation Script

This PowerShell script will automate the T-SQL backup flow utilizing snapshots on Amazon FSx for ONTAP filesystem and TSQL suspend and metadata backup. This will replace the full and incremental backups of SQL. Recommended to run every 6hrs or at desired frequency. Transaction log backups would continue in the traditional way more frequently at 15min or lesser as needed.

The PowerShell script fetches the disks assigned to SQL instance/databases and maps that back to LUN and volume on Amazon FSx for ONTAP. You can run at the required schedule using SQL agent or Windows scheduler.

Script Location

Get the script here: TSQL_Backup.ps1

Pre-requisites:

Some of the pre-requisites are as follows:

Store the Amazon FSx credentials as an AWS Systems Manager Parameter Store parameter for secure storage and retrieval a. Create a SecureString parameter named /tsql/filesystem/<FSxN filesystem ID>. Replaced <FSxN filesystem ID> with Amazon FSxN ID.

b. Set its value to a JSON object with your FSx username and password. Example:
```
{
    fsx: {
        username: 'fsxadmin',
        password: 'password'
    }
}
```
Install AWS.Tools.SimpleSystemsManagement PowerShell module on the system where script is running.

Usage:

To run backup on a single database:

TSQL_Backup.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name> -databaseName <database name>

Example:

TSQL_Backup.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'MSSQLSERVER' -databaseName 'Finance'

TSQL_Backup.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'ENGINEERING' -databaseName 'Payments'

To run backup on a group of databases:

TSQL_Backup.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name> -databaseName <comma-separated database names>

Example:

TSQL_Backup.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'ENGINEERING' -databaseName 'Finance,Resources,Accounts'

To run backup on a Server(all databases):

TSQL_Backup.ps1 -FSxID <FSx filesystem ID> -FSxRegion <AWS region> -serverInstanceName <SQL Server instance name>

Example:

TSQL_Backup.ps1 -FSxID 'fs-07a22f282fd4f5a20' -FSxRegion 'eu-south-2' -serverInstanceName 'ENGINEERING'