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 :).
-
2This is why named instances are preferred...OMG Ponies– OMG Ponies2011-10-20 04:29:41 +00:00Commented Oct 20, 2011 at 4:29
-
I don't think its possible without a reinstall but I cannot be 100% sure. The question is why do you want to change the default instance? Why won't named instance work for you?shashi– shashi2011-10-20 04:38:21 +00:00Commented Oct 20, 2011 at 4:38
-
@sassyboy I wanted to work with only ip :)kinkajou– kinkajou2011-10-20 11:21:56 +00:00Commented Oct 20, 2011 at 11:21
Add a comment
|
4 Answers
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
Comments
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.
- Open SQL Server Configuration Manager.
- Navigate to: SQL Server Network Configuration -> "Protocols for XYZ" treenode.
- Right-click on the TCP/IP item in the right pane.
- Choose Properties menu item.
- On Protocol tab, ensure Enabled is set to Yes.
- On IP Addresses tab, scroll to bottom, and enter 1433 in IPAll->TCP Dynamic Ports field.
- Then restart your named instance.
- 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)
1 Comment
Chiramisu
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! :)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.
3 Comments
GaTechThomas
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 stackoverflow.com/a/11921896/284598
marc_s
@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.
GaTechThomas
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.
"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.
