Edition downgrade for SharePoint Search Server Express 2010 from SQL 2008 R2 Enterprise to SQL 2012 Express

DowngradeThis is just a quick post to highlight issues with two SharePoint Search Server Express 2010 databases when downgrading from SQL 2008 R2 Enterprise to SQL 2012 Express (although applies to any Express version, 2008 R2 or higher).

The customer needed to swap out an Enterprise trial version of SQL and replace it with Express since the investment for Enterprise wasn’t forthcoming and neither was the investment for further immediate work in SharePoint.

They were however getting great value from Search within Search Server Express 2010 so didn’t want that to go anywhere, it was the entire companies home page!

When moving the databases from Enterprise to Express two errors were encountered during the database restore, one with the search Property database and one with the Web Analytics Report database.

After some research, I opted to correct the issue with the Property database but recreate the Web Analytics Report database.

Fix for the Property database

Follow the steps here –

http://social.msdn.microsoft.com/Forums/en-NZ/tfsadmin/thread/9365b0da-f5e9-4219-b8b2-498df11d921b

In Management Studio create a new query with the property DB selected and run this script –

SELECT

SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]

,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]

,[rows]

,[data_compression_desc]

,[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

 

For me is listed out the following tables using the data compression and vardecimal storage format –

  • MSSAlertDocHistory
  • MSSDefinitions
  • MSSDefinitions
  • MSSDocProps
  • MSSDocProps
  • MSSDocProps
  • MSSDocResults
  • MSSDocSdids

As per the fix, then run this script against these tables –

ALTER INDEX ALL ON <TABLE NAME>

REBUILD WITH (DATA_COMPRESSION = None);

Backup the database and then restore it over to the SQL Express instance and it worked.

For the Web analytics db, simply recreate the Web Analytics Service Application in Central Administration.

 

Errors during the SQL Express DB Restore

TITLE: Microsoft SQL Server Management Studio

——————————

Restore of database ‘Search_Service_Application_PropertyStoreDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)

——————————

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

Database ‘Search_Service_Application_PropertyStoreDB’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=909&LinkId=20476

——————————

BUTTONS:

OK

——————————

 

and

TITLE: Microsoft SQL Server Management Studio

——————————

Restore of database ‘WebAnalyticsReport’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended)

——————————

Database ‘WebAnalyticsReport’ cannot be started in this edition of SQL Server because it contains a partition function ‘DateIdPF’. Only Enterprise edition of SQL Server supports partitioning.

Database ‘WebAnalyticsReport’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 905)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=905&LinkId=20476

——————————

BUTTONS:

OK

——————————

Moral of the story

SQL Enterprise automatically enables features in tables and rows so downgrading editions can always be a pain.

Senior consultant and company director at The Full Circle. Passionate about automation scripting, end user experience and cloud technology.

Comments are closed.