SharePoint Technologies

Microsoft SQL Server 2008 R2 Enterprise Edition and Microsoft SharePoint Server 2010
Better Together
Contents

Introduction.. 4
Scale to Meet the Most Demanding Workloads.. 4
Processor Scale. 4
Memory Scale. 4
Hot-Add Hardware Support 5
Resource Governor and Performance Data Collector 5
Table Partitioning Improvements. 5
Meet Complex Compliance Requirements.. 6
Transparent Data Encryption. 6
SQL Server Audit 6
Reduce Cost with Storage Efficiencies.. 7
Backup Compression. 7
Remote Blob Storage. 7
Enable Effective Business Continuity Management.. 8
Database Mirroring. 8
Failover Clustering. 9
Log Shipping. 9
Fast Recovery. 9
Database Snapshots. 10
Develop Rich Business Insight Capabilities.. 11
SQL Server Reporting Services. 11
PowerPivot 11
Summary.. 11
Additional Resources.. 12
Feedback.. 12


Introduction
The adoption of Microsoft® SharePoint® Server 2010 can grow exponentially across an organization quickly gaining momentum and taking root as a critical part of an organization’s ecosystem. To help protect data, ensure availability, and keep costs down, we recommend that customers choose Microsoft® SQL Server® 2008 R2 Enterprise Edition during initial deployment.
Choosing an edition of SQL Server 2008 R2 is an important step when planning your SharePoint Server 2010 deployment, from understanding which features are important to your deployment needs, to enhancing security, or designing topologies that enable seamless scale.
To understand which features are right for your deployment you should first understand and prioritize your organization's underlying business requirements. For example, you may need to support a rich media management solution or government regulations that require you to deploy a high level of data protection to meet strict compliance rules.. In either scenario, SQL Server 2008 R2 Enterprise edition offers dimensions of scale and a rich feature set to support a variety of workloads.
Note: Some of the features discussed in this paper span SQL Server 2008 R2 and SQL Server 2008 editions and are annotated where appropriate.
Scale to Meet the Most Demanding Workloads
SQL Server 2008 R2 Enterprise edition enables several dimensions on which it can scale, enabling even the most demanding SharePoint Server 2010 deployments. For example, rapidly growing Enterprise Content Management (ECM) or Web Content Management (WCM) workloads may often require compute resources beyond those associated with traditional collaboration scenarios. SQL Server 2008 R2 Enterprise can support this scenario through a number of improvements, by enabling greater processor scale, the ability to address more physical memory on 64-bit hardware, and hot-add hardware support.
Processor Scale
When used as an ECM platform, SharePoint Server 2010 can become resource intensive and place heavy demands on the system as transactions are processed. The improved CPU support in SQL Server 2008 R2 Enterprise allows it to scale CPUs up to eight processors with no limitation on the number of cores, to more efficiently sustain and balance load under these and other demanding workload conditions.
Memory Scale
Physical memory scale and support is important when hosting a large number of sizeable content databases, particularly where performing upgrade and patching operations that are often resource intensive and depend heavily on both physical memory and TempDb. SQL Server 2008 R2 Enterprise can support up to 2 terabytes of physical memory to address today’s most demanding SharePoint deployments. High volume records management scenarios can be further supported by SQL Server 2008 R2 Datacenter Edition in which physical memory is limited only by the operating system on which SQL Server resides.
Hot-Add Hardware Support
When considering virtualized topologies, you can further benefit from SQL Server 2008 R2 Enterprise with hot-add hardware support. Hot-add hardware support enables administrators to dynamically add CPUs and physical memory to a live system, without having to stop database services. This support enables SQL Server 2008 R2 to scale seamlessly and minimize downtime when additional resources are required to support ever changing business requirements. As SharePoint Server 2010 becomes a mission critical application for both ECM and WCM workloads, scheduling downtime to add hardware or scale up can be difficult.
Resource Governor and Performance Data Collector
To more efficiently monitor and control the consumption of resources, SQL Server 2008 Enterprise Edition provides the Resource Governor. It enables administrators to gain control of the workload environment by defining workload prioritization, more granularly control inefficient queries, and allocate resources to the workloads where they are most required. SQL Server 2008 provides the Performance Data Collector, which enables an administrator to gather performance data from performance counters, dynamic management views, SQL Trace, and other sources.
To learn more about SQL Server 2008 performance and scalability, see SQL Server: Performance and Scalability (http://www.microsoft.com/sqlserver/2008/en/us/performance-scale.aspx).
Table Partitioning Improvements
The new Usage and Health Data Collection logging and Web Analytics reporting databases contain large tables. When SQL Server Enterprise Edition is installed, the large tables are partitioned onto separate file groups in order to improve performance and query efficiency. SQL Server 2008 improved how partitions are queried, so that you can expect fast and consistent query execution, no matter how many partitions are queried. SQL Server 2008 also provides the ability to escalate to a partition lock, which minimizes the impact of lock escalation without affecting queries on other partitions, thereby reducing downtime, and further optimizing performance.
Note: Table partitioning is only supported for the Web Analytics reporting databases.
To learn more about partitioning in SQL Server 2008, see Partitioned Table and Index Concepts (http://msdn.microsoft.com/en-us/library/ms190787.aspx).
To learn more about performance and scale in SQL Server 2008, see SQL Server: Performance and Scalability (http://www.microsoft.com/sqlserver/2008/en/us/performance-scale.aspx).
Meet Complex Compliance Requirements
Whether required to meet U.S. Payment Card Industry Data Security Standards (PCI DSS), U.S. Health and Human Services HIPAA, or Federal Drug Administration 21 CFR 11 regulatory codes with your SharePoint Server 2010 deployment, SQL Server 2008 R2 Enterprise is designed with the highest levels of security in mind through both a robust authentication and authorization model and improvements in data encryption.
Transparent Data Encryption
Transparent Data Encryption (TDE) is a feature added in SQL Server 2008 Enterprise that performs real time I/O encryption and decryption of data and log files without requiring unsupported modification of the underlying table schema or increasing the size of the database—that means no changes to SharePoint to enable TDE. Transparent Data Encryption offers the next level of data protection beyond securing the communication channel, by protecting the physical media in the event it is lost or stolen. Transparent Data Encryption is performed at the page level—encryption is performed before the pages are written to disk, and decryption is performed when pages are read into memory. To learn more about Transparent Data Encryption, see Understanding Transparent Data Encryption (TDE) (http://msdn.microsoft.com/en-us/library/bb934049.aspx) in SQL Server Books Online.
Transparent Data Encryption is available in SQL Server 2008 Enterprise Edition and SQL Server 2008 R2 Enterprise Edition and Datacenter Editions.
SQL Server Audit
In many scenarios encryption is often combined with auditing to meet government or standards requirements such as Payment Card Industry Data Security Standards (PCI DSS). SQL Server 2008 R2 Enterprise Edition addresses these scenarios through SQL Server Audit, which enables tracking and logging events that occur on the system, for example detecting changes or modifications to database objects/stored procedures, surfacing changes to server configuration settings, or detecting changes to audit configuration settings.
To learn more about SQL Server Audit, see Understanding SQL Server Audit (http://msdn.microsoft.com/en-us/library/cc280386.aspx).
SQL Audit is available in SQL Server 2008 Enterprise Edition and SQL Server 2008 R2 Enterprise and Datacenter Editions.
Reduce Cost with Storage Efficiencies
Hosting vast quantities of Microsoft SharePoint Server 2010 data can become costly over time as data continues to grow exponentially. These costs can rise exponentially due to data retention policies, or when digital media assets comprise the information corpus. SQL Server 2008 R2 Enterprise Edition provides a broad array of features to address storage concerns and minimize the data storage footprint in a cost effective manner with little operational and administrative overhead.
Backup Compression
Backup Compression can help reduce the amount of I/O required in writing backups to the backup device, significant improvements can be expected in the speed of the backup, as well as providing greater storage efficiency for your SharePoint backup sets and as a result reducing the overall storage costs associated with data protection.
Consider the following common scenario; an organization is required to adhere to a recovery service level agreement (SLA) which requires that data lost or removed from a system can be requested to be restored up to 14 days following the initial date of deletion or loss, and within 24 hours of the request. In order to effectively support such an SLA, the organization will typically need to maintain 21 days of backup data on disk, in most cases, provided through 3 full and 18 differential backups. Assuming the total amount of data hosted on the SQL Server instance is 5 terabytes and the compression ratio achieved is 3:1, the amount of disk space required in this scenario becomes clear. Backup compression can provide a solution to mitigate the impact of the both short term and long term data retention and allow the organization to meet the desired SLA while reducing the overall storage costs associated.
To learn more about Backup Compression in SQL Server 2008, see Backup Compression (SQL Server) (http://technet.microsoft.com/en-us/library/bb964719.aspx).
Backup Compression is available in SQL Server 2008 Enterprise Edition and SQL Server 2008 R2 Standard, Enterprise, and Datacenter Editions.
Remote Blob Storage
Remote Blob Storage integration with SharePoint Server 2010 in SQL Server 2008 R2 Enterprise enables an administrator to externalize SharePoint binary large object (BLOB) data by hosting it on less expensive, commodity hardware solutions and managing it with SQL Server through the same data management techniques they use today. Remote Blob Storage can be an integral component in the most stringent compliance scenarios by enabling third-party technologies to implement solutions such as expunge on the server(s) hosting the externalized BLOBs. For additional information on Remote Blob Storage availability, see Table 1 below.
Table 1 Remote Blob Storage Licensing Matrix
Configuration
SQL SKU for SharePoint
SQL SKU for BLOB Store
SQL RBS + SQL FILESTREAM Provider storing BLOBs locally in the same content DB
ALL

SQL RBS + SQL FILESTREAM provider storing BLOBs in a remote DB
SQL Server 2008 Enterprise Edition or above.
SQL Server 2008 Standard Edition or above.
SQL RBS + Third party provider
SQL Server 2008 Enterprise Edition or above.
N/A
Enable Effective Business Continuity Management
Business Continuity Management is a combination of high availability and disaster recovery. High availability ensures a certain absolute degree of operational continuity in the event one or more components fail in an isolated location. Availability requirements are defined by Operating and Service Level Agreements. Disaster recovery ensures a certain absolute degree of operational continuity in the event that all systems fail in one or more locations. Disaster recovery requirements are defined by Operating Level Agreements, Recovery Point, and Recovery Time Objectives.
SQL Server 2008 R2 provides a number of native capabilities to enable the design and deployment of a highly available SharePoint Server 2010 deployment including database mirroring, failover clustering, and log shipping.
Database Mirroring
New in SharePoint Server 2010 is native support for database mirroring across its database architecture. Database mirroring provides increased data protection with complete or almost complete redundancy of the data and increased availability of a database by quickly bringing a standby copy of the database online. Mirroring can improve the availability of the production database during upgrades by enabling sequential upgrade of the instances of SQL Server that are participating in the mirroring session.
First introduced in SQL Server 2005, database mirroring in SQL Server 2008 R2 Enterprise has been improved by reducing the overall recovery time in a failover event with log performance enhancements that include stream of data compression, write-ahead on the mirrored server, improved log caching, and page read-ahead during the undo phase. These improvements in performance are complimented with improvements in resiliency through the addition of Torn Page Repair which provides automatic recovery from corrupted pages where the unreadable page is automatically replaced by a copy if the request is successful.
To learn more about Database Mirroring in SQL Server 2008 R2, see Database Mirroring Overview (http://technet.microsoft.com/en-us/library/ms189852.aspx).
To learn more about Database Mirroring performance available enhancements, see Availability Enhancements (Database Engine) (http://msdn.microsoft.com/en-us/library/cc645581.aspx).
Database mirroring is available in SQL Server 2008 Standard (Single Threaded, Safety Full Only) Enterprise Edition, and SQL Server 2008 R2 Standard (Single Threaded, Safety Full Only), Enterprise and Datacenter Editions.
Failover Clustering
Where database mirroring does not meet the needs for high availability or where an instance is shared across services, Microsoft SharePoint Server 2010 continues to provide support for failover clustering. Failover clustering enables seamless failover capabilities in the event of a CPU, memory, or other non-storage hardware failure by sharing disk access between nodes and restarting SQL Server on a working node in the event of a failure.
To learn more about Failover clustering in SQL Server 2008 R2, see Getting Started with SQL Server 2008 Failover Clustering (http://msdn.microsoft.com/en-us/library/ms189134.aspx).
Failover clustering is available in SQL Server 2008 Standard and Enterprise Editions, and SQL Server 2008 R2 Standard, Enterprise, and Datacenter Editions.
Log Shipping
Log shipping enables administrators to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The second instances provide a standby copy of the data in the event the primary copy is compromised either through corruption, administrative error, or disaster. Log shipping can be combined seamlessly with database mirroring or failover clustering to provide a complete business continuity management solution.
To learn more about Log shipping, see Log Shipping Overview (http://msdn.microsoft.com/en-us/library/ms187103.aspx).
Fast Recovery
Fast Recovery enables optimizations for recovery after restoring from a backup.
To learn more about Fast Recovery, see Understanding Recovery Performance in SQL Server (http://msdn.microsoft.com/en-us/library/ms189262.aspx).
Log shipping is available to SQL Server 2008 Web, Workgroup, Standard, and Enterprise Editions and SQL Server 2008 R2 Web, Workgroup, Standard, Enterprise, and Datacenter Editions. Fast Recovery is available to SQL Server 2008 Enterprise Edition and SQL Server 2008 R2 Enterprise and Datacenter Editions.
Database Snapshots
Database snapshots are read-only, static views of a database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. Snapshots can be used in the event of a user error on a source database, because an administrator can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
A new feature of content deployment that was added for SharePoint Server 2010 is the option to use SQL Server 2008 Enterprise database snapshots during export. If the database snapshots option is enabled, a snapshot of the source content database is created before the export phase of the content deployment job starts. The content deployment job then uses the database snapshot to perform the export, instead of exporting directly from the live content database. After the export has successfully completed, the snapshot is deleted. By using the database snapshot option, you eliminate any potential problems with users editing content in the content database while a content deployment job is running.
If you are using SQL Server Enterprise, you can also choose to use database snapshots when backing up a site collection or exporting a site by calling the appropriate Windows PowerShell® cmdlets with the -UseSqlSnapshot parameter. The snapshot is automatically removed when the backup or export operation is finished.
Database snapshots can also be used to maintain historical data for report generation by using points in time to capture historical data. When using database mirroring as a high availability solution, snapshots can optionally be taken against the mirror data to offload reporting and free up resources on the principal. Additionally database snapshots provide added resiliency to protect the environment from administrator error, user error, protecting the integrity of databases during routine update and patching, or using snapshots as a test database that can be called upon when running test protocols which require the same data to be present for each test scenario.
To learn more about database snapshots, see Database Snapshots (http://msdn.microsoft.com/en-us/library/ms175158.aspx).
Database Snapshots are available in SQL Server 2008 Enterprise Edition and SQL Server 2008 R2 Enterprise and Datacenter Editions.
Develop Rich Business Insight Capabilities
Whether aggregating data from multiple sources or creating rich dashboards, SQL Server 2008 R2 Enterprise provides the tools and resources and delivers rich business intelligence solutions with familiar tools.
SQL Server Reporting Services
SharePoint Server 2010 and SQL Server 2008 R2 Enterprise provide close business intelligence capabilities integration through SQL Server Reporting Services. Using SQL Server Reporting Services, administrators can configure reporting servers to enable real-time access to information and control who has access to that information. End users can benefit from this integration by publishing SQL Server reports directly to Document Libraries or by optionally embedding reports in pages hosted on one or more sites in a Microsoft SharePoint Server 2010 deployment.
To learn more about Reporting Services in SQL Server 2008 see SQL Server Reporting Services (http://msdn.microsoft.com/en-us/library/ms159106.aspx).
PowerPivot
Microsoft PowerPivot for SharePoint is a data analysis add-in that provides users the ability to create compelling self-service BI solutions by facilitating sharing and collaboration on user-generated BI solutions in a Microsoft SharePoint Server 2010 environment. It also enables IT organizations to increase operational efficiencies through SQL Server 2008 R2-based management tools.
PowerPivot for SharePoint gives users the ability to analyze massive quantities of data, and its seamless integration with Microsoft SharePoint Server 2010 helps IT departments monitor and manage how users collaborate.
To learn more about PowerPivot, see SQL Server PowerPivot for SharePoint: Deliver and Succeed with BI (http://www.microsoft.com/sqlserver/2008/en/us/powerpivot.aspx).
Summary
In planning your SharePoint Server 2010 deployment you should carefully evaluate your SharePoint deployment needs and the features available in SQL Server 2008 R2 Enterprise for the following scenarios:
· Web Content Management scenarios where content deployment is used and in which you plan to rely on database snapshots.
· Regulated environments that require a high level of data security and compliance.
· Environments that support digital media archives that rely heavily on available storage.
· Large collaboration and Enterprise Content Management environments where write activity will exceed read activity.
· Mission-critical scenarios where high availability, disaster recovery, and minimal downtime are required.
· BI-driven deployments where data is made available to thousands of users.
Additional Resources
Features Supported by the Editions of SQL Server 2008 R2 (http://msdn.microsoft.com/en-us/library/cc645993(SQL.105).aspx)

Microsoft SQL Server 2008 Edition Feature Comparisons
(http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx)
Feedback
Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how you would rate this paper and why you have given it this rating. For example:
· Are you rating it high because it has good examples, excellent screenshots, clear writing, or another reason?
· Are you rating it low because it has poor examples, fuzzy screenshots, or unclear writing?
This feedback will help us improve the quality of white papers we release.
Send feedback (mailto:itspdocs@microsoft.com).