How to migrate from embedded-db to Microsoft SQL Server 2005 in OpenFire 3.6.4
By Scott Autry
Our company started out withembedded-db in OpenFire to test the platform and soon found it sufficient forour needs. Our needs grew to being able to create reports of user activity andquickly found that parsing the embedded-db was nigh impossible apart from crazyregular expressions and lots more work that we just didn’t have the time for. Weneeded to keep our existing chat archive history along with the other settingsin place. So I began looking into a way to migrate our data without a wholesalere-install. Here is what I found, step-by-step. This tutorial assumes you havea basic working knowledge of Microsoft SQL Server 2003/2005/etc and WindowsServer products.
Step 1. Shut down OpenFire. Start it back up,and then shut it down again. This will insert all of your recent activity fromthe clients to the openfire\embedded-db\openfire.script file which we will use later. The majority of yourwork will need to be done while OpenFire is offline mainly because messagingwill still be running and logging data into the openfire\embedded-db\openfire.script file otherwise.
Step 2. Create a database in SQL Server and runthe script to create the database tables located at: openfire\resoureces\database\openfire_sqlserver.sql
Make sure at this point you know your dba username andpassword to grant OpenFire access during the pseudo-fresh install we willundertake in a moment.
Step 3. Make a copy of the entire openfire folder and keep it as a backup.
Step 4. Edit the openfire\conf\openfire.xml file in this way.
Findthe code that says true andchange it to false
Thisstep essentially tells OpenFire that the next time you start up the Administratorconsole to begin the setup wizard for a fresh install. Don’t worry all of yourinfo is still saved in the openfire.script file.
Step5.This step is probably the most tedious step of them all. Here is where you areactually going to take the openfire\embedded-db\openfire.script and format it slightly to make it ready for a nice,smooth import into SQL. If you are not familiar with SQL statements then thismight seem daunting. The reason we need to edit the file is the fact that thereare many SQL statements in the openfire.script filethat are not compatible with MS-SQL because they were written for MySQL. Here’swhat I remember.
- CREATE MEMORY TABLE will need to be CREATETABLE
- LONGVARCHAR
Theselines can be deleted:
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE USER SA PASSWORD “”
GRANT DBA TO SA
SET WRITE_DELAY 20
SET SCHEMA PUBLIC
INSERT INTO OFUSERVALUES(‘admin’,‘admin’,NULL,‘Administrator’,‘admin@example.com’,‘0’,‘0’)
Hereis where a good text editor like Notepad++ comes in.
Openthe openfire\embedded-db\openfire.script file and use the Find & Replace feature andsearch for CREATE MEMORY TABLE and replaceit with CREATE TABLE in the editor. Repeatthe process for LONGVARCHAR and replace itwith NTEXT.
Step6.Copy (Ctrl+A then Ctrl+C) the contents of the openfire.script file and run it onyour SQL server.
CAUTION:If you have been running OpenFire for quite a while the amount of lines youwill have to copy may be in the tens-of-thousands. Depending on your SQL serverperformance you might want to break the update statements into smaller chunks(i.e. copy UPDATE statement lines 100-1000 and run the update, etc.) If youhave a decently powerful server then run the entire thing…just know yourservers performance as it might take a minute to complete.
Asyou run the script you might notice that there are some errors stating thatthere are already tables that exist, simply go to the openfire.script file and delete that line along with its CREATE INDEX reference line which should belocated directly below the CREATE TABLE line.Repeat the process until you have all of the CREATETABLE scripts and the correlating CREATEINDEX scripts in your file deleted that already exist in the database.
Idecided to write the migration manual in this way because I didn’t want to anytables to be left out and since every configuration is a little different theremight be some tables that are in your installation that are not in mine.Otherwise, we could have just run the openfire.script fileto create all of the tables instead of the prior Step 2 where we created thetables using the openfire_sqlserver.sql script. Make sense?
Step7.If you’ve gotten this far and you received a message stating that your queryran successfully then we are done! Start OpenFire, go to the Administratorconsole and login just like you did before with the same credentials.
Asa side note if you see the error Violation of PRIMARYKEY constraint ‘ofRRDs_pk’. Cannot insert duplicate key in object ‘dbo.OFRRDS’ don’tworry about it.