Late tonight I tried something that I didn't think would work because @@version on SQL Azure didn't report anything different from last week. (Microsoft SQL Azure (RTM) - 11.0.1814.30 Nov 21 2011 16:46:09 Copyright (c) Microsoft Corporation). It didn't work last week. But it does work now. That's the last time I listen to @@version... ;-) This thing that works is...

CREATE FEDERATION foo (id bigint range);

That's right...SQL Azure federations are up and appear to be working. At least on my server. They do a rolling upgrade in SQL Azure, so they be not yet be in all servers. If you've missed reading about these in the recent past, Cihan Biyikoglu is one of the "fathers" of the SQL Azure Federation implementation, and he's written all about it, for months, in his blog. He was a special guest at SQLskills' week 4 immersion event that I gave in August and gave a talk there, mostly on federations. The CREATE FEDERATION DDL statement isn't available on the web yet, so that's where to get it to try it out.

Note: You can also do this on the new SQL Azure Management portion of the Windows Azure portal. Select a database on your SQL Azure server, choose "Manage" off the ribbon at the top. You'll need to sign on again. Choose the "Administration" entry at the bottom right, then on administration page, it's the "New" button on the left of the top riboon (whew, that was harder to describe than it was to do).

BIG NOTE: Since I get the impressions that federations will likely be cost items, THIS WILL LIKELY COST YOU MONEY. Don't say I didn't warn you, if it does. I don't know how much. The corollary is DROP FEDERATION foo;

Folks that are familiar with patitioning can think of federations as similar to partitioning, but with federations in SQL Azure you can't do SQL statements that cross federation members yet (i.e. no built-in fan-out queries or joins between federated tables and tables that live outside the federation member). Good thing we all know what map-reduce means from the Dr David Dewitt talk at SQLPASS this year. Folks that are more familiar with data-dependent routing (a.k.a. data-directed routing) like to draw parallels to this technology and its use in scale-out. There's lots of information on data-directed routing, I even wrote about it in a whitepaper called "Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005", once upon a time. And some folks like to liken federations to NoSQL variant that uses the, ah, SQL language.

Whatever you'd like to compare it to, if you want federations to work with your application, you'll need to design for it. You can start by thinking in terms of three different types of tables: federated tables, reference tables, and global tables. Federated tables must have the federation key as part of the table's key in the SQL Azure implementation. So if the federation key is a customer id, orders can be a federated table if you make customerid part of its key. You can even stretch it to order_details containing the composite orders key that contains customer id. I have a harder time thinking of the products table working that way.

If you want to join order_details and products, you can make products a reference table. A reference table lives in every federation member. When you split a federation into two members, both members contain a full copy of the products table. BTW, these copies are not automatically kept in sync, so you'd have to update all of them yourself. In case you're thinking as I was, we'll need to find out if Sync services for Azure will support reference tables in federation members right away.

Finally, you can have global tables, that don't know anything about the federation. These could be in the federation root (which keeps track of the federation member metadata but doesn't know about individual federated tables), or in a different SQL Azure database. Joins between federated tables on global tables, however, happen on the client/middle-tier.

So, if you're interested in using SQL Azure Federations, the first thing to do is to think about what would make a good federation key. And what type of table each of your tables would be; federated, reference, or global. Cihan probably was a blog entry about this somewhere.

Cheers. And a big congrats to Cihan, Michael, and the rest of the team.

@bobbeauch

A short while ago, I wrote a blog entry about SQL Server 2012 features that are currently available in SQL Azure. In that post, I mentioned that Ed Katibah would publish an article detailing the current state of the SQL Server 2012 spatial features in SQL Azure, in detail.

That article is now available at the Technet Wiki. Note that not all features are available currently, and Ed has a nice detailed chart that covers this feature-by-feature. Thanks, Ed! And, as with the SQL features in SQL Azure, the available spatial features will increase in future as parity with SQL Server 2012 RTM is acheived. Watch this space.

@bobbeauch

Earlier this month I noticed that SQL Azure, with the latest upgrade has a version of 11...something (actually 11.0.1467.26, to be exact). And I wondered, aside from the long-touted new spatial library, if there were any Denali T-SQL enhancements that were now available. That is, what version of "version 11" is this? I did do some cursory tests of spatial functionality, but Ed Katibah (aka SpatialEd) will be posting the long list of spatial feature tests directly, I don't want to steal his thunder on those. But here's the tests for Denali T-SQL features, tested against SQL Azure, today.

Working in SQL Azure, now:
1. ORDER BY with OFFSET and FETCH
2. THROW (adjunct to TRY-CATCH)
3. Metadata discovery system procedures (sp_describe_first_result_set and friends)
4. EXECUTE ... WITH RESULT SETS
5. FORCESCAN and FORCESEEK hints with extensions

Not working in SQL Azure yet:
1. Sequences
2. UTF-16 collations with supplimentary characters
3. New functions (CONCAT, IIF, EOMONTH, and friends)
4. Windowing (OVER clause enhancements, LAG/LEAD and others, new analytical functions)

So it appears as if T-SQL and Spatial in SQL Azure is now approximately SQL Denali CTP1-- (that's one-minus-minus). Interesting. Wonder when the rest of the functionality will appear. And will it beat the SQL Denali release to RTM?

Bear in mind, of course, that most of the SQL Denali funtionality doesn't apply to SQL Azure (e.g. Always On, security enhancements) because SQL Azure doesn't support this level of control in the first place. But you could almost (if not exactly) state that SQL Azure databases were "contained" databases all along.

Followup from a friend on the SQL Azure team: "The features from Denali that are enabled in SQL Azure are fully supported and can be safely used. There aren't any changes to terms of use of the SLAs we provide."

@bobbeauch

I've been trying this out every day or so since I'd heard about the update (originally named the July 2011 Service Release), but I'd forgotten about it for about a week. So I don't exactly know when this happened on my SQL Azure server. But tonight, around midnight, SELECT @@version returns:

Microsoft SQL Azure (RTM) - 11.0.1465.26 Aug 10 2011 22:54:49 Copyright (c) Microsoft Corporation

So cool, we're at Denali on SQL Azure. Or are we just on Denali spatial (which was the new feature that was mentioned in the announcement). Or, where are we?

First to try some T-SQL Denali features.
  The new THROW statement works, as does OFFSET and FETCH, but...
  Creating a sequence fails, as does LAG/LEAD and EXECUTE WITH RESULT SETS
 
How about spatial? Not trying to be tremendously rigorous for now, I tested some obvious things.
  The new HasM and HasZ properties work, as does the new ShortestLineTo method. Probably some other methods (like the new ST-methods on the geography type) do too.
  The items that were caught by sys.dm_db_objects_impacted_on_version_change WERE affected by the change.
  A test of the new spatial precision indicates that the Denali improved precision is being used but...
  Cannot create a FULLGLOBE type or a CIRCULARSTRING type
  The new AUTO_GRID indexes are not supported
  Attempting to use geometry::UnionAggregate produces "A severe error occurred on the current command. The results, if any, should be discarded."

And BTW, both SSMS from SQL Server Denali and SQL Server 2008 R2 SP1 connect to the updated SQL Azure version just fine. I don't have any earlier SSMS versions to try right now.

Hmmm...well that's kind of a mixed bag now, isn't it? I know we're not in Kansas anymore, but we're not all in Denali either. I'm sure someone will come up with a feature matrix for this SQL Azure version (or I will), but there's enough of a subset of new features to make it interesting. More info as it arrives.

@bobbeauch

The next version of Data-Tier Applications is version 2.0. You can get a CTP of it today; where the CTP is located really gives away the game. It's located at SQL Azure Labs, listed under SQL Azure "Import/Export". The writeup on this page identifies a few interesting things:

1. It's a preview of the Denali Data-Tier Application Framework (although its not the version in Denali CTP1).
2. It's meant to be "a solution for archival and migration scenarios".
3. (Easy implication on my part) - they're concentrating on SQL Azure "database movement" for now. Although you can use it with SQL Server, coverage includes exactly what's in SQL Azure.

With respect to the second point listed, DAC 2.0 supports two new operations: import and export. This import and export is on an entire database at a time, as opposed to say, BCP, which is table-by-table. It also imports/exports data and schema at once, unlike BCP which needs a schema to be defined ahead of time. This import/export is NOT backup/restore. There's no point-in-time recovery, and, if the export is executed on an "active" database, there's no guarentee that its transactionally consistent. It's just of copy of all of the schema and data into one container called (are you ready for this?) a .bacpac. The data is actually stored row-at-a-time, in JSON (Javascript Object Notation) format.

Better than that (depending on your point of view), DAC 2.0 adds support for additional database objects and types, including ALL of the database objects supported by SQL Azure Database (but not objects not supported by SQL Azure, like Service Broker objects). Objects added in the CTP include:

Geometry, Geography, and HierarchyId data types
Spatial Indexes
Statistics
Synonyms
CursorParams
Security-Related Objects
  Permissions
  Role Memberships
  Logins and Users (I don't see Denali's Contained Database logins, but I bet these will be added eventually)

The CTP offering consists of five components: new versions of the four components in earlier DAC versions (DAC framework, SMO, SQLClrTypes, T-SQL LanguageService) and one new component: the T-SQL ScriptDom. The T-SQL ScriptDom is a component that generates SQL Server version-specific scripts for create and alter/update.

When you install DAC 2.0 CTP (the components actually show up in Add/Remove Programs as e.g. "Microsoft SQL Server "Denali" Data-Tier App Framework (CTP 2.1)", there are four such components), it does NOT replace existing DAC components. It installs side-by-side in the GAC and in the "110" subdirectory SQL Server in program files. I didn't install it on Denali CTP1 system, but installed it on an OS with SQL Server 2008 R2. It doesn't affect any GUI components, and existing VS 2010 (RTM or SP1) and SQL Server 2008 R2 (RTM or SP1) DAC 1.0 or 1.1 functionality continues to work as it used to.

Because there's no GUI components or new VS Projects for now, it comes with a command-line utility. Unless you program it using the object model, you interact using the utility for now (although the code for the command-line utility is available on CodePlex). The utility, DACImportExportCli.exe (sounds like they'll implement a Windows Azure service over it, no?), contains at least one exciting feature: BACPACs can be imported/exported from/to Windows Azure Blob storage. You just name your Windows Azure storage server and cert as command-line parameters and "it all happens in the cloud" (and no, I'm not contracting to write advertising slogans, thanks).

So, to wrap this up, both SQL Azure Database and SQL Server Denali work with the concept of the portable, movable, database. It's meant to be unencombered by server-level (or MSDB-level) objects, and supports a subset of database objects that increases with each new SQL Azure Database Service Release. And the vehicle for wrapping these databases up and moving them around is the DAC. In addition to working with multiple SQL Server versions, it also encapsulates the differences between applying database schema changes to disperate versions. As an example, at TechEd 2011, Adrian Bethune illustrated how to change the data type of a clustering key on SQL Server Denali and SQL Azure Database. It's different on Azure because you can't simply drop and re-create the clusting key, so DAC accomplishes it differently on each instance. Very interesting...

@bobbeauch

In looking at the what's new for SQL Azure (May 2011) page, I came across the following: "Upcoming Increased Precision of Spatial Types: For the next major service release, some intrinsic functions will change and SQL Azure will support increased precision of Spatial Types."

There's a few interesting things about this announcement.

Firstly, the increased precision for spatial types is not a SQL Server 2008 R2 feature. It's a Denali CTP1 feature. Although the article doesn't indicate whether they've made up a special "pre-Denali" version of this feature, or when exactly "the next major service release" will be (and when SQL Server Denali will be released is unknown), it would be interesting if updated SQL Server spatial functionality made its appearance in SQL Azure *before* making its appearence in an on-premise release of SQL Server. As far as I know, this will be the first time a new, non-deprecation feature is deployed in the cloud before on-premise (non-deprecation because, for example, the COMPUTE BY clause fails in SQL Azure but not in any on-premise RTM release of SQL Server). Note that usage of SQL Server "opaque" features (for example, are instances managed internally be a variant of the Utility Control Point concept?) cannot be determined.

In addition, this may be the first "impactful change" (BOL doesn't say breaking change, but change with a possible impact, but one never knows what the impact would be in other folks' applications) in SQL Azure Database. The BOL entry continues "This will have an impact on persisted computed columns as well as any index or constraint defined in terms of the persisted computed column. With this service release SQL Azure provides a view to help determine objects that will be impacted by the change. Query sys.dm_db_objects_impacted_on_version_change (SQL Azure Database) in each database to determine impacted objects for that database."

Here's a couple of object definitions that will populate this DMV:

create table spatial_test (
 id int identity primary key,
 geog geography,
 area as geog.STArea() PERSISTED,
);

-- one row, class_desc INDEX, for the clusted index
select * from sys.dm_db_objects_impacted_on_version_change

ALTER TABLE spatial_test
ADD CONSTRAINT check_area CHECK (area > 50);

-- two more rows, class_desc OBJECT_OR_COLUMN, for the constraint object
select * from sys.dm_db_objects_impacted_on_version_change

Before this, the SQL Azure Database koan was "Changes are always backward-compatible". There is now the sys.dm_db_objects_impacted_on_version_change DMV and the BOL page for it even provides sample DDL to handle the impacted objects. But this begs the question: I can run the DMV to determine objects that would be impacted and fix them when the change occurs, but if I don't know when the SU will be released, how can I plan/stage my app change to corespond? Interesting times ahead...

@bobbeauch

Last week I did a talk at SQLConnections on SQL Azure Database and Data-Tier Applications (DAC). At the time (it was the day of Visual Studio 2010 launch), I explained that conference abstracts had to be submitted 6 months ago. At the time, because of some coincidental feature correspondence (e.g. the DAC whitepaper suggests only using DAC deployment on databases of 10gb or less; 10gb is the current maximum size of a SQL Azure database) I'd actually thought that DAC and Azure were "joined at the hip" and that DAC might already be used in the cloud (internally) for SQL Azure deployment.

It isn't. In fact, neither DAC nor SQL Azure Database supported each other. *Until last week*. At the VS2010 launch, the other DAC talk (by the team) said Azure would be supported as a development/deployment environment. But, except for "import from existing database", even the RTM VS2010 didn't work with SQL Azure.

Imagine my surprise on returning home to see this blog posting by the SQL Azure team. As of last Friday, SQL Azure enhancements "enables deployments of database applications directly from SQL Server 2008 R2 and Visual Studio 2010 to SQL Azure for database deployment flexibility". So, it does hook up, after all.

DAC is a pretty controversial feature because in V1, it only supports a subset of database objects and deployment via a
"new database-copy data-rename databases" funtionality. So, its not for everyone. But, at both talks, attendees seemed to understand the target audience, the "departmental database application", the "600th database application" in a large company, the ones that usually have no DBA support because DBAs are busy with 24x7 line-of-business OLTP apps. If you've ever worked in a big company where database and software development is not the main business of the company (ie, the main business is manufacturing cars, or banking, not developing software), you can grok what exactly what a "departmental application" is.

The attendees got it. When I asked at the end of my talk if, because there are customers for it in the present, the DAC ought to be postponed until it would work with all DBMS apps, I only got 1 taker (for postponed) out of 100. Not so controversial after all.

Now, to see exactly how DAC and Azure work together. As of Azure on Friday, Apr 16.

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.