This 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.