I want to wrap up the year with a quick look at some more of the new and enhanced features in SQL Server 2005. But first, let me quickly tell you about an update. Last week, Microsoft released an update to the SQL Server 2005 documentation. If you've starting working with the database, you'll want to grab this update at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx.

Everyone knows that SQL Server 2005 includes the Common Language Runtime (CLR) and therefore all the new features must be based on .NET, right? Well actually, no. The fact is that T-SQL remains the language of choice for SQL Server. In the past, there were capabilities available in traditional programming languages that couldn't be reproduced using T-SQL. One of those capabilities was recursion. However, that has changed. T-SQL now offers recursion. For information about T-SQL recursion, check out this page about common table expressions: http://msdn2.microsoft.com/en-us/library/ms175972.aspx. For a look at many of the new T-SQL features, I recommend going to http://msdn2.microsoft.com/en-us/library/ms189465.aspx. For a somewhat outdated tutorial on some of these features, download the paper at http://download.microsoft.com/download/c/a/b/cab2cb65-7cf0-4863-a11d-9790486309b9/sqlhol115%20-%20tsql%20enhancements.doc.

T-SQL hasn't just been enhanced within the context of database queries. It also has been extended to work with many other SQL Server components. For example, another new feature in SQL Server 2005 is the Service Broker. The shortest conceptual description I can provide for the Service Broker is that it is Microsoft Message Queue Services (MSMQ) inside the database. The Service Broker provides an asynchronous programming model for database transactions managed from within the database. There are two modes for the Service Broker: internal and external. The internal mode uses T-SQL to provide access to SQL Server 2005 resources. To access the internal mode, SQL Server 2005 has several new T-SQL commands in the Data Manipulation Language (DML) extensions. The external mode provides access to resources outside your SQL Server machine and carries associated security risks. When you configure the Service Broker to access resources outside of your database, you run the risk that one of these resources could crash your server or communicate key data elements to an unwanted listener. To mitigate these risks, SQL Server 2005 supports both transport-level security (e.g., HTTP Secure--HTTPS) for messages and dialog/conversation security related to who the recipient is (e.g., client certificates). For more information about the Service Broker, check out the Microsoft Developer Network (MSDN) Webcast at http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032284275 or a written summary at http://msdn2.microsoft.com/en-us/library/ms166104.aspx. For implementation tips, I recommend the pages linked from this Web site: http://msdn2.microsoft.com/en-us/library/ms171589(en-US,SQL.90).aspx. For a security overview, first read the Web page at http://msdn2.microsoft.com/en-us/library/ms166079.aspx, then click the "CREATE CERTIFICATE (Transact-SQL)" link.

Another SQL Server component that has been enhanced is Notification Services. Similar to Reporting Services, Notification Services was initially a SQL Server 2000 add-on that has seen a major renovation in SQL Server 2005. The underlying concept behind Notification Services is that you can use SQL Server 2005 to trigger text messages. The most common use is triggering Short Message Service (SMS)-style messages. However, Notification Services' rich interface allows much more. You can trigger not only SMS-style text messages but also SMTP-based and even HTTP-based messages, such as Simple Object Access Protocol (SOAP) messages to remote Web services. When you pair Notification Services with Service Broker, you have a very robust application framework in which all the underlying plumbing is provided by Microsoft. Although you'll find information about the Notification Services improvements in SQL Server 2005 Books Online (BOL), a more informative resource is the tutorial available at http://msdn2.microsoft.com/en-us/library/ms170337.aspx.

Another area improved in SQL Server 2005 is replication. In the past, replication was something you essentially configured on the server and hoped it worked for more time than it took you to configure it. Don't get me wrong. Replication could work for extended periods--for one company, I built an enterprise replication program that ran for weeks, even months, at a time without problems--but I still didn't think of replication as a preferred solution.

Fortunately, SQL Server 2005 provides greatly enhanced replication services. It's now possible to hook programmatically into replication and use HTTPS to tunnel replication through firewalls. In addition, the ability to carry out replication on a per-user basis has been simplified. In general, replication is becoming a feasible solution. For basic information about SQL Server 2005 replication, go to http://msdn2.microsoft.com/en-us/library/ms151198(en-US,SQL.90).aspx. For developer-related information about replication, check out the following two links:
http://msdn2.microsoft.com/en-us/library/ms146869.aspx
http://msdn2.microsoft.com/en-us/library/ms152495.aspx

The improvements in SQL Server 2005 replication come at an opportune time because two new factors are changing how replication will be used: smart devices and smart applications. Smart devices are meant to carry a minimum of data, and as powerful as the last version of SQL Server Mobile Edition was, the 2005 version opens a whole new vista for handheld devices. Similarly, more and more people are beginning to leverage smart applications. Accordingly, a robust replication model that perhaps leverages transactions loaded into, say, a Service Broker with follow-up notifications on acceptance of transactions would seem to provide a robust application infrastructure.

Finally, for those of you who didn't make it to one of the recent launch events and are a fan of Orange County Choppers (OCC) on the Discovery Channel (and for the benefit of my 5-year old nephew Nikita whose favorite book is his OCC book), here's the link to the Orange County Choppers' launch video for SQL Server 2005: http://www.microsoft.com/winme/0511/25581/VBS_2005_OCC_GreatPlains_750k.asx. (The video requires Windows Media Player 10.) Paul Sr., Paulie, and Mikey do their best to talk up Microsoft's new accounting packages built on SQL Server 2005 and Great Plains 9.0. Have a happy holiday season.