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.
End of Article
