Tuesday 8 October 2013

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)

13 comments:

  1. Thanks for your solution
    That works correctly...

    ReplyDelete
  2. think this solution is better than more wide spreaded advice of using regedit
    thans

    ReplyDelete
  3. Had issues upgrading SQL2005 to SQL2014. Had this error and searched quite a bit of time in KB. Of all the possible solutions posted in the web, this is by far the BEST and clean solution. It works like a charm.
    Thanks

    ReplyDelete
  4. This was *extremely* helpful! Thank you very much!

    ReplyDelete
  5. Nowadays, the challenge is no more the unavailability of resources, its exactly the opposite, ie, over availability (almost bombardment) of Data. There are too many choices and it takes a lot of effort to zero down on resources that are actually useful.

    Good to know that it had been helpful for others too. :)

    ReplyDelete
  6. Thanks a lot. It worked perfectly, you saved my migration.
    I used this in a cluster node upgrade process that got stuck in the middle. From 2008 R2 to 2012.

    ReplyDelete
  7. Eres la verga, muchas gracias
    Nos has salvado el culo :)

    ReplyDelete
  8. Awesome .. It worked after trying several work arounds.

    ReplyDelete
  9. SImple and Sooperb.. worked like a charm..thanks a alot

    ReplyDelete
  10. Thank you all for feedback ..

    Good to know that it had been helpful for you..

    ReplyDelete
  11. Sorry I can't find the file Datastore_Discovery.xml in windows 10. do you know where the location of this file

    ReplyDelete
  12. well explained and worked for me. thanks for sharing

    ReplyDelete
  13. 10 years later and this post continues to be helpful! Thanks a lot!

    ReplyDelete