migrate data from MS SQL to MySQL

"Since you already you have a winner why look any further ? unless you want it for free...."

yes ! for free ! management do not want that until final minutes !

"Migration of MsSQL to MySQL involves at first some kind of map between the datatypes between each engine...usually this is the easy part"
yeah! just like using Ispirer, configuration page will ask for data type but ispirer will have the default target type for us.

"Then you should look for the interpretation of the built in functions and the translation from one database engine to the other...this depends on the complexity of your database..."

you mean convert of SP , function and trigger?

"You can do it for free .."

this is what I am looking for .

do you think CSV dump can handle it ? but table schema is nearly impossible to migrate as table schema and data type totally different !

"You can do it for free .."

this is what I am looking for .

What john means hard manual labour by you. Reading old statements, look for translation in MySQL and then put that in and test it.

yeah by our own work force.

"There are many many ways you can do it....you can dump your entire schema  to .sql...make the necessary changes and import on sql."

but MySQL and MS SQL are using diff sytax in SQL, how can I import MS SQL format to MySQL ?

"For Sps start with this mini guide from a rival program : http://www.sqlines.com/sql-server-to-mysql"

have you tried that before ? I tried for Oracle to MariaDB migration, it can't even read oracle schema at all ! that's why Ispirer is so good!

that kind of open source is the worse one I ever seen !

"I must ask "why you are still mentioning" Ispirer ...if the management wants to pay only workhours you should start and finish on your own."

as I tried Ispirer but management will not consider it until they found problem and can't do migration.

so I F.S. other option.

Ok lets take it from the start

MySQL and MS SQL are using diff sytax in SQL

Well this is true but its not that far ...compared e.g. to Oracle or PostGre...picture it like this : you dump the sql.....you have the mapping of datatypes....presto migration of data.
Of course there will syntactical changes but once you make the first ones the rest will be a breeze.

have you tried that before

If you mean the Workbench
I am afraid i haven't got a client with a similar case yet but the principals are the same ...you understand both implementations of SQL  and you try to translate one to the other.( i work both in MSSQL and MySQL)

as I tried Ispirer but management will not consider it until they found problem and can't do migration

I don't know the price of Ispirer (if you don't mind share it to have an idea) but the management has to take into account for example how much time right now you are spending in order to find a free solution...if they are fine with this situation it would benefit everyone in the future...but you never know...
Can you share some techincal data like how big is your datababase in terms of tables/views/SPs/UDF/trigger (the data are irrelevant in this case)

Last but not least for now...besides Inspirer there are plenty are tools on the market ...each with their Pros and Cons...some are priced extremely low...like $100 ...so maybe you want to test them.

Something...else...i found a really old application for converting MSSql to MySQL Stored Procedures....maybe it worth a shot : https://sourceforge.net/projects/tsql2mysql/

".presto migration of data."

what is that mean ?

"you dump the sql.....you have the mapping of datatypes"

still a manual process, if there are 10000 of tables then ....... !

"you understand both implementations of SQL  and you try to translate one to the other.( i work both in MSSQL and MySQL)"

all manual work, I prefer easier one. but I will try workbench! tks.

and you work on both and you are not doing migration from MSSQL or MySQL but coexists ?

"I don't know the price of Ispirer (if you don't mind share it to have an idea) but the management has to take into account for example how much time right now you are spending in order to find a free solution.."

I know, last time I do a FS on oracle to mariaDB and I hands on on a lot of convert product ! ispirer is the best and they count the number of objects and data. so case is diff from DB to DB.

"Can you share some techincal data like how big is your datababase in terms of tables/views/SPs/UDF/trigger (the data are irrelevant in this case)"

not know yet but high level in technical first, direction first, if workbench is working fine I might ask Ispirer why need you ! then they will argue on what is bad on workbench.

John ,

"https://sourceforge.net/projects/tsql2mysql/"

did you try it before? I tried converted line , this tools is good but hasn't been updated for a long time so they can't support latest version of MYSQL.

sqlline really forget about it, I tried many version and all the same result ! no use at all.

presto is an expression that means "quick and easy"...i think its Italian but i use it some time... :)
All the above  - since you don't have a commercial product - translates to time and work...yes you will have to do some things manually ...or you have to make an application on some platform that will read the schema from 1 database ...translate it and push it to the other...

Such an old tool is almost certain that will have some issues...BUT....reading the code will help you to undestand the "mappings" between the MSSQL and MySQL and that's a big bonus...probably it might seem useless but the philosophy is that matters.

"yes you will have to do some things manually
"

but a lot of manually work ! each table is diff, how can I quickly do it ?

"or you have to make an application on some platform that will read the schema from 1 database ...translate it and push it to the other..."

so the SQL dump is not easy too, right?

seems need to bounce back to workbench for this purpose. I heard people is expecting CSV dump but that just data, we need schema ahead and I am not sure how easy to to RECREATE the schema in MySQL,

"Such an old tool is almost certain that will have some issues...BUT....reading the code"

I am sorry, how can I read the code of the tools ?

In the Files section you will get the source code of the application (C++)
https://sourceforge.net/projects/tsql2mysql/files/tsql2mysql/1.1/

oh you referring to that one .. tks.

"eally old application for converting MSSql to MySQL Stored Procedures."

that tools only convert SP ? or table and data?

what is the correct way to export the schema and data from MS SQL closest to the MySQL format ?

I don't think there is a correct way of exporting the schema...the schema is exported as is
Here is a post along with some scripts : https://stackoverflow.com/questions/2986404/convert-t-sql-to-mysql

"https://stackoverflow.com/questions/2986404/convert-t-sql-to-mysql"

and it say

"
How about using SQL Server Linked Servers ? You can SELECT, INSERT, UPDATE and DELETE data from MySQL using TSQL"

by this we still need to create the table first. this is the key part.

anyone you know tried that script?

On the previous post a guy(Madagaga) created a complete set of scripts...i am afraid i cannot test it right now...but there are plenty of scripts/tools to export a MSSQL schema to .sql...just google them...

this one you mean ?

@echo off

set host= (local)
set schema=database
set user=user
set pass=pass

cd %cd%
rem tables 
SQLCMD -S %host% -d %schema% -U %user% -P %pass% -s "" -h-1 -W -i "%CD%\mysql_export_table.sql" -o "%CD%\%schema%_tables.sql" 
rem data
SQLCMD -S %host% -d %schema% -U %user% -P %pass% -s "" -h-1 -W -i "%CD%\mysql_export_data.sql" -o "%CD%\%schema%_data.sql" 
rem fk
SQLCMD -S %host% -d %schema% -U %user% -P %pass% -s "" -h-1 -W -i "%CD%\mysql_export_fk.sql" -o "%CD%\%schema%_fk.sql" 

Select allOpen in new window

which call 3x other sQL scripts ?

yes

yes !

but this one is not equal to dump to CSV, right ? as CSV can only handle data but not schema!

so in summary I can only do this:

1) try to use the MySQL Workbench
2) Use a complete robost tools from Ispirer
3) data CSV dump . SP, function and view all need to build again as can't migrate using this method.
4) build a export script for it using the script from https://stackoverflow.com/questions/2986404/convert-t-sql-to-mysql (but same as 3),  SP, function and view all need to build again as can't migrate using this method.

is that make sense ?

this script,https://stackoverflow.com/questions/2986404/convert-t-sql-to-mysql , seems didn't handle data type mapping, right ?

I think your list is correct...you just "adjust" on each step..for example for exporting Schema  & data : https://docs.microsoft.com/el-gr/sql/ssms/scripting/generate-scripts-sql-server-management-studio?redirectedfrom=MSDN&view=sql-server-ver15

what steps should it be in my list ?

3) or 4) , before 3 ) ?
as MS SQL SSMS is not going to allow us to export object compatiable with MySQL, so only import from workbench from MysQL.

Automatic is what you can get from Workbench....everything else is what you do on your own.

I know !

and I also concern is MySQL workbench can only migration data and table, but not any code inside, do you agree with it ?

Yes i will agree on this...

oh my god, seems very little use, but the URL you show me :

"this script,https://stackoverflow.com/questions/2986404/convert-t-sql-to-mysql , seems didn't handle data type mapping, right ?"

also can't do code migration ?

Is just for the export....similar like the way proposed by Ms

haaha. then all can't migrate code except Ispirer.

then seems other than ispirer, workbench is the best as it export and import too, agree ? MS and the script can't do import!

and the CSV can't even handle schema export and import too.

so finally 3 x type:

1) Ispirer if need to convert eveytting.
2) workbench if only data and table need to migrate (team here in the feature want no code in DB but application tier)
3) script .

CSV should not in the list as it is just data dump and no table structure is exported

tks all.

I read this :

mysql_wp_migration_sqlserver.pdf

it say:

"Of course, Microsoft also makes available Data Transformation Services (DTS in SQL Server 2000) and
Integration Services (SQL Server 2005-12), which can help facilitate any SQL Server migration to
MySQL. Microsoft’s built-in migration tools support moving SQL Server data to MySQL with little effort
being required on the part of the DBA. "

and

"
For those using SQL Server who are not familiar with Integration Services, it is possible to move data
from SQL Server to MySQL using a combination of the SQL Server bulk copy program (BCP) and the
MySQL LOAD DATA INFILE utility. A DBA can create data files with SQL Server BCP where the data is
delimited by an appropriate character (such as a comma, semi-colon, etc.) and then load the data into
MySQL with LOAD DATA INFILE with the same delimiter being specified.
"
and

"A final way to load a SQL Server database into MySQL using Microsoft-supplied aids is to use the export
capabilities of Microsoft Access. A DBA can export any dataset in Access to MySQL by clicking on an
Access table and then using a combination of the export function and MySQL’s ODBC driver. Note that
indexes are not normally exported with the table structure and data. "

do you think it is possible ? specially the one STILL use ACCESS ?

why need ACCESS?

This is just another way of performing the migration... This would go pretty much as Inspirer.
i am an Access expert so if it was up to me i would use Access functionality to perform the mapping of the datatypes...and probably i wouldn't go all the way of export...i would perform Pass through Queries to capture the schema (get the tables and their columns) ...create the schema on MySQL based on MsSQL <->MySQL datatypes and then query each table to retrieve the data , manipulate the data and push them to MySQL
Then i would retrieve the SPs,Views,Triggers ...perform again some parsing/mapping and recreate everything on MySQL.
This would be an excellent - interesting - challenging job ... :)

"i am an Access expert so if it was up to me i would use Access functionality to perform the mapping of the datatypes...and probably i wouldn't go all the way of export..."

so you agree that using Access for it is not good, right ?

"i would perform Pass through Queries to capture the schema (get the tables and their columns) ...create the schema on MySQL based on MsSQL <->MySQL datatypes and then query each table to retrieve the data , manipulate the data and push them to MySQL"

using the query from the link you shown me, which based on 3 x queries ?

"Then i would retrieve the SPs,Views,Triggers ...perform again some parsing/mapping and recreate everything on MySQL.
"

rebuild SPs,Views,Triggers from ground up you mean ?

which of the 3 x method I quote from the PDF is the best and why ?

"Best" is a word that fits many cases but not in this case
The "Best" is what you know to do best.
Lets assume i have a conversation with a client for a similar job (C: Client , J:Me)
C: I want you to migrate MSSQL to MySQL
J : OK i will do it in Ms Access and i will need x days
C: No ... i don't want Access
J : OK i will do it in .NET and i will need 1.1*x days
C: we don't want either .NET
J : how about Python but i will need 1,5*x days
.....the talk goes
The thing is that there is no such thing as best way to do because there are so many ways to do it ...the "best" is what you know best and what it will work faster for you ...
Suppose that one method does the job is 6 hours and and the other in 10 hours...would it really matter unless you are a multi million company with critical uptime necessities...but the 2nd way is the one you know best...so what is actually the "best" ?
Out of many option just pick the one you know and you can use to get the job done.

hi,

gotcha and my best is , much less work done and get the same result ! my workbench is one of them example and I am compare the other. there must be some pros and cons for each method.

one thing, can workbench migrate FK relationship, all index ?

"Out of many option just pick the one you know and you can use to get the job done."

as I post 3 more options as i read that and I am not sure if these are true workable ! so I need your advice on that! the access one I am not sure! you can suggest why need an extra piece of Access to site in between MS SQL and MySQL, this is my wonder.

You need Access pretty much for the same reason you need Inspirer....while Workbench will do some work it gives you very little control on what to do.

"You need Access pretty much for the same reason you need Inspirer...."

it can convert all logic, e..g. SP, function for us ? but need a licensed copy of office.

any URL on how to do it using Access?

"Workbench will do some work it gives you very little control on what to do."

might be i don't need to control it as I only need full set and just want a easiest way.

can workbench migrate PK, FK, constraint and index ?

As i said i don't have a handy sample to test this scenario...normally it should do it ...have you give it a try,.

also how can ETL tools migrate all:

1) table,
2) data
3)PK and FK ?
4) SP, function and T-SQL code

to MySQL, any online

but you have experience on how to use Access for that, right ?

Right..:)

any URL show me how this can be done by access?

ETL no idea right?

As for the links for Access ...you start googling  with the terms "VBA", "MSSQL","MySQL", "export" and you add what you want each time.
Here is one to get you started : https://social.msdn.microsoft.com/Forums/office/en-US/e7014a0d-18f6-4664-be3b-0c4e1827cc8d/sample-vba-code-to-pass-a-query-through-to-a-remote-sql-server-database?forum=accessdev

oh,

Here is one to get you started : https://social.msdn.microsoft.com/Forums/office/en-US/e7014a0d-18f6-4664-be3b-0c4e1827cc8d/sample-vba-code-to-pass-a-query-through-to-a-remote-sql-server-database?forum=accessdev

oh, need VBA skills for it?

ok, then have to forget access

Sorry to hear that...what programming skills you have ? or your colleagues

I think I will try workbench first and see if PK, FK, index and constraint can be handle!

they know java !
python

Excellent...both languages have excellent connectivity with both Engine so it would be easy to handle the job

but the one who know it is not me ! I will update them to scare them if they say why I do not use ACCESS and VBA.
 AHAHAA

tks man.

please help to answer this:

https://www.experts-exchange.com/questions/29164115/MySQL-support-life-cycle-what-is-the-different-between-extended-and-sustaining-in-terms-of-pre-existing-items.html

Well it finally found me ( i had some task in MsSQL) so i thought i should pass the info.
MSSQL has the functionality to export everything in a nice (big) .sql file so you can work with it in your migration process
Right Click you database in SSMS -->Tasks -->Generate Scripts
You will presented with a wizard like screen..hit next
Now the wizard will work for a while to gather the available database Objects...like in my case has found Tables, Views,Stored Procedures
You can script the Entire database or by object type
Make your choice and hit next
This screen is the crucial one
 
Very Important --Hit the Advanced button as this controls what to export and what NOT
The default for export is schema (probably is hidden in some settings to change it but for now it worked for me)
So change it to Schema+data
Do explore the Other options...they might be useful depending on the case
Return to previous screen...decide on the name of the exported .sql and hit next
Review what you are about to export and hit Next...when the operation completes..you will have a complete copy of your database as a single .sql file to work your way to translating it to MySQL
Hope that this helps a bit

yeah, tks.

but all that can't import to MySQL directly !

and also can't export SP and all other logic.

Yes you can export SPs

ok. tks. but that export designed to export object to other MS SQL, not mySQL, right? and finally we still can't import it directly as SP and database schema syntax is not the same.

Right on everything...2 different worlds so we need "bridges"

tks.