Configure log shipping for SQL Server on Azure VMs

Grein
02/26/2024

This article teaches you to configure log shipping between two SQL Server on Azure Virtual Machines (VMs).

Overview

Log shipping allows you to automatically send transaction log backups from a primary database on a primary server to one or more secondary databases on a separate secondary server. Transaction log backups are applied to each of the secondary databases individually. An optional third server, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Log shipping is primarily used as a disaster recovery solution and can be combined with other high availability and disaster recovery options, including Always On availability groups.

Prerequisites

To configure log shipping for SQL Server on Azure VMs, you must have the following prerequisites:

At least two domain-joined Azure virtual machines with SQL Server in the same resource group as an Azure storage account for transaction log backups. The secondary server must be on the same or higher version of SQL Server as the primary SQL Server.
The primary database must use the full or bulk-logged recovery model. Log shipping stops functioning if the primary database is switched to a simple recovery model.
The account configuring log shipping must be a member of the sysadmin fixed server role.

Transaction log backups from the primary server are stored in a file share. Before you configure log shipping, you must create an Azure File Share inside of an Azure storage account that can be accessed by both the primary and secondary servers.

To create your Azure file share in the Azure portal, follow these steps:

Go to your resource group in the Azure portal and select the storage account you intend to use for transaction log backups.
Under Data Storage, select File Shares, and then choose +File share to create a new file share.
On the Basics tab, provide the name of the file share, such as log-shipping. You can leave the Tier at the default of Transaction optimized.
(Optional) On the Backup tab, use the checkbox to enable backups of your file share to Azure Backup.
Select Review + create to review your file share settings, and then select Create to create your new file share.

Create backup directories

After the file share is created, you should create the following two directories:

A directory for the primary to write the log backups to
A directory for the secondary to copy and restore the log backup

To create the directories, follow these steps:

After Azure creates the file share, the portal returns you to the Overview page for your new SMB File.
Under Browse, select + Add directory. Provide the name of the new directory, such as log-backups. Select OK.
Repeat the previous step to add a second directory, such as restore-backups. Select OK.

After the directories are created, connect the virtual machines to the file share.

To determine connection details, select Connect from either the Browse or Overview page of the file share to open the Connect window.

The Connect window provides a script to allow a resource to access the file share. Optionally, change the Drive letter to mount the file share to the virtual machine. This guide uses a storage account key for a Windows virtual machine.

Select Show script to view the script, copy it, and then run it on each SQL Server VM where you plan to configure log shipping.

After running the Connect script, you can use the following PowerShell cmdlet to verify connectivity to port 445:

Test-NetConnection -ComputerName yourstorageaccount.file.core.windows.net -Port 445

If the connection test succeeds, you see an output of TcpTestSucceeded : True.

After the SQL Server VMs can successfully connect to the file share, grant the SQL Server service account permission to access the file share by creating a credential in SQL Server using the URL, username, and password from the Connect script.

To create the credential, enable xp_cmdshell, and use it to create the credential before disabling xp_cmdshell once more.

To grant the SQL Server service account access to the file share, follow these steps on every SQL Server instance that you plan to use for log shipping:

Connect to the SQL Server VM with an account that is part of the sysadmin role.
Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.

Open a new query window and run the following Transact-SQL code containing the storage key details obtained from the Azure portal:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO
EXEC xp_cmdshell 'cmdkey /add:"yourstorageaccount.file.core.windows.net" /user:"localhost\yourstorageaccount" /pass:"<yourpasskey>"';
GO
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
GO

After executing the command, SSMS confirms that your credential is added successfully:

Screenshot of the confirmation the credential was successfully created in SSMS.