Monday, June 25, 2012

Beyond the 10GB Companyweb 2010 limit

A hurdle that is fast becoming an issue with Companyweb on SBS 2011 is 10GB database limit imposed by SQL 2008 Express R2. Back in SBS 2008 days Companyweb used SQL 2005 Express Embedded Edition (##SSEE) which allowed unlimited database sizes. However, the issue with SSEE is that it is a 32 bit data platform and SharePoint 2010 Foundation requires a a 64 bit database platform. Enter SQL 2008 Express R2 as the storage platform for Companyweb (SharePoint Foundation 2010) on SBS 2011.

 

As the use of SharePoint increases and customers start storing lots and lots of files in there as well as using version control suddenly 10GB starts to look pretty small. So what options are available to you to break the 10GB barrier with Companyweb on SBS 2011?

 

Option 1 – Split your content across multiple content databases

 

Just because your data exceeds 10GB doesn’t mean it can’t live in multiple content databases. You can easily create a second content database into which you can create another Site Collection. Well, maybe not easily, but it cane be done. See -

 

http://blog.arjanfraaij.com/2011/01/sharepoint-2010-add-site-collection-to.html

 

Once that is created you can create a link from your first site to the second one. In this scenario your total site storage is now 20GB. In theory you could keep adding content databases as you need to, each providing 10GB a pop.

 

Downsides? It is certainly more cumbersome working with multiple SharePoint content databases. You have to make sure that you have set up indexing on that database, assigned rights and linked back and forth from the original. In the end you now have content in two places which can always spell trouble.

 

Option 2 – Implement Remote BLOB storage

 

I have blogged about this before and the issues you may face which you will find here and here. You can do it and it is free but I wouldn’t recommend it for the reasons in those other posts.

 

Downsides? Many (don’t do).

 

Option 3 – Upgrade the existing SharePoint SQL instance on SBS 2011

 

For this you will need to purchase a full version of SQL (typically via the SBS 2011 Premium Add on pack) and then install it over the existing SQL 2008 Express R2 instance that is used for SharePoint.

 

You’ll find details of that process here -

 

http://blogs.technet.com/b/sbs/archive/2011/02/28/how-to-migrate-companyweb-to-a-sql-2008-r2-standard-instance-on-sbs-2011-part-1.aspx

 

Downsides? Firstly, you are going to have purchase a full version of SQL. It also kind of makes SBS 2011 “non-standard”. This doesn’t mean it isn’t a supported configuration (as I understand it) but I can’t say how SBS 2011 wizards and updates handle things being ‘non-standard’ going forward.

 

It also means you have yet another SQL version on your SBS 2011 server that will need separate updates along with the other versions of SQL that are already there (i.e. SQL 2008 Express R2 and SSEE).

 

Option 4 – Install a new version of SQL Server onto the SBS 2011 server

 

Once again here you’ll need to purchase a full version of SQL (typically via the SBS 2011 Premium Add on pack) and then install it as another SQL instance on the SBS 2011 Server.

 

Once you have done this you will need to move the SharePoint content databases from the default SQL 2008 Express R2 instance to the new full SQL instance via SQL detach and reattach as well as ststam –o deletecontentdb and addcontentdb commands.

 

Downsides? Like the above option. You have to buy a full version of SQL and SBS 2011 is now ‘non-standard’ (although supported). You have an additional version of SQL to patch and update. The added downside here is that this new version of SQL has created a new instance which means it is consuming additional RAM on your SBS server. Thus, now you have SSEE, SQL 2008 Express R2, and Full SQL consuming RAM for all their instances, not forgetting that by default SQL wants is not memory trimmed (i.e. it will consume every piece of free RAM it can).

 

Option 5 – Install a new member server that includes SQL Server

 

Here you’ll need to purchase an additional Windows Server and SQL License (typically via the SBS 2011 Premium Add on pack), some additional hardware (or virtual machine) and create a new member server in SBS 2011 domain with full SQL installed on it.

 

Once you have done that you will need to move the databases (much like in Option 4 above) to the new server with full SQL.

 

Details on doing that are found at -

 

http://blogs.technet.com/b/sbs/archive/2011/03/02/how-to-migrate-companyweb-to-a-sql-2008-r2-standard-instance-on-sbs-2011-part-2-premium-second-server.aspx 

 

and -

 

http://blogs.technet.com/b/sbs/archive/2011/03/07/how-to-migrate-companyweb-to-a-sql-2008-r2-standard-instance-on-sbs-2011-part-3-migrating-the-content-database.aspx.

 

Downsides? Again SBS 2011 is not longer ‘standard’ (but again still supported). You need to spend more money on SQL and Windows licenses and potentially additional hardware. The other downside is that now database requests for SharePoint are sent from the second server with SQL installed back to the SBS 2011 box out via IIS to the client (if I understand the flow correctly here). That means extra network traffic and potential latency as data requests for SharePoint are pull from a database across the network. You might never notice this with the fast infrastructure we have today but it is still slower than having it all hosted on one server.

 

But hand on a sec ….

 

This whole discussion has really focused on just the SharePoint content database, however this is not the only database that SharePoint Foundation uses. In SharePoint you’ll also find the following databases typically:

 

Sharepoint_config

SharePoint_admincontent_<GUID>

WSS_logging_<GUID>

WSS_search_<GUID>

 

so what about these? Should they also be moved? The following article does tell you how to go about this (Although I believe there are issues with the document),

 

http://technet.microsoft.com/en-us/library/gg616007.aspx

 

In theory I would say no for every database except one. The first three databases should generally remain small and no exceed the 10GB limit, however what will also grow as your content database grows? The search database. If you leave it where it is on SQL 2008 Express R2 it will be limited to 10GB in size. The only way that you’d probably notice that the search database has reached its limit is when search results are not returned for information you KNOW is in the database.

 

Can’t say that I have ever seen a search database exceed 10GB but in theory, given enough SharePoint content to index, it could. So, if you go a full version of SQL with unlimited database sizes I would also be moving the search database (wss_search_<guid> by default) to the full version of SQL to allow it breathing space as your content grows.

 

To move the search databases the easiest way is probably to recreate them by stopping and restarting the SharePoint search service but in this case nominating the location of the full SQL server for the storage of the index data.

 

Conclusion

 

For most people that need to exceed the 10GB option the best option I would say is to get the Premium Add on. Install full SQL on a new member server and swing the content (ShareWebDb) and the Search database only across to this new SQL instance that has no limits on the database size. However, I hope this post has at least provided you with some options of what is possible.