How To Backup SQL Server Named Instance To Azure Blob Storage #Azure #SQLServer #AzureStorage

Introduction

Starting with SQL Server 2012 SP1 CU2, you can now write SQL Server backups directly to the Azure Blob storage service. You can use this functionality to back up to and restore from the Azure Blob service with an on-premises SQL Server database or a SQL Server database in an Azure virtual machine. Backup to cloud offers a lot of benefits in term of availability and ease of migration.

Starting with SQL Server 2014, Microsoft introduced the first version of automated backup to automatically configures Managed Backup to Microsoft Azure for all existing and new databases on an Azure Virtual Machine running SQL Server 2014 Standard or Enterprise. This enables you to configure regular database backups that utilize durable Azure blob storage.

Starting with SQL Server 2016 and later, Microsoft introduced automated backup V2 to automatically configures Managed Backup to Microsoft Azure for all existing and new databases on an Azure Virtual Machine running SQL Server 2016/2017 Standard, Enterprise, or Developer editions. This enables you to configure regular database backups that utilize durable Azure blob storage.

Both Version 1 and Version 2 depends on the SQL Server IaaS Agent Extension. You can read all about it here.

By using the SQL Server IaaS Agent Extension to automate the backup for SQL Server, you have to take the following requirements into consideration:

  1. The target databases must use the full recovery model.
  2. However, if you require log backups to be taken for Model or MSDB (system databases), you must use full recovery model.
  3. The target databases must be on the default SQL Server instance (MSSQLSERVER). The SQL Server IaaS Extension does not support named instances.

Here is the catch in point 3, if you deployed a new SQL instance on the same Azure virtual machine, then the databases attached to the new named instance won’t be automatically backed up by the SQL Server IaaS Extension.

In this article, I will describe the requirements and components necessary to use the Microsoft Azure Blob storage service as destination to automate the backup of SQL Server named instance.

Requirements

The following are the required components that you need to do a backup to or restore from the Azure Blob storage service:

  1. Azure subscription obviously.
  2. Storage Account: The storage account is the starting point for all storage services.
  3. Container: A container provides a grouping of a set of blobs, and can store an unlimited number of blobs. Containers are addressable using the following URL format: https://<Storage AccountName>.blob.core.windows.net/<ContainerName>
  4. Bob: There are two types of blobs that can be stored in the Microsoft Azure Blob storage service: Block and Page Blobs. SQL Server backup can use either blob type depending upon the Transact-SQL syntax used. For more information about page and block blobs, see Understanding Block and Page Blobs. The automated backup with SQL Server IaaS Extension uses Block Blob type.
  5. URL: A URL specifies a Uniform Resource Identifier (URI) to a unique backup file. The URL is used to provide the location and name of the SQL Server backup file. The URL must point to an actual blob, not just a container. If the blob does not exist, it is created. Here is a sample URL value: https://StorageAccountName.blob.core.windows.net/<ContainerName>/<FileName.bak>. HTTPS is not required, but is recommended.
  6. Credential: A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. The Credential stores either the name of the storage account and the storage account access key values or container URL and its Shared Access Signature token. More on how to create SQL Server Credential and Shared Access Signature (SAS) in the next section. Personally, I prefer to use a container URL with Shared Access Signature (SAS) token instead of storage account and storage account access key. SAS is more secure than the storage account key. One of the main reasons is with SAS, you can ACL the IPs that can access the account, and you can control the permissions on the account in a more granular fashion. But please note that SAS tokens are signed with your key. So invalidating your storage account key should invalidate the SAS Token as well, so keep that in mind.

If you installed SQL Server IaaS Extension and configured SQL Server Configuration for backup, then points 3, 4 and 5 above are done for you automatically.

Create a Shared Access Signature

The following example creates a Shared Access Signatures that can be used to create a SQL Server Credential (next step) on a newly created container. The script creates a Shared Access Signature that is associated with a Stored Access Policy.

Note: Please make sure you have installed Azure PowerShell module before you run the script below.

After successfully running the script above, copy the CREATE CREDENTIAL command from the output. 

Create a SQL Credential

The following example create SQL Server credentials for authentication to the Microsoft Azure Blob storage service.

To create a SQL Server credential, follow these steps:

  1. Connect to SQL Server Management Studio (SSMS).
  2. Open a new query window and connect to the SQL Server 2016/2017 named instance of the database engine in your environment.
  3. In the new query window, paste the CREATE CREDENTIAL statement with the Shared Access Signature from the previous step and execute that script.
  4. The script will look like the following code.

Perform a full database backup

You can perform backup a database to URL using three different methods, I will touch briefly on each method.

1) Backup using SQL Server Management Studio

The following steps describe the changes made to the Back Up Database task in SQL Server Management Studio to allow for backing up to Microsoft Azure storage:

  1. In Object Explorer, connect to the named instance of the SQL Server Database Engine and then expand that instance.
  2. Expand Databases, right-click the desired database, point to Tasks, and then click Back Up…
  3. On the General page in the Destination section the URL option is available in the Back up to: drop-down list. The URL option is used to create a backup to Microsoft Azure storage. Click Add and then Select Backup Destination dialog box will open:
    • Azure storage container: The name of the Microsoft Azure storage container to store the backup files. Select an existing container from the drop-down list or manually enter the container. If you completed Create a SQL Credential step successfully, then you will see the new Azure storage container listed.
    • Shared Access Policy: Enter the shared access signature for a manually entered container. This field is not available if an existing container was chosen.
    • Backup File: The backup file name is generated automatically, you can changed it.
  4. Click OK to add the URL destination.
  5. Click OK to start the backup to Microsoft Azure storage.

2) Automate Backup using Maintenance Plan Wizard

Similar to the backup task described in method 1, the Maintenance Plan Wizard in SQL Server Management Studio includes URL as one of the destination backup options, and other supporting objects required to backup to Microsoft Azure storage like the SQL Credential.

To create a Maintenance Backup Plan, follow these steps:

  1. Back up to: Specifies the medium on which to back up the database. In this case, it’s URL.
  2. Destination: In the destination Tab, specify the SQL credential, Azure storage container name, and the URL prefix. The backup file extension is .bak by default.
    • Important Note: Microsoft does NOT support taking backup to URL using Shared Access Signature (SAS) token via Maintenance Plan. For maintenance plan to work, you need to create a new SQL Credential by specifying the storage account name and access key information instead of SAS token. If you want to automate that task, you need to use T-SQL and schedule that as a job. If you try to schedule a job using the maintenance plan wizard with SAS token, the backup job will fail with the following error:
      • Msg 3225, Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature. BACKUP DATABASE is terminating abnormally.
  3. Click Next > and proceed with the remaining wizard. 

For detailed information, see the Define Backup Tasks section here: Using Maintenance Plan Wizard.

3) Perform Backup using T-SQL query

To create a backup, follow these steps:

  1. Connect to SQL Server Management Studio (SSMS).
  2. Open a new query window and connect to the SQL Server 2016/2017 named instance of the database engine in your environment.
  3. In the new query window, execute the T-SQL BACKUP DATABASE query.
  4. The T-SQL query will look like the following.

Restore database from Azure Blob Storage

The Restore Database task now includes URL as a device to restore from. The following steps describe using the Restore task to restore from the Microsoft Azure Blob storage service:

  1. Right-click Databases and select Restore Database…
  2. On the General page, select Device under the Source section.
  3. Click the browse () button to open the Select backup devices dialog box.
  4. Select URL from the Backup media type: drop-down list. Click Add to open the Select a Backup File Location dialog box.
    • Azure storage container: The fully qualified name of the Microsoft Azure storage container which contains the backup files. If you completed Create a SQL Credential step successfully, then you will see the new Azure storage container listed. Select an existing container from the drop-down list or manually enter the fully qualified container name.
    • Shared Access Signature: Enter the Shared Access Signature for the designated Azure storage container. You need to use the Shared Access Signature (SAS) token that you created in the previous step.
    • Click OK: SQL Server connects to Microsoft Azure storage using the SQL Credential information you provided and opens the Locate Backup File in Microsoft Azure dialog. The backup files residing in the storage container are displayed on this page. Select the file you want to use to restore and click OK. This takes you back to the Select Backup Devices dialog, and clicking OK on this dialog takes you back to the main Restore dialog where you will be able complete the restore.

Hope this article was useful to you and happy backup day!

__
Thank you for reading my blog.

If you have any questions or feedback, please leave a comment.

-Charbel Nemnom-

About Charbel Nemnom 379 Articles
Charbel Nemnom is a Microsoft Cloud Solutions Architect, totally fan of the latest's IT platform solutions, accomplished hands-on technical professional with over 17 years of broad IT Infrastructure experience serving on and guiding technical teams to optimize performance of mission-critical enterprise systems. Excellent communicator adept at identifying business needs and bridging the gap between functional groups and technology to foster targeted and innovative IT project development. Well respected by peers through demonstrating passion for technology and performance improvement. Extensive practical knowledge of complex systems builds, network design and virtualization.

Be the first to comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.