Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Change SQL Server Instance Name Running on a Linux Server


By:   |   Updated: 2018-05-25   |   Comments (2)   |   Related: More > SQL Server on Linux

Problem

Sometimes we need to rename a SQL Server instance name to make it conform to our naming conventions for our environment. If we change a Linux server name, then also we need to change the SQL Server instance name to avoid any issues. In this tip, we will cover how to rename a SQL Server instance running on Linux.

Solution

The process to change a SQL Server instance name has not changed from how it is done on a Windows based SQL Server. We need to drop the existing SQL Server instance name using system stored procedure sp_dropserver and then we need to add the new SQL Server instance name using system stored procedure sp_addserver.

I need to change the instance of SQL Server because I had already change the name of the Linux server where SQL Server is running. I will show you the step by step process in which I have described how to change a SQL Server instance name in the below sections.

You need to be careful when changing a SQL Server instance name to make sure it does not impact anything relying on the name.

NOTE: BE SURE TO TEST THIS SOLUTION IN A LOWER LIFE CYCLE FIRST. DO NOT MAKE CHANGES IN PRODUCTION WITHOUT PROPER TESTING IN LOWER LIFE CYCLE ENVIRNOMENTS.

Rename SQL Server Instance Running on a Linux Server

Step 1:

The first step is to check the name of the existing Linux server and SQL Server instance name. Launch the PuTTY console to establish a connection to the Linux server where SQL Server is installed. You can also directly log in to the Linux server and make a connection. I have used PuTTY to make the connection. Enter your login name and password to establish the connection. Once connected to the Linux server, we will connect to the SQL Server instance.

Connect to the SQL Server instance using the sqlcmd utility and enter the password once prompted.

sqlcmd -S192.168.209.133 -Usa

Check the SQL Server instance name once connected to SQL Server by running the below command.

SELECT @@SERVERNAME

Exit from the sqlcmd prompt to check the Linux server host name by typing the below code command.

hostname

You can see below the SQL Server instance name is SQLServerRHEL whereas the Linux host name is techyrhel.

sqlcmd query and output

Step 2:

Now we must change the SQL Server instance name to match the Linux server name.  Again, connect to SQL Server instance using sqlcmd and run the below command to connect to SQL Server.

Pass your SQL Server instance IP and sa password while password prompt for sa account.

sqlcmd -S192.168.209.133 -Usa

Once connected to the SQL Server instance, we will drop the existing SQL Server instance name from the system metadata and then we will add the new name using system stored procedures. Once connected to SQL Server instance then you need to run the below commands to make these changes.

Drop the existing SQL Server instance name SQLServerRHEL by running the below command.

sp_dropserver SQLServerRHEL

Add new instance name of techyrhel to the system metadata by running the below command.

sp_addserver techyrhel, local

See the below image where we have executed the above commands successfully.

sqlcmd query and output

You can check the SQL Server instance post running the above commands, but this might show you the old name. We need to restart the SQL Server service to apply these changes to reflect the new name.

Step 3:

Here, we will restart the SQL Server service using the below command.

Restart SQL Server service.

Systemctl restart mssql-server

Once you will execute the above command it will ask you to enter your password. Enter your password to proceed with this operation. Once the SQL Server service restarts, you can check the SQL Server instance name again to validate the changes. Connect to the SQL Server instance on your Linux terminal using the sqlcmd utility.

Connect to SQL Server with sqlcmd.

sqlcmd -S192.168.209.133 
-Usa

Enter the sa password, once connected again run the below command to check the SQL Server instance name.

Check the SQL Server instance name.

SELECT @@SERVERNAME

Once you execute the above commands you will get the new name of the SQL Server instance. You can see this in the below screenshot.

sqlcmd query and output

Now you have changed your SQL Server instance name running on a Linux server.

Next Steps


Last Updated: 2018-05-25


get scripts

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 20, 2018 - 12:40:55 PM - Greg Robidoux Back To Top

Hi Ross,

I think this article just shows how to rename the default instance if the server name is renamed. Not specifically a named instance.

-Greg


Monday, November 19, 2018 - 10:39:38 AM - Ross Presser Back To Top

 This does not permit you to use a named instance on SQL Server for Linux. For example you cannot use myhostname\SQLEXPRESS. There is only one instance per Linux host; named instances are only permitted on Windows. 

https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server?view=sql-server-2017 , which discusses named instances, starts with "APPLIES TO: SQL Server (Windows only)" at the top of the article.

 


Learn more about SQL Server tools