"D" is for SQL Server Denali and Developers

Microsoft is hard at work building the next generation of SQL Server (code-named "Denali"). Long gone are the days when Microsoft had to prove that their database was enterprise grade. Of course, each successive version of SQL Server performs better than the last, but now Microsoft has even more time to build out additional niceties and ancillary technologies that make SQL Server really stand out as a database platform. I try not to write too much about databases since there are specific magazines dedicated to the topic (for example,DevProConnections' sister publication, SQL Server Magazine). However, I do like to touch upon database features that have an impact on software developers, and there are some neat ones that Denali has in store that I think are worth mentioning here.

Active Secondary: A New HA Feature
The first feature that I am excited about is part of the High Availability and Disaster Recovery (HADR) technology built into SQL Server Denali. At first, it sounds a lot like "blah, blah, boring IT administration stuff", but hidden in there is a nugget called Active Secondary. In a nutshell, Denali now allows you to have multiple secondary databases in a cluster. Not only that, but you can also perform read-only queries against those secondary databases. What this means is that your backup database server(s) can now help offload some of the processing load of data-retrieval queries (which in most systems is the lion's share of database activity), instead of just being a "money suck" for a disaster eventuality that may never occur. Since Active Secondary servers are in the cluster, the data that they contain is near real time, as opposed to replication or log shipping, which have a higher latency.

The only downside that I see is that Active Secondary servers must have an identical indexing scheme as the primary server; which makes perfect sense because the secondary would become the primary if that unforeseen disaster should occur. This means that while the Active Secondary can help defray load in a linear fashion, it's not as if you can have it index-optimized for read-only operations so that it would help out in an exponential manner. There is another (albeit smaller) limitation in that lock hints are ignored when executed against Active Secondary servers. This may or may not affect your particular situation.

Service Broker: Support for Messaging and Queuing
A second new Denali feature that I think will be hugely impactful is the introduction of the Service Broker. This introduces native support for messaging and queuing applications. It is akin to having the benefits of Microsoft Message Queue (MSMQ) baked right into SQL Server. It will now be almost effortless to offload long-running operations to alternate database servers for processing and have a queue notify your code when a response is ready (through a feature called Automatic Activation). The Service Broker can also be used to guarantee the receipt of messages (and in the right order). Everything is also transactional, and messages can be grouped together into conversations and conversation groups to prevent multiple worker processes from de-queueing messages that need to be processed as a unit (known as Related Message Locking).

Microsoft has created a set of T-SQL commands to support the new Service Broker features. Stored procedures can be written using these or by using one of the CLR languages. These stored procedures can be accessed via traditional means, such as ADO.NET. Since the Service Broker is neatly packed into the SQL Server installation, it is very easy to set up a developer environment that mirrors how production will behave. In contrast, setting up MSMQ in a developer environment can be a nightmare.

Obviously, I've merely cherry-picked some developer-oriented features from the feature-packed upcoming release of SQL Server Denali. Databases may not be your thing, but this next version is going to provide solutions to many of the classic programming problems that we still face today, such as how to handle the various complexities of building loosely coupled applications. I think that we can all appreciate that.


Jonathan Goodyear ( [email protected]) is president of ASPSOFT, an Internet consulting firm in Orlando. He is Microsoft regional director for Florida, an ASP.NET MVP, a Microsoft Certified Solution Developer, and a contributing editor for DevProConnections.