I’m struggling with moving data from a SQL Server 2005 database to MySQL. The database has almost 300 tables that need to be converted.
I tried using bcp first but it creates messy output. The exported files don’t wrap text fields properly, so any records with commas break the import process. Plus I’d have to manually write all the table creation scripts since plain text exports don’t include column types.
What I really need is a migration tool that can connect to both databases at once and handle the transfer automatically. I know I’ll lose things like views and stored procedures, but copying basic table data should be straightforward, right?
Anyone know of a good tool for this job? I’m not worried about fancy features or perfect conversion. As long as it handles basic data types like numbers, dates, and text, that would work. I’ll be cleaning up and restructuring the data afterwards anyway, so I don’t need foreign keys or constraints preserved. I just need to get the raw data moved over quickly.
Any suggestions would be really helpful!
pentaho data integration (kettle) was a lifesaver for me last year. it eases the sql server 2005 to mysql migration and you can set transformations for many tables simultaneously. a bit of a learning curve, but def more reliable than file exports, especially with text fields that have special chars.
Have you tried mysqldump with custom scripts? What MySQL version are you using? That’ll help determine the best migration approach. Any huge tables in those 300? Connection-based tools often timeout on large datasets, so you might need a hybrid approach anyway.
I did a similar migration two years ago and MySQL Workbench’s Migration Wizard was perfect for this. It connects directly to both your SQL Server 2005 and MySQL databases, so you don’t need to mess with file exports that cause those formatting headaches with bcp. The wizard automatically maps data types and creates all your tables. With 300 tables, just run it in batches overnight. Text fields with commas work fine since it keeps the database connection instead of using delimited files. Watch out for DATETIME fields from SQL Server 2005 - they sometimes need tweaking in MySQL, but the wizard catches these upfront. Took me about 6 hours total for a similar-sized database, including verification.