CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
kinkajou
kinkajou

Reputation: 3728

Changing SQL Server named instance to default instance

This question is in regard to instances. That I asked earlier. Is it possible to change default instance in SQL Server without uninstalling and re-installing? How do we do that if possible? If not possible why is that? Thank you for help :).

Upvotes: 6

Views: 17221

Answers (4)

ilmenite
ilmenite

Reputation: 199

"If you want to access a named instance from any connection string without using the instance name, and using only the server name, then you can do as follows :

To access a named instance with just the host name - go to SQL Server configuration Manager, and Enable TCP/IP for that named instance, Right-click and go to properties, and in the IP tab, go to IP All section and make TCP Dynamic Ports blank, and make TCP Port 1433 (which is the default)

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."

See Zasz's answer. He explains with screenshot how you can set which server listens on the default port.

https://stackoverflow.com/a/11921896/7447851

Upvotes: 3

Lee Grissom
Lee Grissom

Reputation: 9953

This might solve your problem: https://kohera.be/blog/sql-server/make-named-instance-look-like-default-instance/ (Full credit to Stefan, but I'll paraphrase below in case his blog disappears)

The idea is to make your named instance appear as the Default instance.

  1. Open SQL Server Configuration Manager.
  2. Navigate to: SQL Server Network Configuration -> "Protocols for XYZ" treenode.
  3. Right-click on the TCP/IP item in the right pane.
  4. Choose Properties menu item.
  5. On Protocol tab, ensure Enabled is set to Yes.
  6. On IP Addresses tab, scroll to bottom, and enter 1433 in IPAll->TCP Dynamic Ports field.
  7. Then restart your named instance.
  8. You should now be able to connect to your named instance via:
    • (local)
    • localhost
    • . ( <-- that's a dot )
    • 127.0.0.1
    • named instance name (e.g. mymachine\myinstance)

enter image description here enter image description here enter image description here

Upvotes: 5

Joe Perkins
Joe Perkins

Reputation: 170

All instanses are named but only one runs as default. Remove the port from the default instance and set the port of the named instance you want as default to 1433 in SQL Server Config Manager under SQL Server Network Config

Upvotes: 13

marc_s
marc_s

Reputation: 754200

No.

If you want to change the name of your instance, or make it the default instance, you have to reinstall. No way around that.

Upvotes: 5

Related Questions