I've installed 3 new named SQL Server 2019 instances on a standalone server. The named install seems to have defaulted to computername\network name. Ex: BHMDB06\sseprod. During the install I had it as SSE\SSEPROD. I have since changed the name using:
sp_dropserver 'Old Server Name\Instance_name'
sp_addserver 'New Server Name\Instance Name','local'
select @@servername and
sp_helpserver both show the new name. However, right clicking on the instance and selecting properties, the Name in General still shows the old name.
I try to connect to the new server's name, but get
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I can connect into the instance using
computername\sse, but not the new name.
To troubleshoot this, I have done the following:
- No firewall is active.
- Created a hosts file with IPs from DNS.
- Checked Allow remote connections.
- SQL Browser service is running.
- I can ping the server.
- Does not connect using the IP Address in DNS.
- All protocols enabled.
I would greatly appreciate any other guidance on how to connect to this instance.
I have created a Hosts file locally with an assigned network DNS for the network name of SSE0. SQL browser is running. As stated the instance will connect using computername\SSE0, but not SSE0\SSEPROD.
With the network name of the install as SSE0 and the Instance name of the install being SSEPROD, I don't know why it is not connecting through SSMS with SSE0\SSEPROD. Even after renaming it. ,\SSE0 will connect locally. servername\SSE0 will connect.
This will be a part of a migration and the instance I will be moving here is the same name without the 0.
When I login using the IP address of the server, it connects with the first instance I installed.
.\sse0 works locally, but not externally. SSE0\SSE0 does not work. Ultimately, I need to be able to connect into SSE0\SSEPROD what will be used as a migration target.
It's just wrong to use the machine name when it's not actually the machine name.
@@SERVERNAMEshould always match the real machine name.
Understood. I guess what I am trying to do is make this standalone server with 3 instances behave like a clustered server with unique named instances. This is a migration that they want to move from a Cluster to a standalone.
There are about 150 databases between the 3 instances.
I am using SQL Server 2019 Enterprise Edition and Windows 2019 Server.