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


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
Filed in ruby on rails tutorials
Tagged as   
Posted on 24 May
91 comment Bookmark   AddThis Social Bookmark Button
Comments

Leave a response

  1. Small Boxes – May 25, 2010 @ 08:51 AM

    Thanks for your post, it is not an easy solution.

  2. jewelry review – June 30, 2010 @ 04:00 AM

    I hope I can improve through learning this respect. But overall, it’s very nice. Thank you for your share!

  3. Acronym List – July 07, 2010 @ 01:47 AM

    The Acronym List is a searchable database of over 8 million acronyms, abbreviations and meanings. Covers: business, international, chat, organizations, common acronyms, computers, science, technology, government, telecommunications, and military acronyms.

  4. moncler – July 07, 2010 @ 11:43 AM

    Thanks for your post, it is not an easy solution.

  5. goldmine – July 12, 2010 @ 08:04 AM

    this is really informative post and a goldmine for me

  6. aipc-mtl-iabc – July 15, 2010 @ 05:20 PM

    I would be confusion if the deal with this. but with this post is, of course I will be able to follow step by step, as you point out

  7. dhilzone – July 15, 2010 @ 05:23 PM

    This is not easy, let alone someone like me who have not studied sql

  8. Jokosutrisno – July 15, 2010 @ 05:29 PM

    sql always makes me dizzy, but your post looks very easy sql

  9. natural tinnitus cure – July 19, 2010 @ 01:06 PM

    Thanks for your guide.

  10. Dicios – July 24, 2010 @ 08:24 PM

    Good article, thank you so much to share :)

  11. Juicy Couture – August 03, 2010 @ 07:33 AM

    Juicy Couture Shop the latest styles juicy couture handbags, juicy couture tracksuit.

  12. Juicy Couture – August 03, 2010 @ 07:40 AM

    Shop the latest styles juicy couture handbags, juicy couture tracksuit. Juicy Couture

  13. white sundresses – August 09, 2010 @ 12:49 AM

    This is great site! Thanks! Awesome! Two thumbs up!

  14. dress – August 10, 2010 @ 03:47 AM

    autoradio navigation, car dvd gps navigation, sat navigation stereo, OEM Factory headunit for all car makes

  15. dress – August 10, 2010 @ 03:48 AM

    autoradio navigation, car dvd gps navigation, sat navigation stereo, OEM Factory headunit for all car makes Higher quality car electronics from Qualir Car DVD Player

  16. dress – August 10, 2010 @ 03:49 AM

    autoradio navigation, car dvd gps navigation, sat navigation stereo, OEM Factory headunit for all car makes Higher quality car electronics from Qualir Car DVD Player

  17. juicy couture – August 11, 2010 @ 06:44 AM

    It sounds good! Thanks for sharing.

  18. juicy couture – August 11, 2010 @ 06:49 AM

    Shop the latest styles juicy couture handbags, juicy couture tracksuit. Juicy Couture

  19. Drew Noakes – August 19, 2010 @ 11:24 AM

    If you set all links on your comments to use rel=”nofollow” then you wouldn’t get so much spam.

  20. Horrorclown loves Maske – August 23, 2010 @ 02:37 PM

    Thanks very much! This has helped me out on another topic i wasnt able to solve since ive read this blog entry! Its so easy i dont understand why i did it another way wasting so much time…

    thanks ruby !!

  21. smackdown izle – August 24, 2010 @ 04:44 PM

    Thanks for your guide.

  22. Kredit-Karte – August 25, 2010 @ 08:29 PM

    Hope all goes well with your new database server! Good luck!

  23. brille – August 26, 2010 @ 08:58 AM

    thanks for this great information. i hope coming more!

  24. Tiffany – September 04, 2010 @ 07:04 AM

    thanks for this great information. i hope coming more!

  25. Gucci – September 04, 2010 @ 07:05 AM

    thanks for this great information. i hope coming more!

  26. MBT – September 04, 2010 @ 07:05 AM

    thanks for this great information. i hope coming more!

  27. Exchange Rate – September 08, 2010 @ 01:59 AM

    Well worth the read. Thanks for sharing this information. I got a chance to know about this.

  28. 2 Blackjack 1 – September 10, 2010 @ 02:33 AM

    Good post, thanks for share.I like to visit more often, is there a place to get tutorials on this game, Your post is amazing.

  29. coach bags – September 29, 2010 @ 07:30 AM

    Welcome to fashion goods online store, http://www.coach-outlet.co.uk coach bags http://www.coach-outlet.co.uk Coach Outlet http://www.coach-outlet.co.uk coach outlet store online http://www.coach-outlet.co.uk coach handbags

  30. nike – October 08, 2010 @ 12:53 PM

    mason0507 thank you

  31. nike – October 09, 2010 @ 03:07 AM

    maosn05407

  32. Currency – October 11, 2010 @ 02:20 AM

    About Moving from Mysql to Microsoft SQL Server in Ruby on Rails 31 ,you give a wonderful solution,but this is not easy, let alone someone like me who have not studied sql.

  33. Moncler Homme – October 19, 2010 @ 08:01 AM

    I would like to explain the misunderstanding for women and men. That is, we often think that men like women to wear high heels. Especially the women just fall in love, in order to perform better on her former boyfriend can easily lead to misunderstanding of some error. Such an interesting story: a couple just contact a week, early morning when the men want to see her lovely girlfriend, came to his girlfriend’s doorstep. But when he saw his girlfriend wearing a fine pair of boots, the man immediately lost her good opinion. Because men want to look like his girlfriend just woke up. Most men like to wear high heels in addition to women, women will love to wear sportswear. Perhaps this is a man to a woman from his

  34. coach bags – October 22, 2010 @ 12:32 PM

    http://www.coach-outlet.co.uk coach bags http://www.coach-outlet.co.uk Coach Outlet http://www.coach-outlet.co.uk coach outlet store online http://www.coach-outlet.co.uk coach handbags

  35. Anna – November 02, 2010 @ 11:37 AM

    Moving database to MySQL offers many benefits but not always is the best solution for your application. here are many applications out there that use a combination of Microsoft Access and JET to manage data. These applications are only used by a single user, and often are used in situations where the data file is simply copied to a new machine when it needs to be moved.

  36. alomda – November 03, 2010 @ 08:00 AM

    Beautiful and attractive site. برامج

  37. dw – November 03, 2010 @ 08:17 AM

    thnx 4 sharing الجياش

  38. Dental Jobs – November 05, 2010 @ 10:42 AM

    Defenitly been the best soloution for our business, great article

  39. Regim Hotelier – November 05, 2010 @ 12:10 PM

    Not an easy solution and not always the best. Thanks for the post, much easier after reading it.

  40. Rate My Muscle – December 14, 2010 @ 10:07 PM

    Ahh moving databases is a tricky thing to do. I had to go through that when I changed hosts….what a nightmare it was!

    Rate My Muscles

  41. juicy couture charms – December 16, 2010 @ 06:24 AM

    Nice post.Thank you for taking the time to publish this information very useful!I’m still waiting for some interesting thoughts from your side in your next post thanks

  42. Tim Rente – December 23, 2010 @ 07:33 AM

    Tough stuff. This is not easy, let alone someone like me who did not study sql, but I’ll give it a try.

  43. ugg boots – December 27, 2010 @ 06:49 AM

    I ordered these because I thoughtUGG Australia is a brand that is all about luxury and comfort for everyday life.

  44. Cluj Hotels – January 13, 2011 @ 01:29 PM

    Thanks for the post, much easier after reading it. Not an easy solution and not always the best.

  45. Juicy Jewelry – January 20, 2011 @ 06:04 AM

    Hello!!It is my horour to see you blog.Iam agree with you,Ithink friendship is very important,so we must have a lot friend,History repeated proof: friend, health than leadership than performance than IQ, eq, holiday greetings than usual, asss an important than. Space than the ground is good, to visit friends, no tickets.

  46. localtime – January 21, 2011 @ 07:13 AM

    I want to Moving from Mysql to Microsoft SQL Server in Ruby. thanks for sharing this.And i believe you have solved the problem.

  47. marek – January 22, 2011 @ 10:00 PM

    Moving database to MySQL offers many benefits but not always is the best solution for your application. Our website has a back office built in MySQL and i sware it’s a nigthmare to operate . Lunettes Ray Ban

  48. Ruby on Rails programmer – February 01, 2011 @ 09:20 AM

    I’ve had a general idea of moving from MySQL to MSSQL, but your detailed solution now makes all things clear =)

  49. Photo 555 – February 05, 2011 @ 12:00 PM

    This article is interesting, but unfortunately, it seems quarkruby doesn’t live any more…

  50. Chirurgie esthetique tunisie – February 08, 2011 @ 01:07 PM

    My company use Ruby, so it’s very interesting

  51. sildenafil – February 08, 2011 @ 09:23 PM

    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.

  52. silk blanket – February 11, 2011 @ 07:13 AM

    this guide is helpful, thx!

  53. Leather Wallet – February 12, 2011 @ 03:01 PM

    This is exactly what I’m looking for. Really nice tutorial this.

  54. Prietene – February 18, 2011 @ 08:47 AM

    I`ll be back to read your site , lets hope that your future news will be as good as this ones are. I`m glad i found this site! What you`ve wrote here is very true and can be very usefull for the readers of this site. Have good luck with your site and i`ll be back to read your new informations.

  55. printing – February 20, 2011 @ 10:41 AM

    Really nice, im doing a new website for my new printing business and this will help me set up my new database of printing prices.

  56. menu printing – February 20, 2011 @ 10:42 AM

    really great i have a printing site too, it’s been really good for me.

  57. MBT Changa Shoes – February 23, 2011 @ 03:05 AM

    really nice blog,I MBT Changa Shoes think most people would agree with your article. I am MBT Changa Shoes going to bookmark this web site,I will check this site ofen.

  58. What are <a href="http://www.chaneloutletstores.org" > chanel store</a> the s – February 25, 2011 @ 07:38 AM

    What arestorm…Gossip: what kind of dressing girls have treasure?Together and see it!

  59. Vegas Strippers – February 25, 2011 @ 12:43 PM

    This will really help us with website devlopment

  60. lv – March 01, 2011 @ 02:26 AM

    xiaoru

  61. lv – March 01, 2011 @ 02:32 AM

    xiaoru

  62. lv – March 01, 2011 @ 02:32 AM

    xiaoru

  63. Albert Frank – March 01, 2011 @ 08:54 AM

    For any C, C++, Java, ASP.net, PHP, JSP, Fortran or any other Programming and IT Security Assignment help you need, please visit our site at http://www.helpwiththesis.com. We help College and University students at a minimal price.

  64. hospedagem – March 01, 2011 @ 07:55 PM

    Solid tutorial and explanatory. Congratulations. It was exactly what I was looking for. Thank you.

  65. shop chanel – March 04, 2011 @ 01:53 AM

    For any C, C++, Java, ASP.net, PHP, JSP, Fortran or any other Programming and IT Security Assignment help you need, please visit our site at http://www.helpwiththesis.com. We help College and University students at a minimal price.

  66. Ruggy – March 04, 2011 @ 01:42 PM

    For any C, C++, Java, ASP.net, PHP, JSP, Fortran or any other Programming and IT Security Assignment help you need, please visit our site at http://www.helpwiththesis.com. We help College and University students at a minimal price. take me home tonight download watch jane eyre online jane eyre download watch red riding hood online

  67. stainless steel stair handrails – March 08, 2011 @ 04:40 AM

    Hello,

    I have a strong sql base but am looking for some courses that can help me learn the structure and ins and outs of sql. I need a class in Houston, or even online may work. Has anyone had experience with a good training class for sql in Houston? Thanks!

    Regards, Benix

  68. ebooks – March 10, 2011 @ 09:09 AM

    Outstanding tuto. Thanks for this.

  69. Chi Ceramic Straightening Iron – March 11, 2011 @ 11:38 AM

    tyujhyujhgjhgjme courses that can help me learn the structure and ins and outs of sql. I need a class in Houston, or even online may work. Has anyone had experience with a good training class for sql in Houst

  70. recherche femme russe – March 17, 2011 @ 01:24 PM

    What an excellent blog! Thanks very much! This has helped me out on another topic i wasnt able to solve since ive read this blog entry! Its so easy i dont understand why i did it another way wasting so much time…

  71. Minnesota Criminal Lawyer – April 05, 2011 @ 09:04 PM

    This tutorial is exactly what I was looking for. Step by step. Thanks for sharing

  72. angel – April 06, 2011 @ 02:14 PM

    je vous remercie de partager, très gentil de votre part

  73. Dental Jobs – April 12, 2011 @ 12:11 PM

    Great Blog, i’m new to SQL but it’s becoming more apparent as I start understanding the concept. We are in the process of redeveloping out site in aspx and more information the better.

  74. Dental Jobs – April 12, 2011 @ 12:12 PM

    I forgot to mention does anyone know where I can find a good tutorial on profile building?

  75. mensagens para orkut – April 13, 2011 @ 06:16 PM

    Is there any open source tools available for migration.

  76. saint petersburg hotels – April 24, 2011 @ 07:51 PM

    Thank you for submiting this entry. Keep it up!!

  77. shoutbox – April 26, 2011 @ 01:58 PM

    how to create the same Shoutbox without using Flash?

  78. Sirto – April 29, 2011 @ 03:55 PM

    Thank you for submiting this entry. Keep it up!! fast five download

  79. Best free antivirus – April 30, 2011 @ 07:15 PM

    The comments must be somehow moderated. This is the only option if you want to remove a part of these spam comments.

  80. miere tei – May 07, 2011 @ 11:25 AM

    This is a relative issue. It’s about what you want at a time. Switching can be done, but you can not please you.

  81. long term disability attorney – May 08, 2011 @ 07:18 AM

    I got a chance to know about this. Thanks for sharing this information.

  82. chaussures mbt – May 11, 2011 @ 06:51 AM

    très bon site et j’aime beaucoup.

  83. Martin Giroux – May 11, 2011 @ 04:19 PM

    I had a real problem with my DNS config, after a while I realized that the ODBC Data was not pointing to the right names.

  84. James Richard – May 12, 2011 @ 02:42 PM

    While I was running the TDS driver config, I had this idea of reverting some of my code to a better approach with MySQL. Thanks to you

  85. STS – May 18, 2011 @ 09:04 AM

    Thanks for the post !

  86. Charity Jobs Scotland – May 20, 2011 @ 09:34 AM

    While Setting up the Mssql connection I am getting error installing the required dependencies. yum install unixODBC unixODBC-devel yum install freetds freetds-devel

  87. alex777 – May 21, 2011 @ 09:02 AM

    InterestingI layout on your blog. I really enjoyed reading it and also I will be back to read more in the future.

  88. alex777 – May 21, 2011 @ 09:03 AM

    InterestingI layout on your blog. I really enjoyed reading it and also I will be back to read more in the future.

  89. I really hate those nofollow stuff. Not because the link does nit counts, more because it sucks to g – May 22, 2011 @ 07:28 AM

    I really hate those nofollow stuff. Not because the link does nit counts, more because it sucks to give someone content for nothing. Here in germany some “webmaster” are really sick! Very often for example bookmarking services grab as many content they can get, but after a while they got nofollow. Thats sick! Sorry…

  90. yml should make it an easy solution. I have shared my findings in following two sections. Brian Brun – May 22, 2011 @ 07:28 AM

    yml should make it an easy solution. I have shared my findings in following two sections. 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. ADO mode allows Rails applications running on Windows to access SQL Server. We start with installing the required dependencies. Please refer to the table at the bottom of this post for queries to easily get started with tsql. Lets configure the database connection. Thanks for sharing this information. I got a chance to know about this. Because men want to look like his girlfriend just woke up. here are many applications out there that use a combination of Microsoft Access and JET to manage data.And i believe you have solved the problem. We help College and University students at a minimal price. It was exactly what I was looking for. We are in the process of redeveloping out site in aspx and more information the better. This is the only option if you want to remove a part of these spam comments. I really enjoyed reading it and also I will be back to read more in the future. They are devoted to web technology boutique.com or can be hired for consulting at Downcase Consulting. I really hate those nofollow stuff. Not because the link does nit counts, more because it sucks to give someone content for nothing. Here in germany some “webmaster” are really sick! Very often for example bookmarking services grab as many content they can get, but after a while they got nofollow. Thats sick! Sorry…

  91. luckybrand – May 24, 2011 @ 04:46 AM

    This is a difficult thing. Seriously, I’ve experienced it myself. If it’s possible, I would avoid moving server.

Comment