We received this question from a member of the community:

Can you recommend a good tool to transfer large amount of data from SQL Server to the MySQL infobright table?
I want to load a few hundred giga bytes data for the initial test.

The simplest option is to export your data to a CSV file using the “bcp” command line utility for SQL Server. Transfer the file to the Infobright server. Then load the file using the Infobright loader.

To avoid the file overhead, you can use a named pipe (FIFO) instead of a file. Create the pipe on the local server where IB is running. Launch a LOAD DATA INFILE command to read from the named pipe.

If you need to stream data from a remote Unix machine, you can use the netcat (nc) command to read data from the network and stream it to the named pipe. See “man nc” for more info.

Best regards
Geoffrey

We now have a Windows gui tool to help SQL Server users migrate to Infobright. It takes information from MS SQL and generates a file with the create table statements, bulk copy commands and MySQL load statements. It’s called ICE Breaker for SQL. If anyone is interested in this, please let me know.

I would be interested in a data tranfer wizard like this.

We have a tool that I developed called ICE Breaker for MS SQL Server that will generate the create table statements, the bulk copy statements and the load statements to help you migrate. It is going through a QA process now but I sent you the latest via email.

I would be interested in this also.  Do you know if you support OpenQuery formatted Queries?

It only assists in migrating data. Queries would either have to changed manually or you can use a tool like SwisSQL that does SQL conversions from many different databases to MySQL. Also converts stored procedures which has helped me in the past. Their website is http://www.swissql.com/.

I would be cautious in their migration tool because it’s going to try and create indexes and such and may not work with Infobright.

I actually ran into many problems loading data from SQL Server 2k -> Infobright. In my process, I ended up having to use the MS DTS Import/Export Wizard, export to a “Text file” format (csv) delimited by tabs, with no enclosures, then use something like:

  LOAD DATA INFILE ‘data.csv’ INTO TABLE `data_table` FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ‘NULL’;

This was mostly due to problems with quotes (both single and double) contained in some of the data blowing up the loader. Both the DTS wizard and bcp would refuse to properly escape these characters, causing the loader to simply soil itself.

The ICE Breaker was immensely helpful in replicating all of the tables I needed though! That was a great relief!