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!


About Cloud Architect Joran Markx
I have been working on Microsoft Technology since 2003. In addition to (lead) developer and software architect, I am certified Microsoft Specialist and active in design and implementation of Hybrid Cloud platforms. In 2011 I have achieved a Master of Science in IT Management. This made me capable to solve complex issues from the business in an efficient and structured way. As Cloud Architect I am working on various challenging projects with a variety of clients. Within my organisation I fullfill a leading role when it comes to internal development and sharing of knowledge. My goal is to provide reliable and predictable services to our clients with a strong focus on the results achieved for the organisations I am working for.

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

  1. Greybear says:

    Nice article, just helped me out.
    One more thing: Webanalytics databases could be partitioned. That must be handled too.

  2. Mike says:

    Thank you. Huge help when we “upgraded” from Trial to Standard!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: