Using Azure SQL for Jira server 7.13 - Collation issue

[matthew.mellifont]

Situation:

I built our Jira server instance in azure (using windows server 2016 vm and Azure SQL DB). All went smoothly until the instance health check ran post install and I found that the default Azure SQL db collation 'SQL_Latin1_General_CP1_CI_AS' is not supported.

The only way I have found to change this is to export the db and load it onto an instance of on-prem SQL, then change collation and migrate back into Azure SQL. This is all well and good if you have an on-prem sql instance, which unfortunately I do not. 

Questions:

1) What is the impact for Jira 7.13 using this database collation, safe or not? 

2) As I have only just built this instance, should I just tear it down and start again? or is there an easier way to change and rebuild the DB once the installation has completed?

Thanks in advance for any insight into this!

Matt 

[Johan Soetens _Dumblefy_ · Accepted solution]

Hi Matthew,

Azure SQL should be supported but it's not that well documented.

Supported platforms

There's a resolved issue where they confirm it's supported but should be configured using the MSSQL type.

• If configuring manually, use mssql as your DB type.
• If configuring using the installer, also select the MSSQL drop down (or if providing a connection string, use jdbc:sqlserver://)
• Use your whole DNS host and port in the connection string with the credentials you setup in the Azure SQL Database setup process

JRASERVER-35444

You might as well open a support request regarding the health check and collation.

[matthew.mellifont]

Thanks for the input Johan, I think setting up a ticket is my best bet.

[matthew.mellifont]

To confirm, I just rebuilt the server in the end and deleted the Azure SQL DB and rebuilt with the correct collation. 

[Solomon Rutzky]

Hi there. It is quite possible (even highly likely) that there never has been a problem using a collation that is not one of the two "supported" collations.  Other people have used other collations and have been fine:

1. related question on DBA.StackExchange with user who has been using SQL_Latin1_General_CP1_CI_AS for a few years now with no noticeable issues:
   

   Process to change collation on a database

2. I have been using Latin1_General_100_CI_AI for a while with no issues (though I am not a heavy user of Jira so there are certainly code-paths that I have not tested)

It is much more likely that the reason for having those two "supported" collations is due to a lack of understanding of how collations work (or at least work in SQL Server) than it is from their being actual functional requirements tied to those two collations. I am 99.999999999% certain that the only things that matter are:

1. the collation needs to be case-insensitive
2. the collation for the Jira DB should ideally be the same as the instance-level collation, else you might run into errors related to temp tables (please see: Collation error in Jira when the database and server/default collation do not match in SQL Server  ). However, I have not run into this issue and my instance-level collation is different than my Jira DB collation, and the same hold trues for the person who posted the question on DBA.StackExchange (linked above).
3. you might want the collation to be accent-insensitive, depending on if you want accents to matter when searching / sorting.
4. You should stick with the Latin1_General collations. In many cases, SQL Server will default to using SQL_Latin1_General_CP1_CI_AS, and so these older SQL Server collations (i.e. those having names starting with "SQL_") are commonly used (unfortunately), but it is actually best to use the newest version of a particular collation. In this case, that would be the Latin1_General_100_CI_* collations, and those are available in SQL Server 2008 and newer. Again, I am using Latin1_General_100_CI_AI, though had I thought more about it at the time, I would have used Latin1_General_100_CI_AI_SC since it includes full support for supplementary characters. But either with or without the "_SC" should be fine as they are mostly the same anyway.

So to answer your questions:

1. There should have been no noticeable impact to using SQL_Latin1_General_CP1_CI_AS. And in fact, it would have increased the chances of having an impact if the instance-level collation did not match the Jira DB collation.
2. Changing a SQL Server database's collation (via ALTER DATABASE [db_name] COLLATE new_collation; ) changes only the default collation. And that affects only the collation of string literals, variables (including parameters), and new / altered columns that don't specify the COLLATE clause. Meaning: the collation of all existing columns in all user tables will not be changed. So yeah, for Azure SQL DB you will probably need to start over (but again, you probably didn't need to change the collation in the first place, but that's a moot point since you already have, this is just an FYI, especially for others who haven't already done that most likely unnecessary work). For on prem installations, you have some options. Please see my post: Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Take care, Solomon...