Move SharePoint 2010 from SQL Server Enterprise (Trial/Evaluation) to Standard

Recently we had an emergency call from one if our clients who was migrating their databases from one Microsoft SQL Server 2008 to another Microsoft SQL Server 2008. No big deal, you would think, but unfortunately there is a caveat when using the SQL Server 2008 Enterprise or Trial edition. When restoring the Search Service Property Store Database the client ran into the following error:

Database ‘SharePoint_SearchServiceApplication_PropertyStoreDB’ cannot be started in this edition of SQL Server because part or all of object ‘MSSDocProps’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

The SharePoint 2010 Search databases make use of the feature set of the Enterprise edition when it is available.

In our case the client used the Trial edition of SQL Server which is actually the Enterprise version.

To remove the data compression from the database we need to run several SQL commands on the Search Property Store DB on the original database server.

Step 1, Find objects with issues

First we need to find the objects where the data compression is used

SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName

Step 2, rebuild the table index with no compression

For every line return in step 1 where object name is distinctive execute the following statement.


Step 3 Run step 1 again

Execute the query from step 1 again. You will see that there are still objects with data compression, this are the indexes on the tables.

Step 4 Find indexes with issues

Use this statement to find the index name by IndexID from step 3

DECLARE @TableID int
SET @TableID = OBJECT_ID('MSSDocProps')
FROM sysindexes
id = @TableID
AND IndID = 3 ‘ index number

Step 5, remove compression from index
To rebuild the index without compression use this command.


Now, you can do a detach/atach or backup/restore without any problems. Good luck!


Add “Link to a Document” Content Type to the Default Content in a Document Set in SharePoint 2010

The Document Set feature in SharePoint 2010 is a great addition to the document management functionality of the platform. Today we used the document set to make it able to prefill a folder with a predefined set of documents. One document which should be added as default content is the “Link to a Document” contenttype. Which is a difficult one because you don’t have a template file like a docx or xsls. After some try and error I found a solution to template the Link to a Document page.

· Create a Link to a Document or other content type with Link to a Document as parten in a document library

· Use a dummy url for the Document URL and click OK

· Open the site with SharePoint Designer, Select all files

· Select the Link to Document you created in the Document library and choose export in the context menu

· Go back to the SharePoint Site and navigate to Site Content Types

· Open the Document Set where you want to add the default content and open Document Set settings

· Select the Link to a Document content type and choose browse to select the saved file and upload it

· Now you are able to use the Link to a Document content type to add as default content!


– You can also use this trick to set the template as the template for the content type, instead of the default /_layouts/CreatePage.aspx

Maximum Storage Capacity SharePoint (Online)

Lots of people are convinced of the inability to store gigabytes or terabytes (not even thinkable) of information in a SharePoint website. The most common argument is related to the way SharePoint stores the data, which is in a SQL database. Databases are not designed store files, so store you files on files shares. This is, off course so pre SharePoint and the most system administrators (not all) feel more comfortable with their fileshare then handing their data over to the db admin. Now, we reach the point where the real pain exists, the lack of knowledge about managing large SharePoint environment with gigabytes or even terabytes of data.

And I think they are right! SharePoint is not just about Disks, LUNs or Volumes. No, you have make a lot of architectural decision based on good estimated on usage and volume. Several technical boundaries will influence your logical architecture, so be sure to spend some time to structure your data in SharePoint before you begin. For the physical part of your storage SharePoint Online provides you a solution to store up to 25Tb (yes, Terabyte). I am sure your system administrator will become interested how Microsoft realizes this with a database storage! The good part is, that you can build this your own, but you will not able to compete with Microsoft which has just lowered their pricing for Office 365 SharePoint storage to just 0,20 dollar per GB, which is just amazingly low!

SharePoint Online for enterprises: key features and specifications (from here)

Feature Description
Storage (pooled) 10 gigabytes (GB) base customer storage +
500 megabytes (MB) per enterprise user.
Storage per Kiosk Worker Zero (0). Licensed Kiosk Workers do not bring additional storage allocation.
Maximum number of Active Directory user objects Up to 500,000 AD user objects
Additional storage (per GB per month); $0.20 USD/GB/month.
Site collection storage quotas Up to 100 gigabytes (GB) per site collection.
My Site storage allocation 500 megabytes (MB) of personal storage per My Site (once provisioned).
Site collections (#) per tenant Up to 300 (non-My Site site collections).
Total storage per tenant Up to 25 terabytes (TB) per tenant.
File upload limit 250 megabytes (MB) per file

Argument to convince your business

Migrating your fileshare to SharePoint introduces a lot of business benefits which cannot be achieved with network storage. Hereby a bunch of benefits to convince your business!


  • Access your documents Anywhere, anytime, and on any device
  • Versioning (configurable) of documents; automatically saves versions of the document on save or upload
  • Metadata; automatically stores author information and allows to add additional structured or unstructured metadata
  • Search; be able to find documents and information within your business
  • Fileshare; SharePoint is accessible as a fileshare via WebDAV (nice to tell your System administrator)


  • Checkin/Checkout to prevent “double” editing; prevent multiple edits within the same version by locking the document before editing
  • Working together within one Word document; this is a really cool but even more functional feature which makes it able to work with multiple users within the same Word document
  • Office Web Apps; Open and edit files within your file browser (IE, Firefox and Chrome)
  • Alerting; get email or TEXT/SMS messages on changes to documents in SharePoint


  • Document approval and traceable workflows
  • Audit reports; report on reads, updates, etc

And lots of other features

Counter Arguments from your System Administrator

Heard arguments against SharePoint which can be expected from your system administrator:

Argument: SharePoint/SQL is not intend to store files
Your answer: Can you tell me why Microsoft offers up to 25Tb for a single tenant in Office 365/SharePoint Online?
Possible reaction: Yeah sure, and who is using this kind of storage right now?Your answer: Shell, Philips, should I continue? All Enterprise which terabytes of data use SharePoint

Argument: SharePoint is expensive
Your answer: $10 per month/per user is expensive? People spend more than 5 hours a day searching for information within their organization, what do you think about these costs?
Possible reaction: What is the source of this information?
Your answer: check or if you want to check it right now

Argument: Where hear a lot of failed SharePoint projects
Your answer: This is true, a lot of companies don’t take time to design their SharePoint environment.
Possible reaction: Do we want to spend this time, on designing while fileshares are just fine
Your answer: file share just fine? What is so nice about fileshares for the business?

Argument: SharePoint is complex
Your answer: that right! Do you think you can’t handle it? Hehe


With the right SharePoint Physical and Logical architecture you’re able to store Terabytes of documents in SharePoint. Use the given arguments to convince your business about the usefulness of SharePoint for your organisation.

Default Custom View for Document Set in SharePoint 2010

When working on a Document Management Solutions with Document Sets I had a struggle to find out how to select a custom view to use within a Document Set. After my search I would like to share this information with you. The next steps will help you to accomplish this.

· Go the the document library settings

· Define a new view for the document set on the document library

· Define your view and optionally choose where you are able to select this view

· Click on OK

· Click on the Document Set Content Type

· Click on Document Set Settings

· At the end of the screen you will find a drop down list under “Welcome page view” where you can select default view

Somehow the procedure makes sense, but you have to know it.

Configure Send To Connection with SSL and multiple Web Applications

My customer would like to make it able to send documents from their mysite to a central document store. The central document store is used as a publication site for primary versions of the documents. The most common solution is to provide a Send To connection from Central Administration to the mysite web application. Although this seems a common task, several issues had to be encountered.

The issues were related to:

  • The SharePoint 2010 environment is only accessible on a secure channel (SSL)
  • The central document store is a different web application with an own application pool and service account in comparison with the mysite web application.

Additional steps for SSL


You will get an “URL is not a valid routing destination” when you try to configure the https url as a Send To connection.

You need to establish a trust for the Root Certificate of the SSL certificate used for the webapplication.

To establish trust by using Central Administration

  1. Verify that the user account that is performing this procedure is a member of the Farm Administrators SharePoint group.
  2. On the SharePoint Central Administration Web site, click Security.
  3. On the Security page, in the General Security section, click Manage trust.
  4. On the Trust Relationship page, on the ribbon, click New.
  5. On the Establish Trust Relationship page:
    1. Supply a name that describes the purpose of the trust relationship.
    2. Browse to and select the Root Authority Certificate for the trust relationship. This must be the Root Authority Certificate that was exported from the SSL Certificate used for the SharePoint Web Application.
    3. Ignore Provide Trust Relationship.
    4. Click OK.

Perform a IISRESET on all sharepoint servers.

Additional configuration for multiple web applications


When manually sending records to the send to connection you will het “The <send to connection> site could not be found or accessed.”

Configure/check permissions

1. Go to the receiving web application

2. Go to the site permissions

3. Find a group called “Configure a group Records Center Web Service Submitters for <Site Name>”

a. If this group is missing, deactivate and activate the feature “Content Organizer” on the Site which will receive documents

4. Add the application pool account user from the other webapplication

Now you should be able to add the Send To Connection

SharePoint Document Information Panel cannot create a new, blank form

The document information panel in Microsoft Word is a great way to enter metadata for documents stored in SharePoint 2007/SharePoint 2010. Especially the combination with content types and Word Templates is a great way to make it easy to start new documents in SharePoint. But when you are changing columns in the contenttype when you have already assigned a Word Template you can run in to trouble.

You will probably get errors like “The document information panel was unable to load” or “Document Information Panel cannot create a new, blank form”

After some research I have found a pretty simple solution to this problem.

Go to the Document Type Advanced Settings

Click Edit template

In Word, go to File

Click on Inspect

Click on Remove All behind Custom XML Data

Save the template and close Word.

You’re all set. The template will not anymore throw an error when creating new documents and the document information panel will load successfully.

Nagation is broken after upgrade from SharePoint 2007 to SharePoint 2010 with Dutch Language pack

During a SharePoint 2007 to SharePoint 2010 migrations with Dutch Language Packs you will encounter a broken navigation.

Microsoft has published a PowerShell script which is worthwhile to share with you!

$site = Get-SPSite("http://WebApplicationURL")
foreach ($web in $site.AllWebs)
  Write-Output("web:" + $web.ServerRelativeUrl);
  $pageslistid = $web.AllProperties["__PagesListId"]
  if ([String]::IsNullOrEmpty($pageslistid))
    Write-Output($web.ServerRelativeUrl +" doesn't have a pages list id set - skipping");
    $pageslistguid = new-object System.Guid($pageslistid)
    $pageslist = $web.Lists[$pageslistguid]
    $pagesurl = $pageslist.RootFolder.Url
    $pb = $web.Properties;
    $pb.Add("__PagesListName", $pagesurl);
    Write-Output($web.ServerRelativeUrl +" set pages list to " + $pagesurl);