Chapter 9. Managing and Maintaining SQL Server in a SharePoint Environment

SharePoint 2013 introduces multiple new Structured Query Language (SQL) Server databases and, subsequently, more SQL Server resources are needed. SQL Server databases are the heart of every SharePoint farm; they are the back-end repository for all SharePoint data and provide for critical architectural elements. To achieve maximum stability of a SharePoint farm, administrators must monitor and maintain SQL Server’s storage and performance on a regular basis because heavy stress on an SQL Server can ultimately degrade the end-user experience.

This chapter covers the most important SQL Server administration concepts and techniques useful for administrators. It focuses on specifics for monitoring, maintaining, and managing SharePoint databases; discusses the essential monitoring and maintenance tools; and presents guidelines for improving the performance and storage of SharePoint Server solutions running on Microsoft SQL Server.

In addition, at the end of this chapter, administrators learn how to solve the storage, performance, and manageability issues associated with huge SharePoint content databases by making use of the powerful storage externalization capabilities of SQL Server 2012 with the Remote Binary Large Objects Storage (RBS) feature.

The new high availability features of SQL 2012 that involve the use of SQL Server 2012 AlwaysOn Availability Groups (AOAGs) are covered in Chapter 4, “Advanced SharePoint 2013 Installation and Scalability.”

WMI is a huge initiative and certainly deserves an entire book of its own. However, what administrators need to know is that the architecture of WMI enables extensibility through the use of providers, which are dynamic link library files that interface between WMI and software or hardware components.

Each provider contains a set of WMI classes. Each WMI class represents a manageable entity, exposes information through properties, and enables the execution of some actions via methods. Because a provider is designed to access some specific management information, the WMI repository is logically divided into several areas called namespaces. Each namespace contains a set of providers with their related classes specific to a management area.

Administrators should also know that SQL Server, as part of its installation process, adds two providers to the WMI repository (WMI Provider for Configuration Management and WMI Provider for Server Events):

The WMI Provider for Configuration Management enables administrators to use WMI to manage SQL Server services, SQL Server client and server network settings, and server aliases. For example, after a connection is established with the WMI provider on a remote computer, not only is it possible to retrieve information about SQL Server instances, but it’s also possible to perform actions on them such as starting and stopping the instances.

The WMI Provider for Server Events enables administrators to use WMI to monitor events in SQL Server. Included are Data Definition Language (DDL) events that occur when databases are created, altered, or dropped and when tables are created, altered, or dropped, for example. In addition, software developers can write code that responds to these events, and they can even author their own set of monitoring tools. Administrators can also create an SQL Server Agent alert that is raised when a specific SQL Server event occurs that is monitored by the WMI Provider for Server Events.

It’s worth mentioning that WMI enables scripting languages such as VBScript or Windows PowerShell or even the WMI command-line utility (Wmic.exe) to manage local and remote servers. This enables administrators to query this huge amount of management information through an SQL-like language called the WMI Query Language (WQL).

To explore the available namespaces, classes, and events, administrators can use a downloadable tool such as the WMI Explorer shown in Figure 9.1.

Administrators can use information in the SQL Server error log to troubleshoot problems related to SQL Server. Browsing the SQL Server logs for irregular entries is an essential administration task; preferably, it should be carried out on a daily basis to help administrators spot any current or potential problem areas. An application-aware solution such as Microsoft’s System Center Operations Manager (SCOM) can help to automate the process of monitoring SQL (and SharePoint) logs.

SQL Server error log files are simple text files stored on disk, but it is good practice to examine them by using SQL Server Management Studio or by executing the xp_readerrorlog extended stored procedure to prevent any SQL operations from being blocked by opening one of these files in a text editor.

A new error log file is created each time an instance of SQL Server is started; however, the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server.

The Windows application log describes events that occur on the Windows operating system and other events related to SQL Server and SQL Server Agent. Administrators can use the Windows Event Viewer to view the Windows application log and to filter the information. These event logs should be another place that administrators go to look for information about any issues that take place with SQL Server.

In the past, administrators had to view the SQL Server and Windows event logs independently; however, the SQL Server Management Studio Log File Viewer makes it possible for administrators to combine both sets of logs into a united view.

1. Click Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance and expand that instance.

3. In Object Explorer, expand Management.

4. Right-click SQL Server Logs, click View, and then select either SQL Server Log or SQL Server and Windows Log.

5. Double-click any log file, such as the one shown in Figure 9.2.

To configure the number of log files to be retained, follow these steps:

1. Click Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance and expand that instance.

3. In Object Explorer, expand Management.

4. Right-click SQL Server Logs, and click Configure.

5. As shown in Figure 9.3, check the box to limit the number of error logs created before they are recycled. SQL Server retains backups of the previous six logs, unless this option is checked and a different maximum number of error log files specified.

6. Specify a different maximum number of error log files and click OK.

DMVs and functions return valuable information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. They give administrators an easy way to monitor what SQL Servers are doing and how they are performing by providing a snapshot of the exact state of the SQL Servers at the point they are queried. They replace the need to query the system tables or to use other inconvenient methods of retrieving system information in use prior to SQL Server 2005. SQL Server 2005 introduced DMVs, and the latest release, SQL Server 2012, includes additional useful DMVs.

Whenever an instance is started, SQL Server starts saving state and diagnostic data into DMVs. When an instance is restarted, the information is flushed from the views, and new data begins to be loaded.

DMVs and functions are part of the sys schema in the Master database. Administrators can find a list of dynamic views in SQL Server Management Studio under Master/Views/System Views, and the dynamic functions are located under Master/Programmability/Functions/System Functions/Table-valued Functions. Each dynamic object’s name has a dm_ prefix.

For example, later in this chapter, the sys.dm_db_index_physical_stats dynamic management function is used to determine the fragmentation percentage of the indexes for efficient database maintenance.

The Reliability and Performance Monitor can monitor resource usage for the server and provide information specific to SQL Server either locally or for a remote server. It provides a massive set of counters that can be used to capture a baseline of server resource usage, and it can monitor over longer periods to help discover trends. It can also detect abnormal values at a glance for key performance counters on critical SQL Server instances. In addition, administrators can configure it to produce alerts when preset thresholds are surpassed.

After opening the Reliability and Performance Monitor, as shown in Figure 9.4, the % Processor Time counter from the Processor object is automatically monitored in real time with a 1-second refresh interval. Additional counters can be appended to the graph by clicking the green plus icon on the toolbar and navigating through objects, which classify the counters into groups. When an SQL Server instance is installed on a server, it adds more than 1,000 new performance counters to the Performance Monitor section of the Reliability and Performance Monitor. Of the many performance counters that can be selected when troubleshooting an SQL Server instance, choosing the appropriate key indicators can significantly help administrators quickly isolate bottlenecks and direct their investigation to the appropriate resources for corrective actions.

In addition, administrators can capture performance counters to log files for long-term analysis by creating data collector sets. Creating data collector sets is beyond the scope of this chapter.

The sections are Overview, Processes, Resource Waits, Data File Input/Output (I/O), and Recent Expensive Queries. In SQL Server 2012, right-clicking an SQL Server instance within Object Explorer and specifying the Activity Monitor launches the tool, as shown in Figure 9.5.

Overview: Shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec), and the Number of Batch Requests/second.

Processes: Lists all the active users who are connected to the SQL Server database engine. This is beneficial for administrators because they can click any of the session IDs, run an SQL Server Profiler trace to capture all its activities, or even kill a specific process.

Resource Waits: Displays resource waits vertically based on the following wait categories: CPU, SQL Common Language Runtime (SQLCLR), Network I/O Latch, Lock, Logging, Memory, Buffer I/O, Buffer Latch, and Compilation. From a horizontal perspective, the Wait Time, Recent Wait Time, Average Waiter Counter, and Cumulative Wait Time metrics are published for each Wait category. Analogous to the Processes section, data can be filtered based on items within a column.

Data File I/O: Displays disk-level I/O information related to all the data and log files of user and system databases. Administrators can use this to rapidly recognize databases that are performing badly because of disk bottlenecks.

Recent Expensive Queries: The last section in Activity Monitor is Recent Expensive Queries. This section gives administrators the opportunity to capture the queries that are performing the worst and negatively influencing an SQL Server instance. Approximately 10 to 15 of the worst and most expensive queries are displayed in the performance dashboard. The actual query is displayed with augmenting metrics such as Execution in Minutes, CPU ms/sec, Physical Reads/sec, Logical Write/sec, Logical Reads/sec, Average Duration in ms, and Plan Count. It is also possible to right-click the most expensive query and show the execution plan.

The Management Data Warehouse is a relational database inside the SQL Server 2012 instance that holds a variety of performance-related statistics. The performance statistics in the Management Data Warehouse are gathered via special data-gathering routines, known as data collections. The Management Data Warehouse can include data collection information from a sole instance or can alternatively hold data collected from multiple instances. The data collection process depends on prebuilt SQL Server Integration Services (SSIS) routines and SQL Server Agent jobs, which diminishes the number of things administrators need to do to build and maintain a database that contains performance statistics.

SQL Server 2012 provides three different system data collection definitions. These data collections are Disk Usage, Query Activity, and Server Activity. Each of these data collection definitions identifies the data to be collected, how often it should be collected, and how long it should be kept in the Management Data Warehouse.

Data collections can be run manually, on a schedule, or continually. Manual and scheduled data collections collect and upload data into the Management Data Warehouse on the same schedule. These types of data collections are known as noncached collections. When a data collection runs continually, data is cached in a directory and then uploaded to the Management Data Warehouse from time to time. These are known as cached collections.

It is also worth mentioning that Microsoft has also provided standard reports to enable administrators to drill down into data gathered for each of these collections using SQL Server Management Studio.

Stepping through problematic queries to find the cause of a problem.

Finding and diagnosing bad-performing queries.

Capturing the series of T-SQL statements that lead to a problem. Saved traces can then be used to replicate the problem on test servers for later diagnostics.

Monitoring the performance of SQL Server to tune architecture.

Correlating performance-collected counters to diagnose problems.

To use SQL Server Profiler, an administrator needs to understand the terms that describe the way the tool functions.

Login connections and disconnections

T-SQL SELECT, INSERT, UPDATE, and DELETE statements

Remote procedure call (RPC) batch status

The start or end of a stored procedure

The start or end of statements within stored procedures

The start or end of an SQL batch

An error written to the SQL Server error log

Lock-related actions on a database object

Security permission checks

All the data generated by an event is displayed in the trace in a single row. This row is intersected by data columns that describe the event in detail.

SQL:BatchCompleted

Audit Login

Audit Logout

Lock:Acquired

Lock:Released

If a filter is not set, all events of the selected event classes are returned in the trace output.

2. Click File, New Trace, and then log in to the desired server.

3. The Create Trace window pops up, as shown in Figure 9.6.

4. Administrators can provide the trace name and choose a template for tracking some events or filters in addition to the way to store the trace. This can be an SQL Server table or a simple file.

5. By clicking the second tab, the event selection opens as shown in Figure 9.7.

6. Administrators can select the events to trace, to view the query and its execution parameters; the info will reside in SQL:BatchCompleted because this event occurs when a query is fired to the database. In this event, the actual query appears, who fired it, and how long it took to execute.

7. Figure 9.8 shows the trace that starts listing all events requested in the previous window and the actual statement that’s being executed on the database. Stopping or erasing the trace for better readability options exist, and filteration in the previous window is very useful to query down all the rows with useful information about the desired database.

Here are some recommendations for using DBCC CHECKDB:

Administrators should run DBCC CHECKDB rather than the individual operations because it identifies most of the errors and is generally safe to run in a production environment.

After running DBCC CHECKDB, administrators should run it again with the REPAIR argument to repair any reported errors.

DBCC CHECKDB can be time-consuming, and it requires schema locks that prevent metadata changes; therefore, it is highly recommended that administrators run it during nonproduction hours.

The command should be run on a table-by-table basis if it is used to perform consistency checks on large databases.

When indexes are first built, little or no fragmentation should be present. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes might begin to increase.

When a page of data is completely full and further data must be added to it, a page split occurs. To make room for the new arriving data, SQL Server creates another data page somewhere else in the database (not necessarily in a contiguous location) and moves some of the data from the full page to the newly created one. The effect of this is that the blocks of data are logically linear but physically nonlinear. Therefore, when searching for data, SQL Server has to jump from one page to somewhere else in the database looking for the next page it needs instead of going directly from one page to the next. This results in performance degradation and inefficient space utilization.

In SharePoint 2013, administrators no longer need to worry about fragmentation because SharePoint can do that on their behalf via the health analyzer. The health analyzer performs “health checks” based on timer jobs and self-heals the database index fragmentation automatically.

However, shrinking data files is not recommended unless the content database has lost at least half its content. This usually happens after some activities create white space in the content database, such as moving a site collection from a content database to another one or deleting a massive amount of data. Shrinking SharePoint databases other than content databases is not recommended, because they do not generally experience as many necessary deletions to contain considerable free space.

1. Click Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance and expand that instance.

3. Expand Databases, right-click the database to be shrunk, click Tasks, click Shrink, and click Files.

4. Select the file type and filename from the dialog box shown in Figure 9.9.

5. (Optional) Select Release Unused Space. Selecting this option causes any unused space in the file to be released to the operating system and shrinks the file to the last allocated extent. This reduces the file size without moving any data.

6. (Optional) Select Reorganize Pages Before Releasing Unused Space. If this option is selected, the Shrink File To option must be set to value. Selecting this option causes any unused space in the file to be released to the operating system and tries to relocate rows to unallocated pages.

7. (Optional) Select Empty File by Migrating the Data to Other Files in the Same Filegroup. Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.

8. Click OK.

Fortunately, Microsoft has provided maintenance plans as a way to automate these tasks. A maintenance plan performs a comprehensive set of SQL Server jobs that run at scheduled intervals. Specifically, the maintenance plan conducts scheduled SQL Server maintenance tasks to ensure that databases are performing optimally, are regularly backed up, and are checked for anomalies. Administrators can use the Maintenance Plan Wizard (included with SQL Server) to create and schedule these daily tasks. In addition, the wizard can configure database and transaction log backups.

It is also worth mentioning that administrators should set any maintenance operations or maintenance plans to run during off-hours to minimize the performance impact on users.

1. Click Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance.

3. Click Management, right-click Maintenance Plans, and click Maintenance Plan Wizard. (The SQL Server Agent Service should be running.)

4. On the Welcome to the Database Maintenance Plan Wizard screen, click Next to continue.

5. On the Select Plan Properties screen, as shown in Figure 9.10, enter a name and description for the maintenance plan.

6. Decide whether to configure one or more maintenance plans.

To configure a single maintenance plan, select Single Schedule for the Entire Plan or No Schedule. This option is chosen in the example in Figure 9.10.

To configure multiple maintenance plans with specific tasks, select Separate Schedules for Each Task.

7. Click Change to set a schedule for the plan. The Job Schedule Properties dialog box appears, as shown in Figure 9.11.

8. Complete the schedule, click OK, and then click Next to continue.

9. On the Select Maintenance Tasks screen (see Figure 9.12), select the maintenance tasks to include in the plan, and then click Next to continue.

10. In the Select Maintenance Task Order page, change or review the order that the tasks will be executed in, select a task, and then click Move Up or Move Down. When tasks are in the desired order, click Next. The wizard helps you through setting the details for each task. For example, Figure 9.13 shows the configuration of the Database Check Integrity Task.

11. On the Select Report Options page, select Write a Report to a Text File, select a location for the files, as shown in Figure 9.14, and then click Next until the wizard is completed.

To enable TDE encryption on a SharePoint database, follow these steps:

1. Open SQL Server Management Studio by clicking Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio. Connect to the desired SQL Server database engine instance, and then create a master key and a certificate in the master database by executing the following T-SQL commands:

Click here to view code image

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<CERTIFICATEPASSWORD>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ENCRYPTION_CERTIFICATE';
go

2. Create the encryption key that will encrypt the database with the pre-created certificate by executing the following T-SQL commands:

Click here to view code image

USE WSS_Content;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE WSS_Content
SET ENCRYPTION ON;
GO

3. The process runs and starts encrypting the database. An administrator can check the status of the encryption process by executing the following T-SQL command:

Click here to view code image

SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;

3 is the status for an encrypted database.

4. Back up the encryption certificate to restore it on other servers:

Click here to view code image

BACKUP CERTIFICATE sales05 TO FILE = 'c:storedcertsMyServerCert'
  WITH PRIVATE KEY (FILE = 'c:storedkeysMyServerCertKey',
  ENCRYPTION BY PASSWORD = 'STRONGPASSWORD'),

5. Before restoring the database on other servers, restoring the certificate is mandatory by using the following T-SQL command:

Click here to view code image

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'STRONGPASSWORD';
GO

CREATE CERTIFICATE MyServerCert
FROM FILE = 'c:storedkeysMyServerCert'
WITH PRIVATE KEY
(
    FILE = 'c:storedkeysMyServerCertKey',
    DECRYPTION BY PASSWORD = 'STRONGPASSWORD'
);
GO

An administrator can now guarantee the security of the database files with an encryption standard that is compliant with most of the different security standards.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Central Administration.

2. On the SharePoint Central Administration (SPCA) website, click Application Management.

3. In the Databases section, click Manage Content Databases.

4. On the Manage Content Databases page, as shown in Figure 9.15, click Add a Content Database.

5. On the Add Content Database page, as shown in Figure 9.16, do the following:

A. Select a web application for the new database.

B. Select a database server to host the new database.

C. Specify the authentication method that the new database will use, and supply an account name and password, if they are necessary.

D. Specify both the total number of top-level sites that can be created in the database and the number at which a warning will be issued.

6. Click OK.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type the following command, and then press Enter:

Click here to view code image

New-SPContentDatabase -Name <String> -WebApplication <SPWebApplicationPipeBind>

The -Name parameter specifies the name of the content database to be created. The -WebApplication parameter specifies the web application to which the new database is to be attached.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type the following command, and then press Enter:

Click here to view code image

New-SPSite -Url <String> -OwnerAlias <String> [-ContentDatabase
<SPContentDatabasePipeBind>]

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type the following command, and then press Enter:

Click here to view code image

Get-SPSiteAdministration –Identity http://ServerName/Sites/SiteName| ft
Url,DiskUsed

Replace http://ServerName/Sites/SiteName with the name of the site collection.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type the following command, and then press Enter:

Click here to view code image

Move-SPSite <http://ServerName/Sites/SiteName> -DestinationDatabase
<DestinationContentDb>

Replace http://ServerName/Sites/SiteName with the name of the site collection, and replace <DestinationContentDb> with the name of the destination content database.

1. Pause any service applications and services that might run against the desired content database.

2. Remove the content database from the SharePoint web application.

3. Detach the database from the source SQL Server instance.

4. Move the database files to the target location.

5. Attach the database files to the destination SQL Server instance.

6. Add the content database to the destination SharePoint web application.

2. On the SPCA website, click Monitoring, and then click Check Job Status, as shown in Figure 9.17.

3. For each Timer Job running for the target database, click Edit Job, Disable, and then click OK, as shown in Figure 9.18.

4. On the SPCA website, click Application Management.

5. In the Databases section, click Manage Content Databases.

6. On the Manage Content Databases page, choose the desired content database.

7. In the Remove Content Database section, as shown in Figure 9.19, click Remove Content Database, and then click OK.

8. Open SQL Server Management Studio by clicking Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

9. Connect to the desired SQL Server database engine instance and expand that instance.

10. Right-click the content database, point to Tasks, and then click Detach, as shown in Figure 9.20.

11. Using Windows Explorer, move all database files (.mdf, .ldf, and .ndf) to the desired location in the other server.

12. In the destination server, open SQL Server Management Studio and connect to the desired SQL Server instance.

13. Right-click the destination instance in Object Explorer, point to Tasks, and then click Attach, as shown in Figure 9.21.

14. In the Attach Databases dialog box, point to the database files that were just moved, choose the .mdf file, and then click OK, as shown in Figure 9.22.

15. On the SPCA website, click Application Management.

16. In the Databases section, click Manage Content Databases.

17. On the Manage Content Databases page, click Add a Content Database while verifying that the target web application is selected.

18. Type the exact name in the Database Name field while specifying the required authentication.

19. On the SPCA website, click Monitoring, and then click Check Job status.

20. Start the paused services by clicking Edit Job and then starting back all the previously paused services.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), to stop all associated services to the database, type the following command, and then press Enter:

Click here to view code image

Get-SPTimerJob -webapplication <http://WebApplicationURL> | select name |
Out-File <c: imerjob.txt> –Append –Encoding ascii
ForEach($tmrjob in (Get-Content <c: imerjob.txt>)) { Get-SPTimerJob -Identity
$tmrjob | Disable-SPTimerjob }

The –webapplication is the URL of the desired web application and the c: imerjob.txt is the location of the file that will contain all timer jobs associated with this database.

3. Detach the desired content database using the following cmdlet:

Click here to view code image

Dismount-SPContentDatabase "<ContentDB>"

where ContentDB is the name of the desired database.

4. Detach the content database from the source SQL Server and reattach it again on the destination server by following steps 8 to 14.

5. Reattach the content database to the destination SharePoint web application using the following cmdlet:

Click here to view code image

Mount-SPContentDatabase "<ContentDb>" -DatabaseServer "<DbServer>"
-WebApplication <http://SiteName>

The -Name parameter specifies the name of the content database to be created. The -DatabaseServer specifies the database server where the database resides to be mounted. The -WebApplication parameter specifies the web application to which the new database will be attached.

6. To start back all the paused services, execute the following command:

Click here to view code image

ForEach($tmrjob in (Get-Content <c: imerjob.txt>)) {Get-SPTimerJob -Identity
$tmrjob | Enable-SPTimerjob}

The c: imerjob.txt is the location of the file that contains all timer jobs associated with this database.

SQL Server provides special data types for dealing with such large volumes of binary data. These various data types have changed over time.

In SQL 2000, there were two different families of data type options for this type of data: binary and image. The binary family included two different data types: the binary data type and the VARBINARY data type. The VAR in VARBINARY means that the size is variable rather than fixed, as in the case of the standard binary data type. However, it still has a maximum length of 8,000 bytes.

The image data type family was used to store BLOBs that are greater than 8,000 bytes. This data type is still present in newer versions, but it is deprecated. Microsoft recommends avoiding using these data types in new development work and recommends modifying applications that currently use them.

Starting in SQL Server 2005, Microsoft included the VARBINARY(MAX) data type to the binary data type family. This variation extends the usual limit of around 8,000 bytes and allows storage up to 2GB of data.

Later, in SQL Server 2008, Microsoft introduced the FILESTREAM option for VARBINARY(MAX) fields. This enables storage, efficient streaming, and integrated management of large BLOBs in an SQL database by using the underlying New Technology File System (NTFS) for BLOB storage/streaming, while managing and accessing it directly within the context of the database.

Instead of being a completely new data type, FILESTREAM is a storage attribute of the existing VARBINARY(MAX) data type. FILESTREAM alters how the BLOB data is stored—in the file system rather than in the SQL Server data files. Because FILESTREAM is implemented as a VARBINARY(MAX) column and integrated directly into the database engine, most SQL Server management tools and functions work without modification for FILESTREAM data.

It is also worth mentioning that the behavior of the regular VARBINARY(MAX) data type remains entirely unchanged in SQL Server 2008/R2/2012, including the 2GB size limit. The addition of the FILESTREAM attribute means a VARBINARY(MAX) column can essentially be unlimited in size. (In reality, the size is limited to that of the underlying NTFS volume.)

In May 2007, Microsoft released a hotfix for Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 that was later rolled into Service Pack 1. The hotfix exposed an External BLOB Storage (EBS) application programming interface (API), which enabled the storage of BLOBs outside content databases by implementing a set of interfaces. This seemed revolutionary, but EBS was difficult to implement because it exposed an unmanaged interface. Accordingly, this part had to be handled by third parties, or independent software vendors (ISVs). In addition, EBS had some limitations; for example, it could be enabled only on the farm level and not on the content database level.

Starting in SharePoint 2010, Microsoft heard its customers’ feedback loud and clear and decided to fix the BLOB storage pain points. In SharePoint 2013, it is possible to move the storage of BLOBs from database servers to commodity storage solutions by using RBS technology, which was first introduced in SQL Server 2008.

Using RBS, applications can store large amounts of unstructured data (such as Office documents, PDF files, or videos) and enjoy both the relational capabilities of SQL Server and the scalability of a dedicated BLOB store. Best of all, developers do not have to write the code to handle the job of tying together the SQL metadata and the BLOB data. RBS handles the transactional consistency completely.

An application stores and accesses BLOB data by calling into the RBS client library. ISVs and storage solution vendors can create their own RBS provider library to enable the use of custom stores with applications written against the RBS API set. Microsoft has even created a provider named FILESTREAM RBS provider, which comes with RBS 2008 R2 and RBS 2012, and can be used for storing BLOBs on the underlying NTFS file system. The FILESTREAM RBS provider ties the RBS technology with the FILESTREAM feature that was first introduced in SQL Server 2008.

SharePoint 2007 did not take advantage of the recent SQL Server features that Microsoft first introduced for unstructured data in SQL Server 2008, such as the FILESTREAM attribute or RBS technology; instead, SharePoint 2007 provided its own options to enhance the storage efficiency and manageability of huge data through EBS.

SharePoint 2010 and SharePoint 2013 support RBS and can leverage the SQL Server FILESTREAM RBS provider, thus providing cheaper storage and much better performance. Figure 9.24 illustrates how RBS works with SharePoint 2013.

1. Enable FILESTREAM on SQL Server.

2. Prepare the database, and create a BLOB store.

3. Install the RBS client.

4. Enable RBS using Windows PowerShell.

1. Click Start, All Programs, Microsoft SQL Server 2012, Configuration Tools, SQL Server Configuration Manager.

2. In the left panel of SQL Server Configuration Manger, click SQL Server Services, which lists all the SQL Server 2012-related services on the right-side panel.

3. Locate the instance of SQL Server on which FILESTREAM is required to be enabled, right-click the instance, and then click Properties.

4. In the SQL Server Properties dialog box, click the FILESTREAM tab and select the Enable FILESTREAM for Transact-SQL Access check box, which enables the rest of the options.

5. Select all the check boxes, and then click Apply.

6. Click Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

7. Connect to the desired SQL Server database engine instance.

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

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

Click here to view code image

EXEC sp_configure filestream_access_level, 2; RECONFIGURE

10. Click Execute.

1. Click Start, All Programs, Microsoft SQL Server 2012, SQL Server Management Studio.

2. Connect to the desired SQL Server database engine instance and expand that instance.

3. Expand the databases, select the content database for which a BLOB store will be created, click New Query, and then execute the following commands:

Click here to view code image

use [ContentDatabaseName]
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'
use [ContentDatabaseName]
if not exists (select groupname from sysfilegroups where groupname=
N'RBSFilestreamProvider')alter database [ContentDatabaseName] add filegroup
RBSFilestreamProvider contains filestream
use [ContentDatabaseName]
alter database [ContentDatabaseName] add file (name = RBSFilestreamFile,
filename = 'c:RemoteBlobStore') to filegroup RBSFilestreamProvider

1. Launch SQL Server 2012 RBS with the FILESTREAM provider (RBS.msi) on the database server (RBS.msi must first be downloaded from Microsoft), all the web front ends, and all the application servers.

2. On the database server, run the following commands from the location of the RBS.msi file using the command prompt:

Click here to view code image

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi TRUSTSERVERCERTIFICATE=true
FILEGROUP=PRIMARY DBNAME="ContentDatabaseName" DBINSTANCE=
"DatabaseInstanceName" FILESTREAMFILEGROUP=RBSFilestreamProvider
FILESTREAMSTORENAME=FilestreamProvider_1
msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME=
"ContentDatabaseName" DBINSTANCE="DatabaseInstanceName" ADDLOCAL="Client,Docs,
Maintainer,ServerScript,FilestreamClient,FilestreamServer"

This should be run against each content database that should support RBS.

3. On all the web front ends and all the application servers, run the following command from the location of the RBS.msi file using the command prompt:

Click here to view code image

msiexec /qn /lvx* rbs_install_log.txt /i RBS.msi DBNAME="ContentDatabaseName"
DBINSTANCE="DatabaseInstanceName" ADDLOCAL="Client,Docs,Maintainer,ServerScrip
t,FilestreamClient,FilestreamServer"

These commands kick off an msiexec service that runs in a silent mode, not providing any feedback about their success or failure. Administrators can monitor the service in the Task Manager to ensure that they are finished.

4. Administrators can confirm the RBS installation by looking for the text Product: SQL Remote Blob Storage—Configuration Completed Successfully in the RBS log file. The previous installation commands create a log file named rbs_install_log.txt in the same location as the RBS.msi file. The installation also creates several tables in the specified content database with names that are preceded by mssqlrbs. Administrators can also look for these tables to confirm the installation.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type each of the following commands and press Enter after each one:

Click here to view code image

$cdb = Get-SPContentDatabase ContentDatabaseName
$blobstoragesettings = $cdb.RemoteBlobStorageSettings
$blobstoragesettings.Enable()
$blobstoragesettings.SetActiveProviderName($blobstoragesettings.
GetProviderNames()[0])

The Enable cmdlet enables the usage of RSB for a certain content database. When RBS is enabled, BLOBs get stored in the active BLOB store. When the active BLOB store is disabled through the Disable cmdlet, the BLOBs get stored back in the content database.

The GetProviderNames cmdlet can be used to list all the registered providers in the farm, and the names are retrieved from the configuration database.

The SetActiveProviderName cmdlet is used to activate a certain provider for a certain content database. A farm can have multiple RBS providers, but only one of them can be active at a time for a given content database.

Another useful cmdlet that was not used previously is the MinimumBlobStorageSize cmdlet. This cmdlet can be used to set a size threshold. For example, an administrator can decide to store files smaller than 1MB in the content database and larger ones in the BLOB store.

3. Now, all the uploaded documents should go to the BLOB store (c:RemoteBlobStore) rather than being saved in the specified content database. Figure 9.25 illustrates the upload operation workflow in SharePoint 2013 after enabling RBS.

It is also worth mentioning that the Migrate cmdlet performs a deep copy of the BLOBs one BLOB at a time, and there is no downtime required for moving all the BLOBs. The migration process also can be paused and resumed at any time, which means that at a point, part of the BLOBs can be in SQL Server, and the other part can reside in another BLOB store.

This cmdlet can even be used to move BLOBs from one BLOB store to another one by moving them back to SQL Server and then migrating them to another store.

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type each of the following commands and press Enter after each one:

Click here to view code image

$cdb = Get-SPContentDatabase ContentDatabaseName
$blobstoragesettings = $cdb.RemoteBlobStorageSettings
$blobstoragesettings.Migrate()

1. Click Start, All Programs, Microsoft SharePoint 2013 Products, SharePoint 2013 Management Shell.

2. At the Windows PowerShell command prompt (PS C:>), type each of the following commands and press Enter after each one:

Click here to view code image

$cdb = Get-SPContentDatabase ContentDatabaseName
$blobstoragesettings = $cdb.RemoteBlobStorageSettings
$blobstoragesettings.Disable()
$blobstoragesettings.Migrate()

Keep an eye on SQL Server performance and storage using the vast range of available monitoring tools.

Use the SQL Server Management Studio Log File Viewer to monitor both SQL Server and Windows event logs. It is highly recommended to look through the logs on a daily basis to detect any existing or possible issues.

Use the SQL Server Maintenance Plan Wizard to set up SQL maintenance plans.

Do not include the option to shrink the database when creating SQL Server maintenance plans; this should be run manually only if the content database has lost at least half its content.

Set the maintenance plans in production environments to run during off hours.

Ensure that the health analyzer is configured to automatically self-heal the database’s index fragmentation.

Saving the TDE certificate in a secured place is very important and the first action to be done after encrypting the database.

To enhance the performance of SharePoint implementations, try not to let content databases grow larger than 100GB.

Create new site collections in new content databases; this enhances the performance, simplifies the manageability, and provides flexibility for disaster recovery strategies.

Move the storage of BLOBs from content databases to commodity storage solutions by using the RBS technology to avoid storage and performance issues.

Highly consider the use of SQL Server 2012 AOAGs for high availability and disaster tolerance of your data. This concept is covered in Chapter 4.