tim laqua dot com Thoughts and Code from Tim Laqua


Upgrading from SQL 2008 R2 Enterprise to SQL 2012 Business Intelligence

With all the new licensing considerations in SQL 2012, everyone's busy trying to figure out exactly what the cheapest license they can get by with is. For one of our instances, we decided rather than Enterprise, we could run the Business Intelligence edition to save some cash. That upgrade path is explicitly listed in the Supported Version and Edition Upgrades documentation.

What happened?

We clicked through the upgrade wizard as usual and it got through about 85% and then prompted an error:

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

We clicked ok, then it finished and the results showed that the SQL engine and a few other features failed the upgrade. Great. Off to the event logs:

Then take a look at the error log and we have this at the end:

2012-06-12 14:25:16.74 spid5s      Error: 926, Severity: 14, State: 1.
2012-06-12 14:25:16.74 spid5s      Database 'sysutility_mdw' cannot be opened. It has been 
                                   marked SUSPECT by recovery. See the SQL Server errorlog
                                   or more information.
2012-06-12 14:25:16.74 spid5s      Error: 912, Severity: 21, State: 2.
2012-06-12 14:25:16.74 spid5s      Script level upgrade for database 'master' failed because 
                                   upgrade step 'msdb110_upgrade.sql' encountered 
                                   error 926, state 1, severity 14. This is a serious error 
                                   condition which might interfere with regular operation 
                                   and the database will be taken offline. If the error happened 
                                   during upgrade of the 'master' database, it will prevent the 
                                   entire SQL Server instance from starting. Examine the 
                                   previous errorlog entries for errors, take the appropriate 
                                   corrective actions and re-start the database so that the 
                                   script upgrade steps run to completion.
2012-06-12 14:25:16.77 spid5s      Error: 3417, Severity: 21, State: 3.
2012-06-12 14:25:16.77 spid5s      Cannot recover the master database. SQL Server is unable to 
                                   run. Restore master from a full backup, repair it, or rebuild 
                                   it. For more information about how to rebuild the master 
                                   database, see SQL Server Books Online.

Well - now you're probably off searching for error fragments and rebuilding your master database, etc. We'd suggest you scroll up a little further and figure out why that sysutility_mdw db couldn't be opened. That's the actual error, the rest of the error is the standard pile of garbage it spits out when a database in script upgrade mode (master) has the script crash - basically, ignore that, it's worthless.

Now we scroll up to the beginning where it tried to bring up that database it couldn't open (sysutility_mdw):

2012-06-12 14:24:35.08 spid21s     Error: 909, Severity: 21, State: 1.
2012-06-12 14:24:35.08 spid21s     Database 'sysutility_mdw' cannot be started in this edition of
                                   SQL Server because part or all of object
                                   'sysutility_ucp_smo_properties_internal' is enabled with data
                                   compression or vardecimal storage format. Data compression and
                                   vardecimal storage format are only supported on SQL Server
                                   Enterprise Edition.
2012-06-12 14:24:35.09 spid21s     Error: 905, Severity: 21, State: 1.
2012-06-12 14:24:35.09 spid21s     Database 'sysutility_mdw' cannot be started in this edition of
                                   SQL Server because it contains a partition function 
                                   'sysutility_ucp_aggregation_type_partition_function'. Only
                                   Enterprise edition of SQL Server supports partitioning.
2012-06-12 14:24:35.10 spid21s     Error: 933, Severity: 21, State: 1.
2012-06-12 14:24:35.10 spid21s     Database 'sysutility_mdw' cannot be started because some of
                                   the database functionality is not available in the current 
                                   edition of SQL Server.

Oh dear. What happened is we're smack in the middle of an upgrade (the MDF is actually marked as a 2012 db at this point), but the engine won't attach it because it's using an Enterprise Only feature (actually it's using a few of them). Further, your instance is stuck in that terrible script upgrade mode and won't come out of it until it can finish its work. What's the issue here? Well, either it made a list of the databases that it upgraded earlier and is iterating over them, or the script isn't checking to see if each db is online before trying to run against it. Doesn't really matter, there's only one quick fix here:

  1. Copy all of the data and log files for that database on to a server with SQL 2012 Enterprise (or probably Developer) Edition installed
  2. Attach the database
  3. Remove the unsupported features
  4. Detach the database
  5. Copy the data and log files back to their original location on the Business Intelligence Edition server
  6. Start the SQL service again

We were a little surprised that the upgrade wizard didn't flag that database as unsupported in the GUI portion of the install - It does a few thousand checks already, seems they just forgot a few important ones that only come in to play now that they're turning off commonly used features.