Ruby, TinyTDS, and Azure SQL Database update

I got a note the other day that the 0.6.3-rc2 version of TinyTDS now includes the security bits needed to communicate with Azure SQL Database, on the Windows platform (it worked on other platforms just fine.) Previously, you had to manually build it against OpenSSL for it to work on Windows platforms, but with the new version it 'just works'. Use the following to install it from the command line:

gem inst tiny_tds --pre

or in a Gemfile use:

gem "tiny_tds", "0.6.3-rc2"

The following code is an example of connecting to SQL Database using the tiny_tds gem:

 require 'tiny_tds' client=TinyTds::Client.new(:username=>’user’, :password=> ‘password’,
  :dataserver=>’servername.database.windows.net', :port=>1433, :database=>’databasename’,
  :azure=>true) 
results=client.execute("select * from [names]") 
results.each do |row| 
puts row end

Tiny_tds can also be used with ActiveRecord. The following is an example database.yml for using a dblib connection to SQL Database using the tiny_tds gem.

 development:
 adapter: sqlserver 
 mode: dblib 
 dataserver: 'servername.database.windows.net' 
 database: databasename 
 username: user 
 password: password 
 timeout: 5000 
 azure: true

NOTE: All tables in SQL Database require a clustered index. If you receive an error stating that tables without a clustered index are not supported, add a :primary_key field.

NOTE: If you are using an older version of Ruby on Rails, Active Record, or the activerecord-sqlserver-adapter gem, you may receive an error when running migration (rake db:migrate). You can run the following command against your SQL Database to create a clustered index for this table after receiving
this error:

 CREATE CLUSTERED INDEX [idx_schema_migrations_version] ON [schema_migrations] ([version])

After creating the clustered index, rerun the migration and it should succeed.

Enjoy!

- Larry