SharePoint 2010–Don’t downgrade SQL!

At my current client we’d hurriedly installed SQL so that we could get a SharePoint 2010 base environment up and running.  They didn’t have al their licensing requirements finalised so we went for the least risky strategy of using an evaluation version of SQL while they sorted that out.

Much later, we came to almost go-live and the portal died

– Cannot connect to database

Our evaluation had expired!!!

So, next – simple install of SQL Standard Edition.

Hmmm, seems OK, but hang on a minute why doesn’t search work?

It looked very much like the property store database had died but looking further into it… It would seem that if SharePoint detects and Enterprise version of SQL (as per our evaluation) it uses data compression which is not supported in Standard edition.

If that’s the case you’ll get a message something akin to:

Database ‘[name]’ 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.

So, what now.  Well I gave up trying to find a way to transform the data and decided it would be easier to just recreate the search application.  That process had it hitches too:

  1. The search service application delete just hung (I waited, believe me I waited)
  2. Using PowerShell was no different
  3. Trusty STSADM saved the day!

For reference the PowerShell command was:

  • Get-SPServiceApplication |?{$ -eq “Search Service Application”}
  • RemoveSpServiceApplication –Identity [id from above] –removedata

The STSADM command:

  • STSADM –o deleteconfigurationobject –id [id from PS command above]

Recreated search, waited for a crawl to complete and all was good again.  I’m glad the search config was standard!!!


2 thoughts on “SharePoint 2010–Don’t downgrade SQL!”

  1. Andrew – Thank you for posting this information. I’ve been trying for a couple of days to get this resolved. We had the exactly same thing happen. You save us!

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s