With SQL Azure right around the corner (expected to go live in November 2009), I had to evaluate if we could migrate some databases 'into the microsoft cloud'.

The pitch from Microsoft senior program manager David Robinson is "With SQL Azure, developers building Web 2.0, ASP.Net and PHP applications can use familiar tools and data models to develop on a pay-as-you-grow, secure, scalable and highly available database service at minimal infrastructure cost,".

First, there are limitations, but essentially microsoft is offering a subset of SQL server 2008 running on their infrastructure.

SQL Azure is the first hosted database product with good support for Transact-SQL. This means that any application using nearly standards compliant SQL queries could easily be migrated to use SQL Azure.

For example, using the MV framework, it could be as simple as changing your connection

PostgreSQL:

$db = MV_Database_Connection::create('pgsql://user:pass@localhost/database');

SQL Azure:

// Windows
$db = MV_Database_Connection::create('odbc_mssql://user:pass@DRIVER=Driver={SQL Server Native Client 10.0};Server=serverName.database.windows.net;Port=1433;Database=database;UID=user@serverName;PWD=pass;');

// Unix
$db = MV_Database_Connection::create('odbc_mssql://user:pass@DRIVER={FreeTDS};Server=serverName.database.windows.net;Port=1433;Database=database;UID=user@serverName;PWD=pass;TDS_Version=8.0;');

Since Microsoft has made it relatively easy to connect to SQL Azure from Windows (SQL Server Native Client), this post looks at connecting php5 on unix to SQL Azure.

There are two methods to connect to SQL Azure from UNIX.

a) Using mssql_connect()


// Install php5-mssql
// Check freetds version, v0.64 compiled with openssl or gnutls works with Azure

[root@dev] $ tsql -C
Compile-time settings (established with the "configure" script):
Version: freetds v0.64
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Thread safety: yes
iconv library: yes
TDS version: 5.0
iODBC: no
unixodbc: yes

// Add .conf entry so you can mssql_connect('AZURE', 'username', 'password');
// http://www.freetds.org/userguide/freetdsconf.htm#FREETDSCONFFORMAT

[root@dev] cat /usr/local/etc/freetds.conf
dump file = /tmp/freetds.log
debug level = 10
[AZURE]
host = serverName.database.windows.net
port = 1433
tds version = 8.0
client charset = UTF-8

b) Using odbc_connect()


// Install php5-obdc
// Check unixODBC version, 2.2.12 works well with PHP
[root@dev] $ isql --version
unixODBC 2.2.12

// Add the 'FreeTDS driver' so that unix obdc can use it
[root@dev] $ cat /usr/local/etc/odbcinst.ini
[FreeTDS]
Description = v0.64 with protocol v8.0
Driver = /usr/local/lib/libtdsodbc.so

// Add dsn entry that obdc can use, point to FreeTDS name...
// http://www.freetds.org/userguide/odbcconnattr.htm
[root@dev] $ cat /usr/local/etc/odbc.ini
[TestServer]
Driver = FreeTDS
Description = Azure test with FreeTDS
ServerName = AZURE
Database = gol

Once your unix server is properly configured, you can go ahead and test php connections.


// SQL Azure supports tabular data stream (TDS) protocol client version 7.3 or later
// @see http://msdn.microsoft.com/en-us/library/ee336245.aspx

// On unix, we can connect using open-source FreeTDS (http://www.freetds.org/).
// Replace XXX with your database information.
$serverName = 'XXXXXXXXXX';
$user = 'XXX@' . $serverName;
$pass = 'XXXXXX';

if(function_exists('mssql_connect')) {
// Using DSN name 'AZURE' (specified in /usr/local/etc/freetds.conf)
$c = mssql_connect('AZURE', $user, $pass);
echo "mssql_connect('AZURE', $user, $pass): ". ($c ? 'Success' : 'Error') . "
";
mssql_close($c);
sleep(1);
}

if(function_exists('odbc_connect')) {

// All examples require entry in /usr/local/etc/odbcinst.ini:
// [FreeTDS]
// Driver = /usr/local/lib/libtdsodbc.so

// Using DSN name 'TestServer' (specified in /usr/local/etc/odbc.ini)
$c = odbc_connect('TestServer', $user, $pass);
echo "odbc_connect('TestServer', $user, $pass): ". ($c ? 'Success' : 'Error') . "
";
odbc_close($c);
sleep(1);

// Using DSN name '$serverName' (specified in /usr/local/etc/freetds.conf)
$dsn = "DRIVER={FreeTDS};ServerName=$serverName;UID=$user;PWD=$pass;Database=test;";
$c = odbc_connect($dsn, $user, $pass);
echo "odbc_connect($dsn, $user, $pass): ". ($c ? 'Success' : 'Error' ) . "
";
odbc_close($c);
sleep(1);

// DSN-less connection (more portable / recommended)
$dsn = "DRIVER={FreeTDS};Server=$serverName.database.windows.net;Port=1433;Database=test;UID=$user;PWD=$pass;TDS_Version=8.0";
$c = odbc_connect($dsn, $user, $pass);
echo "odbc_connect($dsn, $user, $pass): ". ($c ? 'Success' : 'Error') . "
";
odbc_close($c);
sleep(1);

// **Note: Driver= must be uppercase!? Find out where the bug comes from!
// $dsn="Driver={FreeTDS};ServerName=AZURE;Database=gol;UID=$user;PWD=$pass;";
}

If all went well, you should see 'Success' everywhere.

Be sure to check the version of FreeTDS and unixODBC you are using! If you run into bugs with these tools, well report them or write a patch!

My conclusion, although FreeTDS works, it is not enterprise ready and needs some corporate backing. Naturally, this brings the question, what Microsoft is up to?