With any application organizations face consistent key challenges such as high efficiency and business value, complex configuration, and low total cost of ownership. Extending applications to the cloud in hybrid scenarios addresses many of these challenges, whether distributing SharePoint content across on-premises and Office 365 while leveraging search as a service (hybrid search) or externalizing data, extending it to the cloud with Remote Blob Storage or related technologies.

SQL Server 2014 and Windows Azure Blob Storage provide a unique solution that balances the needs of IT with those of the business - SQL Server Data Files in Windows Azure which allows you to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage.

This enhancement especially simplifies to move databases between machines by using detach and attach operations. In addition, it provides an alternative storage location for your database backup files by allowing you to restore from or to Windows Azure Storage. Therefore, it enables several hybrid solutions by providing several benefits for data virtualization, data movement, security and availability, and any easy low costs and maintenance for high-availability and elastic scaling.

Prerequisites

SQL Server 2014 on-premises or as a Windows Azure Virtual Machine

Storage Account and Container in Windows Azure

When using SQL Server Data Files in Windows Azure feature, you need to create a storage account and a container in Windows Azure, create a SQL Server credential, which includes information on the policy of the container as well as a Shared Access Signature that is necessary to access the container.

Getting Started

Create a Storage Account

To store files and data in the Blob, Table, Queue, and File services in Azure, you must create a storage account in the geographic region where you want to store the data.

Create a Container

Click Storage from the list of available options and select the Storage Account created in the previous steps.

Click CONTAINERS from the list of available options.

On the CONTAINERS dialog click + Add and specify the name for the new container.

NOTE

Keep the default access level ‘Private’.

By default, the container is private and can be accessed only by the account owner. To allow public read access to the blobs in the container, but not the container properties and metadata, use the "Public Blob" option. To allow full public read access for the container and blobs, use the "Public Container" option.

The following steps assume that a Windows Azure Storage container has been created, and a policy has been created with read, write, list, rights. Creating a policy on a container generates a SAS key which is safe to keep unencrypted in memory and needed by SQL Server to access the blob files in the container.

Create a Credential

Creating a credential creates a record that contains the authentication information that is required to connect to a resource outside SQL Server. (such as Azure Blob Store). To create a credential for Azure Blob Store requires a Windows Azure Store Container and a policy to allow read, write, and list rights. Creating a policy generates a SAS key which SQL Server uses to access the blobs in the container.

Syntax

CREATE CRENDENTIAL [Container Url] -- Specifies the name of the credential being created, I.e. the Windows Azure Storage Container

WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’ -- Specifies the name of the account to be used when connecting outside the server, I.e. when used with a Windows Azure Storage Container this value is always ‘SHARED ACCESS SIGNATURE’. A Shared Access Signature is a URI that grants restricted access rights to containers, blobs, queues, and tables for a specific time interval (see example below).

SECRET = ‘Secret’ -- Specifies the secret required for outgoing authentication. In this scenario the SECRET represents the Shared Access Signature associated with the policy created on the container (see example below).

Validate Database Creation

To validate the database and related files were created successfully in the container select Connect in SQL Server Management Studio and choose Windows Azure from the list of available options. Specify the name of the storage account associated with the container and provide the related access key to access the container.

Add Database(s) to SharePoint

To add the database use the Mount-SPContentDatabase cmdlet which attaches an existing content database to the farm.

Syntax

Mount-SPContentDatabase

-NameSpecifies the existing content database to attach to the farm.

-DatabaseServer Specifies the name of the host server for the content database specified in the Name parameter.

Example

Summary

SQL Server Data Files in Windows Azure simplifies migration processes by moving one database at a time between machines on-premises as well as between on-premises and cloud environments without any application changes and provides near limitless storage without the overhead of managing storage.