5

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?

Improve this question
5
  • 1
    I would highly discourage renaming instances/servers. Backup all your DB's, install a new instance with your desired name, and restore all the DB's. Commented Nov 2, 2018 at 19:24
  • The only way to change the name of the instance is to uninstall SQL Server and reinstall it with the new instance name. That will mean downtime, and you'll need to make sure you have back ups of all your databases, and have scripted out the system ones Commented Nov 2, 2018 at 19:27
  • 1
    You can connect by port number instead of instance name. It's easier to change port number if required so that all instances use the same number. However dynamic port numbers are preferable from a security perspective. If you change the port number to the default port (1433), I believe you can connect using just the server name as in case #2 (I've not tried this personally / I've only ever used MSSqlServer (default instance name) on 1433. Commented Nov 2, 2018 at 19:31
  • Totally a good thoughts all, but before I do that i'm wondering will it fix my problem? How does one set up a server like MDGSQLP02? There's not even a `\` character in the server name. If create a new instance can I just not set a name for the instance? Commented Nov 2, 2018 at 20:11
  • You could set alias names for your instances. So setting the alias name to one common name, and then in your connection string you would just change the server and the instance name will remain same for all. Commented Jul 13, 2022 at 16:15

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.