SQL Server, convert a named instance to default instance?

Question

I need to convert a named instance of SQL server 2005, to a default instance.

Is there a way to do this without a reinstall?

---

The problem is, 2 out of 6 of the developers, installed with a named instance. So its becoming a pain changing connection strings for the other 4 of us. I am looking for the path of least resistance to getting these 2 back on to our teams standard setup.

Each has expressed that this is going to be, too much trouble and that it will take away from their development time. I assumed that it would take some time to resolve, in the best interest of all involved, I tried combing through configuration apps installed and didn't see anything, so I figured someone with more knowledge of the inner workings would be here.

Beseem

If you want to access a named instance from any connection string without using the instance name, and using only the server name : Look at my answer below. – Naomanaomi

Answer 1

As far as I know, no. One reason is the folder structure on the hard drive; they will have a name like MSSQL10.[instancename]

Wimple

Actually this is the response to this question but if you need change your instance name, please see Zasz answer. Please do not downvote because is not what you are looking for, check the question first. – Oshinski

Wrong. See answer below. – Dumbfound

was really hoping Tomas Beblar authored the below – Broadside

Answer 2

I also wanted to convert a named instance to default - my reason was to access it with just the machine name from various applications.

If you want to access a named instance from any connection string without using the instance name, and using only the server name and/or IP address, then you can do the following:

• Open SQL Server Configuration Manager
• Click SQL Server Network Configuration
• Click Protocols for INSTANCENAME you want to make available (i.e. SQLExpress)
• Right-click TCP/IP and click Enabled
• Right-click TCP/IP and go to Properties
  • Go to the IP Addresses tab
  • Scroll down to the IPAll section
  • Clear the field TCP Dynamic Ports (i.e. empty/blank)
  • Set TCP Port to 1433
  • Click Ok
• Go to SQL Server Services
• Right-click your SQL Server (INSTANCENAME) and click Restart

This will make the named instance listen on the default port. Note : You can have only one instance configured like this - no two instances can have same port on the IP All section unless the instance is a failover cluster.

Naomanaomi

This doesn't directly answer the original question but it answered my question from Google. – Crankcase

The difference between answering a question and solving a problem. Thanks a lot mate. – Jacki

Also exactly what I was looking for. For anyone having trouble connecting to the old default instance, I found this blog post stating that the MSSQLSERVER instance can't participate in dynamic port allocation, so you need to assign it an alternative port and connect to that specifically: blogs.msdn.com/b/karthick_pk/archive/2009/01/15/… – Bible

Note for windows 8 users, the Configuration Manager is not accesible via Start Menu. Must run it from command console as SQLServerManager11.msc (technet.microsoft.com/en-us/library/ms174212.aspx) – Vachill

Thanks @Naomanaomi this helped me a lot moving to SQL Server 2014 – Supremacist

This is one of the sweetest tricks I've ever seen in my development lifetime. – Sensate

After these changes, when I log in to the server through SQL Management Studio using localhost or . I still go to the back default server. Why? – Zinnes

Thanks Pay attention to "You can have only one instance configured like this", especially if you have multiple versions of SQL server running. – Brusquerie

Well done for the exact Solution that solve this and my problem. Very clean indeed! – Phenomenology

Answer 3

This is why a lot of companies store their applications' connection strings at the machine level instead of the application level.

Just take the connection string out of the source code entirely. Then have everyone put their connection string in their machine.config.

This has the added benefit of avoiding unnecessary app-specific environment logic, i.e. when you copy your application to the staging server, the staging server already "knows" what database to use.

Was

Answer 4

The only way to change the instance name is to re-install - uninstall and install as default instance.

Hasty

Correct. But that wasn't the question. – Dumbfound

Answer 5

A lot of times I'll use client alias to point an application at a different sql server than the ones it's connection string is for, esp. handy when working on DTS or an application with a hard coded connection string. Have everybody use a commonly named alias, use the alias in the connection string and point the alias’s on each dev box to the to the different instances. That way you won't have to worry about if the server is the default instance or not.

Hasty

Answer 6

You shouldn't ever really need to do this. Most software that claims to require the default instance (like Great Plains or Dynamics) doesn't actually.

If you repost with your situation (installed X, then Y, but need to accomplish Z) I bet you'll get some good workarounds.

Was

Answer 7

I think you can migrate your data from Sql Server without having default instance installed. You can just specify the port number of your Sql Server instance in Oracle Sql Developer and you can connect just using the server name, not using the server name and the instance. Like this: connect to "MYSERVER, 1433"

Fabrication