4

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'
go
sp_addserver 'New Server Name\Instance Name','local'
go

After reboot, 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 .\sse or 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.

Server setup


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. @@SERVERNAME should 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.

1

3 Answers 3

0

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.

I previously answered this, but in a different way, though I believe this fits your needs based on the actual needs you've noted above.

I also wanted to make a few things clear:

  1. Using Add/Drop server does not actually change the computer name or connection strings in general.
  2. You can only have one instance listening on a single ip:port combination.
  3. Multiple instance of SQL Server on the same computer is generally a bad decision.
0

I have an article on dba.se titled What are valid connection strings for SSMS login box?. The article gives you a general overview of what you can expect when connecting to a multi-instance SQL Server on a Windows Server.

When installing Microsoft SQL Server on given Windows Server, there are some things you can change and other things you should not change.

sp_dropserver is not a procedure to use lightly as it can have undesired effects:

sp_dropserver to change the local server name may cause unintended effects or unsupported configurations.

Reference: sp_dropserver (Microsoft Learn | SQL Server)

That aside, lets kneel into the possible issues you are encountering and have a look at possible solutions.

I'll take the following statements you made for granted and build up my answer around those facts:

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

General SQL Server Instance Installation

When installing SQL Server on a Windows Server, the installation will retrieve the host name from the current Windows Server and use that as the first part of any connection string when you connect locally on the Windows Server. The second part of the connection string will be the instance name you provided during the installation.

Local Connection String

E.g. If your sever has an original host name of MYSERVER and you installed the instance with the name SSE0 then the connection string to use locally when using SQL Server Management Studio will be: MYSERVER\SSE0.

The default protocol used during a local connection will be the Shared Memory protocol. This is because it is more efficient to use an in-memory connection than to use the TCP/IP stack to connect.

Remote Connection String

Given the facts stated about your configuration the remote connection string (from a client computer to your SQL Server instance) should be the same: MYSERVER\SSE0

Verifying SQL Server is Listening

Your SQL Server instance will be listening on a port with the IP address of your computer or in some cases on the generic 0.0.0.0 address, together with a port number. If you run nestat -abon in a Command Prompt, then you will find something like this:

...
  Proto  Local address          Remote address         State           PID
  TCP    0.0.0.0:3138           0.0.0.0:0              LISTENING       26580
 [sqlservr.exe]
...

If you take the PID from your output for nestat -abon and open up Windows Task Manager, then you should find a process with that PID and if you add the column imagepathname to the list of columns displayed in Task Manager, then you should have a line that contains this:

C:\Program Files\Microsoft SQL Server\MSSQL15.SSE0\MSSQL\Binn

The SSE0 after the dot is the instance name of the SQL Server as it was initially installed. In your case you should possibly see a MSSQL15.SSE0.

If this is the case, then your SQL Server instance is waiting for connections on all IP addresses on a given port (e.g. 3138 in my case) and any remote connection to YOURSERVER\SSE0 should be redirected via the SQL Server Browser to the correct instance and the correct port.

Possible Issues

Going through your list of things you have checked...

  • No firewall is active.

Are you really sure that the firewall is deactivated? If not add a rule to allow connections on the port that is visible in the output of the nestat -abon command.

  • Created a hosts file with IPs from DNS.

The hosts file is only valid for local connecitons. If you are connecting remotely, then you will have setup an alias or a hosts entry for the (alternate) Windows Server in your DNS.

  • Checked Allow remote connections.

No issues here. But did you restart your instance after changing the settings?

  • SQL Browser service is running.

This should be ok.

  • I can ping the server.

A remote ping is a good thing, but can you TELNET into the server name and port given in the output of the nestat -abon command? E.g.

C:\> telnet YOURSERVER PORT
  • Does not connect using the IP Address in DNS.

Because you have multiple instances on your Windows Server, you will have to supply the port number along with the IP address or the instance name. Otherwise the SQL Server Browser Service has no way of knowing which instance to connect to. See my article for valid connection strings.

E.g. 10.0.2.33\SSE0 or 10.0.2.33,3138 taken from my example output

  • All protocols enabled.

Good.

In some cases you might have to use the fully qualified domain name as the server name and add the SQL Server instance:

YOURSERVER.DOMAIN.TLD\SSE0

e.g.

YOURSERVER.company.com\SSE0

Hint: kerberos

Let me know if I can be of any further assistance.

0

If you have a reasonable number of clients, then perhaps creating aliases at the client side can be an option. That allows you to "manipulate" not only the machine name (as in DNS and hosts) but also the instance part. - Tibor Karaszi

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.