How to Fix a failed In-Place Upgrade/Install from SQL Server 2008/R2 to SQL Server 2008 R2/2012 -- - MSSQLSERVER.INACTIVE

While working on a project ,I've come across situations where I was trying to upgrade SQL Server 2008 R2 to SQL Server 2012. The upgrade failed with error message below. It doesn't matter what version of SQL you're on, upgrading to etc...this issue can pretty much be resolved using this process

--------------------------------------------------------------------------------

The Instance ID ‘MSSQLSERVER’ is already in use by SQL Server instance ‘MSSQLSERVER.INACTIVE’. To continue, specify a unique Instance ID.

--------------------------------------------------------------------------------

Problem/Sympton: I went to upgrade a perfectly working SQL 2008 R2 standard server (which was using a Default Instance) to SQL 2012. The Setup failed halfway through with a random error connecting to SQL Analysis Services (your failure could be different)...then it indicated all I had to do was fix the problem and re-run my SQL 2012 Upgrade...this wasn't true :-)

Brief Background of upgrade process:

Here we have an orphaned SQL instance named MSSQLServer.Inactive. Let’s understand the upgrade process here. During your upgrade, there are two stages:

Pre-PointOfNoReturn

Post- PointOfNoReturn

Point of No Return is a point at which 2008 R2 will take over the instance name and SQL Server 2008 will be then uninstalled.

The REAL Resolution Steps:

Search your machine for files called Datastore_Discovery.xml (depending on how much you've got or done, you might see many of these...just open the most recent one and you'll be fine

Open Datastore_Discovery.xml in an editor...given the formatting, what worked for me was in Visual Studio 2008/2010 and clicking Edit, Advanced, Format Document (this arranges the XML nicely)

What you're looking for is all places where MSSQLServer.Inactive exists in the document as the Instance ID...below is a sample of how the line starts:

<Instance Urn="Machine[@ID='IWE']/Product[@ID='SQLVNEXT']/Instance[@ID='MSSQLSERVER.INACTIVE']" ID="MSSQLSERVER.INACTIVE" Name="MSSQLSERVER.INACTIVE" ........

Scroll along the lines around the above one and find the following value field: ProductCode="{9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}"

There might be multiple ProductCodes, depending on how many times the <Instance URN...> line lists MSSQLServer.Inactive, so make sure to find all ProductCodes...BUT BE CAREFUL that you ignore any VALID instances of SQL. Only look for the ones under MSSQLServer.Inactive entries.

Now open a command line

For each of the ProductCodes that you found for the Inactive SQL Instances, type the following:

msiexec /x {9FFAE13C-6160-4DD0-A67A-DAC5994F81BD}

Do this for each Product Code that relates to an Inactive Instance

Once complete, go back to your SQL installation media and run Setup

Once the main splash screen launches, click on Tools, then Installed SQL Server features discovery report

This will launch a web page listing the SQL Instances and all features per instance

If you still see any MSSQLSERVER.INACTIVE there, you missed one of the ProductCodes and you need to double check. Otherwise, all of the orphaned SQL Instances should now be gone, and you can either Add Features or do a clean Default Instance reinstall

Note : /x means uninstall

I hope this will help you for sure!

Brgds,

Chhavinath Mishra

Sr. Database Administrator

Microsoft Certified IT Professional (MCITP)