SharePoint 2010 Better Together with SQL 2008 R2 Database Enhancements Top 10

I’ve been thinking a lot about the SQL 2008 R2 and SharePoint 2010 better together story and wanted to capture some of what’s been floating around in my head.  I haven’t seen enough content and blogs on this stuff and hope that this will help spur more conversations on the topic.  Mike Watson has put out a couple of well put together posts on SQL 2008 and SharePoint 2010 in his storage considerations post and on SharePoint 2010 high availability improvements.  I also put some thoughts on why SQL 2008 for SharePoint from a while back.  With the product team’s announcement of requirements of 64 bit SQL and SQL 2005 and SQL 2008, many are asking questions.  Having spent time with SQL 2008, I do see it as a no brainer to skip 2005 or to simply go with the gold and do the right thing from the beginning.  A lot of the high availability (mirroring) and security features (transparent data encryption) are in the enterprise version, so be careful to do the right thing.  Ironically I did run into 10 Things DBAs will love about SharePoint 2010 which only enriches this story which focuses on the SQL side even more.  I have been using SQL 2008 R2 on all of my SharePoint 2010 installs.  Love the new features “Top 10 Features of SQL 2008 R2”

1. Mirroring Aware – Yeah, right in the SharePoint 2010 box you get the ability for SharePoint to understand primary and secondary content databases.  Configure it right in Central Admin. Woohoo!  Downside, SharePoint doesn’t setup the mirrored pair, and it doesn’t set this up automatically for new databases.  It requires you or your SQL buddies to setup the mirroring and then tell SharePoint about it.  Definitely a step in the right direction.  The fact that the Indexing support is redundant now makes the full story truly highly available.  That difficulty around what to do with the SSP for disasters has been cleaned up a lot making the story better for SQL and SharePoint people alike.

2. SharePoint Patching greatly increases HA for SQL – You wouldn’t think patching would have any dependencies on SQL, but the great news here is the ability to upgrade the binaries across your SharePoint Servers and not make the entire farm unavailable.  Your binaries can simply be out of sync with your databases for a limited time, essentially you can patch for security, and then update your databases during your scheduled maintenance window.  This allows you to keep your SLAs and prevent patching from causing unnecessary downtime a big win with SharePoint 2010. Mike talks about the patching in his HA for 2010 post. There was even a FULL session at SPC09 (SharePoint Conference) on patching!  Also in his post pay attention to what’s happened with the Index service due to the enhancements with the FAST search integration and architectural changes.  HUGE.

3. SQL content database structure – The structure in the content database has been redesigned.  The underlying way it stores the blobs has been enhanced.  I’m sure more info will come out, but this was a major step in the way of scale and reducing blocking.  By simply upgrading to SQL 2008 a lot of the blocking went a way due to the nature of the way SQL 2005 did it’s row locking.  64bit also helps, and since that is required we see better scalability for both numbers of databases and size.  Instead of having all the blobs in one single filestream table it is now better distributed which significantly increases the scalability of the databases as well as provides the ability to split the structure.  Don’t think this means a site collection can be split across databases.  Not yet. SharePoint Sandbox goes into more of the structural db changes.

4. SQL content database file group support – With the structural changes in the way content is stored in the database.  (That’s right your data is still in the database by default.)  You can now setup file groups on your content database in extreme cases.  The average environment won’t care, but they will still take advantage of the structural improvements.  Those in the really extremely large case, such as the more than 200-300GB you will now be able to split your content database across LUNs, or volumes to both increase performance and to simply grow the environment as it needs to.  (Still not a best practice, but with the extremely large, you’ll find TB repositories with the new scaling features.)

5. More Specialized databases – Where did all these databases come from?  Before you get upset, it’s actually a good thing, not only for Microsoft, but for you.  With the tens of new databases that we have we can now focus the write intensive databases on their own LUNs and put the read intensive databases on theirs.  Why not have Microsoft do the right thing by default?  They don’t have a clue whether your environment is a collab, social, portal, or BI solution.  You will know best if your app will focus on search, or as an application, and by having databases be specialized through the new service apps you will be able to do the right thing with the right databases.  Don’t provision what you don’t need, and allow your applications to become specialized and do the right thing from a disk perspective.  The profile database even has options for synchronization a much needed enhancement.  It isn’t managed on the SQL side, so don’t worry about that.  There are architectural decisions about where to put what service and consuming those services for some services is even possible across the WAN!  That’s pretty huge.

6. Remote blob storage enhancements – While the scenario around migrating your WIDE (Windows Internal Database Engine) to the SQL Express with 4 GB limit sounds kind of wierd and scary in SharePoint 2010, right out of the box with in place upgrade Microsoft tries to do the right thing and move your blobs into remote storage with the configuration and lists in the database and the data outside the database.  The enhancements around RBS and EBS in SharePoint 2010 still require an RBS provider.  There is support for transparent Remote blob storage.  This blog post on externalizing SharePoint content in SharePoint 2010 captures the meat of it in a recap of the SharePoint Conference session by MS people from the SQL advisory team.  It has some great detail around how it has been enhanced and some great considerations.

Enhancements in SQL 2008 R2 handles blobs way better, and enhancements in SharePoint 2010 make using remote blob storage with vendors a more manageable proposition as well.  I highly caution people against using RBS to simply save on management costs.  If you’re going to do it, do it for disk storage costs.  Why?  Because it isn’t easier to manage outside of SQL, it’s harder and more complex.  Really!  There is a free RBS filestream provider from MS, but I refer to above with use with SharePoint 2010 (Not in the box), but don’t go down that route unless you really know what you’re doing.  Again I refer to the post above.  Not many have yet gone this route without support from a vendor.  Caution.

7. Throttling – List and performance throttling reduces blocking in the database.  While these features are exposed to the SharePoint administrator… conversations around what blocking should look like and where to set the new thresholds is a good conversation to have.  The throttling on lists should definitely mean we’ve found a solution to the blocking list.  Large site deletes are another matter entirely, but on the surface, the common scenarios are now much more manageable. I’ve put together a number of blogs on the throttling.

8. External Lists – This is definitely near the top of my list.  Being able to pull in a SQL table or even exposing relational databases and put together a story of business connectivity services exposed as a SharePoint list and taking that data offline in SharePoint!  Come on this is a HUGE SQL story for usability and accessibility and extensibility.  Loved the contacts BCS demos (see more on Channel 9) I’ve seen on SQL database editing with full CRUD (Create Read Update Delete) operations in SharePoint, as well as editing them in Outlook and taking them offline in Outlook in 2010 or in SharePoint workspace.  The ability to create these external connections doesn’t take an expensive tool and can easily be created in free SharePoint designer 2010 or Visual Studio 2010.  Add on the power of SQL 2008 R2 and powerpivot and now you have the most scalable lists of any app out there with serious performance improvements.

9. Cleaner SQL Backup and Recovery Story – While the backup story of the SSP gets cleaner with having a more robust backup for SQL databases right out of the box and a less messy Index and Search backup, the configuration based backup.  You don’t need to do anything special to get config based backup.  The configuration of your services is stored in the backup.  So when you get ready to do your restore it will actually ask you.  It will ask Do you want A) same or B) new configuration during restore.  Pretty wild.  Definitely simplifies the story.  More info on the SharePoint Solutions Help blog on SharePoint 2010 Configuration Restore capability.

10.Resource Governor for SQL & SharePoint – The new schema of making databases specialized lends itself more to the idea of running resource governor to allow you to be smarter about resources.  The new logging and usage databases are good candidates.  On the SharePoint side it’s the resource governor of managing resources from a code and performance perspective which will ultimately save your front end web servers and save your backend, literally!  Refer to the throttling link above on more resource management features as well.