Michael J Swart posted a SQL query on his blog that produced a rendition of Botticelli’s Birth of Venus, in the spatial results tab of SQL Server Management Studio when executed. But, when everyone rushed over in anxious anticipation, some folks could execute the query but couldn't see the picture on their 64-bit editions. Here's why...

It's an obscure problem with rendering spatially invalid results that surfaced around SP1 timeframe. The map control that produces the tab cannot execute the MakeValid() method. It's looking for the 32-bit version of SqlServerSpatial.dll that should have been installed in C:\Windows\SysWOW64, as it needs to be on a 64 bit machine. The exception happens inside the Microsoft.SqlServer.Spatial assembly, which cannot locate SqlServerSpatial.dll.

Here's a simple repro:
DECLARE @g GEOMETRY
SELECT @g = GEOMETRY::STGeomFromText('POLYGON((1 1, 3 3, 3 1, 1 3, 1 1))',0)
SELECT @g -- Spatial results tab doesn't display in SSMS x64
SELECT @g.MakeValid() -- This works
GO

So if this doesn't work for you, you'll need to either,
    A. Locate and install 32-bit SqlServerSpatial.dll in C:\Windows\SysWOW64
or B. Change the script to output the result to a table variable, where you can call MakeValid() on the spatial column.

Reinstalling the spatial library from the SQL Server 2008 Feature Pack may work as well, but I've not tried this so I can't promise.

Then you'll "get the picture".

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.

There's another change to the SQL Server database engine in SQL Server 2008 R2. This concerns poison-message handling behavior in Service Broker applications.

Service broker's messaging is always transactional. A RECEIVE SQL statement is transactional and can be combined with other database operations as part of a transaction. For example, you can code a RECEIVE for a Service Broker queue combined with an INSERT of a database row, based on the information in the message. If the transaction fails (say the row's primary key already exists), the message that was RECEIVEd will be put back on the queue. But then, the next RECEIVE could read the same message again; if the message is never able to be processed correctly, and the RECEIVE-INSERT-ERROR-ROLLBACK statement can loop on this particular message. This is known as a poison message.

It is suggested that you write your own poison message handling strategy, a few strategies are listed here. If you do not write your own strategy, Service Broker's default strategy (which happens after five consecutive transaction rollbacks) is to disable the queue. You can receive a QUEUE_DISABLED event if you write a service broker service that handles the BROKER_QUEUE_DISABLED event notification.

Some folks have complained that this is a fairly drastic step (and limits the poison message-handling strategies you can write), but poison message loops do have the capability to waste SQL Server resources. So...

In SQL Server 2008 R2, both the CREATE QUEUE and ALTER QUEUE DDL statements allow the (new) specification POISON_MESSAGE_HANDLING(STATUS = OFF/ON). According to the description "This allows for a custom poison message handing system to be defined by the application." This does not relieve you from writing your own strategy, it just means that you can turn off disabling the queue for a 5-rollback poison message.

As long as I'm blogging about filestreams...

Folks always want to know if the filestream information can be stored on a remote share. They look kind of disappointed when they hear that the filestream filegroup must exist on a local (to the SQL Server instance) drive. However... (isn't there always a however?)

For SQL Server 2008, the SQL Server team released a companion feature to filestream called remote blob storage (RBS). RBS consists of a set a stored procedures and an SDK that allows you to store your blob on a remote blob store. The SDK works differently than the filestream storage feature. And, to use the feature, you need a RBS blob store provider. Looking around, EMC² has an RBS provider for the Centera product line. And there's a sample provider and code to use it up on CodePlex.

The SQL Server team released the first version of RBS as part of the SQL Server 2008 Feature Pack.

They'd always said they'd be bridging the gap between filestream storage and RBS in future. We'll...the future is soon (or now, depending on how much you like CTPs). The November CTP of the SQL Server 2008 R2 Feature Pack comes with an RBS provider for Filestream. You still have to use the RBS APIs, but you can have your (filestream) cake and eat it too. Remoting. There's little info right now, but there are a number of postings on the RBS team's blog.  Including a comparison of filestream storage and RBS. In addition, RBS & filestream appears to hook into SharePoint 2010, but I'll leave the description of that to the SharePoint 2010 documentation (which has just been updated today).

And, BTW, if you were going to ask "Are features that appear in the SQL Server Feature Packs supported?", the answer is yes. I asked.

I was under the impression that the database engine/programming model changes in SQL Server 2008 R2 were minimal and could be counted on one hand. Today I was running an old demo that I had, having to do with filestreams on the R2 Nov CTP. Usually I run this one line-by-line, but I was in a hurry and ran the whole thing. Funny...I didn't remember this few errors caused by limitations when using filestream. Hmmm...

In SQL Server 2008 R2, filestream storage now support snapshots transaction isolation levels. Both flavors, read-committed snapshot and snapshot. This should expand the possibilities for using this feature because, in SQL Server 2008 (non-R2), you can't even enable either of these isolation levels at the database level if you have a filestream filegroup/column. Now, you can not only enable the levels, but the filestream goes exhibits the transactional semantics.

This is actually doc'd in the SQL Server 2008 R2 BOL, right here.  BTW, although the chart in the BOL lists the streaming access doesn't support ReadUncommitted, RepeatableRead, or Serializable iso levels, in my experiments, you don't use an error using these iso levels with streaming. The stream just doesn't exhibit the expected transactional semantics.

So you can count 'em on one hand, 'eh? Between this enhancement and the perf improvements blogged about recently on the SQL Server Storage Engine blog, maybe I'll need another hand.