Accessing SQL Azure from an Azure Worker Role?

Question

I need to access a SQL Azure databse from a worker role.  It seems I have 3 choices:

ODBC
System.Data.SqlClient
PHP

My worker role is running a ruby on rails app - with the SQL Server adapter.  It works fine on the development fabric using OLEDB, but this is not an option for deploying to the cloud.

Neither can I use php. 

Is there any way I can either:

specify System.Data.SQLClient as the provider on the connection string?

or

set up an ODBC data source as part of the initialisation of the worker role and use that?

Thanks

Answer 1

Why not a web role, if I may ask?

---

mysorian

Answer 2

Hello, currently OLEDB is not supported. I don't know Ruby, but a search for Ruby ODBC on the internet returns a lot of results. Can you try to use an ODBC solution for Ruby? Please also make sure you have configured SQL Azure's firewall rule to "Allow Microsoft Services access to this server".

---

Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

Yes.  The normal (only) way to access SQL Server from ruby on linux is via ODBC.


I am happy to try this - but how should I set up the ODBC data source?  I know this can be done programatically during one of the start up events in the worker role, but is there an easier way to do this - by configuration or something?  Has anyone got any advice?

Answer 4

Why not a web role, if I may ask?

---

mysorian

Hi.  I am using Ruby.  This would mean I need an IIS fast-cgi component for ruby and as far as I know that's a little problematic?  Do you know differently?

Answer 5

Ignore me.  it works out of the box.  Need to set database.yaml as follows:

development:

adapter: sqlserver

mode: ODBC

dsn: Driver={SQL Server};Server=<server>.database.windows.net;Database=<database_name>;Uid=user@server>;Pwd=<whatever>;