It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Using Azure SQL for Jira server 7.13 - Collation issue Edited

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 

2 answers

1 accepted

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.

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

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

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...

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Calling all Jira Cloud users! Give us feedback on our exploration of a new navigation.

Hi everyone! My name’s Matt and I’m a product manager at Atlassian. I work in the navigation & findability space for all our Jira Cloud products. We’ve been working on trying to improve the exp...

1,063 views 16 12
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you