Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008
Moderator: crythias
Forum rules
Dont create your support topics here! No new topics with questions allowed!
Dont create your support topics here! No new topics with questions allowed!
Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008
*** 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
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
Last edited by vivektj on 19 Dec 2013, 12:13, edited 1 time in total.
Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2
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:
in english
Can help me anyone? ThankS
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.
Code: Select all
the value of typ byte[] can't convert to typ nvarchar
You do not have the required permissions to view the files attached to this post.
Linux Debian Jessie
DB: postgres
DB: postgres
Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2
Great Job!!!! Thanks a lot! Merry Christmas!
Linux Debian Jessie
DB: postgres
DB: postgres
Re: Migrate OTRS from MySQL to MSSQL
Gald I could be of help...
Merry Christmas to you too..
Merry Christmas to you too..
-
- 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
hi, here there are a few tables which may require convert from varbinary to nvarchar as described in first post
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
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
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
Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008
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:
- In SQL Management Studio, change the destination column type from nvarchar(max) to binary(max).
- Migrate data with SSMA.
- Change the column from binary(max) to varchar(max), save the changes.
- Change the column from varchar(max) to nvarchar(max) save the changes.
Nick
-
- 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
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
Re: Migrate OTRS from MySQL to MSSQL (Microsoft SQL Server 2008
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
"Testing": ((OTRS Community Edition)) and git Master
Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
-
- Znuny newbie
- Posts: 1
- Joined: 29 Jun 2021, 14:07
- Znuny Version: 6.0.32
- Real Name: alexandra zayceva
- Company: Ispirer Systems
-
- Administrator
- Posts: 4002
- 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
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 ?
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 ?