archive

Moving from Mysql to Microsoft SQL Server in Ruby on Rails

Recently one of our client asked us to make a shift from Mysql database to Microsoft SQL Server (Mssql) in a project where we have been developing in Ruby on Rails collecting online shopping data. We agreed thinking RoR comes with ActiveRecord ORM, so just changing adapter and configuration in config/database.yml should make it an easy solution. Right?

Not exactly. Why?

1. Setting up MS Sql connection from rails application is a serious pain in the a** and I have to do days to research to get it right. I have shared my findings in following two sections.

   Understanding the process to connect Mssql from Ruby on Rails

   The communication protocol used by client software to communicate with MS SQL Server is called Tabular Data Stream(TDS). Brian Bruns started an open source project called FreeTDS to provide an implementation of the TDS protocol for anyone who wants to connect to Mssql. FreeTDS comes with several interfaces to TDS including ODBC.

   The rails SQL Server database adapter has two modes: ADO and ODBC. ADO mode allows Rails applications running on Windows to access SQL Server. ODBC mode also allows Rails applications running on non-Windows platforms to access SQL Server. In ODBC mode, the adapter connects to SQL Server through an ODBC driver.

   In ODBC mode, the SQL Server adapter requires the Ruby/DBI ODBC driver, which depends on the Ruby ODBC module. The Ruby/DBI module provides a database independent interface for accessing relational databases from within Ruby. Ruby/DBI has a two layer architecture. The database interface (DBI) layer provides a set of common access methods that are used the same way regardless of the underlying database. The database driver (DBD) layer is database dependent. Each driver provides access to a particular database, translating between the DBI layer and the database. The Ruby/DBI ODBC driver provides access to databases for which an ODBC driver is available.

   In short, the connection to the Mssql from Rails app would be something like this:
   
   Ruby/Rails => ActiveRecord SQL Server Adapter => DBI, DBD-ODBC Gem => Ruby ODBC Gem => unixODBC => FreeTDS => SQL Server.

   Setting up the Mssql connection

   We have done the setup on CentOS, but it should pretty much work with any unix flavor. We start with installing the required dependencies.

   
   yum install unixODBC unixODBC-devel
   yum install freetds freetds-devel
   

   These two will provide a Mssql client "tsql" to connect to sql server from command line, which is very much like "mysql" command for mysql. Please refer to the table at the bottom of this post for queries to easily get started with tsql.

   
   gem install dbi
   gem install dbd-odbc
   gem install activerecord-sqlserver-adapter
   gem install ruby-odbc 
   

   So, we are done with installing the required gem packages. Lets configure the database connection.
   
   • TDS Driver Config file: /etc/odbcinst.ini
     unixODBC needs to know about all ODBC drivers you intend to use, installed drivers are specified in this file. Add the following lines in the end
     

     
     [FreeTDS]
     Description     = ODBC for SqlServer
     Driver          = /usr/lib64/libtdsodbc.so
     Setup           = /usr/lib64/libtdsS.so
     FileUsage       = 1
     

     You may want to check if the file exists at given location. If not, the right path will be /usr/lib/libtdsodbc.so
     
     
   • Sql Server Definition Config file: /etc/freetds.conf
     For each of the Sql Server, define a entry, something like:
     

     
     [my_config_server]
       host = sql.example.com
       port = 1433
       tds version = 8.0
     

     Give a useful name for "my_config_server" because it will be used further in config files
     
     
   • DSN Config file: /etc/odbc.ini
     ODBC client applications will typically work with ODBC Data Source Names (DSN). In layman terms, for each database, we need to define a connection like
     

     
     [my_dev_server_dsn]
     Driver=FreeTDS
     Description=My Server Connection
     Servername=my_config_server
     Server=my_config_server
     Port=1433
     Database=SampleDatabase
     APP=ConnectionString
     

     The config unique identifier my_dev_server_dsn, will be used in database.yml config file.
     
     
   • Rails App Config File: RAILS_ROOT/config/database.yml
     

     
     development:
       adapter: sqlserver
       mode: ODBC
       username: sample
       password: sample
       database: SampleDatabase
       dsn: my_dev_server_dsn
     

   
   And, yes we are done! ./script/console should work like a charm connecting Mssql :-)
   
   
   
2. Mysql and Mssql have different sql query format. So, in few cases you may have to fix your find queries:
   • Specifying "join on columns" in a join query is mandatory with Mssql, which is optional with Mysql and can be done in where clause.
   • "Order by RAND()" does not works in Mssql, because RAND() is only a Mysql function. Moreover, rails will not raise an error if u r using this function in your queries :(
     The Mssql method is NEWID()
3. ActiveRecord/Migrations is not 100% Mssql complaint.
   • The "date" logical type of rails, which maps to "date" datatype in mysql becomes "datetime" datatype in Mssql. Beware of this, if you are doing any date related calculations.
   • The boolean logical type of rails maps to "boolean" datatype in mysql, becomes "bit(1)" datatype in Mssql. Keep in mind, code snippets like these doesn't works and neither they raise an exception :(

     1 2	Product.find(:first, :conditions => {:valid => true})

     Such queries are to be rewritten to:

     1 2	Product.find(:first, :conditions => {:valid => 1})

   • "float" logical type of rails maps to "float" data type in mysql, becomes "decimal" datatype in Mssql. This doesn't have direct impact anywhere in your code. But, if you were trying to import your Mysql data dump into Mssql, then you might not get correct values imported. In my case, values like 2.33 became 2.33e+19 etc.

Running Mssql queries from Tsql

Purpose	Mysql server	Ms Sql Server
Connection To Server	mysql -uusername -ppassword -hhostname	tsql -S hostname -U username -P password
Use Database	mysql> use databasename;	1> use databasename 2> go
show tables	mysql> show tables	1> select name from databasename..sysobjects where xtype='U'; 2> go
show some rows from a table	mysql> select * from tablename limit 10;	1> select top 10 * from tablename 2> go

Our Products

         

We consult at

    

-->