Setup SQL server for connection without instance name

user_2778405 0
Viewed 5781

I have a problem at my company where the SQL Server names that were set up are really inconsistent and i'm trying to fix it while redoing these servers. I basically have 3 servers with different names and I'd like to use the one that just requires the server name. For example:

Server 1:
ServerName: MDGSQLP01\MDG
InstanceName: MDG

Server2:
ServerName: MDGSQLP02
InstanceName:

Server3
ServerName: MDGSQLP03/MDGSQLP03
InstanceName: MDGSQLP03

So when I connect to all of these I have to use the server name obviously. I would rather that these all be named like Server2 so my connection string is always just, MDGSQLP0_ . Anyone know how this is done? Do I need to uninstall the server to be able to drop the instance name?

sql-server
1 Answers

To connect to all servers in a consistent way, instead of changing the instance name you can change the port number, so they all have the same value. If you make this new value the default port number (1433) you can connect without having to specify instance name or port number.

  • Open SQL Server Configuration Manager.
  • Select SQL Server Network Configuration.
  • Select the instance to configure.
  • Right click TCP/IP protocol .
  • Select Properties.
  • Select IP Addresses tab.
  • Change the port number to 1433.

NB: This means you can't use dynamic port numbers (which use the SQL Browser service to resolve a request using instance name to the relevant port number). This has a few disadvantages:

  • Port sniffers are more likely to spot the instance if running on a default port.
  • If some other application on the server uses this port before SQL does, SQL is not able to find a free alternative port to use.

However, these issues are generally not a significant concern.

user_3618420
Related