I did a live webcast the other day for SharePointVCX talking about SharePoint 2010 and how it affects your SQL and Storage environment. The webcast was recorded and is now available on demand if you would like to watch it. (I believe you need to register to watch)

If you’re like me and hate watching videos not created by professional video people I’ll do you a favor and summarize my discussion here:

• Big! - SharePoint Server 2010 is the biggest version of itself yet. It’s unbelievable the number of things they’ve rolled into SharePoint. In 2001 we had a very simple portal with some bad search and team sites. 2003 added mysites, people, and better search. 2007 saw ECM, Excel, BDC, InfoPath among others. Now in SharePoint 2010, Microsoft is not only keeping with tradition, but also capitalizing on the success of SharePoint by building all of its office and business applications into the SharePoint platform and as long as SharePoint doesn’t implode I can guarantee this tradition will continue in V.Next and beyond. The bottom-line is that all this new functionality comes at a cost which is mostly additional overhead on SharePoint’s platform elements including SQL and storage. Make sure to plan for this overhead with additional capacity.
• Flexible - You may be worried about what SharePoint 2010 will do to your SQL and storage after reading the above bullet point (after all that was the intent), but fear not because with all this new functionality comes with additional flexibility and control. Microsoft saw the limitations of its previous service infrastructure and designed the new service infrastructure with scalability and flexibility in mind. The new service infrastructure called Shared Service Applications or service apps for short are very flexible. Let’s not go into the details of service apps as they aren’t necessary for this article and are already drafted in another blog post. The key point about service apps when it comes to SQL and storage is that the flexibility they afford allows for advanced SQL topologies to meet your unique needs. If Search scalability is something that’s important to you, you can create a whole tier of SQL servers just to handle search components. If you virtualize SQL and need to host SharePoint for 100K users. No problem… scale out to your hearts content. If you need to add disks to handle your Managed Metadata Service (MMS) load… add away.
• Available – No more Kludge involved with Database mirroring and better availability during upgrade and patching. Read more about this on my blog about HA improvements in SharePoint 2010
• Better multi-tenancy – Hosters and anal-retentive enterprises…. you now have a friend in the security business and her name is SharePoint. Hosting securely and smartly was a theme for improvement in SharePoint 2010. It’s no wonder with all the hosting Microsoft itself is doing these days. Between Office Live, MMS, and SharePoint Online (standard and dedicated), Microsoft now knows just how bad hosting was in previous versions. Microsoft delivered a much better story in SharePoint 2010. Namely, around 2 major improvements; Site Subscriptions and Service Applications. The former is a new way to group and administer site collections. Built specifically with hosters in mind, now a SharePoint admin can segment site collections across tenants and delegate administration to the group via a tenant administration page. Tenants can now control security, features, and services across site collections. Site subscriptions lie between web applications and site collections as a virtual level of hierarchy. Microsoft introduced site subscriptions because web applications were never meant for per-tenant hosting as they didn’t scale well after say 20 or 30. Conversely, site collections (on a per tenant basis) scaled really well, but tenants wanted more sites, more urls, more storage, and more control. An added benefit, though not new, is each tenant organization can be cordoned off into their own databases. With Transparent Database Encryption (TDE) introduced in SQL Server 2008, administrators can provide additional protection to tenant content through data encryption. The other principle feature for hosters is service app partitioning. Again, I will go into service apps later on my blog, but for now know that service apps can be partitioned for security across tenants.
• Command & control – Included with the aforementioned architectural improvements, MSFT provided more knobs and levers in SharePoint 2010. These knobs allow administrative control over how SharePoint uses resources including SQL and storage.

  •Throttling – Tell end users to leave you alone. Your busy! Learn more in my article on HA in SharePoint 2010 at http://www.sharepointmadscientist.com/Lists/Posts/Post.aspx?ID=46

  •Large List controls – tell end users they’re crazy trying to make that query, but admins, no problem! – Large lists were the bain of SharePoint existence in 2007. OK, so not quite, but large lists have been a thorn in the side of SharePoint for a while. Microsoft addressed this in 2010 with improved backend performance and controls that govern how lists are used. You can now set controls on what kind of queries users can perform and even what time of day those queries can be performed. You can even set different parameters for administrators. A quick search didn't turn up any detailed articles on the feature so it looks like another blog opportunity.

  •SharePoint designer – maybe not – Many administrators have horror stories about SharePoint designer and while MSFT has made SPDesigner nicer (and free-er :-) than ever, admins can now choose if they want to allow SPDesigner usage or not.

  •Developer Dashboard – Learn just how bad your developers are – Every SharePoint architect worth his or her consulting rate has a horror story about developers doing something stupid like initiating 800 SQL queries per page load. The developer dashboard is a monitoring solution for SharePoint customizations. The dashboard monitors several important KPI’s around rendering a specific customization to let you know things like how long it takes to load, how many SQL queries it issues, and how long those queries take. (This is probably one of my favs)

  •Sandboxing – Like prison for unsavory code - Microsoft had a problem with customization in SharePoint 2007. Customizations were difficult to onboard and manage at their best, and at their worst, completely unhostable. There were a number of reasons for this (including dev stupidity) but Microsoft came up with a better model. Its complicated and smarter people than I have already explained. The best article I’ve read yet is Scot Hillier’s treatment at http://www.shillier.com/Lists/Posts2/DispForm.aspx?ID=19

  •SQL Resource Governor – The nuclear option! – While not a SharePoint specific thing, SQL Server 2008 introduced a new way to control resource usage called SQL Resource Governor. It’s a very powerful feature and can cause some problems if not used correctly. Make sure you know what you’re doing! You can learn more at my old blog on Improving Sharepoint with SQL Server 2008 http://blogs.msdn.com/mikewat/archive/2008/08/19/improving-sharepoint-with-sql-server-2008.aspx

The bottom line – With all the new capabilities in SharePoint 2010 SQL and storage have never been so important. Make sure to plan accordingly by confirming or configuring SQL and storage to handle the additional load. The good news is that best practices are best practices and for SQL and storage best practices survive even in light of all the new stuff in SharePoint 2010. While I cant go into each specific best practice feel free to study my previous presentations and articles on the subject and additional resources from others including Microsoft’s own SQL Server resources. Since there are so many great resources instead of listing them here I’m going to create a SharePoint list and point you there instead. I feel this will be more dynamic and collaborative instead of a blog entry. You can find that list at http://www.sharepointmadscientist.com/Lists/BP As always, let me know if you have any questions or concerns. You can find me at www.twitter.com/mikewat www.facebook.com/mikewat , at mike.watson at quest.com or in person. Check my events calendar at http://www.sharepointmadscientist.com/Lists/Events for more info on where I’ll be.  I look forward to hearing from you.