6

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 :).

Community
  • 1
  • 1
kinkajou
  • 3,664
  • 25
  • 75
  • 128

4 Answers4

13

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

Joe Perkins
  • 160
  • 2
  • 6
5

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

Lee Grissom
  • 9,705
  • 6
  • 37
  • 47
  • I left the `TCP Dynamic Ports` set to the default and instead set `TCP Port` to the default port 1433 for the default instance. Very similar to your answer. This worked for me and allowed me to simply enter `.` in the `Server Name` field in SSMS. Love that! :) – Chiramisu Dec 01 '17 at 18:35
4

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • For others looking for the answer, this answers the question as asked, but to solve the intent of the question, see other answers to this question, or go to https://stackoverflow.com/a/11921896/284598 – GaTechThomas May 09 '19 at 16:32
  • @GaTechThomas: this is ***NOT REALLY*** renaming the named instance to a (nameless) default instance. I stand by my answer - if you **REALLY** want to rename your instance, you have no other option than to do a reinstall. Period. – marc_s May 09 '19 at 17:55
  • Thanks @mark_s... I found out the hard way that things like @@servername are affected by using multiple instances. When you have @@servername in build scripts (legacy decision), it may be worth just rebuilding the server. – GaTechThomas May 20 '19 at 21:42
3

"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

ilmenite
  • 199
  • 1
  • 3
  • 11