Vantage Point: Bob German's Weblog

Notes from BlueMetal Architects, where Bob is SharePoint Principal Architect. Here you will find postings on all things SharePoint, especially developer related topics.

MOSS 2007 and SQL Server 2008 - Better Together

MOSS 2007 and SQL Server 2008 - Better Together

Rate This
  • Comments 2

Many thanks to everyone who attended the MOSS 2007 and SQL 2008 "Better Together" webcast today. This blog entry will provide pointers to resources mentioned on the webcast, and will also stand on its own as an overview of the topic. Rich Crane will also post about this on his blog, and will include the slides for those who wanted them. Please check it out at http://blogs.msdn.com/rich_crane.

Microsoft Office SharePoint Server 2007 (MOSS) and Windows SharePoint Services 3.0 (WSS) both added support for SQL Server 2008 in Service Pack 1. This same service pack allows MOSS and WSS to run on Windows Server 2008 as well. The SharePoint Team Blog has a great write-up on these, including pointers to both upgrade and slipstreamed installations, here.

As it turns out, there are a number of features in SQL Server 2008 that provide significant benefits to SharePoint shops. These include:

  • Database backup compression - save significant time and storage, even with SharePoint's out of the box backup facility
  • Compression in High Availability / Disaster Recovery scenarios - saves time and bandwidth when using SQL Mirroring or Log Shipping
  • Failover Clustering - SQL 2008 improves the failover scenario through simplified installation and maintenance, and by removing the need for a drive letter per SQL Server instance
  • Transparent Data Encryption - Allows the complete encryption of SharePoint content (or other) databases, including backups
  • SQL Performance Studio - Provides rich reports for monitoring SQL usage, capacity planning and troubleshooting
  • Policy Management - Facilitates policy enforcement, such as limiting the size of content databases or configuring Autogrowth as appropriate for SharePoint. This especially helpful when administration is shared by SharePoint admins and DBA's.
  • Central Management - Very helpful when scaling out SQL or running multiple instances, central management allows the DBA to make changes across many databases such as setting policies or shrinking data files after removing content

Database Backup Compression

Enabling backup compression in SQL Server PropertiesSharePoint stores all content and most configuration data in SQL Server, so a large part of backing up SharePoint is backing up the databases. SQL Server 2008 can compress those backups, saving time and storage costs.

We tested this with a content database consisting of about 370,000 text documents in a document library. Without compression, using either SQL Server 2005 or 2008, the backup ran in about 30 seconds (to disk) and created a file 3.6GB in size. With SQL Server 2008 backup compression, the same backup (after a reboot to ensure we weren't using the disk cache) ran in 16 seconds and consumed only 729MB, only 20% of the original size in about half the time!

This can be done as simply as adding "WITH COMPRESSION" to the SQL command, as in

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

What's more interesting, however, is that this can be used for ordinary SharePoint backups as provided in SharePoint Central Administration. To do this, go to the SQL Server 2008 instance and open the property page on the instance itself. On the "Database Settings" page, there is a checkbox labeled "Compress backup". One click later, every backup will be compressed, including the SharePoint out of the box backups (which use SQL Server's backup under the covers, but also save server and IIS settings to provide a comprehensive farm backup.)

For more information on SQL Server 2008 backup compression, see http://technet.microsoft.com/en-us/library/bb964719.aspx.

Compression in High Availability / Disaster Recovery Scenarios

Many SharePoint customers configure SQL Server to use either database mirroring or log shipping in order to provide high availability at the database level. A 2nd SQL server (or servers) receives the logs or mirrors and thus becomes a hot standby in case of a failure of the primary SQL Server(s).  The redundant servers can be placed in a remote data center, providing for disaster recovery in addition to availability.

For a background on these topics, from a SharePoint perspective, see:

All these articles pre-date SQL Server 2008, however. SQL Server 2008 adds log stream compression, to save time and bandwidth, as well as bad page protection to reduce risk of hardware failures. This can reduce network utilization and reduce the failover time window.

For more information on SQL Server 2008 mirroring with log stream compression, see http://msdn.microsoft.com/en-us/library/cc645581.aspx and http://sqlcat.com/technicalnotes/archive/2007/09/17/database-mirroring-log-compression-in-sql-server-2008-improves-throughput.aspx

Failover Clustering

The other primary availability solution for SharePoint databases is clustering. This is the solution of choice for SAN users who enjoy the ability to share physical storage among servers. It is easier to install and manage than in previous versions, and it removes the need for drive letters for every SQL instance. It's also aligned with Windows Server 2008 clustering to allow heterogeneous hardware support as well as the use of IPv6 and DHCP.

For more information see "Getting Started with SQL Server 2008 Failover Clustering" at http://technet.microsoft.com/en-us/library/ms189134.aspx.

Transparent Data Encryption

Every day there are more reasons to secure enterprise data, for regulatory compliance, privacy and to stave off security threats in general. So in many cases it makes sense to encrypt SharePoint content.

There are a few ways to do this:

  • You can encrypt at the volume level using Windows Bitlocker, available in Windows Server 2008. This works well but doesn't protect data if it is moved off of the encrypted volume, as in the case of a backup or log stream.
  • You can encrypt at a file level using NTFS; this has been available since Windows Server 2000. The encryption only applies to specific file(s), so again it won't protect backups, log streams or other copies of the database.
  • You can encrypt at a field level using cell encryption, introduced in SQL Server 2005, however there are schema restrictions as to what can be encrypted. Since SharePoint needs to run on SQL Server 2000, its schema was not designed to take advantage of cell encryption, so it won't help us.
  • SQL Server 2008 introduces Transparent Data Encryption. This encrypts at a database level with no schema restrictions or application awareness, so it's perfect for SharePoint scenarios.

Transparent Data Encryption (TDE) encrypts entire databases, including backups and transaction logs. Thus, offsite copies of confidential data (such as backups) are fully encrypted. If you move files to new storage, there's no need to remember to turn on encryption at the volume or file level because it's already encrypted by SQL Server 2008.

Note that for completeness, enabling TDE on any user database also turns on encryption of TempDB. This is goodness, since it ensures complete encryption of the SharePoint content in question! However the implication is that even if only one in ten content databases are encrypted, all database activity that uses TempDB will incur the encryption overhead. For this reason, it may be advantageous to use a separate SQL Server 2008 instance for TDE databases.

The most important thing to remember when setting up TDE is to BACK UP YOUR SERVICE MASTER KEY AND CERTIFICATE. See "Career Limiting Move, SQL Server 2008 TDE" on Andrew Fryer's blog for details.

For more information on setting up TDE, see

Data Usage CollectionServer Activity HistorySQL Performance Studio

SQL Server 2008 includes a great new tool for managing database performance and resource usage, called SQL Performance Studio. This is very useful for monitoring SQL Server in any application scenario, and SharePoint installations are no exception.

SQL Performance Studio includes 3 out-of-the-box collection sets: Disk Usage Collection Set, Query Activity Collection Set and Server Activity Collection Set. These make it easier to monitor all aspects of database performance. For example, it's possible to monitor content database growth over time to better plan splitting of content databases. The Server Activity Collection Set retains data on CPU usage, memory, disk I/O and network utilization. This can be useful in indentifying peak usage periods, or knowing when to scale out to multiple servers.

For details, see:

Policy Management

It's common in a large enterprise that different people or teams will be responsible for SharePoint administration and Database administration. This can lead to some challenges, as these two must work closely in concert to successfully manage a large scale SharePoint farm.

A specific issue that arises is that the easiest way to set up SharePoint databases is to let SharePoint create them directly. Thus, one or more SharePoint service accounts are given securityadmin and dbcreator SQL server roles so SharePoint administrators can create, say, content databases right from SharePoint Central Administration.

The other alternative is to ask DBA's to create the databases, and to let the SharePoint administrator connect to them rather than create them. This is documented in "Deploy using DBA-created Databases" (http://technet.microsoft.com/en-us/library/cc262869.aspx). While this works, it's complex, and may discourage SharePoint administrators from doing the right thing in terms of managing content databases for growth over time due to the overhead of setting them up.

SQL Server 2008 brings another option to the table: Policy Management. This allows DBA's to grant SharePoint service accounts and administrators the rights to manage some or all of their own databases, while maintaining tight control over database policies. For example, a policy could be created to enforce size limits on content DB's, or to ensure the Autogrowth feature is set to a fixed size rather than a percentage, as recommended for SharePoint content databases.

Policies can be run on demand, on a schedule, or when a change is made. The last of these can be set up to prevent an out-of-policy change, or to simply log it and alert an administrator.

For details on Policy Management, see:

Central Management

A typical whiteboard drawing of a SharePoint farm will generally contain a single SQL Server, or a pair of SQL Servers set up for failover using clustering, SQL mirroring or log shipping. However in practice, it's possible to scale out the database layer onto multiple SQL servers or instances. For example, content databases could be distributed across multiple servers.

Of course this presents a management challenge - the more SQL Servers, the more management. If only there were a way to centrally manage multiple server instances, and even work with queries or policies across these servers.

This is exactly the situation addressed by SQL Server 2008 Central Management. Registered servers, or groups of servers, can be queried simultaneously. In addition, policies can be managed and checked across servers using this facility. For example, a DBA could check for policy compliance across all servers from a single screen, with a single action.

For details, see:

Resources

 

General SharePoint/DB Resources

Other takes on SharePoint with SQL Server 2008

SQL Server 2008 Availability Topics

Transparent Data Encryption

SQL Performance Studio

SQL Server Central Management

(Cross-posted to the MTC Blog)

This posting is provided "AS IS" with no warranties, and confers no rights. Thank you for reading it!

Blog - Comment List MSDN TechNet
  • Loading...
Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post