November 27, 2017 at 9:36 am
Hello everybody.
I'm new with SQL Server administration and just start learning some of it concepts and characteristics. I'm playing with it on linux and docker and until now most of things are running smooth, not so many annoyances.
But there is this little thing I'm not able to modify, the server collation on docker.
First I tried to define the collation parameter when creating the container, so I tried this syntax:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<my_super_secret_password>' -e 'MSSQL_PID=Developer' -e 'SQLCOLLATION=Latin1_General_CS_AS' -p 2433:1433 --name sqldocker -v c:\sqldocker:/var/opt/mssql microsoft/mssql-server-linux:2017-latest
This didn't produce any error, the server installed correctly and the container run with no problems, but I noticed this message
The default language (LCID 0) has been set for engine and full-text services .
But when I connect and check the collation of the server, it says SQL_Latin1_General_CP1_CI_AS. Although I got no error on the container creation it seems the collation parameter was simply ignored (maybe by the.
I think there's no way to define it on install, is there?
Anyway, I tried to change it after installing/creating the container with mssql-conf. Tried several syntaxes, inside and outside the container, until I got one that seemed to work:
docker exec -it new_sqldocker /opt/mssql/bin/mssql-conf set-collation
It prompts me to enter the collation, but when I enter Latin1_General_CS_AS it gives me this error:
/opt/mssql/lib/mssql-conf/invokesqlservr.sh: line 13: sudo: command not found
I don't understand very well the docker archtecture but it seems there's no sudo on it. This seems more like a docker/linux incompatiblity, so I'm not sure if this is the right place to discuss such error.
But anyway, does anybody know or have a workaround to change de default server collation on docker?
Thanks in advance.
November 27, 2017 at 10:21 am
This page: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-environment-variables
list MSSQL_COLLATION as a parameter. Perhaps that's it?
November 27, 2017 at 12:15 pm
I knew I saw that list of parameters before but then I was not able to find that page again! Thanks a lot, it goes to my favorites right away.
But unfortunatelly that did not help. The page states:
| MSSQL_COLLATION | Sets the default collation for SQL Server. This overrides the default mapping of language id (LCID) to collation. |
But after creating the container setting this parameter the new server still has SQL_Latin1_General_CP1_CI_AS as collation.
I logged into the container to inspect the environment variables avec SET and I confirm that MSSQL_COLLATION is well set to Latin1_General_CS_AS. The installation was supposed to use this value but it seems that SQL Server does not support a different server collation; it simply ignores it without giving any error.
its not a major problem, definining the collation when creating databases works pretty well, but being able to define it as the default on the server would prevent future problems.
If anyone has a solution or other ideas I would be pleased to hear/try them.
November 27, 2017 at 1:04 pm
Changing the Collation of the server, post install, is quite a different task. It's much easier to do at install. Have a look here for post installation: https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation
I'm assuming from your above posts that you're trying to amend the current instance. If you're early doors, and you can, I'd suggest starting again (either by uninstalling SQL Server, or dropping and recreating the container), using the correct parameter name Steve suggested above.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 28, 2017 at 7:39 am
Thank you for your answer, Thom.
I tried Steve suggestion but without luck. It just seems the SQL Server installation simply ignores the collation parameter and sets it to its defaut SQL_Latin1_General_CP1_CI_AS. I'm starting to believe there is no way to have a different default collation for the server, specially at the installation.
In fact this may be a problem for us here because like that the masterdb and tempdb will be created with that collation, wich can have bad consequences for our applications.
At the moment we are just evaluating SQL on Linux (as well as on docker) in order to certify and support our applications on such environment. But if we can't circumvent a so basic characteristic maybe the actual state is not yet ready for us.
I'm just wondering if anyone else have the same issue, I don't believe we are the only ones who need this specific setup. Maybe SQL on linux is not so attractive yet?
November 28, 2017 at 7:50 am
Hmm, I'll create a container on the desktop and have a go myself quickly. I'll let you know how it goes.
edit: Sorry, not in a position to give it a try at the moment. Will have a play when i get in tonight.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 28, 2017 at 11:52 am
I may be wrong, but as far as i remember SQL_Latin1_General_CP1_CI_AS and Latin1_General_CS_AS are the same, only difference is Latin1_General_CS_AS is used in analysis services ...
November 28, 2017 at 12:12 pm
They shouldn't be. CI and CS are case insensitive and case sensitive.
November 28, 2017 at 1:46 pm
They definitely are not the same. As Steve explained, CI stands for Case Insensitve while CS stands for Case Sensitve, and that's why I need to modify it.
In our environment we have unique indexes on some char columns so the case is crucial for the uniqueness.
Well, I'm about to throw the towel. Like I said we're just experimenting on Docker but this small detail represents a major obstacle.
We'd better wait a bit longer and come back when things have improved.
Thank for all the help and insights, folks.
November 29, 2017 at 1:55 am
leandromdelima - Tuesday, November 28, 2017 1:46 PMThey definitely are not the same. As Steve explained, CI stands for Case Insensitve while CS stands for Case Sensitve, and that's why I need to modify it.
In our environment we have unique indexes on some char columns so the case is crucial for the uniqueness.
Well, I'm about to throw the towel. Like I said we're just experimenting on Docker but this small detail represents a major obstacle.
We'd better wait a bit longer and come back when things have improved.Thank for all the help and insights, folks.
Sorry I didn't get a chance last night. My attempt, however, would me on a Linux (Ubuntu 17.10) Container mind. If it's still in your interest let me know and as soon as o have some work/evening time i'll have a go and see how successful I am.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 29, 2017 at 7:20 am
Thank you very much for your help, Thom.
I think it will not be necessary, though. I talked with my team, explained my tests and findings and for the moment we are satisfied. In fact our applications work with no flaws with SQL Server on docker (since the collation is well defined when manually creating its databases).
The default server collation is just something we have to study more in order to fully understand its impact before certifying our programs on this environment, but at the moment we have no clients interested on this architecutre so we will perform more analysis in a later moment (if there is enough demand).
Once again I thank Thom and Steve and all of you for your suggestions and support.
March 21, 2018 at 9:56 am
This works for me REMEBER the " " placement 🙂
docker run -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD="Password1" -e MSSQL_COLLATION="Latin1_General_CS_AS" -p 1435:1433 --name sql3 -d microsoft/mssql-server-linux:2017-latest
March 21, 2018 at 11:12 am
Hello, wasim.
Yeah, you're right. There's been quite some time since I posted the question, but I eventually figured the double quotes issue.
It's weird that for me it worked slightly different:
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=P4$$w0rd' -e 'MSSQL_PID=Developer' -e "MSSQL_COLLATION=SQL_Latin1_General_CP1_CS_AS" --name sqldocker microsoft/mssql-server-linux:2017-latest
Single quotes for the other parametes worked correctly, but the for the collation itself I had to use double quotes.
I also noted that you quoted only de values but for me I needed to quote the whole parameter.
Thanks for you reply.
September 22, 2021 at 3:06 am
It's not the placement of ' ' or " " (you can use either or), but the problem is that MSSQL_COLLATION is the right parameter. When leandromdelima used SQLCOLLATION, it basically just ignored this parameter (since it was incorrect) and the default collation was untouched. You can check with any variation of ' ' or " " as long as MSSQL_COLLATION is used.
Thank you.
November 5, 2021 at 10:30 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply