Warning: This guide is not complete. If you see errors here or want to contribute more information feel free to edit it.
Warning: I did compile and attach hsqldbutil.jar here. Do not use it or at least use it at your own risk. `jdk/bin/upack200 hsqldbutil.jar.pack.gz hsqldbutil.jar´.
MySQL to MSSQL
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
Using HSQLDB Transfer Tool
You need to compile the GUI tool. You also need the JDBC drivers, likely hsqldb.jar and mysql.jar. Start the tool with java -cp Path-To/hsqldbutil.jar:Path-To/hsqldb.jar:Path-To/mysql.jar org.hsqldb.util.Transfer´ (;´ instead of `:´ on Windows) and enter then the connection details.
For the embedded database (source) you may want to enter:
Type: HSQLDB In-Memory
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:Path-To/embedded-db/openfire
User: SA
Password: (enter nothing here)
For the new database (destination) you may want to enter the proper values.
Todo: someone may want to complete the guide after migrating a database.
Manual transfer
This is the hard way but it should also be possible. And it is of course not tested.
Prepare the `openfire.script´ file
The embedded database is located in the embedded-db´ directory. To migrate to another database there should be noopenfire.log´ file, so you may need to stop, start and stop Openfire to make sure that everything is in the `openfire.script´ file.
openfire.sql.txt file
Now make a backup of openfire.script´ and name itopenfire.sql.txt´.
Open it and search for `SET SCHEMA PUBLIC´. Remove this and all lines above.
Search for `INSERT INTO JIVEUSER VALUES('admin’´ and remove this line as you did already create an admin user.
Now you want to convert the \uNNNN characters to either UTF-8 or a local encoding. If you skip this step you may end up with \uNNNN characters in the new database.
Append `commit;´ as the last line.
Save the file.
New database
Install a new Openfire server and connect it to the new database engine. Install all plugins as they may add some tables which are needed to import all data.
Shutdown Openfire and import the `openfire.sql.txt´ file. Some errors may occur which you need to correct manually.