IT Pro Resources
Bill Baer on...
With any solution that externalizes the unstructured content with SharePoint you need to understand the limitations and optimal use of those solutions as documented at http://technet.microsoft.com/en-us/library/ff628583(v=office.15).aspx to include related latency and performance requirements.
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.
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.
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.
For step by step instructions on how to create a Storage Account using the Windows Azure Management Portal see http://azure.microsoft.com/en-us/documentation/articles/storage-create-storage-account/.
All storage blobs reside in a container. To create a container:
Sign in to the Management Portal.
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.
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.
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.
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).
For information on creating and using Shared Access Signatures see http://msdn.microsoft.com/en-us/library/azure/jj721951.aspx.
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).
USE masterCREATE CREDENTIAL [https://sqlcloud.blob.core.windows.net/data]WITH IDENTITY = 'SHARED ACCESS SIGNATURE',SECRET = 'sr=c&se=2014-08-25T18%3B34%3B29Z&sp=rwl&sig=wJripFB9nq%2FrwlARE11TYS1OccKpFrpn6y3QuRS%2Fv4o%3D'
To view the credential created in the steps above use:
SELECT * FROM sys.credentials
The sys.credentials Security Catalog View returns one row for each stored credential which includes:
name = Name of the credential. Is unique in the server.
credential_identity = Name of the identity to use.
create_date = Time at which the credential was created.
modify_date = Time at which the credential was last modified.
target_type = Type of credential. Returns NULL for traditional credentials such as SHARED ACCESS SIGNATURE.
target_id = ID of the object that the credential is mapped to. Returns 0 for traditional credentials.
Creating a database does not differ significantly from creating a database in a traditional on-premises SQL Server storage scenario using the CREATE DATABASE statement.
CREATE DATABASE [Name] -- Is the name of the new database.
ON ( NAME = [Database Name], FILENAME = [Data File Path and File Name] ) -- Specifies that the disk files used to store the data sections of the database, data files, are explicitly defined.
LOG ON ( Name = [Log Name], FILENAME = [Log File Path and File Name] ) Specifies that the disk files used to store the database log, log files, are explicitly defined.
CREATE DATABASE WSS_ContentON ( NAME = WSS_Content, FILENAME = 'https://sqlcloud.blob.core.windows.net/data/WSS_Content.mdf' )LOG ON ( NAME = WSS_Content_Log, FILENAME = 'https://sqlcloud.blob.core.windows.net/data/WSS_Content.ldf' )
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.
To add the database use the Mount-SPContentDatabase cmdlet which attaches an existing content database to the farm.
-Name Specifies 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.
-WebApplication Attaches the content database to the specified SharePoint web application.
Mount-SPContentDatabase “WSS_Content” –DatabaseServer CP-SQ-01 –WebApplication http://sharepoint.wbaer.com.co
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.
How does the performance of this solution compare to the case where the data files are stored on the local file system of the Azure VM hosting the SQL Server 2014?
in your Scenario, runs the SharePoint Server on premises or in the cloud? When splitting the infrastructure of a SharePoint Farm, isn't it correct do talk about a "stretched farm"? And as i know, a stretched farm has very high requirements to still be supported.
For an onprem solution at least, I can't imagine this solution would give you acceptable performance. Also for azure based solutions, I doubt this will work with geographically redundant storage. Do you have any thoughts on that?
Hi, is there a minimum latency recommended when selecting a Location Affinity Group during the Storage Account setup?
Awesome, thanks Bill!
Looks good. What are IOPS on a blob like you describe above?