In-Depth

Codejockeys Assess the Risks and Rewards of Next-gen SQL Server

• By Stephen Swoyer
• June 1, 2005

• 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 that.”

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 night.

“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 risk.”

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 wrong?”)

Irreconcilable differences
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.