Wednesday, May 29, 2013

Virtual Infrastructure SQL Server won't start after SP Install

As your virtual infrastructure is growing and changing rapidly a result is that the configuration of your SQL Servers could be changing also. Virtual infrastructure growth can result in additional drives and drive letter changes for our SQL servers.

Unfortunately, if the default Data and Log paths are not updated when the drive letters change, there will be errors after installing (or uninstalling) a Service Pack or Cumulative Update. The GUI portion of the install will complete successfully, but there are updates that occur on the initial service start after the GUI portion of the install is complete. One update that occurs that that initial service start is database upgrades. If your default paths for data and log files are not correct the upgrade of the master database will fail and the SQL Server service will crash.

In a production setting, this can induce fear, panic, and mass hysteria (or at the very least mass Google searching, which is likely how you arrived at this page to begin with). However, the solution is pretty simple as the default paths are stored in the Windows registry.

Launch Registry Editor (regedit.exe) and navigate to the following section of the registry tree:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\{SQL Version}\MSSQLServer

Note: If you are running a 32-bit version of SQL Server on a 64-bit OS, you'll need to navigate to the Wow6432Node tree (HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\{SQL Version}\MSSQLServer)

SQL 2000 {SQL Version} = MSSQLServer
SQL 2005 {SQL Version} = MSSQL.1
SQL 2008 R2 {SQL Version} = MSSQL10_50.MSSQLSERVER

Then update the paths specified in the following keys:

Once the paths are correct, the master database upgrade will complete successfully (as SQL Server will be able to create the temporary files in the default paths) and once all other system and user DBs are upgraded you should be back up and running.

Note: The database upgrades will take some time if you have a lot of user DBs, but you can monitor the ERRORLOG to observe the progress.

No comments: