Monday, October 8, 2012

How To Remove A SQL Server Instance From A Multi-instance SQL Server

Today a colleague and me bumped into an issue where we had to remove a SQL Server instance from a SQL Server which ran multiple SQL Server instances.

Of course, there are TechNet/MSDN articles about just that but still we were cautious since while following the instructions, we soon bumped into this screen:
image

So we could click it and see what happened, but it didn’t feel right. It was a production SQL Server running another instance as well which wasn’t to be removed at all… So we could afford no risks here.

Gladly I ran my own test environment based on SSD. On my own SQL Server I added an another dedicated SQL Server instance (named Test), snapshotted the VM, removed the SQL Server instance and documented every single step of it. This posting is about just that.

Important to know is that the SQL Server instance which we had to remove didn’t host any SQL Database anymore besides the regular system databases.

Procedure

Before you start make sure there is a valid backup/snapshot/clone in place so you can go back when things go wrong!

  1. Log on to the SQL Server with an account which has local admin permissions;
  2. When it’s a virtual machine, make sure you connect through RDP based on session 0 (AKA physical console session);
  3. Make sure you’re the only one who is logged on to that SQL Server on that moment;
  4. Go to the Control Panel > Program and Features and select Microsoft SQL Server 2008 R2 (64-bit) > click on Uninstall/Change;
    image
  5. Now this screen appears:
    image
    Select the option Remove
  6. Setup Support Rules are installed/loaded now, just wait a moment;
    image
  7. Just give it a couple more seconds (depending on the speed of your server);
    image
  8. Now you can select the SQL Server instance to remove. Look at the dropdown box next to the option Instance to remove features from:
    image
    As you can see, both SQL Server instances are shown here. I select the SQL Server instance Test;
  9. Only select the features which are SQL Server instance specific. So DON’T select the Shared Features like Management Tools and so on…
    image
  10. Removal Rules are loaded now, be patient;
    image
  11. Now a summary is shown about the steps you’re going to make. Until now you can go back without doing any harm at all. So READ this screen and at Step 9 MAKE SURE you’ve selected the CORRECT SQL Server instance.
    image
    When you’re 100% SURE all is OK hit Remove. Now you’ve entered the Point-of-No-Return…
  12. After a while the in Step 9 selected SQL Server instance and it’s related Features are removed;
    image
  13. Click Close and you’re done Glimlach.

As you can see, the removal of SQL Server instance on a SQL Server running multiple SQL Server instances is a straight forward process. But like anything, prepare yourself, make a valid backup/snapshot/clone and know what you’re doing.

14 comments:

Tanya Melnychenko said...

Thanks!
You saved my time!

Benedict Dalay said...

Thank you very much. Very helpful.

Malik Lange said...

hi should it be a problem if the service is stopped because i have a SQL that is stopped and i cannot remove it

Malik Lange said...

hi should it be a problem if the service is stopped because i have a SQL that is stopped and i cannot remove it

Marnix Wolf said...

Hi Malik.

I am not sure whether the state of the SQL Server service influences the removal of that same instance. What error do you get?

Cheers,
Marnix

Ariel Alarcon said...

thanks!

H K said...

Thanks. That was very helpful.

goud anthati said...

thank you so much brother

Sergiu Munteanu said...

I think is you need to go to SQl Server Configuration Manager and Stop the instance that you want to remove before doing.

Alan Sidles said...

Nice work bud. Saved me some valuable time.

Larry Carr said...

Perfect and helpful post!

Akhil Kumar said...

Thanks

René Råen said...

Thanks for the timesaver!

Firandika Wildan said...

Thanks you sir.