Attending events such as the SQL Server Magazine LIVE! conference (October 27-30) always gives me a lot to think about, and the most recent LIVE! conference was no exception. During the course of the event, I experienced a particularly exciting "aha" moment when I began to better appreciate the long-term significance of stored procedures in the up-and-coming Web services world.
SQL Server currently lets you expose a stored procedure as a Web service. (For information about this functionality, see the resources I recommend in "Use the SQL Server 2000 Web Services Toolkit to Get Started with .NET". I always thought this ability sounded like a good idea but was impractical. Calling a stored procedure through a Web service isn't fast enough for most real-world applications. But I've seen the light. The role that stored procedures exposed as Web services will have in future .NET-based architectures is related to "extreme performance," which I've discussed several times in past commentaries.
As I said in "Designing Databases for Extreme Performance", the need for extreme performance sometimes forces me to make a design decision that sacrifices some response time and performance in the short term to ensure needed scalability as the application grows. But what if I didn't have to sacrifice short-term response time for long-term scalability? What if I could cram as much processing onto the database tier as possible, then easily redeploy some processes to a mid-tier application server when my database began to run out of CPU steam? What if the trade-offs between scalability and response time became a runtime deployment issue rather than a fixed-in-stone design issue?
Although Yukon, the next release of SQL Server, won't be rolled out anytime soon, Microsoft says that XML integration in Yukon will be tighter than in SQL Server 2000. And the company has indicated that it will add features to solve many of the performance and integration problems currently associated with using stored procedures exposed as a Web service. In addition you'll be able to write stored procedures in any .NET Common Language Runtime (CLR)-compliant language. This ability will make it more practical to design and build certain business functions as stored procedures to gain performance benefits. You'll have the flexibility to redeploy those CLR-based stored procedures to a scalable Web farm if database server processing power begins to run short.
Thus, Yukon might make my current theory of extreme performance irrelevant by letting me dynamically make trade-offs between response time and scalability. The future of SQL Server is Yukon, and what an exciting world it's shaping up to be.