In place SQL Upgrade error – 2005 to 2008

No, I don’t keep my blog updated.  Not stressing about it either.  I’ve got a forum to post my issues so that perhaps if others face them, they won’t repeat history.

I was working on an in place upgrade from SQL 2005 to SQL 2008.  The default instance’s upgrade ran just fine – a bit lengthy, but fine.  I restarted the server and everything was peachy.

I moved on to upgrading the named instance and wasn’t nearly as paranoid about the process.  It seemed to run a little faster than the default instance did – I put that to the fact the perhaps some shared files were already done in the earlier effort.  Eventually, though, the installed finished and showed failure.  Not cool, but I figured good ol’ Google would take care of me.

Wrong.

When you Google for my error code from the install logs:

Wait on the Database Engine recovery handle failed.

You get a number of results, but none of them addressed my specific error, which was:

Script level upgrade for database ‘master’ failed because upgrade step ‘provisionsystemaccounts.sql’ encountered error 15151, state 1, severity 16. 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.

This business of the “provisionsystemaccounts.sql” script error had only one result in Google and it didn’t help me.  I couldn’t find the script hidden on the computer.  I opened FileMon and couldn’t see it being referenced.

All of the results I did find talked about having changed the sa account to a different name and that you’ll need to rename it back to make things work.  They walked me through starting the SQL services without letting the upgrade scripts execute, and that was a significant help in the end.

Let me walk you through the solution and then I’ll explain what I think happened.

1. First, open your most recent SQL Error Log file
2. Look for “Starting provisionsystemaccounts.sql …”
3. Following that string will be a “Cannot find the user” value – this is your key

Using the following steps, start SQL and create the user / group:

1. Start SQL Services avoiding the upgrade scripts:
   net start mssqlserver /f /T3608
   Note: I would wager you actually want to start a named instance, so replace ‘mssqlserver’ with ‘mssql$<instancename>
2. Connect to SQL using the DAC.  The following command will get you to a sqlcmd prompt via a trusted connection:
   sqlcmd -E -A
3. Issue a ‘create login’ command to create the user / group you’re missing, followed by the all important GO
4. Type ‘exit’ in the sqlcmd prompt to return to the command line
5. Stop the SQL Server services you started earlier with:
   net stop mssqlserver (insert appropriate service name)
6. Run the Repair from the SQL 2008 installation media

The repair should run right along and fix everything for you.  If you encounter another error I suppose you can check to see if there is another “missing” login or you’ve run into something I didn’t see.

Now – why did this happen?  Well, in my situation it seems to be related to the fact that I upgraded the default instance first which also upgraded the Full Text Engine.  That, in turn, removed the Full Text Engine user group from the local groups.  In particular, I was missing “<ServerName>\SQLServer2005MSFTEUser$<ServerName>$MSSQLSERVER”.  Note the fact that it mentions the default instance, not the named instance!

The SQLServer2005MSFTEUser login refers to a local Windows group that is used for controlling access to the Full Text Engine.  I suspect that when I upgraded the default instance, the installer removed the group from Windows, but not necessarily from the named instance of SQL.  Once I recreated the group, granted my service account access to mirror the other similar groups, and added that login back into SQL via the DAC, everything went fine from there.

This took me 4 hours and I nearly did a full rebuild of the system databases, which would have set me back by a day or two.  I hope that I’ve saved you a few minutes or a few gray hairs with this one…

Advertisement