Deploying Remote BLOB Storage with SQL Server 2012 AlwaysOn Availability Groups

AlwaysOn Availability Groups can provide a high-availability and disaster recovery solution for SQL Server Remote Blob Store (RBS) BLOB objects (blobs).  AlwaysOn Availability Groups protects any RBS metadata and schemas stored in an availability database by replicating them to the secondary replicas.

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.

See also AlwaysOn Availability Groups (SQL Server).

Remote BLOB Store Overview

SQL Server Remote BLOB Store (RBS) is an optional add-on component that lets database administrators store binary large objects in commodity storage solutions instead of directly on the main database server.

Unstructured Data

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

See also Remote BLOB Store.

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).

Drawing1

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.

To learn about the differences between FILESTREAM and SQL Server Remote BLOB Store see also http://blogs.msdn.com/b/sqlrbs/archive/2009/11/18/sql-server-remote-blob-store-and-filestream-feature-comparison.aspx.

Step 1 Failover Availability Group(s) to the Secondary Replica

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Management Studio.
  2. On the Connect to Server dialog, enter the name of the Availability Group Listener and click Connect.
  3. In SQL Server Management Studio, expand AlwaysOn High Availability, and then expand Availability Groups.
  4. Right-click the desired Availability Group and select Failover… from the list of available options.
  5. On the Failover Availability Group: <Availability Group> dialog click Next >.
  6. On the Failover Availability Group: <Availability Group> dialog select or accept the Secondary Replica and click Next >.
  7. On the Failover Availability Group: <Availability Group> dialog click Connect… to connect to the Secondary Replica and click Next >.
  8. On the Failover Availability Group: <Availability Group> dialog click Finish to initiate manual failover of the Availability Group.

See also Planned Manual Failover (Without Data Loss) [http://msdn.microsoft.com/en-us/library/hh213151.aspx#ManualFailover] under Failover and Failover Modes (AlwaysOn Availability Groups).

Step 1.1 Enable FILESTREAM on the Primary Replica

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance and then click Properties.
  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
  6. Select the Enable FILESTREAM for Transact-SQL access check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
  9. Click Apply.
  10. In SQL Server Management Studio, click New Query to display the Query Editor.
  11. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2

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

RECONFIGURE

Step 2 Failover the Availability Group(s) to the Primary Replica

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, and then click SQL Server Management Studio.
  2. On the Connect to Server dialog, enter the name of the Availability Group Listener and click Connect.
  3. In SQL Server Management Studio, expand AlwaysOn High Availability, and then expand Availability Groups.
  4. Right-click the desired Availability Group and select Failover… from the list of available options.
  5. On the Failover Availability Group: <Availability Group> dialog click Next >.
  6. On the Failover Availability Group: <Availability Group> dialog select or accept the Secondary Replica and click Next >.
  7. On the Failover Availability Group: <Availability Group> dialog click Connect… to connect to the Secondary Replica and click Next >.
  8. On the Failover Availability Group: <Availability Group> dialog click Finish to initiate manual failover of the Availability Group.

See also Planned Manual Failover (Without Data Loss) [http://msdn.microsoft.com/en-us/library/hh213151.aspx#ManualFailover] under Failover and Failover Modes (AlwaysOn Availability Groups).

Step 2.1 Enable FILESTREAM on the Secondary Replica

  1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.
  2. In the list of services, right-click SQL Server Services, and then click Open.
  3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
  4. Right-click the instance and then click Properties.
  5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
  6. Select the Enable FILESTREAM for Transact-SQL access check box.
  7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
  8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
  9. Click Apply.
  10. In SQL Server Management Studio, click New Query to display the Query Editor.
  11. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2

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

RECONFIGURE

Step 3 Provision the RBS Data Store

      

  1. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.
  2. On the Connect to Server dialog, enter the name of the Availability Group Listener and click Connect.
  3. Expand Databases.
  4. Select the content database for which you want to create a BLOB store, and then click New Query.
  5. In SQL Server Management Studio, click New Query to display the Query Editor.
  6. In Query Editor, enter the following Transact-SQL code:

USE [Database Name]

IF not exists (SELECT * FROM sys.symmetric_keys WHERE name = N’##MS_DatabaseMasterKey##’)create master key encryption by password = N’Admin Key Password !2#4′

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

USE [Database Name]

IF not exists (SELECT groupname FROM sysfilegroups WHERE groupname=N’RBSFilestreamProvider’)alter database [Database Name] add filegroup RBSFilestreamProvider contains filestream

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

USE [Database Name]

alter database [Database Name] add file (name = RBSFilestreamFile, filename = ‘c:BlobStore’) to filegroup RBSFilestreamProvider

NOTE

The Blob Store directory will be provisioned on both the Primary and Secondary Replicas.

Step 4 Install the Microsoft SQL Server 2008 R2 Remote Blob Store

Download the x64 package for the Microsoft SQL Server 2012 Remote Blob Store from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en.

Open a Command Prompt with Administrator permissions and execute the following command to install RBS.MSI downloaded in the previous step:

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true FILEGROUP=PRIMARY DBNAME="<Database Name>" DBINSTANCE="<Instance Name>" FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FilestreamProvider_1

NOTE

Specify the full path to RBS.MSI in the above state, i.e. C:UsersAdministratorDesktopRBS.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

  1. On the Start menu, click All Programs.
  2. Click Microsoft SharePoint 2013 Products.
  3. Click SharePoint 2013 Management Shell.
  4. 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

  1. On the Start menu, click All Programs.
  2. Click Microsoft SharePoint 2013 Products.
  3. Click SharePoint 2013 Management Shell.
  4. 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

To validate the FILESTREAM configuration and RBS installation:

  1. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.
  2. Expand Databases.
  3. Select the content database for which you want to create a BLOB store, and then click New Query.
  4. In SQL Server Management Studio, click New Query to display the Query Editor.
  5. In Query Editor, enter the following Transact-SQL code:

USE [Database Name]

SELECT * FROM dbo.DatabaseInformation

Confirm that both the RBSCollectionId and RBSProvider rows are available.

Step 7 Test the RBS Data Store

  1. Select a desired Document Library on a site in the configured content database.
  2. Upload a file that is greater than 1 MB.
  3. On the computer that contains the RBS data store, click Start, and then click Computer.
  4. Browse to the RBS data store directory.
  5. 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.

Drawing1

    Resources

    FILESTREAM Overview

    How to: Enable FILESTREAM

    AlwaysOn Availability Groups (SQL Server)

    Remote BLOB Storage (SQL Server Whitepaper)

    Microsoft SQL Server AlwaysOn Solutions Guide for High Availability and Disaster Recovery

    SQL Server 2012 High Availability

    SharePoint Server 2013 March 2013 Public Update

    The March 2013 Public Update for SharePoint Server 2013 was released on March 12, 2013 and in addition to resolving several issues, features new and added capabilities such as the ability to configure multiple app domains in when using AAM or Host Header configurations.  For a complete list of fixes included in this update see also Description of the SharePoint Server 2013 update: March 12, 2013.

    NOTE

    Ensure adequate disk space is available to extract the SharePoint Server 2013 March 2013 Public Update content.  The SharePoint Server 2013 March 2013 Public Update is 565 MB, 1.65 GB extracted.  A minimum of 2 GB available disk space should exist to install the SharePoint Server 2013 March 2013 Public Update.

    Patching servers with the Search service installed requires a specialized installation process.

    1. Download the SharePoint Server 2013 March 2013 Public Update
    2. Stop the SharePoint Timer Service, SharePoint Server Search 15, and SharePoint Search Host Controller services (open a Command Prompt and run each command in the order below):
      1. NET STOP SPTimerV4 (Performs host deployment and management for SharePoint search components)
      2. NET STOP OSearch15 (Sends notifications and performs scheduled tasks for SharePoint)
      3. NET STOP SPSearchHostController (Administers and crawls content from repositories)
    3. Install the SharePoint Server 2013 March 2013 Public Update.

    For additional information on patching Search servers see also How to install update packages on a SharePoint farm where search component and high availability search topologies are enabled.

    NOTE

    Installation of the SharePoint Server 2013 March 2013 Public Update will require a reboot of each server where the SharePoint Server 2013 March 2013 Public Update is installed.  The SharePoint Timer Service, SharePoint Server Search 15, and SharePoint Search Host Controller services are set to start automatically and should be stopped using the process above prior to running the SharePoint Products Configuration Wizard.

    1. Run the SharePoint Products Configuration Wizard
    2. Start the x services in the order specified below:
      1. NET START SPSearchHostController
      2. NET START OSearch15
      3. NET START SPTimerV4

    The SharePoint Server 2013 March 2013 Public Update establishes a baseline for future Public and Cumulative Updates from Microsoft for SharePoint Server 2013 and is required to support the installation of future updates.  The resulting build number for the SharePoint Server 2013 March 2013 Public Update is15.0.4481.1005.

    FAQ

    Q:  Is there a difference between Public and Cumulative Updates?

    A:  Yes.  Public Updates are released monthly and are broad in distribution containing both security fixes and critical non-security fixes.  Cumulative Updates are released every two months and contain approved hotfixes.

    Q:  Do I need to install the SharePoint Server 2013 March 2013 Public Update?

    A:  The SharePoint Server 2013 March 2013 Public Update establishes a baseline for future product updates and must be installed to support the installation of future product updates.

    Resources

    Description of the SharePoint Server 2013 update: March 12, 2013

    Update center for Office, Office servers, and related products

    SharePoint Server 2013 Extranet and Office 365 External Sharing Considerations

    Introduction

    What is an Extranet?  A common definition for the term Extranet is a network that enables controlled access to external users or an extension of an organization’s intranet extended to external users to include customers, partners, suppliers, etc. in isolation from other internet or intranet users.

    Extranet topologies with SharePoint Products have become an increasingly popular solution to enable collaboration with partners, customers, and external users; however, can be complex to implement and maintain over time.

    SharePoint Server 2013 Extranet Environments

    Traditional extranet environments encompass a private network securely extended to share a portion of an organization’s information and/or process with remote employees, partners, or customers. Extranet environments can provide the breadth of functionality provided to internal users such as branded informational content, personalized content and views based on user profiles, collaborative content which can include documents, lists, libraries, etc., and document repositories. Extranet environments provide content and services through a single, unified location, which complies with corporate and/or governmental regulatory and security requirements.

    Extranet environments enable support of remote employees or geographically disperse users through a seamless authentication experience, and can enable external partners to participate in business processes with a consistent security context including partner isolation and internal data segregation, limited authorization on an as needed basis and restriction of data across a broad range of supported partners. In addition to remote employees and partners, an extranet can provide customers with access to targeted content, segmentation controls to mitigate data cross-pollenization, and limitation of content access and search results based on audience profiles.

    SharePoint Server 2013 provides flexible options for the configuration of extranet access to sites and site data such as providing Internet-facing access to a subset of sites and site data in an environment or by making available the breath of SharePoint capabilities available over the Internet. Extranet content is typically hosted within an organizations’ corporate network and made available through an edge network or in some cases through an environment isolated within a perimeter network.

    clip_image002

    Figure 1 illustrates a common Extranet environment with SharePoint Server 2013

    Extranet Advantages

    • Data isolation within a trusted network, external access can be isolated to a perimeter network

    • Data maintenance within a single location

    • Can be comprised of a single environment that facilitates both internal and external collaboration with granular access controls

    • Can be implemented with a separate or shared AD infrastructure

    Extranet Disadvantages

    • Additional network and infrastructure configuration required increases cost and complexity

    For customers seeking only to share limited content and/or collaborate short-term without the need for a seamless logon experience or whose extranet environment is not subject to corporate or governmental policies, Office 365 provides an external sharing experience that can be leveraged to make data available over the Internet to remote employees, customers, and partners.

    Office 365 External Sharing

    External Sharing in Office 365 enables an organization to extend access to sites and site data with users that are not members of the Office 365 subscription or do not have accounts within the source Office 365 subscription. Individuals who do not have user accounts for the source SharePoint Online environment are considered “external users”. External users can be comprised of vendors or customers, for example. Activating the external sharing feature in SharePoint Online allows a site collection to invite external users to use the site and/or site content through email-based invitation.

    In Office 365 Enterprise plans, an organization can choose to manage external sharing centrally through the SharePoint Online Administration Center, enabling or disabling external sharing globally or through specifying more granular sharing options such as allowing sharing only with sign-in at the site collection level. Additionally management of external sharing can be achieved through using Windows PowerShell.

    In Office 365 Small Business plans, the administration experience for the management of external sharing is provided through a simplified on/off switch within the Office 365 Service Settings.

    External Sharing can be implemented in one of three ways when using Office 365:

    There are three ways that you can do this:

    NOTE External Sharing in Office 365 requires SharePoint Online Enterprise (E1), SharePoint Online Enterprise (E3 & E4), SharePoint Online Midsized Business.  External Sharing is available in all commercial Office 365 offerings.

    External Sharing Advantages

    • Simplified sharing model with granular access capabilities
    • Cost-effective
    • Light-weight solution to basic data sharing where complex business logic is not required or access to other internal resources/capabilities is required

    External Sharing Disadvantages

    • Organizations who elect to share data using Office 365 external sharing should be cautioned that shared data will be isolated from on-premises data resulting in two distinct data siloes. Capabilities to include metadata, etc. will also be isolated to the shared data environment as opposed to being consumed from on-premises services.
    • Organizations who elect to share sites or site content should consider isolating such data from internal business sites as granting external users access to a site and/or sub-sites that share permissions with the parent site providing access to sensitive content. In the event there is an ongoing need to collaborate with customers and/or partners, an organization should consider configuring a site explicitly used to enable external sharing configured with unique permissions to isolate it from other sites in the subscription.

    Additional Resources

    Overview of publishing to Internet, intranet, and extranet sites in SharePoint Server 2013 [http://technet.microsoft.com/en-us/library/jj635881.aspx]

    Design Sample: Extranet with Dedicated Zones for Authentication for SharePoint 2013 [http://www.microsoft.com/en-us/download/details.aspx?id=30368]

    Plan for Internet, intranet, and extranet publishing sites in SharePoint Server 2013 [http://technet.microsoft.com/en-us/library/jj635878.aspx]

    Share sites or documents with people outside your organization [http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/share-sites-or-documents-with-people-outside-your-organization-HA102894713.aspx?CTT=5&origin=HA102476183]

    Share a site with external users [http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/share-a-site-with-external-users-HA102476183.aspx]

    Manage external sharing for your SharePoint online environment [http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/manage-external-sharing-for-your-sharepoint-online-environment-HA102849864.aspx?CTT=5&origin=HA102476183]

    What is an external user? [http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/what-is-an-external-user-HA104036809.aspx]