Over the last two months, I've devoted this column to looking at the many SQL Server 2005 features that you can take advantage of with little or no work. But SQL Server 2005 breaks some interesting new ground by providing features that could fundamentally change the way you think about what a database system is. In this month's column, I touch on some of the features that require a little more work— and possibly a change in the way you think about designing and building database systems.The most obvious example of this kind of fundamental change is in the integration of the Common Language Runtime (CLR) into the heart of the product. Another significant example is Service Broker, which underlies many of the other new SQL Server 2005 features including Query Notifications and Proactive Caching in Analysis Services. Let's take a look at some of the changes you'll see when you take advantage of these two new additions.
The SQL Server CLR (SQLCLR) provides many new possibilities for the design and architecture of new systems. To get the most advantage out of the SQLCLR, you'll want to look at your existing systems and make decisions about which areas of the existing design the CLR will benefit. For example, you might want to consider using the CLR to replace existing extended stored procedures.You can probably replace 98 to 100 percent of your extended stored procedures with CLR stored procedures, and when you do, you should see immediate performance, stability, and manageability improvements.
Another area that can benefit from CLR integration is any middle-or client-tier logic that's performing heavy data access or processing, especially functions that do aggregation work on data in your database. For example, using the new user-defined aggregates (UDAs) in the database should significantly reduce your network traffic and probably increase processing speed. If you're performing a more comprehensive redesign of your database system, you'll also most likely want to take advantage of the ability to effectively extend the database by using other features such as user-defined types (UDTs), which let you better model your business data, and the new XML data type, which lets you better store and manipulate your shared XML data.
As well as changes to your storage and processing design, your systems might also benefit from changes to the data-access design, especially when you use such features as Web services and query notifications. Particularly in the case of query notifications, you can get significant performance improvements for your Web applications by implementing CacheSync through ASP.NET 2.0, which takes advantage of the query notifications infrastructure that's built into SQL Server 2005. Query notifications give you an automated way to create and manage a middle-tier cache. When you turn on CacheSync in ASP.NET 2.0, your Web application will start using the query notifications technology automatically, so whenever you query the database from a Web page, not only do you get the result set, you also set up a subscription to the database that will automatically inform you if the data underlying the query changes.To enable CacheSync, all you need to do is add a couple of lines of code to your ASP.NET 2.0 application. First, add the following line of code to your default.aspx file on the second line, right after the <@Page... code line:
<@OutputCache SqlDependency="Command Notification" Duration="100000" VaryByParam="False" %>
Next add a Web-configuration file (global.aspx), and replace the (commented) line after Application_Start with the following line of code (no line breaks):
System.Data.SqlClient.SqlDepend ency.Start(Configuration Manager.ConnectionStrings (<your connection string>) .ConnectionString)
By just adding these two lines of code to the Web application, you'll enable middle-tier caching against the database. The first time the Web page is called, it will retrieve all of the data for the page and cache it locally. Subsequent page requests will work directly from the cached data. If the data underlying the Web page changes, the query notifications infrastructure in SQL Server 2005 will send ASP.NET a notification, and the next time a user accesses the Web page, the new data will be retrieved from the database and re-cached.
Although this process is simple when you're working with a whole Web page, the implementation is more complex if you need to work with dynamic areas on your Web page—which is why this is a design choice. SQL Server 2005 provides query notifications functionality, but you might not be able to use the default functionality of ASP.NET if you're refreshing only active areas of a page. In such complex cases, you'll have to set up manually the response to the notification and customize your page to respond to it. Although such a setup requires more work than some features, it should still be significantly easier than building your own caching mechanism and will certainly perform better than a typical polling mechanism.
Second only to SQLCLR integration, SQL Server 2005 Service Broker is probably the most significant addition to the product. Service Broker is a reliable, asynchronous queuing mechanism. The previous query notifications example would not be possible without the Service Broker technology in the database engine. Service Broker's asynchronous messaging capabilities enable the ability to send notifications to connected applications when data changes.
You can also use Service Broker to build your own reliable asynchronous queue mechanisms.When I first encountered Service Broker, I immediately thought of how it would help me build workflow applications in which all the workflow was around multiple people interacting with data. In such applications, many actions happen at the same time, but only one path lets you meet all the required conditions.In a future column, I'll cover Service Broker in more depth and propose some possible scenarios in which an asynchronous queuing system built right into the database can be a huge benefit.
You'll find many other SQL Server 2005 features that, with a little planning and work, can significantly increase the performance, scalability, and security of your database systems, as well as potentially let you build new types of systems that were impossible or difficult-previously. Among these features are peer-to-peer replication, which you can use both to provide better availability to your systems and to let you scale geographically and handle more workload; transaction-level snapshot isolation, which gives you data consistency at a transaction level without locking the data but does require you to develop a contingency plan for data consistency when someone alters data during a transaction; and client controls for Reporting Services, which let you embed Reporting Services reports in both Smart Client and Web Applications without requiring a Reporting Services server. All of these features require some level of re-architecture of your systems to take full advantage of their benefits. Next month, I'll look at peer-to-peer replication as an option to help you create a more available system and also as an interesting scalability option that lets you scale your geographic workloads by replicating data between remote sites.
