Thursday, January 7, 2010

CF: Connecting ColdFusion to Microsoft Azure SQL

I am impressed by what Microsoft has been able to create with the Azure platform. The more I play with it the more I like it. What impresses me most is how simple it is to get going (I am comparing with Amazon). The big caveat here is that you use MS technologies. If so, Azure should be on the short list for evaluation.
However, and, here I go again reversing myself, if you want to connect a ColdFusion front end to a Microsoft Azure SQL back end, things are a little more quirky. Please don't ask why you would do such a thing, just stay with me in that it is highly cool ;o)

First, you should evaluate whether your application will give to this kind of setup. On the ColdFusion side you should have good control on the amount of data you exchange with the database(pagination and other techniques); you also should have some tolerance for higher latency; there is delay in the round trip to get data from Microsoft etc.

Of course the usual evaluation of whether you are ok with hosting data in the cloud should take place. But on the other hand, taking this for a spin is easy enough.

In my setup I am hosting the CF server on my laptop and MS is supplying the database.

1) Learn about Azure: Learn about Azure things and then Create Account

2) Create your first database instance online using your browser

3) Get your tools. You do not need to download the Visual Studio or other SDKs to work with SQL Azure, but you will need to use the SQL Server 2008 R2 Management tools. Currently only available as CTP edition.

4) Optional: Migrate some data. For me, using the script wizard in the Management Studio worked the best. You can switch it to produce SQL Azure specific scripts, but you still have to tweak them. Your primary keys have to be clustered when creating tables.
e.g.:

CREATE TABLE [dbo].[TestTable](
[Test_ID] [int] NOT NULL,
[TestName] [nvarchar](50) NOT NULL,
[TimeZoneOffsetHours] [smallint] NULL,
[TimeZoneOffsetMins] [smallint] NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (
[Test_ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)



5) Once you have some data we start with the ColdFusion parts. I have not been able to use the Adobe supplied drivers. I had to download the Microsoft JDBC 2.0 drivers. When you extract the jdbc drivers there are two .jar files. For CF you will need to use [sqljdbc4.jar]. Copy this driver into [cfroot]\lib folder.

6) Restart ColdFusion

7) Configuring your Data Source in the ColdFusion administrator. Here it gets a little complicated. You need to create a data source of type "other". Also, you cannot use the connect strings that are supplied by MS directly, though pieces are useful. The current stance is that Microsoft does not support JDBC connection but if you can work it they are fine with it. To make it simple I am attaching an image: (please note the way in which we have to set the User name)(replace myID with the database User ID and myServer with your server name from Azure):



8) Configure your connection settings. The Azure platform will drop your connection frequently. So maintaining connection is not really an option. This is what worked best for me to make it appear as if we had no connection issues.



Ok, hopefully this all worked out for you and you can take this for a spin.

Cheers,
Bilal

13 comments:

Phillip said...

Interesting post Bilal.
I have a friend that wants to experiment with Azure, so maybe I can get the ball rolling on my end first.

TJ Downes said...

Bilal, thanks for the CF on Azure article. I got mine up and running with ORM, thanks to your info.

I am curious if you've used CF with Azure blob storage yet?

bman said...

TJ I have not used the Microsoft blob service. I am investigating using Amazon RDS next.

Jack Roark said...

Thank you so much for posting this!

JT said...

Hi, Bilal --

I appreciate your posting this but am hoping that you can shed some ideas/thoughts
on why I am running into a problem after following your post.

I have an Azure SQL DB built and available (with data) and a local version of CF on my dev pc.

I downloaded the (more recent) MS JDBC drivers, followed your direction and got a DSN configured and it
verifies in the CF admin.

However, when I try to run my CF app which sets some CF variables:
dsn - set to the datasource name in the CF admin,
databaseUserName - set to the DB user@azureservername as indicated in your post
and the databasePassword

and then attempts to perform a query via a cfquery tag:


<cfquery
datasource="#dsn#"
name="web"
username="#databaseUserName#"
password="#databasePassword#"
blockfactor="100">



I get a CF error:

Error Executing Database Query. The connection is closed.
(Detail): com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.


Any ideas/pointers/suggestins would be MOST appreciated! Thanks in advance!

JT

bman said...

@JT:
Having the CF Admin confirm the connection is pretty good.

Normally you do not need to supply a username and password in the query again if they are specified in DSN.

I am assuming you ran into the second issue with frequently dropped connections. Try adding a validation query (click advanced button, scroll to the bottom). Something like : SELECT @@Version

JT said...

Thanks for the response, Bilal.

What I still don't get is how in the CF aAdmin, it will successfully connect(?)/verify the datasource but I get the 'connection closed' error in the .cfm code on ANY cfquery attempt so its not like it connects than timeout - they never work. It's almost like the connection is never initiated???

Just to make sure I wasn't getting 'false' verification in the CF Admin, I tried changing the connection string to have an invalid DB and subsequently got:

Connection verification failed for data source: Trucking_azure
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "sucking" requested by the login. The login failed. ClientConnectionId:3239d6da-cf91-4369-93ec-61aded1f331b
The root cause was that: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "sucking" requested by the login. The login failed. ClientConnectionId:3239d6da-cf91-4369-93ec-61aded1f331b


Soooo frustrating! (Reaching now) Could it be a CF execution permissions issue??

bman said...

@JT
I did a complete test and things still seem to work. I did not update the jdbc driver though.

Reading your error message again I would guess that the source of your issues seems to be your login.

You will need to specify your login in the specific format as on image. For example if your login name was supersa and you connect to endpoint
pptuntngy44.database.windows.net you need to add this to the jdbc definition and your username like so (there is space character between User and ID):
jdbc:sqlserver://pptuntngy44.database.windows.net:1433;databaseName=suckng;User ID=supersa@pptuntngy44.database.windows.net

Your username box would be:
supersa@pptuntngy44.database.windows.net

Providing a validation query helps CF to check before each re-use of a connection whether the connection is still active. It will drop and recreate connection when this one is not working for any reason.

Larry said...

Thanks Bilal, this worked great.
I was setting up a new Coldfusion server and a new SQL Server and thought I'd try out SQL Azure as an option.

Michael said...

I'm getting "No suitable driver found" - is this because I'm running CF9 @ Java Version 1.6.0_14?

bman said...

@Michael:
Should work with CF9.
You most likely have not copied the microsoft jdbc jar file in the right lib subfolder.
Though an upgrade to the JVM is a good idea for you as well.

Glenn Williams - tinylion said...

thank you thank you thank you! had been struggling with this until your post.

regrds
glenn

Glenn Williams - tinylion said...

ps it worked a treat. I had missed out the @serverName added to end of user name in both string and the username ui text input. once my old eyes spotted that all was wonderful. thanks for taking time to share this. you've just saved me a couple of hours googling and scratching my head ;-)

cheers
glenn
tinylion