That approach wasn't possible until recently for users such as Nasdaq. Two of the most heavily promoted new features in SQL Server 2005 are the deeply integrated Visual Studio development tool set and the .Net Common Language Runtime (CLR) execution environment. Microsoft executives have cited the work required to integrate those technologies as the main reason for the more than two-year delay of the database upgrade, which the company finally released last month.
Ken Richmond, Nasdaq's vice president of software engineering, expects that his staff will be able to write all of the stored-procedures code for the upcoming projects in CLR-supported languages such as C# or C++ instead of Transact-SQL, Microsoft's extension of SQL. "It's one of the things that I find very, very attractive" in SQL Server 2005, Richmond said.
He isn't alone. More than half of 35 IT managers who responded to a random survey conducted by Computerworld also said they expect the integration of Visual Studio and the CLR with SQL Server to be helpful. Some even predicted that it will change their philosophy about database programming and lead them to put more logic directly into their database servers.
"It standardizes programming languages and gets us away from the sometimes archaic SQL language," said Christopher Siegle, a Pittsburgh-based systems analyst and project lead at
Kirkpatrick & Lockhart Nicholson Graham LLP, a law firm with offices in the U.S. and the U.K. The integration will also help strengthen communication between database administrators and developers, he added.
Timothy O'Rourke, vice president for computer and information services at Temple University in Philadelphia, said his staff can "start building reusable code with .Net to execute logic from SQL Server." That is "much more flexible and powerful" than putting business logic at the application server tier, he said.
But any IT shop that hopes to make heavy use of the new development options will need to do a careful analysis, because using .Net languages likely won't be the best approach for all of their database development work, according to several programming experts, analysts and users.
There will be plenty of instances where code written in T-SQL "will beat the pants off .Net CLR code," said Phil Hummel, a solution architect who works at Microsoft's technology center in Mountain View, Calif. "I don't want to see people waste time heading down the wrong track of trying to do everything with .Net procedures just because we have new features that will help people in some selected situations."
Users such as Keith Glennan, chief technology officer at Los Angeles-based Northrop Grumman Corp., are already anticipating the need for additional training to make sure their IT staffs can achieve optimal use of the CLR. Glennan said the new options will present a challenge to developers who are trying to determine when it's appropriate to embed code in a database.
On one hand, Glennan said, the new capabilities should make it simpler and more efficient for developers to encapsulate business rules within a database. But on the other hand, application logic embedded in the database will have to be controlled the same way that it was when it was in the application layer, he said. "The trade-off is between optimized performance and transparency of your object model," Glennan said. "It requires a lot of discipline in how and where you define objects and services. There's never a completely black and white answer to how you ought to do this."
The balance could get particularly delicate for companies such as Northrop Grumman that are moving to service-oriented architectures. The SOA approach encourages the separation of application-specific business logic into a different tier from databases. But the new capabilities in SQL Server 2005 may entice some IT shops to consider shifting business logic to the database, said Forrester Research Inc. analyst Carl Zetie.
Zetie said he worries about the prospect of users "backsliding into entangled code" after having spent a considerable amount of time layering their systems.
Andrew Brust, co-author of the upcoming book Programming Microsoft SQL Server 2005, said the CLR will be best reserved for two database programming scenarios: creating aggregate functions, in which numeric data from a series of separate data records is calculated or collected; and defining custom data types where data values might have different interpretations or expressions, such as a calendar-year quarter vs. a fiscal-year quarter.
But those aren't typical bread-and-butter data manipulation and retrieval uses, added Brust, who is chief of new technology at Citigate Hudson Inc., a New York-based Microsoft business partner that specializes in development of business intelligence applications. T-SQL will remain the better choice in those scenarios because it's optimized for testing, retrieving and changing large sets of data, Brust said.
Microsoft said that in SQL Server 2005, it has improved T-SQL's exception-handling capabilities and added a set of relational operators and query-language extensions. Those are designed to better align it with the SQL:2003 standard and to enable users to navigate hierarchical relationships in a table, handle large data values and pivot data.
"T-SQL is a wonderful language," said Paul Flessner, senior vice president of server applications at Microsoft. "We're not going to orphan it in any way. As long as there's a SQL Server, there's going to be T-SQL."
Some users have no plans to shift away from T-SQL. Mediterranean Shipping Co. worked with SQL Server 2005 for months as a participant in Microsoft's Technology Adoption Program and went live with the database earlier this year. But at the time of the product's official launch, the company still hadn't bothered to enable the CLR, which is turned off by default in the new database.
Mediterranean Shipping has more than 7 million lines of business logic written in T-SQL and employs 25 programmers who specialize in that language, said Fabio Catassi, chief technical officer at the Geneva-based operator of container ships.
Catassi said he thinks the CLR is best used for managed code on the application server and on the client - not in the database layer. He said Mediterranean Shipping will stay with T-SQL for its databases, with the possible exception of smaller projects. "If you have a large amount of data, T-SQL is still the fastest way of manipulating [it]," Catassi said.
Developers for years have had the ability to write stored procedures in Java for Oracle and IBM databases, but most haven't made use of that option, according to analysts and officials at the vendors.
Mark Townsend, senior director of product development at Oracle Corp., estimated that no more than 30 percent of the developers who write code for his company's databases use Java to build stored procedures.
And most of the IT managers who responded to the informal Computerworld survey indicated that the Java capabilities have done little to change the way their shops program to Oracle and IBM databases.
"There's a perception -- accurate or not -- that a [Java virtual machine] operating in the database slows the database down and makes it more resource-hungry," said James Brockman, a developer at the Missouri Department of Insurance. "DBAs in particular are unpersuaded by arguments from developers that using Java everywhere would help their productivity or mean one less learning curve to climb. They don't understand Java, so developers are not allowed to use it."
Microsoft users may be more likely to want to take advantage of the company's new programming options, said Gartner Inc. analyst Mark Driver. He noted that it isn't unusual in the Microsoft world to find a developer working on the user interface as well as the business and database logic, whereas in Oracle environments, database developers tend to focus only on database code.
Yet Driver doesn't expect the ability to write stored procedures in languages like Visual Basic to be the driving factor that gets IT managers to upgrade to SQL Server 2005. "Most of the energy and excitement around SQL Server is around scalability and the database engine," he said.
Sidebar
Microsoft starts from behind with its own technology
In a strange bit of irony, Microsoft 's database rivals supported its Common Language Runtime engine for stored procedures even before the company provided the CLR capabilities itself with the November release of SQL Server 2005.
Les King, program director of DB2 marketing at IBM , said his company was first out of the gate with support for CLR stored procedures two years ago. Oracle followed suit this year as part of its 10g Release 2 database. In both cases, the CLR support is available only when users run their databases on Windows servers.
But despite running behind, Microsoft claims that it will have the upper hand because its implementation of the CLR is built inside the process that manages all the resources for SQL Server 2005.
"What that means -- and this is the key difference between our implementation and our competitors' implementations -- is you have the ability to write CLR code for both stored procedures and triggers, and all with a consistent security model," said Corey Thomas, a group product manager for SQL Server at Microsoft. "However you set the security policies inside SQL Server, those policies will be the same that apply to your CLR code."
That eliminates the need for developers to manually write the security policies at each layer of the application, Thomas said.
An IT architect at a manufacturing company who asked not to be identified said he expects his development group to slowly shift to CLR-supported languages in order to put more database access controls in SQL Server itself. Much of that logic is currently built into the company's applications, and the databases are accessed via application accounts, he said.
"The idea is for the database to protect itself, rather than writing an external application layer to control data access," the architect said. "Then you can give end users direct access to views for end-user reporting, rather than extracting the data into a data warehouse and building your data security controls all over again."
The manufacturer's small group of database administrators could do the work with stored procedures in T-SQL, but they already have their hands full overseeing the company's databases, he added. Keeping the security logic with the application developers, working in the languages they're familiar with, makes more sense because they typically have a better understanding of the business and security rules that apply to the company's data, he said.
In addition, they will have access to a rich set of .Net components that could, for instance, help them to get data from an Oracle database as part of an authorization check, the architect said.
"T-SQL and stored procedures are not the best tools for accessing non-SQL Server data," he noted. "But sometimes you need data from other sources to securely process a query."
Sidebar
Key questions that developers should ask
-- Will T-SQL or a .Net procedure-based language, such as C# or Visual Basic, enable a database job to be processed with the least amount of resources?
-- For jobs that are well suited to .Net languages, should the code run inside the database engine or on a separate system in order to save resources on the database server?
Sidebar
T-SQL vs. CLR
In SQL Server 2005, developers have a choice of programming in Transact-SQL or languages supported by Microsoft's Common Language Runtime.
-- CLR: Best for code that's computation- or logic-intensive. A better option than 'extended stored procedures,' which Microsoft offered in earlier SQL Server releases for creating server-side code with logic that's difficult to write in T-SQL.
-- T-SQL: Best for data manipulation and retrieval.
Sidebar
Users split on wisdom of logic in database
Although the deeply integrated .Net technologies in SQL Server 2005 will give Microsoft users new options for programming business logic into their databases, it's unclear to what degree that will inspire IT shops to modify their current development strategies.
The 35 IT managers polled by Computerworld were split about evenly on the topic. Some respondents insisted that logic has no business being in the database. Others said they plan to take advantage of the new capabilities.
"The more business logic that we can integrate directly into the database, the better," said David Buzzell, CIO at The Sedona Group , a staffing services and IT consulting firm in Moline, Ill. "This helps to ensure all data within the system is following established business logic. If the data isn't adhering to our business rules and isn't properly structured, then the data won't help us in the long run."
An IT manager at a major national retail chain said SQL Server 2005 will make it easier for internal developers to follow the IT department's strategic-directions document. That plan advocates moving business logic closer to the data in order to make it easier to provide standard and consistent ways to access information. "We have been attempting to get our development teams to think in this way for a while," he said.
Not everyone shares that view. For example, Rick Stuller, CIO at Hawaiian Electric Co. in Honolulu, said the power company makes only limited use of stored procedures in order to ensure database independence. Switching databases can be hard work for an IT shop that uses a database vendor's SQL extension to write stored procedures.
Scalability concerns are the main reason why First American Title Insurance Co. tries to keep business logic in the business services tier, according to Sue Binks, vice president of IT strategic product development at the Santa Ana, Calif.-based company. "We can always throw more servers into the pool to process the logic," Binks said.
Tyson Hartman, chief technology officer for the Americas region at Avanade Inc., a Seattle-based consultancy that is a joint venture between Accenture Ltd. and Microsoft, said it's easy to scale out application servers, whereas it can be challenging to do the same thing with database servers. "If you have a solution that requires significant scale, putting all the logic in the database has its limits," he said.
Sundial Software Corp. tries to maintain all of its business logic in the application layer, said Ethan Roberts, a software architect at the Madison, Wis.-based IT consulting firm. But database stored procedures built with .Net technologies could minimize network traffic for repetitive operations against a large number of data records, Roberts said. He noted that with the logic in a middle tier, the data would have to cross the network.