Integration of SQL Server 2008 and Office SharePoint Server 2007

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

Topic Last Modified: 2016-11-14

In this article:

• About Office SharePoint Server integration with SQL Server 2008

• Database backup compression

• Log stream compression

• Transparent Data Encryption (TDE)

• SQL Server 2008 Resource Governor

This article provides prescriptive guidance for implementing some of the new features in Microsoft SQL Server 2008 that enhance the deployment of Microsoft Office SharePoint Server.

About Office SharePoint Server integration with SQL Server 2008

For Office SharePoint Server, the most important aspects of SQL Server 2008 are improvements in the following:

• Performance, by implementing database backup compression.

• Availability, by implementing log stream compression.

• Security, by implementing Transparent Data Encryption (TDE).

• Resource management, by using SQL Server 2008 Resource Governor.

Database backup compression

SQL Server 2008 Enterprise Edition enables you to compress database backups, and you can use any version of SQL Server 2008 to restore compressed database backups. The ability to compress database backups is a significant feature of SQL Server 2008 for Office SharePoint Server users. By setting the compression flag in your backup script, or by configuring the application server running SQL Server 2008 Enterprise Edition to compress by default, you can significantly decrease the size of your database backups and shipped logs.

The cost and complexity of backup and restore operations are important aspects of IT manageability. Office SharePoint Server running on SQL Server 2008 might be able to provide a cost savings for backup and restore operations by using backup compression. The amount of cost savings depend on how document-centric your Office SharePoint Server deployments are, and which versions of Office client applications exist in your environment.

By default, Office SharePoint Server stores document streams in SQL tables. Depending on your deployment, document streams can consume a large percentage of total database capacity (as much as 70 percent to 95 percent, based on some sample Office SharePoint Server databases). If your environment consists predominantly of Office 2007 client applications, backup compression might not provide a significant cost savings. In this scenario, the cost savings might be as little as 20 percent or less. However, if your existing data consists predominantly of content that was created using Office 2003 (or earlier versions of Office), enabling SQL Server 2008 backup compression can significantly reduce the size of your backup images and provide a compelling cost savings.

You can implement backup compression by configuring SQL Server properties in SQL Server Management Studio on the application server that is running SQL Server 2008 Enterprise Edition. For more information, see Introducing SQL Server Management Studio (https://go.microsoft.com/fwlink/?LinkId=129379&clcid=0x409).

You can also configure database backup compression by adding the compression clause to your backup script, as shown in the following code sample:

BACKUP DATABASE [<DATABASE_NAME>]
TO DISK = 'E:\BACKUP\<DATABASE_NAME>.bak'
WITH COMPRESSION;

Compression requires CPU resources and in many environments CPU is a precious commodity, even during backup windows. By default, backing up using backup compression significantly increases CPU usage, and the additional CPU resources consumed by the compression process can adversely impact concurrent operations. For more information, see Backup Compression (SQL Server) (https://go.microsoft.com/fwlink/?LinkId=129381&clcid=0x409).

Log stream compression

One of the performance limiting factors of database mirroring is network throughput between the principal and mirror servers. SQL Server 2008 supports the compression of the log stream, which consists of the inserts, updates, and deletes that are transferred between servers. Log stream compression can help maximize network throughput, especially in environments where network conditions are poor. You should expect to see compression effectiveness that is similar to backup compression, with a similar impact on CPU resources. Unlike backup compression, however, the impact on CPU resources is difficult to control. Log stream compression is on by default as soon as you start a mirroring session. You can, however, disable log stream compression by using trace flag 1462. For more information about log stream compression and mirroring, see Availability Enhancements (Database Engine) (https://go.microsoft.com/fwlink/?LinkId=129382&clcid=0x409).

Transparent Data Encryption (TDE)

SQL Server 2008 Enterprise Edition support for TDE is an important enhancement to the encryption capabilities of Microsoft SQL Server 2005. TDE enables you to encrypt entire databases and requires no application awareness or modification. TDE encrypts data as it is written to disk and decrypts data as it is read from disk. You can enable TDE by performing the procedure shown in the following code samples:

1. Create an encryption key

2. Create a certificate

3. Configure your database to use the certificate for encryption

4. Enable encryption

Create an encryption key

USE MASTER;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@$$W0rd'

Security Note:
If complexity is enforced, the password must meet complexity requirements.

Create a certificate

CREATE CERTIFICATE MYENCRYPTCERT WITH SUBJECT = 'TDE';

Configure your database to use the certificate for encryption

USE <ContentDB>;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MYENCRYPTCERT;

Enable encryption

ALTER DATABASE <ContentDB>
SET ENCRYPTION ON;

TDE is a CPU intensive operation and, to guarantee security, encryption has to be scoped to:

• The encrypted database

• The log

• The temporary database

• Backups of the encrypted database

This means that on busy systems a lot of CPU resources will be used, even if you are encrypting a small number of databases. Therefore, use encryption carefully. Implement encryption only where it is absolutely necessary and after thoroughly testing the performance and operational ramifications. Consider limiting the implementation of TDE to subsets of databases where security is extremely important and scale is not very important.

For more information about database encryption in SQL Server 2008 Enterprise Edition, see Database Encryption in SQL Server 2008 Enterprise Edition (https://go.microsoft.com/fwlink/?LinkId=129383&clcid=0x409).

Additional factors to consider when you use TDE

• Backup compression will be less effective on encrypted databases.

• TDE might not take advantage of multiple processors.

• TDE affects restore operations and disaster recovery because you need an encryption certificate to restore a database. Without an encryption certificate, the restore operation will fail.

• TDE affects database mirroring and log shipping. You need to install an encryption certificate on partner servers to be able to mirror or log ship.

• File stream data will not be encrypted.

For more information about TDE, see Understanding Transparent Data Encryption (TDE) (https://go.microsoft.com/fwlink/?LinkId=129384&clcid=0x409).

SQL Server 2008 Resource Governor

SQL Server 2008 Resource Governor enables you to precisely control how your SQL Server resources, such as CPU and memory, are allocated. You can use Resource Governor to apply thresholds to incoming connections based on assigned criteria. You can apply Resource Governor to any attribute that makes an incoming request unique. For example, if you want to prevent a reporting application that is running on a separate server from consuming too much memory, you can create a policy that identifies the application by application name, host name, or user name.

Resource Governor enables you to maintain and administer your SQL Server resources. For example, if you want to ensure that your team cannot use Management Studio or Query Analyzer to overload SQL Server with ad hoc queries, you can use Resource Governor to prevent ad hoc queries from consuming more than 25 percent of available CPU resources by performing the procedure shown in the following code samples:

1. Create a resource pool to limit CPU usage to 25 percent

2. Create a workload group for ad hoc queries and register it with the new resource pool

3. Create a function that classifies Management Studio and Query Analyzer as members of the ad hoc group

4. Register the new function with Resource Governor

5. Restart Resource Governor

Create a resource pool to limit CPU usage to 25 percent

CREATE RESOURCE POOL poolAdhoc
WITH (MAX_CPU_PERCENT = 25);

Create a workload group for ad hoc queries and register it with the new resource pool

CREATE WORKLOAD GROUP groupAdhoc
USING poolAdhoc;

Create a function that classifies Management Studio and Query Analyzer as members of the ad hoc group

CREATE FUNCTION adhocQueryClassifier() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @grp_name AS SYSNAME
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
         OR (APP_NAME() LIKE '%QUERY ANALYZER%')
         SET @grp_name = 'groupAdhoc'
   RETURN @grp_name
END
GO

Register the new function with Resource Governor

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION= dbo.adhocQueryClassifier);

Restart Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

Queries originating from SQL Management Studio or Query Analyzer will be allocated to poolAdhoc and, if CPU resources become constrained, queries will be restricted to using no more than 25 percent of available CPU resources. When CPU resources are not constrained, Resource Governor allows each query to consume needed resources without any restrictions. Resource Governor can also be used to allocate minimum resources.

For more information about Resource Governor, see Managing SQL Server Workloads with Resource Governor (https://go.microsoft.com/fwlink/?LinkId=129385&clcid=0x409).

Download this book

This topic is included in the following downloadable book for easier reading and printing:

• Planning and architecture for Office SharePoint Server 2007, part 2

See the full list of available books at Downloadable content for Office SharePoint Server 2007.