Has anyone migrated their Confluence database to SQL Server

[Espen Sandall]

We plan to migrate ours from Oracle 12C to SQL Server 2017.

If anyone has gone through such a process, or any other migration to a different database platform, please share your experiences and advice. :-)

Best regards,
Espen

[Daniel Ebers · Accepted solution]

Hi Espen,

basically the way always leads to XML export/import.

It is described further in the knowledge base article:
https://confluence.atlassian.com/doc/migrating-to-another-database-148867.html

The asking towards Oracle -> MS SQL is very specific but who knows - probably somebody has gone through exactly that and can share some advise.

In general:
I would test this migration in your case on a non-production environment when time permits. This is the most reliable way to see how things go. Even the best shared advise cannot tell much about your specific installation (customizations, Apps, probably some specialities in database).

Cheers,
Daniel

[Espen Sandall]

Thank you for the quick reply Daniel,

It's reassuring that you recommend the XML import/export, as we've had trouble with 3rd party database migration tools on earlier attempt, from Oracle 11G.

However, the article you're referring to emphasizes that "Large data sets will require third party database migration tools."
But how big is a large data set?
We plan to start with our 10 GB instance, which has the attachments stored in a shared folder.
Our other instance is about 40-50 GB (also with attachments stored in the file system).

BR, Espen

[Daniel Ebers]

The XML import/export is what is recommended from documentation. The 3rd party migration tools are something where an external partner is recommended.

Before involving such resources it might be easier to try the migration using the XML approach on a non-production test system.

As for the question of the sizes the same applies - people who did migrations like this say "XML is the safest way" if the data is not too big. They did not tell how big their data was as well, so testing this also might be a good idea.
In other reports here on Community somebody said the import took a while but succeeded.
Another example I found in the past told they used a direct conversion from MySQL to PostgreSQL, supported by a dedicated database administrator - it was reported not to be fun so I believe with any doubt your approach with 3rd party tooling was causing issues.

Probably somebody else can say something to the import experiences from recent migrations.

Attachments on the other hand should not be a problem - they are just "there". I would concentrate on the database, especially when switching from one database to a completely different.

[Espen Sandall]

Thanks again for taking the time Daniel.

So far only the Jira migration is ongoing (and has already initiated a support case), but I hope to start the first Confluence test migration within a few days. Will share my progress here. :-)

[Espen Sandall]

Daniel, an extra question if I may.

Is it adviced to empty the plugins-cache before switching to the new database, and re-install them after a hopefully successful import?

Two plugins are causing problems for our Jira migration - one is even logging sql exceptions on startup, before the import.

ES

[Daniel Ebers]

Emptying the cache should be no disadvantage in that case.

This is contrary to start without plugins, though.

Putting the plugins aside for the Import could be also a good idea but you would need to test.

Troubling plugins can probably fixed just by putting updated ones (so that are compatible with the Jira/Confluence version of the Target system) into place (file system in the corresponding folder). But that would need further inspection .. this is hard to say if it works without further fiddling.

[Espen Sandall]

For any one interested. An XML backup of our 8 GB Confluence instance never succeeded while we where on v6. Once we had upgraded to v7 it ran without errors - but took a _long_ time.

While our 5-6 GB Jira instance takes only 11-12 minutes to dump it's data to xml, the Confluence instance took several hours(!)

First attempt to import the xml backup failed due to an invalid character, but Atlassian's XML Clean utility fixed that quickly. Then the import ran flawlessly, and relatively quicker than the export.

Atlassian recommends using a db utility for "large" instances. (How large is "large"??)
We tried using the SQL Server MIgration Assistant first, but ended up with a mess of tables and columns in upper case which should have been in lower case.

Thanks again @Daniel Ebers for the moral support for the xml exp/imp approach, which proved successful - considering enough maintenance time is available. :-)