November 10, 2009

DataObjects.Net and SQL Azure

Right now we're testing DO4 with SQL Azure, and it already works. We've made few changes related to compatibility with Azure SQL:
  • Azure SQL does not support MARS, but DO4 utilizes this feature to implement on-demand materialization of query results. Fortunately, we already implemented adapters for non-MARS DbDataReaders while working on future queries. There is a very similar problem: if you switch the reader to the next result, previous one becomes inaccessible; but since you must provide access to all of them, the only option you have is to cache their content. So we used the same solution here, and now DO4 does not require MARS for SQL Server, but utilize it when it is available.
  • Azure SQL does not support tables without clustered primary keys. Likely, you know that DO creates all the tables with clustered primary key, if this option is supported by the database. But, as we discovered, there was one exception: key generator tables. Obviously, it's not important if keys there are clustered or not - these tables are always empty. But I'd prefer them to be clustered as well - just to not break the genericity; moreover, in case with Azure this is really important. So we fixed this when this was discovered.
  • Azure SQL does not support tables without primary keys. Or, more precisely, it allows to create such tables, but does not allow them to contain the data. But our schema upgrade layer produces upgrade sequences that may e.g. drop the primary key from some table (containing the data) and create it again, if its structure was changed. So in general, it is incompatible with this requirement. That's what we're fixing right now. But if you use Recreate or Validate schema upgrade mode, this isn't important.
That's all. After getting first and second issue fixed, all our tests (except tests for schema upgrade - they wait for the third issue to be fixed) have passed on SQL Azure.

So you'll be able to connect to SQL Azure using today's nightly build (or the latest repository snapshot). Connection URL must be the same as for SQL Server:
  • "sqlserver://"
Btw, we added a separate "Azure" version of SQL Server provider for SQL DOM. So in general, we were ready to handle complex differences in SQL we produce for it. Fortunately, this wasn't necessary ;)