Restoring an MS SQL 2022 DB from a ANF SnapShot

PoC and Validation

Overview

Using Azure NetApp Files (ANF) with SAP systems offers several advantages that can significantly enhance performance, reliability, and manageability. Here are some key reasons:

 

High Availability and Reliability: ANF provides a highly available NFS/SMB file server with a Service Level Agreement (SLA) of 99.95%, which is usually much higher. This eliminates the need for creating a Linux pacemaker cluster on two Azure VMs, reducing costs and administrative overhead.

 

Cost Savings and Simplified Management: By using ANF, there is no need for additional VMs or operating systems, which translates to cost savings and less administration. Additionally, there is no need for complex configurations like DRBD block shipping between local VM disks, which reduces latency and further simplifies management.

 

• Performance: ANF can deliver enterprise-level NFS/SMB performance required by SAP workload for database support, with sub-millisecond latency. It supports both SMB and NFS protocols, allowing for near-instantaneous snapshots and volume cloning.
• Backup and Recovery: ANF offers robust backup solutions, including policy-based (scheduled) and manual (on-demand) backups. These backups are stored in Azure storage, independent of volume snapshots, providing long-term recovery, archive, and compliance capabilities.
• Scalability and Flexibility: ANF allows for flexible volume assignment according to application needs and supports online volume resizing. This makes it easier to scale and adapt to changing requirements.
• Simplified Deployment: ANF simplifies the deployment of SAP systems by providing a consolidated structure and design concepts for multiple SAP systems in one ANF volume. This includes optimal volume design for SAP HANA and considerations for backup and recovery.

These benefits make ANF a compelling choice for running SAP systems, offering a combination of high performance, reliability, cost savings, and simplified management.

 

This Document

This document describes a recovery procedure of a Microsoft SQL Server 2022 Database from a NetApp SnapShot. The principles and processes shown, are working on our Azure Large Instances or on Azure VMs with ANF infrastructure. There is no difference between both infrastructure variants.

We assume that enhanced Azure Large Instances, Azure VM, ANF and MS SQL knowledge is available because we start right away with the topic without any explanation of the underlying technologies.

For basic information please use our learning site where you can find anything you will need to understand this article Microsoft Learn: Build skills that open doors in your career.

Explaining MS SQL 2022 snapshot feature: Create a Transact-SQL snapshot backup - SQL Server | Microsoft Learn

 

Setup

We configured one Windows VM and installed MS SQL Server 2022 on it.  

We recommend splitting the Data and Log volumes from the Backup volume in different Azure Zones. 

This will be more optimal for data resiliency.

To scale the I/O throughput for larger systems, we configured multiple data volumes and data files. and separated the log file of the SQL Server database into a different volume as well. 

 

Database files:

\\smb-5bd6.anfsmb.com\SQ4Data1\SQ4.mdf
\\smb-5bd6.anfsmb.com\SQ4Data1\SQ4_2.ndf
\\smb-5bd6.anfsmb.com\SQ4Data2\SQ4_3.ndf
\\smb-5bd6.anfsmb.com\SQ4Data2\SQ4_4.ndf

 

 

Log files:

\\smb-5bd6.anfsmb.com\SQ4log\SQ4_log.ldf
\\smb-5bd6.anfsmb.com\SQ4log\SQ4_2_log.ldf

 

Backup Volume structure:

Here we recommend to create a directory for every DB like:

\\SMB-Server\Volume\SID1
\\SMB-Server\Volume\SID2 

and so on…

 

In this directory now create three directories:

Backup --> here a “normal” SQL Full Backup can be stored

Metadata --> here the Metadata of the DB will be stored which we need for the SQL SnapShot procedure

T-Log : Here we will store our log backup files.

 

In our setup it looks like this:

\\smb-c80b.anfsmb.com\SQLBackup\SQ4\Backup
\\smb-c80b.anfsmb.com\SQLBackup\SQ4\Metadata
\\smb-c80b.anfsmb.com\SQLBackup\SQ4\T-Log

AzAcSnap

The Azure Application Consistent Snapshot tool (AzAcSnap) is a command-line tool that enables data protection for third-party databases. It handles all the orchestration required to put those databases into an application-consistent state before taking a storage snapshot. After the snapshot, the tool returns the databases to an operational state.

Before performing the installation, it is important to understand the functionality of an ANF snapshot and how it is working:

How Azure NetApp Files snapshots work | Microsoft Learn

 

So far AzAcSnap was “only” available for databases like SAP HANA, IBM DB2 and Oracle. Up to now we only supported Linux as an operating system for the snapshot backup scenarios.

With this blog we will show that AzAcSnap is also capable of creating application consistent snapshots for Microsoft SQL Server (version 2022 and newer) on the Windows operating system.

 

Setup and configuration of AzAcSnap on Windows.

Download the executable from https://aka.ms/azacsnap-windows and store it in a directory e.g.

C:\Users\<SID>adm\azacsnap

 

Create the metadata directory for AzAcSnap e.g.

\\smb-c80b.anfsmb.com\SQLBackup\SQ4\Metadata

Create either a managed identity or service principal authentication file for AzAcSnap to be able to access the ANF volumes. We highly recommend using managed Identity!

Look for a detailed description for “Enable Communication with Storage” in

Install the Azure Application Consistent Snapshot tool for Azure NetApp Files | Microsoft Learn

 

Configuration of AzAcSnap

Login as the domain user which has access to the SQL data base.

Create the config file for AzAcSnap

azacsnap.exe --configfile mssql.json -c configure --configuration new
...
..
.
Enter the database type to add, 'hana', 'oracle', 'db2', 'mssql', 'exit' (to abort without saving), 'save' (to save and exit)?
mssql
=== Add MS SQL Server details ===
What is the MS SQL Server ConnectionString?
Trusted_Connection=True;Persist Security Info=True;Data Source=anfsmbdb01;TrustServerCertificate=true
What is the MS SQL Server Instance (Database) Name?
SQ4
What is the MS SQL Server Backup Meta-Data File Location?
\\smb-c80b.anfsmb.com\SQLBackup\SQ4\Metadata
BEGIN : Database tests
PASSED: Successful connectivity to MSSQL version 16.00.4015
*** Connection to SQ4 successful. ***
=== Add Mssql Storage section ===
Do you want to add Mssql database Storage?
...
..
.

You need to snapshot SQ4data1, SQ4Data2 and SQ4log at the same time. All three volumes must be configured under the point data-volumes in the azacsnap config. The SQLBackup volume does not need to be snapshotted because only offline files are stored there. You can directly backup file from this volume, if you like.

 

After you created the mssql.json file and both tests (storage and Database) were successful you can create the first snapshot.

 

C:\Users\sq1adm\azacsnap>azacsnap.exe --configfile mssql.json --preview -c backup --volume data --prefix test --retention 2 –trace

 

Now we have created will see a new snapshot in each volume. The timestamp indicates that it is really created at the same time.

You can now schedule the snapshot creation via the Windows scheduler, Azure Power Automation, Azure Logic Apps or any 3^rd party scheduling tool.

Log Backups

Depending on the desired RPO you can schedule the Log-Backups automatically from the database.

I assume the RPO is 10 minutes, this would require a log backup every 5 minutes.

Two prerequisites must be provided:

      1)   DB must be in FULL Recovery mode

• You must have taken a FULL Backup in FULL Recovery mode.

 

To schedule a log backup every 5 minutes I recommend creating a small SQL script and schedule it via the MS Windows scheduler.

 

Step 1: (optional)

Create a backup user which is able to execute the backup script which we create in step 2.

CREATE LOGIN azacsnap WITH PASSWORD = '<your Password>';
CREATE USER azacsnap FOR LOGIN azacsnap;
ALTER SERVER ROLE sysadmin ADD MEMBER azacsnap;

 

Verify that the user is created

USE SQ4;
SELECT name AS username, create_date, modify_date, type_desc AS type
FROM sys.database_principals
WHERE type NOT IN ('A', 'G', 'R', 'X') AND sid IS NOT NULL AND name != 'guest';

Sesult:

Step 2: Create a Backup Script

First, create a Transact-SQL script to back up the transaction log with a unique name that includes the date and time. Save this script as backup_log.sql:

SQL:

DECLARE @BackupFileName NVARCHAR(500)
SET @BackupFileName = '\\smb-c80b.anfsmb.com\SQLBackup\SQ4\T-Log\t-log_' +
                      CONVERT(VARCHAR(20), GETDATE(), 112) + '_' +
                      REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', '') + '.trn'
BACKUP LOG [SQ4]TO DISK = @BackupFileName
WITH NOFORMAT, NOINIT, NAME = 'SQ4-Transaction Log Backup'

 

Step 3: Create a Batch File

Next, create a SQ4_T-Log_Backup .bat) to run the SQL script using sqlcmd. This script will store 100 logfiles in the \logs directory of azacsnap.

@echo off
setlocal
:: Set the timestamp
set "timestamp=%date:~10,4%-%date:~4,2%-%date:~7,2%_%time:~0,2%-%time:~3,2%-%time:~6,2%"
set "timestamp=%timestamp: =0%"
cd C:\Users\sq1adm\azacsnap
:: Execute the SQL script and write to the log file
sqlcmd -S localhost -d SQ4 -U "azacsnap" -P "<your Password>" -i SQ4_T-Log-Backup.sql -o "logs\SQ4_T-Log_Backup_%timestamp%.log"
:: Manage log files to keep only the latest 100
set "logdir=C:\Users\sq1adm\azacsnap\logs"
set "logcount=100"
for /f "skip=%logcount% delims=" %%F in ('dir /b /o-d "%logdir%\SQ4_T-Log_Backup_*.log"') do del "%logdir%\%%F"
endlocal

 

Step 4: Schedule the Task

Use Windows Task Scheduler to run the batch file every 5 minutes:

1. Open Task Scheduler.
2. Click on Create Task.

Name your task and provide a description.

Select New Task and configure the required frequence. In my case 5 minutes.

Set the start time and select Repeat task every 5 minutes for a duration of 1 day.

Choose Start a program and click Next.

Browse to your batch file

Provide the credentials of your selected OS user

Click Finish to create the task.

 

 

DB Recovery

If you want or have to restore the database from a snapshot, we recommend the following steps:

• Shutdown or kill the database
• Drop the Database
• Revert, or even better Clone, the snapshot you would like to use for the recovery
• Use the Metadata file (belonging to the SnapShot you reverted) for the “RECOVER DATABASE” command. Important here is to start the recovery with NORECOVERY
• Apply each T-Log file you need to Apply and still use NORECOVERY
• If all T-Log files are applied, start the DB with the “WITH RECOVERY” option.

Needless to state that you should test and exercise the procedure of recovering from a snapshot backup on a regular basis to make sure that:

• Changes in your environment did not make the documented or coded procedures obsolete
• To make sure that operators are able to execute the procedure
• To make sure that the restored database is consistent

 

Here the detailed commands for our setup:

Drop the DB:

USE master;
GO
ALTER DATABASE SQ4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SQ4;
GO

 

Use The Portal to revert ALL snapshots in our case:

• SQ4DATA1
• SQ4DATA2
• SQ4Log

Logon to the master DB and use the Metadata file for the recovery

 

Important: Be sure to use the correct Metadata file.  This can be found by matching the Metadata file name with the snapshot name.  The Metadata file is generated by AzAcSnap during the snapshot backup and is called “MSSQL_BACKUP_<snapshot name>.bkm”.

 

RESTORE DATABASE SQ4 FROM DISK = '\\smb-c80b.anfsmb.com\SQLBackup\SQ4\Metadata\MSSQL_BACKUP_test__FACFAFFDB2D.bkm'
WITH METADATA_ONLY,  NORECOVERY ;

 

Now Apply the logfiles

RESTORE LOG SQ4 FROM DISK = '\\smb-c80b.anfsmb.com\SQLBackup\SQ4\T-Log\202408131716.trn'
WITH NORECOVERY;
RESTORE LOG SQ4 FROM DISK = '\\smb-c80b.anfsmb.com\SQLBackup\SQ4\T-Log\202408131721.trn'
WITH NORECOVERY;
RESTORE LOG SQ4 FROM DISK = '\\smb-c80b.anfsmb.com\SQLBackup\SQ4\T-Log\202408131735.trn'
WITH NORECOVERY;

 

Now open The Database

RESTORE DATABASE SQ4 WITH RECOVERY;

The best is to take fresh SnapShot or backup after the restore.