Re: [Dev] Methods to migrate database from MySQL to MSSQL
Hi Madusanka/ Dhanuka, Thanks for your responses. @Madusanka, The SSMA tool you mentioned seems to work fine. It seems to convert the schema properly. Will do some more testing and verify, thanks a lot for the info. On Mon, Apr 27, 2015 at 6:38 PM, Madusanka Premaratne madusan...@wso2.com wrote: Hi Shani, I used a tool called SQL Server Migration Client. But I did this a few years back and I think it only works with windows. Have a look at [1] [1] - http://blogs.msdn.com/b/ssma/p/mysql.aspx Thanks, Madusanka On Mon, Apr 27, 2015 at 4:44 PM, Dhanuka Ranasinghe dhan...@wso2.com wrote: I haven't done such thing before but you can try out something like this. Write some sql scripts which has some select insert statements (select form mysql insert into mssql). Hope this is not much complex since column names and table names are equal. You may need to write simple Java program to select queries and map them to insert statements which use both jdbc drivers. If you can use JPA this can be done easily. Cheers, Dhanuka *Dhanuka Ranasinghe* Senior Software Engineer WSO2 Inc. ; http://wso2.com lean . enterprise . middleware phone : +94 715381915 On Mon, Apr 27, 2015 at 4:08 PM, Shani Ranasinghe sh...@wso2.com wrote: Hi, I have come across a situation where I need to migrate my MySQL db to MSSQL in wso2 api manager. I have so far tried the following. 1) Using the ODBC connector and creating linked databases in MSSQL In this approach, when importing the database, the schema seems to change, for e.g. for tables that have Auto increment has been disappeared when converting to a MSSQL schema, also the not null, null definitions have been swapped at certain instances. 2) Used ODBC connector to import data to an existing database I kept getting the error : The Source - am_api_comments was unable to retrieve column information for the SQL command. The following error occurred: ERROR [42000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.73-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'am_api_comments' at line 1 for every table. 3) Tried some online tools This too brought the errors in not converting the schema's properly as step 1. 4) Exporting MySQL table by table as csv and importing them to MSSQL This is the only way I could get it working. It is a very tedious way of getting it done :( i.e. 1) Export the data in MySQL data as csv file. When importing must have a IFNULL check done for every varchar and not null column so that the csv value is at the end correct so that the SQL can import it. An example for this is that, when the MySQL column's have null values, when exporting to csv, in the csv file it is needed to have a blank value, for this we need to do the check. Once the csv's are ready from table to table, we can import them to the MSSQL database, where we have already created the schema. I am not sure if I did step 1 2 correct or if there was any issue in it. I followed some online article and blog but could not succeed. If anyone has any experience on this or knows of any other ways I could try please do let me know. -- Thanks and Regards *,Shani Ranasinghe* Senior Software Engineer WSO2 Inc.; http://wso2.com lean.enterprise.middleware mobile: +94 77 2273555 linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- *Madusanka Premaratne* | Associate Software Engineer WSO2, Inc | lean. enterprise. middleware. #20, Palm Grove, Colombo 03, Sri Lanka Mobile: +94 71 835 70 73| Work: +94 112 145 345 Email: madusan...@wso2.com | Web: www.wso2.com [image: Facebook] https://www.facebook.com/maduzanka [image: Twitter] https://twitter.com/rmmpremaratne [image: Google Plus] https://plus.google.com/u/0/+MadusankaPremaratnemaduz/about/p/pub [image: Linkedin] http://lk.linkedin.com/in/madusanka/ [image: Instagram] http://instagram.com/madusankapremaratne [image: Skype] http://@rmmpremaratne -- Thanks and Regards *,Shani Ranasinghe* Senior Software Engineer WSO2 Inc.; http://wso2.com lean.enterprise.middleware mobile: +94 77 2273555 linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
[Dev] Methods to migrate database from MySQL to MSSQL
Hi, I have come across a situation where I need to migrate my MySQL db to MSSQL in wso2 api manager. I have so far tried the following. 1) Using the ODBC connector and creating linked databases in MSSQL In this approach, when importing the database, the schema seems to change, for e.g. for tables that have Auto increment has been disappeared when converting to a MSSQL schema, also the not null, null definitions have been swapped at certain instances. 2) Used ODBC connector to import data to an existing database I kept getting the error : The Source - am_api_comments was unable to retrieve column information for the SQL command. The following error occurred: ERROR [42000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.73-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'am_api_comments' at line 1 for every table. 3) Tried some online tools This too brought the errors in not converting the schema's properly as step 1. 4) Exporting MySQL table by table as csv and importing them to MSSQL This is the only way I could get it working. It is a very tedious way of getting it done :( i.e. 1) Export the data in MySQL data as csv file. When importing must have a IFNULL check done for every varchar and not null column so that the csv value is at the end correct so that the SQL can import it. An example for this is that, when the MySQL column's have null values, when exporting to csv, in the csv file it is needed to have a blank value, for this we need to do the check. Once the csv's are ready from table to table, we can import them to the MSSQL database, where we have already created the schema. I am not sure if I did step 1 2 correct or if there was any issue in it. I followed some online article and blog but could not succeed. If anyone has any experience on this or knows of any other ways I could try please do let me know. -- Thanks and Regards *,Shani Ranasinghe* Senior Software Engineer WSO2 Inc.; http://wso2.com lean.enterprise.middleware mobile: +94 77 2273555 linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] Methods to migrate database from MySQL to MSSQL
I haven't done such thing before but you can try out something like this. Write some sql scripts which has some select insert statements (select form mysql insert into mssql). Hope this is not much complex since column names and table names are equal. You may need to write simple Java program to select queries and map them to insert statements which use both jdbc drivers. If you can use JPA this can be done easily. Cheers, Dhanuka *Dhanuka Ranasinghe* Senior Software Engineer WSO2 Inc. ; http://wso2.com lean . enterprise . middleware phone : +94 715381915 On Mon, Apr 27, 2015 at 4:08 PM, Shani Ranasinghe sh...@wso2.com wrote: Hi, I have come across a situation where I need to migrate my MySQL db to MSSQL in wso2 api manager. I have so far tried the following. 1) Using the ODBC connector and creating linked databases in MSSQL In this approach, when importing the database, the schema seems to change, for e.g. for tables that have Auto increment has been disappeared when converting to a MSSQL schema, also the not null, null definitions have been swapped at certain instances. 2) Used ODBC connector to import data to an existing database I kept getting the error : The Source - am_api_comments was unable to retrieve column information for the SQL command. The following error occurred: ERROR [42000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.73-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'am_api_comments' at line 1 for every table. 3) Tried some online tools This too brought the errors in not converting the schema's properly as step 1. 4) Exporting MySQL table by table as csv and importing them to MSSQL This is the only way I could get it working. It is a very tedious way of getting it done :( i.e. 1) Export the data in MySQL data as csv file. When importing must have a IFNULL check done for every varchar and not null column so that the csv value is at the end correct so that the SQL can import it. An example for this is that, when the MySQL column's have null values, when exporting to csv, in the csv file it is needed to have a blank value, for this we need to do the check. Once the csv's are ready from table to table, we can import them to the MSSQL database, where we have already created the schema. I am not sure if I did step 1 2 correct or if there was any issue in it. I followed some online article and blog but could not succeed. If anyone has any experience on this or knows of any other ways I could try please do let me know. -- Thanks and Regards *,Shani Ranasinghe* Senior Software Engineer WSO2 Inc.; http://wso2.com lean.enterprise.middleware mobile: +94 77 2273555 linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
Re: [Dev] Methods to migrate database from MySQL to MSSQL
Hi Shani, I used a tool called SQL Server Migration Client. But I did this a few years back and I think it only works with windows. Have a look at [1] [1] - http://blogs.msdn.com/b/ssma/p/mysql.aspx Thanks, Madusanka On Mon, Apr 27, 2015 at 4:44 PM, Dhanuka Ranasinghe dhan...@wso2.com wrote: I haven't done such thing before but you can try out something like this. Write some sql scripts which has some select insert statements (select form mysql insert into mssql). Hope this is not much complex since column names and table names are equal. You may need to write simple Java program to select queries and map them to insert statements which use both jdbc drivers. If you can use JPA this can be done easily. Cheers, Dhanuka *Dhanuka Ranasinghe* Senior Software Engineer WSO2 Inc. ; http://wso2.com lean . enterprise . middleware phone : +94 715381915 On Mon, Apr 27, 2015 at 4:08 PM, Shani Ranasinghe sh...@wso2.com wrote: Hi, I have come across a situation where I need to migrate my MySQL db to MSSQL in wso2 api manager. I have so far tried the following. 1) Using the ODBC connector and creating linked databases in MSSQL In this approach, when importing the database, the schema seems to change, for e.g. for tables that have Auto increment has been disappeared when converting to a MSSQL schema, also the not null, null definitions have been swapped at certain instances. 2) Used ODBC connector to import data to an existing database I kept getting the error : The Source - am_api_comments was unable to retrieve column information for the SQL command. The following error occurred: ERROR [42000] [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.73-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'am_api_comments' at line 1 for every table. 3) Tried some online tools This too brought the errors in not converting the schema's properly as step 1. 4) Exporting MySQL table by table as csv and importing them to MSSQL This is the only way I could get it working. It is a very tedious way of getting it done :( i.e. 1) Export the data in MySQL data as csv file. When importing must have a IFNULL check done for every varchar and not null column so that the csv value is at the end correct so that the SQL can import it. An example for this is that, when the MySQL column's have null values, when exporting to csv, in the csv file it is needed to have a blank value, for this we need to do the check. Once the csv's are ready from table to table, we can import them to the MSSQL database, where we have already created the schema. I am not sure if I did step 1 2 correct or if there was any issue in it. I followed some online article and blog but could not succeed. If anyone has any experience on this or knows of any other ways I could try please do let me know. -- Thanks and Regards *,Shani Ranasinghe* Senior Software Engineer WSO2 Inc.; http://wso2.com lean.enterprise.middleware mobile: +94 77 2273555 linked in: lk.linkedin.com/pub/shani-ranasinghe/34/111/ab ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev -- *Madusanka Premaratne* | Associate Software Engineer WSO2, Inc | lean. enterprise. middleware. #20, Palm Grove, Colombo 03, Sri Lanka Mobile: +94 71 835 70 73| Work: +94 112 145 345 Email: madusan...@wso2.com | Web: www.wso2.com [image: Facebook] https://www.facebook.com/maduzanka [image: Twitter] https://twitter.com/rmmpremaratne [image: Google Plus] https://plus.google.com/u/0/+MadusankaPremaratnemaduz/about/p/pub [image: Linkedin] http://lk.linkedin.com/in/madusanka/ [image: Instagram] http://instagram.com/madusankapremaratne [image: Skype] http://@rmmpremaratne ___ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
