Codejockeys Assess the Risks and Rewards of Next-gen SQL Server
- By Stephen Swoyer
- June 1, 2005
SQL SERVER 2005 BRINGS RISKS AND REVOLUTION
- Microsoft says that dropping CLR into SQL Server will be a boon for enterprise
developers because codejockeys will be able to use the .NET programming languages
of their choice.
- For programmers who are well-versed in SQL Server development, Microsoft’s
next-gen SQL Server probably doesn’t bring all that much to the table,
at least from a coding perspective.
- For codejockeys who have little experience programming directly to SQL Server–or
whose experience has mostly been limited to coding with ADO.NET and other
bridging technologies in the middle-tier–SQL Server 2005 Server is nothing
short of a revolutionary release.
Time after time, in market after market, Microsoft has used a similar recipe
for success: Make it easy to use, abstract the underlying complexity of what’s
involved, and customers will come—in truckloads most of the time.
It’s a philosophy the software giant has employed to great effect with
its Windows server operating systems, Internet Information Services Web platform
and Visual Basic IDE. Fewer programming languages are easier to pick up and
start working with than Visual Basic, and Visual Studio—even in its .NET
flavor—is frequently celebrated for its ease of use, along with its tight
integration with the rest of Microsoft’s technology stack.
So ease of use (from the perspective of end users, anyway) is an approach that
has paid huge dividends for Microsoft. However, it’s also an approach
that has gotten Microsoft into trouble: With security professionals, industry
pundits, government regulators and even with many of its users. Simply put,
ease of use comes at a price.
It’s worth keeping these thoughts in mind as Microsoft preps its long-awaited
SQL Server 2005 database. The software giant says the revamped SQL Server is
its most developer-friendly RDBMS ever, and officials point to a bevy of features,
such as improved integration with Visual Studio .NET, and support for the .NET
framework, courtesy of a native (in-process) implementation of the .NET Common
Language Runtime, they believe will appeal to enterprise codejockeys. In delivering
a developer-friendly version of SQL Server, however, some skeptics say Microsoft
is courting disaster. (See related story, “The controversy
is in progress.”)
And while the data management space, especially, is renowned for the passion
and dogmatism of its conceptualists, even many SQL Server enthusiasts confess
to lingering uncertainty about Microsoft’s move.
Programming boon or bust?
Microsoft, unsurprisingly, admits to no such ambivalence. Instead, the software
giant says that dropping CLR into the RDBMS will be a boon for enterprise developers.
The reason? Native CLR will be the first to allow codejockeys to use the .NET
programming languages of their choice—Visual Basic .NET or C#—to
code directly to SQL Server 2005.
One upshot of this, Microsoft officials say, is that enterprise programmers
don’t have to be conversant in Transact-SQL, the SQL flavor favored by
SQL Server. “This is taking the world of the database developer and the
world of the Visual Studio developer and bringing those two worlds together,”
says Tom Rizzo, director of product management for SQL Server with Microsoft.
“They’re very separate today. You can talk to SQL Server from a
Visual Basic application running in the middle tier, or a C# application in
the mid-tier, but you can’t extend it from the mid-tier today. This changes
Microsoft says native CLR and improved Visual Studio .NET integration will
empower developers to work more productively with its SQL Server 2005 database.
Most skeptics think the same thing. That, they say, is what keeps them up at
“This is not an improvement, but a disaster that threatens to destroy
data integrity,” says Joe Celko, author of several SQL Server-oriented
books, including, most recently, Joe Celko’s SQL Programming Style.
Celko and other skeptics warn of an alarming scenario that—in light of
Microsoft’s past experiences on several technology fronts—has the
penumbra of possibility about it. “Programmers with no database experience
will start writing code in the database in a wide range of incompatible languages,”
Celko says. “Since they do not know set-oriented declarative coding, they
will convert an RDBMS into a 1950’s file system.”
Microsoft’s Rizzo, of course, disagrees. There’s the potential
that native CLR in SQL Server 2005 will be abused, he concedes, but the same
could also be said about any non-relational programming extension—such
as native support for XML or Web services connectivity, which Microsoft touts
as major selling points of SQL Server 2005. “You also have people who
are upset about this,” he argues. “The bottom line is, yes, this
is going to strike some people as unnatural, but it’s where the market
is right now. We believe the immense value in doing this far outweighs the minimal
In other words, native CLR—such as native support for XML and Web services
connectivity—is an RDBMS fait accompli. Naysayers can kvetch all they
want about this, Rizzo is saying, but it’s what the market demands. Or—to
put it another way—it’s what enterprise developers want.
Maybe it is, skeptics counter, but that still doesn’t make it a good
idea. (See related story, “What could go
First, an important disclosure. Whenever we speak of “skeptics,”
we’re almost always talking about folks who have backgrounds in data management.
People who—in their workaday lives—are database administrators,
data architects or SQL programmers. It’s not surprising, then, that a
lot of these folks have a strong built-in bias against the programmer-friendly
architectural changes Microsoft has implemented in SQL Server 2005. Most technologists
are protective of their turf and DBAs, whose first instinct is to safeguard
the consistency and accuracy of their data, are even more protective than most.
With this in mind, native CLR proponents argue, what’s the big deal?
It’s not as if VB .NET and C# are going to replace T-SQL—even Microsoft’s
Rizzo agrees as much—and it’s unlikely that any reasonable CIOs
are going to give their rank-and-file codejockeys carte blanche to hammer away
at data (relational and otherwise) that’s housed in SQL Server 2005. Besides,
proponents counter, even if they did, the data management side of the divide
would almost certainly throw up enough roadblocks to frustrate such an enterprise.
What’s important, insists Microsoft’s Rizzo, is that CLR and T-SQL
are on equal footing in SQL Server 2005. Microsoft may have brought programming
for the RDBMS down from the mountain, Rizzo says, but it hasn’t done that
at the expense of SQL Server’s bread-and-butter T-SQL language.
More to the point, Rizzo says, there’s no use fighting the market. Several
vendors currently offer out-of-process (non-native) CLR assemblies for SQL Server
2000, so there’s clearly a demand. By integrating CLR in-process with
the SQL Server RDBMS itself, Rizzo claims, Microsoft is giving such customers
a safe, reliable and faster way to execute managed code in the database tier.
“Say you’ve got some SQL stored procedure that runs, using a lot
of memory, and then we get another query coming in that uses T-SQL, which doesn’t
use CLR, but we want to make sure that we balance the performance of both so
they both get the same performance,” he comments.
Storming the castle
Graeme Malcolm isn’t like a lot of other SQL Server pros. He’s a
principal technologist with technical training and content development specialist
Content Master, which means he spends a lot of time thinking and writing about
abstract database-related issues and figuring out ways in which to translate
them into intelligible snippets for clients.
In this capacity, Malcolm says, he’s worked closely with Microsoft on
several occasions, collaborating with the software giant’s Patterns and
Practices team, and also with Microsoft Learning. Consequently, he may have
a bias strongly in favor of Microsoft’s SQL Server 2005 overhaul.
Nevertheless, Malcolm makes a compelling case that the addition of managed
code (via in-process CLR) can be an excellent complement to traditional T-SQL.
“I suspect that for the most part, database developers will still be able
to—and should—implement most of the data tier logic they’ll
ever need in T-SQL,” he concedes. “However, there will always be
some scenarios where T-SQL doesn’t provide the necessary flexibility,
or where external resources are required, and this is where managed code in
the database makes sense.”
More to the point, he suggests, DBAs, SQL programmers and other data management
pros need not view the move to native CLR as an us-vs.-them imposition. Instead,
Leach says, there are cases in which managed code can conceivably be an attractive
alternative to T-SQL—even for those (such as SQL programmers) well versed
in the latter language. “You might need a stored procedure that writes
the results of a FOR XML query to a file,” Leach explains. “You’ve
always been able to develop extended stored procedures in C++ and call them
from Transact-SQL, but the ability to internalize them with managed code, and
bring them within a controllable security context, makes a lot of sense.”
Likewise, SQL Server MVP Sommarskog offers a few more potential benefits. For
starters, he says, in-process CLR can be a useful alternative to extended stored
procedures in cases in which OLE objects are invoked from T-SQL (call to sp_OAxxxx).
Similarly, managed code makes more sense than T-SQL if programmers need to code
for complex computations—such as string manipulations or complicated math—that
don’t necessarily involve data access. Even in cases in which complex
computations do require data access, Sommarskog says, it might make more sense
to exploit native CLR, particularly if it’s otherwise difficult to do
so (using T-SQL) in a set-based manner.
More on ADTmag.com
Self-service data warehouses
By Alan Radding
On the demise of .NET,
and other poppycock
By Mike Gunderloy
Would you like a slice
of FUD with your Java?
By Mike Gunderloy