Best approach to move/upgrade from 2008 to 2014 MS SQL version Edited

Hi all,

Im looking for some advice in regards to the best approach for migrate/upgrade MS SQL server 2008R2 up to 2014 for the following desire upgrade path of Jira server for one of our customer:

Jira server 5.2.7 -> 6.4.14 -> 7.1.10 (perform tentative database cutover here) -> 7.latest 

What could be the repercussions of each potential approach? Is create a new database in MS SQL server 2014 as per https://confluence.atlassian.com/adminjiraserver071/connecting-jira-applications-to-sql-server-2014-808761244.html and just do an xml backup/restore into it the cleanest one?

Regards,

Matías

1 answer

1 accepted

1 votes

There are two different ways to do this.   You could, as you stated, create a new database, and then follow the setup wizard to import an XML backup of your previous data.  That method will work.

The other method would be to create a full database backup with MS SQL Server Studio Manager and then restore this database backup to a SQL 2014 instance.  From my days of MS SQL administration, I recall that MS SQL would let you restore older DB versions to newer  DB instance, but not older.   I'm not really a SQL admin, but I remember you could take SQL 2005 databases and restore them to 2008 SQL instances.  I believe this method is still valid for moving old MS databases into newer versions of SQL.  This is one way to get your data to a newer DB.

The thing about this kind of restore is that Jira started implementing database collation health checks in the 7.0.x versions.  So you might find that restoring a full SQL backup might still have previous database collations that Jira will generate warnings about in these 7.x versions.   If you closely followed the steps when you first created the database for Jira, you aren't likely to see this warning as a problem.  But in support, we have had a lot of users report this warning and ask about how to fix it.

In those cases, the best way to fix it is not to try to change it directly on the existing database, but instead to create a new empty database per the documentation (with the correct collation) and then import an XML backup to this new empty database.   So either method can work, but when switching databases the XML backup method tends to be preferred because it forces you to create a new empty database.

 

In your case, after you complete the upgrade to 7.1.x, I'd be interested to see if Jira generates any kind of warning to you in regards to the database configuration/collation.  You would likely see this in the Jira web UI immediately after a Jira Admin logs into the system after the upgrade.  You can also check for this in Cog Icon -> System -> Troubleshooting and Support tools -> Instance health.  If it does mention an incorrect collation, then I'd recommend the XML backup method and creating a new database in order to migrate that data.  If it does not, then you should be able to use either method successfully.

Hi Andrew,

Yes I'm aware of the collation heath checks in newer versions (I have faced a similar situation in Confluence v6.1). I will go directly creating a new empty database and import an XML backup from the previous version. Will let you know on this thread about any warnings in 7.1.

 

Regards,

Matías

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,870 views 12 18
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot