Leverage Azure NetApp Files (ANF) for Enhanced Microsoft SQL Server 2022 Backups on Windows VMs

This guide explores how to leverage Azure NetApp Files (ANF) with the Azure Application Consistent Snapshot tool (AzAcSnap) to create application-consistent backups of a Microsoft SQL Server 2022 database running on a Windows VM in Azure.

Why ANF for SQL Server Backups?

ANF offers several advantages for SQL Server backups:

High Availability and Reliability: ANF boasts a superior service level agreement (SLA) compared to traditional setups, minimizing downtime. This eliminates the need for complex Linux clusters, reducing costs and management overhead.
Cost Savings and Simplified Management: ANF eliminates the need for additional VMs and complex configurations like DRBD, simplifying management and reducing costs.

Optimizing Backup Strategies

Separate Data, Log, and Backup Volumes: Splitting data, log, and backup volumes across different Azure zones enhances data resiliency.
Scale I/O Throughput: Configure multiple data volumes and data files for larger systems. Maintain a separate volume for the SQL Server log file.

Recommended Backup Volume Structure

Create a dedicated directory for each database within the backup volume.
Within each database directory, create subdirectories for:
Backup: Stores regular SQL full backups.
Metadata: Stores metadata required for the SQL Snapshot procedure.
T-Log: Stores transaction log backup files.

Introducing AzAcSnap for Application-Consistent Snapshots

AzAcSnap facilitates application-consistent snapshots for various databases, including Microsoft SQL Server (version 2022 and newer) on Windows. It orchestrates putting databases into a consistent state before capturing a storage snapshot.

Prerequisites for AzAcSnap on Windows

Understand ANF snapshot functionality.
Download the AzAcSnap executable from the provided link.
Create a directory to store the executable and a metadata directory for AzAcSnap.
Configure authentication (managed identity recommended) for AzAcSnap to access ANF volumes.
Log in as a domain user with access to the SQL database.

Setting Up AzAcSnap Configuration

1. Run the executable to create a configuration file.
2. Specify the database type (MSSQL) and provide details like connection string, database name, and metadata file location.
3. Configure the Mssql storage section, ensuring all data volumes (SQ4data1, SQ4Data2, SQ4log) are included under the “data-volumes” section. The SQLBackup volume doesn’t require snapshots as it stores offline files.

Creating and Scheduling Snapshots

1. Once configuration is complete, create the first snapshot using the `azacsnap.exe` command with appropriate options (preview, backup, volume, prefix, retention, trace).
2. Schedule subsequent snapshot creation using tools like Windows Task Scheduler, Azure Power Automation, Azure Logic Apps, or third-party scheduling tools.

Automating Log Backups

1. Configure the database in FULL recovery mode.
2. Create a Transact-SQL script to back up the transaction log with timestamps for unique filenames. Schedule this script execution every 5 minutes using Windows Task Scheduler.

Restoring the Database from a Snapshot

1. (Optional) Test the restore procedure regularly.
2. Drop the existing database.
3. Use the Azure portal to revert all snapshots.
4. Log on to the master database and utilize the metadata file for recovery. Ensure you use the correct metadata file corresponding to the snapshot.
5. Apply transaction log files sequentially.
6. Open the restored database.
7. It’s recommended to take a new snapshot or backup after the restore process.

Conclusion

This guide demonstrates how to leverage ANF and AzAcSnap for robust and efficient backups of Microsoft SQL Server 2022 databases on Windows VMs in Azure. By following these steps, you can ensure data protection and minimize downtime for your critical applications.

Source: Microsoft blog