While running various performance tests (load balancing, DB drivers, etc.) on an app we're porting to Railo from ACF 8.0.1, I decided to try out the newest JDBC driver from Microsoft for SQL Server 2008 (version 3.0 of this driver was released in April 2010), as the built-in MS SQL server driver notes that it is for SQL Server 2005 (or so say my error logs).
I had yet to use the "Other" option in the Railo Web Administrator for a datasource and wanted to record what steps I took for later (or in the event someone else might be interested in the same). The process itself is pretty simple:
- Download the newest driver from Microsoft: http://www.microsoft.com/downloads/details.aspx?FamilyID=%20a737000d-68d0-453...
- Unpack/unzip the downloaded file. For me, expanding the archive resulted in a folder called sqljdbc_3.0
- Inside the expanded folder is another, called enu (or whatever language was selected with the download from step 1), which contains, among other items, a file called sqljdbc4.jar
- Copy or move sqljdbc4.jar to your Railo 'install' folder (I use Railo on Tomcat and, for me, this is at /Developer/tomcat/railo) ... it'll be the folder that contains a railo.jar file :)
- Restart Railo
- Open the Railo web administrator for your app
- Go to the Datasource page and enter your datasource name, selecting "Other - JDBC Driver" from the Type drop-down
- Enter your username and password as required by your DB on the ensuing page ('Create new datasource connection Other - JDBC Driver')
- For the Class field, enter: com.microsoft.jdbc.sqlserver.SQLServerDriver
- For the DNS field, enter: jdbc:microsoft:sqlserver://SERVERNAMEORIP:1433;DatabaseName=DBNAME
That's it. Not much to it, I know. However, I was not sure of what the right class name was for the MS driver and how to create the connection string for the DNS ... and, as I mentioned above, wanted to note it for future reference.
For anyone wondering, the performance of Railo's built-in Microsoft driver (vendor Microsoft) was nearly identical to the newer JDBC 3.0 driver. I used a pretty intensive query (lots of calculations, etc.) as my unofficial test case. The connection via the 3.0 driver was typically, but not always, a few milliseconds faster. When it was faster, it truly was negligible ... we're talking differences such as 437 ms for the 3.0 driver to 454 ms for the built-in driver.
Even though there wasn't any true performance gain, it's good to know how to deploy and use other JDBC drivers!