SQL Server 2012 Brings New Features and Capabilities for SharePoint 2010

Introduction
If you have SharePoint customer or architect and you really should be looking at the new SQL 2012 release. As we all know SharePoint success is highly contingent on SQL Server. You need a strong SQL Server deployment to ensure there is good performance, high availability, back up/recovery, etc. Additionally Microsoft delivers their Business Intelligence (BI) stack through SharePoint and there have been several new features and capabilities added there as well.
For SQL 2012 there are three areas where there have been major capability additions:

1. Mission Critical Confidence – This is the capability to more easily deliver high-availability with lower total cost of ownership.
2. Breakthrough Insight – New and expanded capabilities for Business Intelligence.
3. Cloud on Your Terms – Additional capabilities to create SQL databases in either the private or public cloud (Office 365).

In this blog I am going to cover some of the new SQL 2010 capabilities specifically focusing on how they can improve your SharePoint 2010 implementation. Please note that there are a lot of new capabilities for SQL Server 2012 which I have not covered such as data warehousing, resource management, full text searching, auditing, Big Data (Hadoop), etc. Here is a good reference to quickly spin up on all the new capabilities (“What's New in SQL Server 2012 Whitepaper” located here).
Mission Critical Confidence
AlwaysOn
First and foremost is the new high-availability solution in SQL Server 2012 called AlwaysOn. One of the first and foremost challenges that organizations face with deploying SharePoint 2010 is providing a solution architecture that will meet the SLA’s of their business users. Business processes, technical processes and Governance need to be put in place to ensure that SharePoint 2010 will be up as much as possible.
In the past with SQL Server 2008 R2, you employ such solutions as clustering, mirroring, log shipping and replication (my previous blog on this topic). However this could require a lot of planning, configuration, and management. With SQL 2012 AlwaysOn, new configuration wizards and tuning tools are now provided that makes set-up and configuration of High-Availability extremely simple.
The concept of Availability Groups have been added which specifically makes configuration of Database Mirroring easier. Availability Groups are a logical of databases that failover together. Through the configuration wizard, you can determine if you need such things as automatic or manual failover, set-up of primary and multiple secondary instances, synchronous or asynchronous data movement, etc.
Availability Groups remove the need for shared disk storage (SAN or NAS) for deployment of a failover cluster instance. Note that AlwaysOn Failover Cluster Instances support multiple-site clustering across subnets which subsequently enables cross datacenter failover.
This feature is very useful when setting up your SharePoint 2010 (or 2007) farm because High Availability is one of the most paramount tasks that are needed when setting up a mission critical SharePoint environment.
Recovery Advisor
Database Recovery Advisor provides many new features and capability for the support of back-up and restore of databases through SQL Server Management Studio. The new Recovery Advisor streamlines the back-up process. One such solution is a visual timeline that provides the backup history and all the available points in which you can restore from.
There is also new capability called Split File Backup which allows you to split a backup into multiple files. This allows for quicker backup and restores because they can be written and restored across disks running in parallel.
This is very helpful for improving the amount of time to work with large SharePoint 2010 (or 2007) Content Databases that have grown significantly.
Breakthrough Insight
As you probably already know SharePoint 2010 is where Microsoft’s Business Intelligence (BI) stack is delivered. This is a combination of solutions such as Excel Services, PerformancePoint, Visio Services, Chart Web Parts (Dundas), PowerPivot, and SQL Reporting Services (SSRS). With the release of SQL 2012 a new solution called Power View is now provided, PowerPivot can now be done on SharePoint 2010 server side and there is significantly enhanced integration with SSRS.
Power View
Power View is a new highly interactive data exploration and reporting tool that allows business users to visually explore data, in an ad-hoc fashion. End users can create the reports/dashboards very quickly, create shapes/graphs with clicks, create animation, highlight capabilities based on rules, drill down relationships and performs very well with large datasets. The design environment is very similar to Office. It can be published through SharePoint 2010 as shown in the diagram below.
Note that Power View requires SQL Reporting Services (SSRS) to be integrated with SharePoint 2010 and there be an instance of SQL Server 2012 Analysis Services (SSAS) or PowerPivot be available.
PowerPivot
Up to this point PowerPivot was a solution that was available through Excel client only via an Add-in. This is a really powerful, end user friendly capability that allows for data analytics. There are actually several new capabilities that have been introduced that I recommend you read up on (in references below).
Specifically to SharePoint 2010, PowerPivot for SharePoint is available as an add-in and can run server side in conjunction with Excel Services. This now allows end users the ability to publish PowerPivot reports through the browser allowing end users easy access to this data.

As well, in SharePoint 2010 Central Administration there is a new PowerPivot Management Dashboard that provides several reports on performance of reports.

SQL Reporting Services (SSRS)
Personally the enhancements to SSRS are very exciting to me as I have had to do configuration of SSRS with SharePoint 2007 and 2010 in the past in production environments. If you have done it before, you may recall it was tedious task of configuration the SSRS Configuration Wizard and getting Kerberos Authentication set-up correctly. Now with SQL Server 2012, configuration of SSRS with SharePoint 2010 is completely done through Central Administration. Additionally there is a new service in SharePoint 2010 that runs SSRS, it supports WCF/Claims authentication, integrated ULS logging, built in load balancing across SharePoint servers, report performance improvements and there are PowerShell commands for management.

Additionally there is a SSRS alerting capability that allows end users to subscribe to alerts that are associated to SSRS report. Users can create conditions for any report, and when they are met a notification will be sent to them.

One more last addition change is you may know that SSRS reports can generate Word or Excel Documents. Up to now, the file formats generated were either .doc or .xls which means they are not Open XML document renditions. With SQL Server 2012 and SSRS now .docx and .xlsx file types will now be generated.
Cloud on Your Terms
With SQL Server 2012, there are a bunch of new capabilities to support even tighter integration with SQL Azure. You will have a unified database development experience between SQL 2012 on-prem and SQL Server. The reason why I bring this up is because it is very common that when creating a solution inside SharePoint, you will have complex data management requirements that can be better supported with a SQL Server database instead of a SharePoint list.
Now we have the ability to more quickly and efficiently create custom database in SQL Azure which can be utilized with SharePoint 2010. Plus this is really good for working with Office 365 SharePoint Online because we can make a cloud to cloud connection to work with advanced data structures.
References

• SQL Server 2012 Technical Whitepapers - http://www.microsoft.com/sqlserver/en/us/learning-center/resources.aspx. Specifically read “What’s New in SQL Server 2012 Whitepaper” and “SQL Server 2012 What’s New Breakthrough Insight Whitepaper”.
• SharePoint 2010 SP1 – Supports project Code Name “Denali” which is now SQL Server 2012 - http://support.microsoft.com/kb/2460045
• SQL Server 2008 High Availability with SharePoint 2010 – Previous article that I wrote - http://www.astaticstate.com/2010/12/sharepoint-2010-high-availability-with.html.

SharePoint 2010 Business Continuity Management

Business Continuity Management

Microsoft uses the term business continuity management in terms of new features that they are providing to support your ability to manage SharePoint backup/restore, high availability and disaster recovery scenarios. On the topic of backup and restored there are several new improvements that you should take note of:

• Farm backup is available in Central Admin and will do backups of each service and content database. Microsoft SharePoint Server 2010 supports backup of databases up to 600GB and supports both Full and Differential. However System Center Data Protection Manager 2007 supports up to 5TB with Full, Differential and Incremental.
  
• New Configuration only backup - configuration database settings can now be written into an XML file. This provides the ability to create a new farm with the same topology of the original farm.
  
• Content database backups that will start a SQL Server backup for each individual content database selected. You then have the flexibility to restore the content database with a new name or overwrite an existing.
  
• Search backups are little more involved (there are two phases) to support both search indexes and the search database. It is important that the index is preserved because rebuilding them from scratch is an expensive operation. You have to understand a little bit about how searching and crawling work but at a high level, crawl services will search for new content and that new content will be merged into the index which users search against. In the first phase of the search back up, the merging of indexes will be stopped so indexes can be copied from the query servers. Crawling for new content will still continue. As well all search databases will be backed up. In the second phase, crawls will be paused and all new indexes that were created while Phase 1 was going will be backed up. Then crawling and merging will be restarted. The reason why this is done to minimize the downtime of crawling for new content. When restoring, indexes are first restored, then the search databases and finally indexes from the second phase and merged into the index backed up in the first phase. Search back ups are only supported through SharePoint Admin.
  
• Service applications in general have their own back and restore routines. The search one we just discussed is special for search. SharePoint will launch SQL Server backups of any associated database to the service application along with the service configuration database. Again this is specific to SharePoint Admin and not supported by SCDP Manager 2007.
  
• Backup and restore of Site Collections have changed such that they are more granular and support back/restore of data that is unattached to a content database. Remember that a content database may have one-to-many site collections stored within it. SharePoint 2010 now supports backing up a collection by extracting data out of the content database instance using Select statements and then saving data to file. It is even possible to have SharePoint create a snapshot of the content database and then create the site collection back up from the snapshot to reduce performance against the live content database. The now unattached backup (.bak file) and be restored into any content database you may have.
  
• Ability to back up both site and lists from central admin is now supported with SharePoint 2010. Similar to the backup/restore of site collections, sites and lists are extracted out using SQL Select statements and stored on file (.cmp file). They can then be restored into any content database you may have.
  
• Improved site deletion logic that ensures that minimizes site blocking by using timer jobs that will perform this operation in the background and do chunk based deletion.
  
• There are new improvements associated to high availability that you should be aware of for SharePoint 2010. The most noticeable one is a new feature support to support SQL Server Database Mirroring for all databases. All databases configured within SharePoint can now take advantage of this. In central admin, it is now as simple is identifying the Failover Server name when for example creating a new content database.
  

Business Continuity Management Thoughts

All of these topics usually came up when I talked with clients about SharePoint 2007. Many of clients did not put enough forethought into how they were going to support backup/recovery, disaster recovery and high availability. I think in many cases well intended clients missed the fact that there is a strong correlation between the logical design of SharePoint and how data should be managed for backup/restore. Typically backup/restored was pushed off to network administrators who would just spin up new site collections and content databases without any thought to context to what was being done with SharePoint. They would not know who is accessing what, how often, why and from where. Things like security and service level agreements were not considered because some site collections are more important than others. The net result was extremely bloated content databases and company has no agility to react to change or to sustain operations without have to buy a third party tool.

Now with SharePoint 2010 we have some better features that allow us better manage data. It is really exciting to see the granularity that has been introduced and the new unattached database feature is really interesting. Plus as well, the new SharePoint PowerShell really enables administrators to write robust scripts to support backup/recovery. I still think the third party market for very rich backup/recovery tools.

My only point is that I do not like seeing new powerful, robust tools being used as a crutch for not thinking ahead. This all goes towards having strong SharePoint governance and procedures in place to understand how SharePoint data should be managed to ensure that service level agreements are met with the business. These means that a strategy should be put in place for tiered support and then have criteria that you measure all new solutions against. For instance, if a group needs a new document center along with some new custom web parts and workflows to manage that data, what is the plan for how the data will be backed up and restored based on business operating scenarios? The architect, designer, or developer creating SharePoint solutions must be able to explain how their solution fits in or affects continuity of operations for other deployed solutions. If they cannot, you have some long term problems.