*** UPDATED to include all steps with screenshots ***
I’m posting this because I could not find any steps on the internet detailing the process of migrating the OTRS database from MySQL to MSSQL Server. Our OLD setup was:
Windows Server 2008 running OTRS 3.0.1 on Apache & MySQL
New Setup was
Windows Server 2008 R2 running OTRS 3.3.2 on IIS and Microsoft SQL Server 2008 R2
1. Install a fresh instance of OTRS on the new server with IIS and MSSQL. Just use the normal OTRS windows installer, followed by the setup and configuration.
2. Stop all otrs services on the new server, On MSSQL, BAKCUP the otrs database and delete the database from the new server (This will be restored again after the migration).
3. Create a new blank database in SQL server called otrs (refer pt.2)NOTE: You cannot export data from MYSQL directly into the MSSQL otrs database created by the installer. This is because some of the columns and datatypes are different and cannot be converted by the migration assistant.
4. Use “Microsoft SQL Server Migration Assistant for MySQL v5.2” to migrate MySQL data to a temporary staging database. (The reason being, there are changes in some of the database columns between MySQL and MSSQL, mainly in the article_attachment and article_plain tables )
5. Using the Migration assistant connect to both databases
6. Use “Convert Schema” to generate the schema
7. From the SQL Server connection, Use “Save as Script” to save the sql server schema script.
8. Execute the script from Management Studio to create the tables and links in SQL server
9. FYI, You will notice the migration tool has converted the LONGBLOB to varbinary, which is ok for now since this is a temporary database. (The otrs installer on the other hand creates this column as “nvarchar”, we will handle the conversion further on)
10. Now run the migration from “migration assistant” tool. Since the schema is an exact replica of the MySQL otrs database, you should have ZERO errors at this stage.
11. On MSSQL, Backup the migrated “otrs” database and restore it to a new database called “otrs_temp”
12. On MSSQL, restore the original database of “otrs” which was backed up in Pt. 2
13. In “otrs_temp”, under article_plain create a new column called “body2” of type nvarchar(max)
In “otrs_temp”, under article_attachment create a new column called “content2” of type nvarchar(max)
14. Use the following command to convert varbinary column to nvarchar in “article_plain” (both this and the next step will take quite a bit of time depending on the number of rows to be migrated)UPDATE article_plain
SET body2 = CONVERT(varchar(max),(CONVERT(varbinary(max),body)))
15. Use the following command to convert varbinary column to nvarchar in “article_attachment” using Base64 encodingUPDATE article_attachment
SET content2 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("content")))' , 'VARCHAR(MAX)' )
16. Migrating data from the temporary database “otrs_temp” to the main “otrs” database can be done using the SQL server import utility. Turn on identity insert while importing.
While migrating you will need to use the new converted columns “body2” and “content2” instead of the original.
17. You will need to migrate the tables in sequence, so that foreign key relations are maintained. Eg. You will need to start by migrating the “users”, “customer_users” then followed by “ticket” then the “article” , then the child tables etc .
There are the main steps I followed, there may be simpler ways of doing this, but I couldn’t find any on google or in the forums. Apart from paying for support from OTRS, this was what worked for us.
Hope this helps anyone else facing the same problem.