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?
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.
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.
The Jira Marketing team is putting together an ebook on migrating to Data Center. We're looking for pro tips on how you staffed your project team and organized your Proof of Concept. Share yo...
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!
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