SharePoint Foundation BLOB storage

If you download and install SharePoint Foundation 2010 from the Microsoft web site and use it with the default options you’ll end up storing the content on SQL Server 2008 Express which is limited to 4GB database sizes. if you work with SharePoint Foundation 2010 on SBS 2011 you’ll end up storing the content on SQL Server 2008 Express R2 which allows databases up to 10GB in size. One of the options beyond that is Binary Large Object Storage or BLOB storage.

So what’s BLOB storage? Many SharePoint sites are full of things like pictures, documents and the like which can vary in size from a few kilobytes up to many megabytes. The larger these ‘binary objects’ become the more size they chew up in the SQL database in which they are stored. SQL databases are typically not the best places for storing large binary data. In order to reduce the size of the SQL databases and improve their performance you can enable BLOB storage which basically allows this large data to be stored on the file system rather than in the database, yet still take advantage of many of the features that databases provide.

Sounds good eh? Well yes and no (and I think more so no for SharePoint). The good thing is that it reduces the total size of your SharePoint database and allows it to break the 10GB limit. However, I see plenty of downsides:

– BLOB storage adds complexity to SharePoint. How do you backup and restore BLOBs? How do you configure and enable it? What happens when you need to migrate data?

– If you work with a lot of files and BLOB storage you are going to end up with lots of orphaned BLOB stores which will require tidy up.

– You cannot assume that your current implementation using the present version of the software will be compatible with future versions of Microsoft Office or SharePoint Foundation (this straight from the Microsoft technical document).

– No utility is available for moving BLOB data from an existing content database into the external BLOB store. After you enable BLOB storage on SQL server on new BLOB data will end up in the BLOB storage location.

– Some backup and restore functions in SharePoint Foundation operate on the content databases but not on the external BLOB store. You must handle the backup and restore of external BLOB data differently to standard SharePoint.

– Microsoft recommends that if the content databases become larger than 16GB you upgrade to the full version of SQL Server that provides for unlimited database sizes.

– Remote BLOB storage is most useful for large file storage, typically in environments that require archiving.

– Once you enable remote BLOB storage all BLOB data ends up there. This means that if you have lots of small BLOB files accesses regularly you may experience latency.

– SQL BLOB Storage using FILESTREAM with SharePoint Foundation can only be to the local drives and does not support snapshot backups.

On balance then I think that if you are approaching the 10GB limit with the free version of SQL Server 2008 Express R2 it’s time to think about upgrading to a full version of SQL rather than implementing BLOB storage. Too me, from what I see, there is just too much downside.

You have been warned.

SBS 2003 Companyweb migration – Part 7

This is Part 7 (and the last) in a series of migrating SharePoint from SBS 2003 to SBS 2011.

Previous posts are:

Introduction – Overview

Part 1 – Caveats and Considerations

Part 2 – Preparation steps on v2

Part 3 – Upgrading v2 database to WSS v3

Part 4 – Attaching upgraded database to WSS v3

Part 5 – Check WSSv3 for migration to Foundation 2010

Part 6 – Move database to SBS 2011

Part 7 – Post migration steps and considerations

In this part we’ll cover some post migration steps and considerations. After the last post you should have a SharePoint site on you SBS 2011 that looks something like it did on SBS 2003. The next step is to import the Fax Centre library.

The first step is to import the file you save in Step 6 into the List template library. To do this go into Site Settings then into the List templates under Galleries.

Uploading the file into the library allows us to create a new library based on this template that also includes the data (which is why we selected to save list content when we created the template).

To re-create the Fax Center Select Site actions again and then More Options. In the list here you should see Fax Center. Select that template, Set the name of the library also to Fax Center, press OK and you’re done.

You can see Fax Center here under the Tracking heading in the bottom right.

I’d go back into the List templates and delete the Fax Center template as it is unlikely you are ever going to need it again.

So now in theory you’re all done? Yes but here’s some additional things you should consider or configure in my opinion.

– You may need to configure a new site administrator if you have migrated from a different domain. You can do that simply by once again going into Site Settings and selecting Site Collection administrators from under the Users and Permissions heading.

– You may also need to add the SBS 2011 SharePoint security groups into the appropriate groups in SharePoint to permit you users access. You can do this by going into site settings and selecting People and groups from under the Users and Permissions section. Then click on the hyperlink Groups.

You’ll a list of all the SharePoint groups, click into each and ensure the following AD groups appear the listed SharePoint groups:

Companyweb visitors = \windows sbs sharepoint_visitorsgroup

Companyweb members = \windows sbs sharepoint_membersgroup

Companyweb owners = \windows sbs sharepoint_ownersgroup

– Not all of us live in Seattle so you probably need to check the regional settings and time zone of your site. You can do this by going into Site Settings then selecting Regional settings under the Site Administration section.

– If you have set complex SharePoint securities these may need to be re-established.

– Configure a scheduled command line backup. Using the STSADM or equivalent Powershell operation (see step 6) that runs everyday to create a single data file of your SharePoint site. As I have said before it is much easier in a DR situation to recreate a clean SharePoint environment and then restore the data into it. It also make it easier to bring up a replacement SharePoint server in case of extended downtime.

– Configure a scheduled command line export. Using the STSADM or equivalent Powershell operation (see step 6) that runs everyday and creates an export file of your SharePoint site. In contrast to a backup an export allows you to extract and merge the information present to another SharePoint site. You need to consider the size of your SharePoint site when doing backups and export but of you want maximum flexibility I’d do both.

– SQL Server memory trimming. Generally all SQL versions install on SharePoint are done so in a standard configuration. This means they have effectively no limit set on the maximum amount of RAM they can utilize. This can mean they end up hogging a large portion solely for their own purposes even if they don’t use it. I’d recommend you go in and trim the amount of RAM each SQL instances uses to something reasonable for that application. SharePoint really shouldn’t need more than 1GB but that is dependent on the size of your SharePoint site.

– Set SharePoint farm password. As I have detailed before in this post, on SBS 2011 the SharePoint farm password is set to a random value. This means that in the case of a DR and you needing to repair the SBS 2011 SharePoint installation you are potentially going to need to know the SharePoint farm password to connect to the existing installation. If you don’t then it is an uninstall/reinstall of SharePoint Foundation on SBs 2011, which not something you want to do if it can be avoided. Thus, use the Powershell commands in the post to set the password to something you KNOW.

– SQL instance is now \Sharepoint. If you need to look at the SQL databases in the management studio you will need to connect to \Sharepoint. The reason for this is the SQL version on SBS is now SQL Server 2008 Express R2 not SQL Embedded Edition 2005.

– Content size limited to 10GB by default. Since SharePoint on SBS 2011 is using SQL Express 2008 R2 the maximum database size supported by this version by default is 10GB. If the information in SharePoint is growing beyond this then the best option is to migrate SharePoint to a full version of SQL Server that has no database size limitation.

– Configure BLOB storage. If you have a SharePoint site that has lots of documents then it is probably a good idea to consider BLOB storage. This basically moves binary large objects (i.e. files) out of SQL but still controlled by SQL. To this you basically need to install and configure a free addon for SQL Server. Once complete then large binary objects will reside outside the SQL database allowing your SQL database to contain more than the ‘default’ 10GB. the recommendation is that you shouldn’t exceed 16-20GB using this method but information on what is required to enable is can be found here. It is my understanding that only AFTER you configure BLOB storage will files end up outside your SQL database, so perhaps you want to that before any migration. I really don’t know enough BLOB storage yet to give any hard and fast recommendations.

– Configuring PDF document actions. As detailed in this previous post, when you click on a PDF file saved in SharePoint Foundation 2010 you are only going to get the option to save not to open. if you want the browser to open PDF documents directly then you’ll need to follow the instructions in the post to enable this.

It seems this post is becoming pretty long so let me summarize what else you need to look at and cover it in more detail in future posts. You should also:

– Set the recycle bin handling

– Ensure indexing and search is operational

– Run a full index of all your content

– Add/Remove and restricted file types

– Configure analysis reporting

– Configure diagnostic reporting

Phew, far more here than I thought at first eh? I’ll keep posting information but do it independently of this series.

Of course, all of this information is found in my SharePoint Guide (www.wssops.com), updated monthly and in full detail for subscribers.

SBS 2003 Companyweb migration – Part 6

This is Part 6 in a series of migrating SharePoint from SBS 2003 to SBS 2011. Series posts are:

Introduction – Overview

Part 1 – Caveats and Considerations

Part 2 – Preparation steps on v2

Part 3 – Upgrading v2 database to WSS v3

Part 4 – Attaching upgraded database to WSS v3

Part 5 – Check WSSv3 for migration to Foundation 2010

Part 6 – Move database to SBS 2011

Part 7 – Post migration steps and considerations

In this part we are going to move the WSS v3 database across to SBS 2011.

After running the ststadm –preupgradecheck shown in the last post and not having any issues, the next step in the process is to copy the databases from the staging WSS v3 server to the SBS 2011 machine.

The easiest way to do this is simply to stop the SQL service on the WSS v3 box. If you installed WSS v3 in a standard configuration the SQL database is Embedded Edition (##SSEE) so locate the Windows Internal Database service and right mouse click on it and select Stop.

Now, locate the raw SharePoint content database files on your WSS v3 server. These are the same ones that you copied from SBS 2003 but are now upgraded to SharePoint v3. Copy these files to you SBS 2011 server.

As with the migration from SBS 2003 to WSS v3, I recommend you backup your default SharePoint 2010 environment via a SharePoint specific backup prior to commencing. This makes it much easier and quicker to roll back if necessary. Now you can still do a backup via the good old STSADM command but Microsoft recommends that we do as much as we can with Powershell. So here’s our chance to get our hands dirty with Powershell.

To launch Powershell select Start | All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Management Shell. Make sure that you run it as administrator as shown above.

After you accept the UAC you should see a DOS box with a blinking cursor as shown above. Type the following command:

Backup-spsite http://companyweb -path c:\companyweb.bak -force

This will create a backup of the SBS 2011 Companyweb site at c:\companyweb.bak. As before I’d also recommend an export of the site. To this type the following PowerShell command:

Export-spweb http://companweb -path “c:\companyweb.exp” -force

This will create an export backup of the SBS 2011 Companyweb site at c:\companyweb.exp. Leave the PowerShell console open for the time being and view http://companyweb.

SBS 2011 has a fax center which is unique to SBS 2011 and is much easier to backup individually and restore in SharePoint than attempt to recreate. So once Companyweb is displayed click on Fax Center. Then select the Library tab at the top of the page to reveal the ribbon.

In the ribbon select Library Settings (to the right).

In the middle, under the Permissions and Management column select Save document library as template.

Enter the details as shown above but make sure you check the option at the bottom to Include Content.

This will create and save a list template in the List Template Gallery. Simply visit the Gallery and click on the Fax Center template you just created and save it somewhere on your hard disk.

Return to your Powershell console and enter the following command:

Dismount-spcontentdatabase sharewebdb

As the screen shot shows you’ll be prompted to confirm this. Once complete the existing SharePoint content database has been removed from Companyweb.

You now need to attach the databases you copied from you staging WSS v3 to your SBS 2011 server. When you attach them it is also probably a good idea to rename them to ShareWebDb just to remain consistent going forward. This rename process will firstly require you to detach the existing ShareWebDB from SQL Express 2008 R2, attach the copied databases renaming in the process. This isn’t too difficult and I’m not going to go through it here.

Once you have attached the copied databases to SQL Server you run the following command at the Powershell console:

Mount-spcontentdatabase “sharewebdb” -webapplication http://companyweb

This will reconnect the new (copied from WSS v3) databases (now called ShareWebDb) to http://companyweb. During this process you will see a percentage complete displayed as the WSS v3 databases are converted to SharePoint 2010 format.

When that is successful, the final step in the process is to convert the interface from WSS v3 to SharePoint 2010 (as SharePoint 2010 can allow migrated WSS v3 sites to still run with the old look and feel). To do this execute the following Powershell commands:

$webapp=get-spwebapplication http://companyweb

Foreach ($s in $webapp.sites)

{$s.visualupgradewebs()}

If you now visit http://companyweb you should see your old content displayed in the new SharePoint 2010 interface.

In the next post I’ll cover off some tidy up, including how to re-import the Fax Center, enable health and analytics reporting as well a few other things I believe ‘must’ be done to make SharePoint 2010 as functional as possible.

SQL Database basics video

I’ve just uploaded a new video to YouTube that shows you how to detach and attach SQL databases via the SQL Management Console.

<img src="http://lh6.ggpht.com/-pqqxckqjdc8/UOzV5j5n08I/AAAAAAAAG48/ys-erSMjR7k/videoee60a66c7a88%25255B4%25255D.jpg?imgmax=800" style="border-style: none" galleryimg="no" onload="var downlevelDiv = document.getElementById('4c6ea4c4-d93f-41aa-b09c-1637314ddd0f'); downlevelDiv.innerHTML = "

http://www.youtube.com/v/xdI-DP_5vyc?hl=en&hd=1“;” alt=””>

SQL Database Basics

Although only very short and simple in my experience I have found that many people tasked with administrating SharePoint (which relies on SQL) do not know how to perform these simple operations.

The detach and attach process is really handy if you want to manually move the SharePoint content bases to another location on the disk.

Subscribers to my SharePoint Guide will receive an extended video covering additional topics such as how to completely remove SQL databases as well as rename them. The Guide not only provides video tutorials but extensive notes. For more information about becoming a subscriber visit www.wssops.com.

SBS 2003 Companyweb migration – Part 5

This is Part 5 in a series of migrating SharePoint from SBS 2003 to SBS 2011. Series posts are:

Introduction – Overview

Part 1 – Caveats and Considerations

Part 2 – Preparation steps on v2

Part 3 – Upgrading v2 database to WSS v3

Part 4 – Attaching upgraded database to WSS v3

Part 5 – Check WSSv3 for migration to Foundation 2010

Part 6 – Move database to SBS 2011

Part 7 – Post migration steps and considerations

In this part we are going to run a check to ensure that our original SBS 2003 Companyweb site (now in Windows SharePoint Services v3) will migrate to SharePoint Foundation 2010.

Prior to any upgrade from WSS v3 it is recommended that you run the stsadm –preupgradecheck from the command line.

To do this go to the command prompt as an administrator on the WSS v3 server. Change to the directory c:\program files\common files\microsoft shared\web server extensions\12\bin. At this location type the following command and press ENTER.

stsadm –o preupgradecheck

You should now see a number of checks being run on your system like so:

When the process is complete you will then see a HTML summary page displayed like so:

Scroll to the bottom of the page to see any errors like shown here:

It is recommended that you read the report carefully and note any warnings and errors as they may prevent successful migration.

Some errors may not be relevant. For example in screen shot above you will note that the error indicates that the system is not running on a 64 bit operating system. This will not apply in the situation where a migration to new hardware is being undertaken.

The –preupgradecheck option is only available on WSS v3 installations that have WSS v3 Service Pack 2 installed.

If everything looks good then you are ready to forklift the WSS v3 databases onto SBS 2011 and attach them to SharePoint Foundation 2010 which I’ll cover in the next part.

SBS 2003 Companyweb migration – Part 4

This is Part 4 in a series of migrating SharePoint from SBS 2003 to SBS 2011. Series posts are:

Introduction – Overview

Part 1 – Caveats and Considerations

Part 2 – Preparation steps on v2

Part 3 – Upgrading v2 database to WSS v3

Part 4 – Attaching upgraded database to WSS v3

Part 5 – Check WSSv3 for migration to Foundation 2010

Part 6 – Move database to SBS 2011

Part 7 – Post migration steps and considerations

In this part we are going to attach the migrated databases we attached to SQL to our staging Windows SharePoint Services v3 (WSS v3).

Adding database to WSS v3

Once the old SharePoint v2 databases have been attached to SQL Server the next step is to connect these databases to WSS v3.

Open a DOS prompt on the WSS v3 server via Start | Run | Cmd. Change directory to c:\program files\common files\Microsoft shared\web server extensions\12\bin and execute the following command to remove the existing WSS v3 database.

stsadm –o deletecontentdb –url http:// -databaseserver -databasename

for example:

stsadm –o deletecontentdb –url http://sharepoint3 -databaseserver VMSBS2003P -databasename WSS_CONTENT

Note, that if you are using the SQL Server 2005 Embedded Edition (SSEE##) that installs with the default standalone installation of WSS v3 you do not need to specify the database server. That is, you should leave out the option –databaseserver

Where http://sharepoint3 is the new WSS v3 created during installation of WSS v3, VMSBS2003P is the name of the WSS v3 Windows Server and WSS_CONTENT is the default name of the WSS v3 content database created during installation of WSS v3. If you have made changes from the default during the installation some of these vales will be different for you.

It is important to remember that this process will remove all the existing content from the WSS v3 site.

You now need to add the migrated SharePoint v2 databases that you have previously attached to SQL Server to the new WSS v3 site. The addcontentdb process will allow you to do this and automatically upgrade your SharePoint v2 data into WSS v3. Execute the following command to add the existing old SharePoint database to the new WSS v3 site.

stsadm –o addcontentdb –url http:// -databaseserver -databasename

for example:

stsadm –o addcontentdb –url http://sharepoint3 -databaseserver VMSBS2003P -databasename STS_VMSBS2003p_1

Note, that if you are using the Microsoft SQL Server 2005 Embedded Edition (SSEE##) that installs with the default standalone installation of WSS v3 you do not need to specify the database server. That is you should leave out the option –databaseserver

The upgrade process will vary on the size of the content databases and speed of your hardware. Eventually, you should receive a message telling you that the process completed successfully.

Once the process is complete, open the default WSS v3 site (typically just the http://server_name) on the WSS v3 staging server and confirm that the data is present and correct. You should see data from from your SBS 2003 Companyweb but now in a WSS v3 format.

In the next part we’ll commence the process to migrate to SharePoint Foundation 2010.

SBS 2003 Companyweb migration – Part 3

This is Part 3 in a series of migrating SharePoint from SBS 2003 to SBS 2011. Series posts are:

Introduction – Overview

Part 1 – Caveats and Considerations

Part 2 – Preparation steps on v2

Part 3 – Upgrading v2 database to WSS v3

Part 4 – Attaching upgraded database to WSS v3

Part 5 – Check WSSv3 for migration to Foundation 2010

Part 6 – Move database to SBS 2011

Part 7 – Post migration steps and considerations

In this part we are going to attach the SharePoint v2 databases we copied across from SBS 2003 to the version of SQL that comes with a standard installation of Windows SharePoint Services v3 (WSS v3).

Part of the migration process of Companyweb from SBS 2003 to SBS 2011 involves migrating to WSS v3 (and eventually to SharePoint Foundation 2010). This means you will have to install WSS v3 somewhere as a staging server. I am not to concerned about this here, as long as you have it running with the default setup you will be able to follow along. However, if was me, I’d be using a Virtual Machine of some form as my staging server for WSS v3 but I am not going to cover any of that. I am going to assume that you already have WSS v3 running in a default installation somewhere.

We need to get the Companyweb databases we copied from SBS 2003 attached to the version of SQL on our WSS v3 server. By default, the WSS v3 databases are stored on the system partition (C: drive) of the server and no graphical management tools are installed. It is however possible to manipulate the databases using the command line but a free graphical management tool is available from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en

It is strongly recommended that you install this application on your server to make working with the Microsoft SQL Server 2005 Express Embedded Edition (SSEE) easier.

Once the management studio has been installed on the server it can be accessed via Start | All Programs | SQL Server Management Studio Express.

Once the management studio is running you will need to connect to the Microsoft SQL Server 2005 Express Embedded Edition (SSEE). To do so use the following string in the server name field:

\\.\pipe\mssql$microsoft##ssee\sql\query

Once the management console has connected you should see an interface similar to that of other SQL 2005 server installations. The databases are located under the database folder.

By default the location of the Microsoft SQL Server 2005 Express Embedded Edition (SSEE) data on the WSS v3 server will be:

c:\windows\sysmsi\ssee\mssql.2005\mssql\data

and this cannot be changed during the installation process. In many cases, as the data held in the databases grows it may cause problems because typically C: is the Windows system partition.

Unlike Microsoft SQL Server 2005 Express Edition the Embedded Edition does not have a limitation on the size of a database, while the non-embedded edition has a maximum database limit of 4GB.

Attaching databases using SQL 2005

Locate the Database folder under the Server name.

Right mouse click the Database folder and select Attach from menu that is displayed.

Press the Add button to locate the SharePoint v2 database that you previously copied.

Navigate to the location on the disk in which you saved the copy of the original SharePoint v2 database. Select the MDF file (here STS_SERVER_1.mdf) and press the OK button.

Check that all the information now displayed is correct and when complete press the OK button to continue.

SQL Server 2005 will now attach the database. You should see the word Executing displayed in the lower left of the screen during this process.

When the process is complete, if you now examine all the databases listed under the Databases folders you should see your SharePoint v2 database (in this case STS_VMSBS2003P_1). Note, that you will also see the WSS v3 database (in this case WSS_content) that was installed during the setup of WSS v3. If you have not already taken note of what the WSS v3 database is you should do it now for later reference.

The next post will cover how to connect this migrated databases to WSS v3.

SBS 2003 Companyweb migration – Part 2

This is Part 2 in a series of migrating SharePoint from SBS 2003 to SBS 2011. Series posts are:

Introduction – Overview

Part 1 – Caveats and Considerations

Part 2 – Preparation steps on v2

Part 3 – Upgrading v2 database to WSS v3

Part 4 – Attaching upgraded database to WSS v3

Part 5 – Check WSSv3 for migration to Foundation 2010

Part 6 – Move database to SBS 2011

Part 7 – Post migration steps and considerations

I am going to assume that you have done your backups and updates on the source SBS 2003 server.

Pre-requisite

If you have enabled full text search on SBS 2003 Companyweb (by migrating the databases to the full version of SQL from MSDE) you will firstly need to disable prior to the migration.

Disabling Full Text Search SBS 2003

The easiest way to tell whether full text indexing has been enabled on WSS v2 is simply to view the WSS v2 site. To do this, simply open a web browser and type http://companyweb into the address. If full text indexing has indeed been enabled you will see the search box in the top right of the window.

Full text indexing on WSS v2 needs to be disabled prior to any migration.

To disable full text indexing on WSS v2 logon to the SBS 2003 server as an administrator and select Start | Administrative Tools | SharePoint Central Administration.

When the Central Administration site appears scroll down to the bottom of the screen.

Under the Component Configuration section select the Configure full-text search link.

You should now see a check box indicating that full-text search is enabled. Simply uncheck this box.

And press the OK button to save the new configuration.

After pressing OK the system will now process you changes and return you to the Central Administration site.

You can now proceed with the WSS v2 migration.

1. Ensure that http://companyweb is operational.

2. Download the prescan.exe utility from:

http://www.microsoft.com/downloads/details.aspx?familyid=e8a00b1f-6f45-42cd-8e56-e62c20feb2f1&displaylang=en&tm

and copy it to your SBS 2003 server.

3. Open a command prompt on the console and run prescan.exe like so:

At the command prompt type prescan /all and press Enter. The scanning tool will examine the existing SharePoint v2 databases and report if there are any issues that may arise when you attempt to migrate. When the process is successful close the DOS prompt. Note, that running prescan does not in any way affect the existing SharePoint v2 database operations, it does however make a minor change to the databases that is required prior to any migration. Thus, you need to run this prescan tool for the migration to succeed. So beware that a small change is made to the databases but this shouldn’t affect their operation in any way.

If there issues they will need to be addressed and resolved before you continue with the migration.

3. The next step is to stop the SharePoint v2 database service so that the existing databases can be copied to a new location. To do this go Start | Administrative Tools | Services.

Locate the service named MSSQL$SBSSHAREPOINT, right mouse click on the service and select Stop from the list that appears.

When this process is complete you should see nothing in the Status column for that service, this indicates that the service is not running. Close the Services window.

4. You now need to locate the original SharePoint v2 databases. Normally these will be located in :\program files\microsoft sql\server\MSSQL$SHAREPOINT. Typically, the files will be named STS__1.mdf and STS__1.log.LDF (in this case STS_VMSBS2003P_1).

Right mouse click on both of these files and select Copy from the menu that appears.

Move to the location where you wish the new databases to be located (on a WSS v3 server typically) and paste the files into that directory.

Once the databases have been copied they need to be attached to the SQL server associated with your installation of WSS v3. I’ll cover this in the next part of the series.

You can now restart the service named MSSQL$SBSSHAREPOINT on the SBS 2003 server so that Companyweb continues to operate. Remember though, if you make changes to the version of Companyweb on the SBS 2003 server AFTER you have copied the databases you’ll need to re-migrate to incorporate these changes.

This will be the last we’ll need from the SBS 2003 server.