Moving from Mysql to Microsoft SQL Server in Ruby on Rails  1121


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.
Tagged as   
Posted on 24 May
1121 comment Bookmark   AddThis Social Bookmark Button

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

PurposeMysql serverMs Sql Server
Connection To Servermysql -uusername -ppassword -hhostnametsql -S hostname -U username -P password
Use Databasemysql> use databasename;1> use databasename
2> go
show tablesmysql> show tables1> select name from databasename..sysobjects where xtype='U';
2> go
show some rows from a tablemysql> select * from tablename limit 10;1> select top 10 * from tablename
2> go