Migrate Table data from MSSQL to MySQL with MssqlMysqlMigrator

This gem puts puts together my code to migrate the data from a MSSQL to a MySQL server. Its was done to move an old rails project from windows to linux.

It is NOT using Rails Active Record. This is because i want to move the data and avoid all overhead od interpreting or validating them. I consider this part of the actuall business logic. Also this frees this gem from all dependency on the actuall rails app, removing of course also the dependency on rails or any particular ruby/rails version combination.

Importing data to mysql is done via CVS file import. Again, this is for efficency reasons. Letting MySQL itself load the data from disk in bulk mode give you access to the most efficient code path there is. You simply can't load SQL data toa database faster and better than the database itself.

Most simple usage to transfer all talbles would be:

$ mssql-mysql-migrator transfer source_db destination_db

The database account are found in 'database.yml' config file. See database.yml.template (like rails, but it goes into Sequel, not ActiveRecord). Overload default config lookup with --database_config=your-config-file.yml.

Documentation for now is limited, please read the source and exploit the code for whatever usage you might have in mind. Over time, when actually demanded, documentation, and error handling for this gem might improve.

A Full blown example like my actuall data transfer would look like:

$ mssql-mysql-migrator  
    --loglevel=info                                 # defines verbosity(error, warn, info, debug)
    --debug                                         # shows callstack in case of error
    --outdir=$(pwd)/tmp                             # where to put the CSV files
    --exclude-tables=schema_info,schema_migrations  # not transfered
    transfer ac-express-live test                   # source and target DB

More usage examples

checking the character encoding

Character encoding, when you have to think about it, tends to easily turn into a nightmare. A triple error in an encoding chain might make it all look nice and right, when it is not. The encoding is also a parameter in the database condig file but to help you tinkering with various setting there is the encoding test where you can check multiple encoding option from the command line. See my example:

$ mssql-mysql-migrator encoding-test database:table_name:column 'id = 31' iso-8859-1,cp1252,latin1

With this command you read exactly one value from a database in all the encoding you list (comma seperated, no blanks) at the end. Easy way check what you get from you db connection depending on the encoding setting.

list tables

yeah, easy:

$ mssql-mysql-migrator list-tables ac-express-live

Will help you to specify which tables you actually have and need to copy.

Installation

Add this line to your application's Gemfile:

gem 'mssql_mysql_migrator'

And then execute:

$ bundle

Or install it yourself as:

$ gem install mssql_mysql_migrator

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake rspec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/[USERNAME]/mssql_mysql_migrator. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

License

The gem is available as open source under the terms of the MIT License.