Re: [Dev] Methods to migrate database from MySQL to MSSQL

[Shani Ranasinghe]

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 
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 
> 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  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]  [image: Twitter]
>  [image: Google Plus]
>  [image:
> Linkedin]  [image: Instagram]
>  [image: Skype]
> 
>
>


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

[Madusanka Premaratne]

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 
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  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]  [image: Twitter]
 [image: Google Plus]
 [image:
Linkedin]  [image: Instagram]
 [image: Skype]

___
Dev mailing list
Dev@wso2.org
http://wso2.org/cgi-bin/mailman/listinfo/dev

Re: [Dev] Methods to migrate database from MySQL to MSSQL

[Dhanuka Ranasinghe]

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

[Dev] Methods to migrate database from MySQL to MSSQL

[Shani Ranasinghe]

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