Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

[vivektj]

*** 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 encoding

UPDATE 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.

Cheers
VJ

[shining01]

Hello,

i have created first with the otrs Script the database schema. After that i migrated data with MSSQL SQL Server Migration Assistant.
But all tables in mysql with longblob i can't migrate data. I get the following error:

Code: Select all

Der angegebene Wert vom Typ Byte[] aus der Datenquelle kann nicht in Typ nvarchar der angegebenen Zielspalte konvertiert werden.

in english

Code: Select all

the value of typ byte[] can't convert to typ nvarchar

Can help me anyone? ThankS

[shining01]

Great Job!!!! Thanks a lot! Merry Christmas!

[vivektj]

Gald I could be of help...
Merry Christmas to you too..

[eandrex]

hi, here there are a few tables which may require convert from varbinary to nvarchar as described in first post

Code: Select all

pm_activity
pm_proccess
pm_activity_dialog
pm_transition
pm_transition_action
standard_attachment
package_repository
gi_webservice_config_history
gi_webservice_config
dynamic_field

and an easy way to restore the migrated data without having to worry about foreing keys is install OTRS from source

so in your ms database you execute: <OTRS_HOME>/scripts/database/otrs-schema.mssql.sql

after that, you migrate the data as you would do in step 16 but without having to worry about parents + childs, etc..just import everything since there are not foreign keys created yet..

once you migrate proccess has finished with no errors, make sure you execute in your ms database: <OTRS_HOME>/scripts/database/otrs-post-schema.mssql.sql

[turnip]

I recently went through the process of migrating from MySQL to SQL Server. I used "Migrate Data" in SSMA without "Convert Schema". One of the problems was that SSMA refused to migrate from LONGBLOB to nvarchar, which I solved as follows:

1. In SQL Management Studio, change the destination column type from nvarchar(max) to binary(max).
2. Migrate data with SSMA.
3. Change the column from binary(max) to varchar(max), save the changes.
4. Change the column from varchar(max) to nvarchar(max) save the changes.

[Karrahahu1]

you migrate the data as you would do in step 16 but without having to worry about parents + childs, etc..just import everything since there are not foreign keys created yet..???

[jojo]

As from OTRS5 MS SQL is not supported as application database thats a bad idea.

[alexandrazayceva]

As from OTRS5 MS SQL is not supported as application database thats a bad idea.

So what is the solution?... still remained unresolved

[root]

As from OTRS5 MS SQL is not supported as application database thats a bad idea.

So what is the solution?... still remained unresolved

Hi,

Once again, there is [*]no support for MS SQL since version 5. No more questions needed.

- Roy