AlwaysOn Availability Groups Overview

The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

Unstructured data refers to information that does not adhere to a defined model or does not fit well into relational tables in SharePoint unstructured data can refer to Microsoft Office document file formats, video, audio, and related file types.

Structured Data

Structured data in SharePoint refers to the metadata associated with its corresponding unstructured data or BLOB. Relational databases are most often cited as examples of structured data.

SQL Server and Unstructured Data Storage

There are three (3) approaches to storing unstructured data with SQL Server, RBS, SQL BLOB, and FILESTREAM:

Remote BLOB Storage (RBS) in which SharePoint relies on a new layer in SQL Server to read or update BLOB data stored outside of the database on separate BLOB Stores (file system or dedicated BLOB stores)

SQL BLOB which refers to traditional BLOB storage with SharePoint, BLOB data is stored along side the structured metadata in the Content Database

FILESTREAM Overview

FILESTREAM is implemented on the varbinary(max) datatype instructing the database engine to store unstructured data on the file system through a FILESTREAM filegroup that contains file system directories instead of the files themselves also known as data containers. Data containers are the interface between database engine storage and file system storage. varbinary is the binary data type designation for binary large objects stored in SharePoint 2010 content databases and refers to variable-length binary data. (MAX) refers to a value that max indicates that the maximum storage size is 2^31-1 bytes or otherwise 2GB. Remote BLOB Storage does not provide a mechanism to exceed to the 2GB file size limit imposed by SharePoint.

In SharePoint 2013 remote BLOB data is referenced by a unique identifier in content databases configured for RBS (see illustration).

FILESTREAM offers several benefits as related to performance 1) FILESTREAM uses the NT system cache for caching file data reducing the effect that FILESTREAM data has on Database Engine performance and 2) the SQL Server buffer pool is not used; therefore, memory is available for query processing.

FILESTREAM provides optimum value in scenarios where SharePoint is used to storage large BLOB data such as video files that will benefit from FILESTREAM or BLOB data that exceeds 1MB.

High Availability Considerations

AlwaysOn Availability Group Protection

The protection for RBS BLOB data depends on the BLOB Store Location:

BLOB Store Location

Protected

same database that contains the RBS metadata

Yes

Another database in the same instance of SQL Server

Yes

Another database in a different instance of SQL Server

Yes

third-party BLOB store

To protect this BLOB data, use the high-availability mechanisms of the BLOB store provider.

Database Mirroring Protection

Database mirroring does not support FILESTREAM since a FILESTREAM filegroup cannot be created on the principal server and database mirroring cannot be configured for a database that contains FILESTREAM filegroups. If the FILESTREAM provider is used to store BLOB data locally (within the same content database) the database cannot be configured for database mirroring.

If the FILESTREAM provider is configured to store the BLOB data within a separate SQL database or when using a 3rd party BLOB store, the content database can be mirroring; however, database mirroring will apply only to the content database and not the BLOB data. The BLOB data needs to be handled separately and kept in sync with the associated metadata (content database). For FILESTREAM BLOB databases, this can be done through log shipping.

Specify the full path to RBS.MSI in the above state, i.e. C:\Users\Administrator\Desktop\RBS.MSI. Replace the values for DBNAME and DBINSTANCE to match your environment.

On additional Web/Application servers open a Command Prompt with Administrator permissions and execute the following command to install RBS.MSI downloaded in the previous step:

Step 5 Enable Remote BLOB Storage

On the Start menu, click All Programs.

Click Microsoft SharePoint 2013 Products.

Click SharePoint 2013 Management Shell.

In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statement to set the content database to be configured:

$database=Get-SPContentDatabase –Identity “Database Name”

In the SharePoint 2013 Management Shell, enter the following Windows PowerShell statement to gets the object that holds settings that determine how the content database uses Microsoft SQL Server Remote Blob Storage:

$rbs=$database.RemoteBlobStorageSettings

In the SharePoint 2013 Management Shell, enter the following Windows PowerShell statement to determine if RBS has been installed for the selected content database:

$rbs.Installed()

NOTE

The result of $rbs.Installed() should be True, if the result is False, verify RBS.MSI has been installed successfully by reviewing rbs_install_log.txt. Ensure the install statement was running In the SharePoint 2010 Management Shell, enter the following Windows PowerShell statement to enable RBS for the selected content database:

$rbs.Enable()

In the SharePoint 2013 Management Shell, enter the following Windows PowerShell statements to set the RBSprovider for the selected content database:

$rbs.SetActiveProviderName($rbs.GetProviderNames()[0])

$rbs

NOTE

The result of $rbs should be:

Enabled

ActiveProviderName

MinimumBlobStorageSize

UpgradePersistedProperties

True

FileStreamProvider

0

()

Appendix for Table 1

Enabled specifies whether or not RBS has been enabled for the selected content database.

ActiveProviderName is name of the SQL Remote Blob Storage provider new files will be stored in. This will be null if new files will not be stored using SQL Remote Blob storage.

MinimumBlobStorageSize refers to the minimum size a BLOB may be to be considered RBS storage worthy, BLOB data exceeding the specified MinimumBlobStorageSize will be stored in the RBS data store.

Step 5.1 Configure the MinimumBlobStorageSize

On the Start menu, click All Programs.

Click Microsoft SharePoint 2013 Products.

Click SharePoint 2013 Management Shell.

In the SharePoint 2013 Management Shell, enter the following Windows PowerShell statements to configure the MinimumBlobStorageSize at 1MB:

$database = Get-SPContentDatabase “Database Name”

$database.RemoteBlobStorageSettings.MinimumBlobStorageSize=1048576

$database.Update()

UpgradePersistedProperties specifies the collection of field names and values for fields that were deleted or changed.

Step 6 Validate Installation

Select the content database for which you want to create a BLOB store, and then click New Query.

In SQL Server Management Studio, click New Query to display the Query Editor.

In Query Editor, enter the following Transact-SQL code:

USE[Database Name]

SELECT*FROMdbo.DatabaseInformation

Confirm that both the RBSCollectionId and RBSProvider rows are available.

Step 7 Test the RBS Data Store

Select a desired Document Library on a site in the configured content database.

Upload a file that is greater than 1 MB.

On the computer that contains the RBS data store, click Start, and then click Computer.

Browse to the RBS data store directory.

Browse to the file list and open the folder that has the most recent modified date (other than $FSLOG). In that folder, open the file that has the most recent modified date. Verify that this file has the same size and contents as the file that you uploaded. If it does not, ensure that RBS is installed and enabled correctly.

The data store directory structure will appear similar to that in the following diagram.