Migrating data from SQL into Windows Azure Table Storage
Monday, September 16. 2013
The error messages when Azure Table Storage data insert fails are far from being descriptive.
This is the complete list of supported datatypes (or Property Types as they call them):
- Binary: An array of bytes up to 64 KB in size.
- Bool: A Boolean value.
- DateTime: A 64-bit value expressed as UTC time. The supported range of values is 1/1/1601 to 12/31/9999.
- Double: A 64-bit floating point value.
- GUID: A 128-bit globally unique identifier.
- Int: A 32-bit integer.
- Int64: A 64-bit integer.
- String: A UTF-16-encoded value. String values can be up to 64 KB in size.
Really. Nothing more. You just have to get along with that one!
The list is taken from Windows Azure Table Storage and Windows Azure SQL Database - Compared and Contrasted.
Things you fail to notice:
- .Net DateTime Structure as range of 00:00:00 (midnight), January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) in the Gregorian calendar. Not from January 1, 1601 AD.
- That shouldn't be an issue. My app had problems and it had recorded dates into year 201. This was a really nice way of finding that out.
- In intergers, there are no unsigned versions.
- In decimal numbers, there is no decimal, a 128-bit floating point number. You have to settle with Double, a IEC 60559:1989 (IEEE 754) compliant version.
- There is no reasonable way of storing money-type data which needs an exact number, no floating point conversions.
- The string really is UTF-16, a two byte -version. It stores up to 32768 characters.
- Which is Not much when compared to TEXT or varchar(max) which range from 2 GiB to anything you have
Hopefully this list helps somebody. I spent a nice while finding all these out.
Using PHP, Zend Framework, PDO and FreeTDS in Windows Azure
Wednesday, September 4. 2013
Earlier I wrote about IPv6-connectivity with MS SQL server into Linux / PHP with FreeTDS.
This time my quest with FreeTDS continued, I put together the minimal possible CentOS 6.4 Linux with enough parts to produce a Nginx / PHP-FPM / Windows Azure SQL Database -based web application. The acronym could be not LAMP, but NPFWASD. No idea how to pronounce "npf-wasd", though.
I packaged a Hyper-V -based Linux .vhd into Azure virtual machine IaaS-image and created couple of load-balanced HTTP-ports into it. The problem was to lure PHP's PDO to connect into Azure SQL via FreeTDS dblib. I spent a good while banging my head and kicking it, before it stopped resisting and started to obey my commands.
Everything would have gone much better, if only I had the proper version of FreeTDS installed into the Linux. When I realized that the TDS-protocol version is hyper-important in Azure SQL, I realised that my FreeTDS-version was not the one it was supposed to be. My own-package would have been the correct one (see the earlier post). My tsql -C says:
Compile-time settings (established with the "configure" script)
Version: freetds v0.92.dev.20130721
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 7.1
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
The default TDS version of 7.1 is really, really important there. With that I can do:
tsql -H -my-designated-instance-in-Azure-.database.windows.net \
-p 1433 \
-U -the-application-SQL-user-without-admin-rights- \
-D -my-own-database-in-the-SQL-box-
It simply works, displays the prompt and everything works as it should be. In my Zend Framework application configuration I say:
resources.db.adapter = "Pdo_Mssql"
resources.db.params.host = "-my-designated-instance-in-Azure-.database.windows.net"
resources.db.params.dbname = "-my-own-database-in-the-SQL-box-"
resources.db.params.username = "-the-application-SQL-user-without-admin-rights-"
resources.db.params.password = "-oh-the-top-secret-passwrod-"
resources.db.params.version = "7.1"
resources.db.params.charset = "utf8"
resources.db.params.pdoType = "dblib"
No issues there. Everything works.
I received couple of comments from other people when I announced that I would try such a feat. It appeared that most people are running their own SQL-instances of various kinds because of performance reasons. The Azure SQL -service is definitely not the fastest there is. But what if you're not in a hurry. The service is there, easily available, cheap and functional, even from Linux/PHP.