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.
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')
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.
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.
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.
Once the change is successful, you will see the information below.
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.
Next Steps
- Change the SQL Server Collation as per your requirements.
- Read more about SQL Server on Linux Tips
