I was trundling through my SQL Azure database today, looking for interesting behaviors. Found one. A "select * from sys.databases"  reveals that both "snapshot_isolation_state" and "is_read_committed_snapshot_on" return 1 (on) for all databases. Because ALTER DATABASE isn't supported, these cannot be changed.

So READ COMMITTED SNAPSHOT is the default transaction isolation level, which may come as a surprise to those applications that depend on the read committed locking behavior of SQL Server. Although so far there's been no big hue and cry. The readcommittedlock query hint works as expected, but if you're expecting read locks, you won't get them by default. And the other three locking-based isolation levels are available, SQL Azure is SQL Server after all, just with the two snapshot isolation switches turned on.

Remember also that the SQL Azure session timeout will rollback uncommitted transactions in progress (as it should). I was reminded of that while testing isolation levels and forgetting to commit a transaction.

There isn't much reference to this in the SQL Azure Books Online, and although I did find a reference to this in the SQL Azure FAQ, the FAQ says "snapshot isolation" is the default. Technically it's "read committed snapshot" (known also as "statement-level snapshot") that's the default, although the SQL Server snapshot isolation level (known as "transaction-level snapshot") is available and works as advertised.

This may be for the best, because you can't use either the dynamic management views or sp_lock to observe the locks in your instance/database in any case. A final point of interest is that application locks are supported, but lack of visibility would mean it may be difficult to troubleshoot these.

SQL Server Management Studio in 2008 R2 (and there's a standalone upgrade for SSMS) supports connecting to SQL Azure. And there were some tools on CodePlex that did too. And that's what I'd always used. But reading along in the latest docs I came across the following statement "Connecting to SQL Azure by using OLE DB is not supported". Later on, the docs talked about using SQLCMD (which is a SQL Server utility that uses OLE DB) and SSIS (which can use ADO.NET/ODBC, but uses OLE DB as API of choice) being supported.

So I decided to try an experiment using SQLCMD (which should be supported), ODBC Data Sources (which should work), and a UDL file (OLE DB, should not be supported). And leave the SSIS question for another day. This turned out to be harder than it sounded. The books online mentioned using (with SQLCMD):

SQLCMD -U {Login@ServerName} -P {password} -S {ServerName} -d master

But the ServerName in question is either a localname (e.g. foo) or DNS name (e.g. tcp:foo.database.windows.net). Turns out that the DNS name must be used for the -S operand (unless you put together a host table) and Login@ServerName MUST specify the localname (no database.windows.net suffix). So this would be:

SQLCMD -U Login@foo -P {password} -S tcp:foo.database.windows.net -d master

And the "tcp:" prefix on server name turned out to be optional if the have your client stack set up for TCP (or TCP & SharedMemory) only.

Got it. This turned out to be key in getting the connection to work in all three cases. But why is OLE DB not supported? It appears to work, the UDL file worked as well as ODBC Driver Manager. Or did it? Turned out that when I tried the dropdown list that enumerates databases in the UDL editor, I received "Connection success, but could not list databases". Trying the database name in worked fine. Hmmm...so here's my wild guess. The SQL Server OLE DB provider will on occasion use its own stored procedures to obtain metadata (like sp_columns_rowset) which retrieve the exact metadata that the OLE DB provider expects to see. Perhaps they didn't bring those along to SQL Azure. Or maybe its just the extra test cycles for an additional API.

But it would be nice to know why OLE DB appears to work but is not supported (except in some utilities). And whether using the OLE DB source and destination in SSIS is recommended.

Yesterday I signed up for my account to the official, live, RTM cloud. Of course it was all for the SQL Server, that is, SQL Server Azure. MSDN premium subscribers get a free 8-month trial, so I signed up for that and they transferred by CTP databases over (actually, my "server name" didn't change at all). See Roger Jennings' step-by-step walkthrough to make sure you get the MSDN plan if you're a subscriber.

I'd be playing with Windows Azure and SQL Azure since CTP1. But its all real and serious this time. I had to enter my credit card number. And this morning I got email that my bill (albeit for $0.00) was ready. Although the MSDN service is free, I did agree to pay if I used more than the allotted amount of resources. So now its time to look at where the limits come into play in earnest.

The thing that stuck out about the SQL Azure docs were the number of "thou shalt not" and "partially supported" things. I'll admit that I haven't worked on SQL Server Express (that supports a subset of features) or SQL CE (that supports a subset of the T-SQL dialect) that often, but even then you could poke at things enough to figure out how it works and why. With SQL Azure, you just can't do that. So you might hear from me about things that sound strange (from the docs) in the next few days. Now that I don't have to ask "will this limit also be in RTM?".

The first thing that was surprising (because I swear it didn't work in early CTPs, maybe I was wrong) is that the legacy TEXT, NTEXT, and IMAGE data types are supported. They've been on the deprecation list since SQL Server 2005. Of course, the neat ways you could manipulate them (like TEXTPTR) are not. But why TEXT, at this late date?

More to come. Cheers.