Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

Dont create your support topics here! No new topics with questions allowed!

Moderator: crythias

vivektj
OTRS newbie
Posts: 6
Joined: 30 Nov 2013, 14:10
OTRS Version?: 3.3.2

Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

Postby vivektj » 15 Dec 2013, 07:28

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

Image

7. From the SQL Server connection, Use “Save as Script” to save the sql server schema script.

Image

8. Execute the script from Management Studio to create the tables and links in SQL server

Image

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)

Image

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.

Image

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)

Image

In “otrs_temp”, under article_attachment create a new column called “content2” of type nvarchar(max)

Image

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
Last edited by vivektj on 19 Dec 2013, 12:13, edited 1 time in total.

shining01
OTRS superhero
Posts: 239
Joined: 07 Apr 2011, 08:02
OTRS Version?: 5.0.1

Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2

Postby shining01 » 18 Dec 2013, 15:04

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
You do not have the required permissions to view the files attached to this post.
Linux Debian Jessie
DB: postgres

shining01
OTRS superhero
Posts: 239
Joined: 07 Apr 2011, 08:02
OTRS Version?: 5.0.1

Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2

Postby shining01 » 19 Dec 2013, 15:53

Great Job!!!! Thanks a lot! Merry Christmas!
Linux Debian Jessie
DB: postgres

vivektj
OTRS newbie
Posts: 6
Joined: 30 Nov 2013, 14:10
OTRS Version?: 3.3.2

Re: Migrate OTRS from MySQL to MSSQL

Postby vivektj » 19 Dec 2013, 21:59

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

eandrex
OTRS superhero
Posts: 213
Joined: 04 Nov 2012, 23:58
OTRS Version?: OTRS 4.x
Real Name: Esteban
Company: NORTON DE COLOMBIA

Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2

Postby eandrex » 17 Jan 2014, 19:10

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
OTRS newbie
Posts: 11
Joined: 20 Aug 2010, 04:50
OTRS Version?: 4.0.7

Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

Postby turnip » 28 Apr 2015, 01:54

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

Karrahahu1
OTRS newbie
Posts: 1
Joined: 12 Feb 2016, 11:37
OTRS Version?: aaaaa
Real Name: aaaaa
Company: 123465

Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

Postby Karrahahu1 » 12 Feb 2016, 11:42

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..???
Ali

jojo
Moderator
Posts: 13900
Joined: 26 Jan 2007, 14:50
OTRS Version?: Git Master
Contact:

Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

Postby jojo » 12 Feb 2016, 12:02

As from OTRS5 MS SQL is not supported as application database thats a bad idea.
"Production": OTRS5, OTRS::ITSM5, SIRIOS 2.3
"Testing": OTRS git Master
OS: Ubuntu / Apache2/MySQL 5+

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com


Return to “Howtos”

Who is online

Users browsing this forum: No registered users and 1 guest