Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to change SQL Server Collation on Linux


By:   |   Last Updated: 2017-04-28   |   Comments   |   Related Tips: More > SQL Server on Linux

Problem

Microsoft released SQL Server to run on Linux servers and some of the administration tasks on Linux are different than on Windows.  One thing you may need to do is change the SQL Server collation and in this tip we walk through how this can be done for SQL Server on Linux.

Solution

Collations are used to specify the rules for how character strings are sorted and compared. This can make a difference on the behavior of queries and therefore there may be a need to change which collation is used.

In previous tips we explored Changing SQL Server Collation After Installation and How to change server level collation for a SQL Server Instance for SQL Server running on Windows and now we will look at how to do this for SQL Server on Linux.

To start with, we will use an installation of SQL Server on Red Hat.  You can read this tip Installing SQL Server vNext CTP1 on Red Hat Linux 7.2 to install SQL Server.

Once we have installed SQL Server on Linux, let's check the SQL Server version as shown below.

SQL Server Version

When installing SQL Server on Linux we don't get an option to select the instance collation, it is installed using the default value.

Let's check the SQL Server instance collation.

select convert(varchar,SERVERPROPERTY('Collation')

SQL Server Version

We can see the SQL Server collation is SQL_Latin1_General_CP1_CI_AS. Now suppose we want to change the SQL Server collation to Latin1_General_CI_AS. Follow the steps below.

Step 1 - Stop the SQL Server Services

First stop the SQL Server services using the below command.

sudo systemctl stop mssql-server

If we don't stop SQL Server services first and try to make the change we will get the below warning message.

SQL Server Version

Step 2 - Change the SQL Server Collation using mssql-conf

Run the "set-collation" option and follow the prompts to enter the collation.

sudo /opt/mssql/bin/mssql-conf set-collation

We will use Latin1_General_CI_AS as follows.


SQL Server collation change

The mssql-conf utility will try to change the databases using the specified collation and restart the service. If there are any errors, it will roll back the collation to the previous value.

SQL Server collation change

Once the change is successful, you will see the information below.

SQL Server collation change

Step 3 - Start the SQL Server services and verify collation

--Start SQL Services
sudo systemctl start mssql-server

--Verify SQL Server Collation after change
select convert(varchar,SERVERPROPERTY('Collation')

As we can see that the SQL Server collation is changed successfully to the desired collation.

SQL Server collation change
Next Steps


Last Updated: 2017-04-28


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

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.



    



Learn more about SQL Server tools