Restoring an MS SQL 2022 DB from a ANF SnapShot

There are many benefits to using Azure NetApp Files (ANF) with SAP systems that can significantly improve performance, reliability, and manageability. Here are some key reasons:

High Availability and Reliability: ANF provides highly available NFS/SMB file servers with service level agreements (SLAs) of typically much higher 99.95%. This reduces cost and management overhead by eliminating the need to create a Linux Pacemaker cluster on two Azure VMs.

Reduce costs and simplify management: ANF eliminates the need for additional VMs or operating systems, reducing costs and management. It also eliminates the need for complex configurations such as passing DRBD blocks between local VM disks, reducing latency and further simplifying management.

• Performance: ANF can deliver the enterprise-level NFS/SMB performance required by SAP workloads for database support, with sub-millisecond latency. Support for both SMB and NFS protocols allows for near-instant snapshots and volume replication.
• Backup and Recovery: ANF provides 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, archiving, and compliance capabilities.
• Scalability and Flexibility: ANF allows flexible volume allocation based on application requirements and supports online volume resizing. This makes it easier to scale and adapt to changing needs.
• Simplified deployment: ANF simplifies SAP system deployment by providing a unified architecture and design concept for multiple SAP systems in one ANF volume. This includes optimal volume design for SAP HANA and backup and recovery considerations.

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

This article describes the procedure to recover Microsoft SQL Server 2022 database from NetApp SnapShot. The principles and processes shown work on Azure Large Instances or Azure VMs with ANF infrastructure. There is no difference between the two infrastructure variants.

Because we start straight from the topic without explaining the underlying technologies, we assume you have improved knowledge of Azure Large Instances, Azure VMs, ANF, and MS SQL.

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

We recommend splitting data and log volumes across backup volumes in different Azure regions.

This is better suited for data resiliency.

Configured multiple data volumes and data files to scale I/O throughput for larger systems. I also separated the log files of the SQL Server database into different volumes.

Database file:

\\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 file:

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

Backup volume structure:

Here it is recommended to create directories for all DBs as follows:

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

etc…

Now create three directories in this directory.

backup –> Here you can save a “regular” SQL full backup.

metadata –> DB metadata required for the SQL SnapShot procedure is stored here.

T-log : Save the log backup file here.

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

The Azure Application Consistent Snapshot Tool (AzAcSnap) is a command-line tool that enables data protection for third-party databases. We handle any adjustments necessary to bring your database to an application-consistent state before creating a storage snapshot. After the snapshot is created, the tool returns the database to an operational state.

Before performing the installation, it is important to understand what ANF snapshots are and how they work.

Log in as a domain user with access to the SQL database.

Create a configuration 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?
...
..
.

Need to take a snapshot SQ4data1, SQ4Data2 and SQ4log simultaneously. All three volumes must be configured under the point data volume in the azacsnap configuration. SQLBackup volumes store only offline files, so there is no need to take snapshots. If you wish, you can back up files directly from this volume.

After creating an mssql.json file and both tests (storage and database) are successful, you can create your first snapshot.

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

You will now see a new snapshot for each volume created. The timestamp indicates that they were actually created at the same time.

You can now schedule snapshot creation through Windows Scheduler, Azure Power Automation, Azure Logic Apps, or all three.^rd Party booking tool.

You can automatically schedule log backups on your database based on your desired RPO.

Assuming an RPO of 10 minutes, log backups are required every 5 minutes.

Two prerequisites must be provided:

1) DB must be in FULL Recovery mode.

• You must perform a full backup in full recovery mode.

To schedule log backups every 5 minutes, I recommend creating a small SQL script and scheduling it through MS Windows Scheduler.

Step 1: (Optional)

Create a backup user who can run the backup script you created in step 2.

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

Check if the user was 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';

result:

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

Then generate SQ4_T-Log_Backup.bat) to run the SQL script using sqlcmd. This script saves 100 log files in azacsnap’s \logs directory.

@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 "" -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 a batch file every 5 minutes.

1. open task scheduler.
2. Please click Create a task.

Name your task and provide a description.

choose new task Configure the required frequency. In my case 5 minutes.

Set and select a start time. Task repetition interval 5 minutes for 1 day.

choose Start program and click next.

Find the batch file.

Provide the credentials of the selected OS user.

click finish Create a task.

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

• Terminating or shutting down the database
• Delete database
• It is better to revert or clone the snapshot you want to use for recovery.
• Use the metadata file (belonging to the snapshot you reverted to) with the “RECOVER DATABASE” command. The important thing here is to start recovery with NORECOVERY.
• Apply NORECOVERY and apply each T-Log file required for continued use.
• Once all T-Log files are applied, start the DB with the “WITH RECOVERY” option.

It goes without saying that you should regularly test and implement procedures for recovering from snapshot backups. Please check the following:

• Changes in the environment have not made documented or coded procedures obsolete.
• To verify that the operator can execute the procedure
• To verify the consistency of a restored database

Here are the detailed commands for setup:

Delete DB.

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

In our case, we use the portal to revert all snapshots.

Log on to the master DB and use the metadata file for recovery

Important: You must use the correct metadata file. This can be found by matching the metadata file name to the snapshot name. The metadata file is created by AzAcSnap during a snapshot backup and is called “MSSQL_BACKUP_”..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 log file

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 your database

RESTORE DATABASE SQ4 WITH RECOVERY;

The best way is to take a new snapshot or backup after restore.