Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

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

Moderator: crythias

Forum rules
Dont create your support topics here! No new topics with questions allowed!
Post Reply
vivektj
Znuny newbie
Posts: 6
Joined: 30 Nov 2013, 14:10
Znuny Version: 3.3.2

Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008

Post by 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

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
Znuny expert
Posts: 270
Joined: 07 Apr 2011, 08:02
Znuny Version: 6.0.4
Real Name: Tom

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

Post by 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
You do not have the required permissions to view the files attached to this post.
Linux Debian Jessie
DB: postgres
shining01
Znuny expert
Posts: 270
Joined: 07 Apr 2011, 08:02
Znuny Version: 6.0.4
Real Name: Tom

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

Post by shining01 »

Great Job!!!! Thanks a lot! Merry Christmas!
Linux Debian Jessie
DB: postgres
vivektj
Znuny newbie
Posts: 6
Joined: 30 Nov 2013, 14:10
Znuny Version: 3.3.2

Re: Migrate OTRS from MySQL to MSSQL

Post by vivektj »

Gald I could be of help... :)
Merry Christmas to you too..
eandrex
Znuny expert
Posts: 213
Joined: 04 Nov 2012, 23:58
Znuny Version: OTRS 4.x
Real Name: Esteban
Company: NORTON DE COLOMBIA

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

Post by 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
Znuny newbie
Posts: 13
Joined: 20 Aug 2010, 04:50
Znuny Version: 4.0.7

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

Post by 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.
Nick
Karrahahu1
Znuny newbie
Posts: 1
Joined: 12 Feb 2016, 11:37
Znuny Version: aaaaa
Real Name: aaaaa
Company: 123465

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

Post by 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..???
Ali
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

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

Post by jojo »

As from OTRS5 MS SQL is not supported as application database thats a bad idea.
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
alexandrazayceva
Znuny newbie
Posts: 1
Joined: 29 Jun 2021, 14:07
Znuny Version: 6.0.32
Real Name: alexandra zayceva
Company: Ispirer Systems

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

Post by alexandrazayceva »

jojo wrote: 12 Feb 2016, 12:02 As from OTRS5 MS SQL is not supported as application database thats a bad idea.
So what is the solution?... still remained unresolved
root
Administrator
Posts: 3931
Joined: 18 Dec 2007, 12:23
Znuny Version: Znuny and Znuny LTS
Real Name: Roy Kaldung
Company: Znuny
Contact:

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

Post by root »

alexandrazayceva wrote: 30 Jun 2021, 08:21
jojo wrote: 12 Feb 2016, 12:02 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
Znuny and Znuny LTS running on CentOS / RHEL / Debian / SLES / MySQL / PostgreSQL / Oracle / OpenLDAP / Active Directory / SSO

Use a test system - always.

Do you need professional services? Check out https://www.znuny.com/

Do you want to contribute or want to know where it goes ?
Post Reply