Chapter 1. Changes in the Database Paradigm: How SQL Server 2005 Redefines the Role of a DBA
• History
• SQL Server 2005 and Database Administrators
Microsoft SQL Server 2005 represents the completion of Microsoft SQL Server’s transformation into a leading database management system. The changes are extensive, completing the capabilities required for the most demanding enterprise applications and introducing a new application paradigm that affects how many new SQL Server applications will be implemented and deployed.
A major upgrade such as SQL Server 2005 brings many benefits to customers. It greatly improves classic “abilities,” such as availability, scalability, reliability, and so on. It introduces powerful new application development capabilities. Many of the rough spots in earlier versions of SQL Server have been polished. And many of the items long requested by the SQL Server community are now provided. SQL Server 2005 has something for everyone.
With any major advance comes challenges. In the case of SQL Server 2005, the challenge for a DBA is adapting to numerous changes from previous versions. Some of these changes fundamentally alter the mechanics of how DBAs interact with SQL Server; others are features with deep, long-term impact on how database applications are built and the demands they place on DBAs.
Before discussing the impact of these changes on DBAs, let’s review the SQL Server philosophy and history. Then we’ll look at the impact of some key changes in SQL Server 2005 on the DBA. Finally, we’ll take a holistic look at these changes as part of the new application architecture.
SQL Server Philosophy
SQL Server 2005, like SQL Server 7.0 and SQL Server 2000, was driven by five major philosophical forces: enterprise suitability, developer friendliness, cost of ownership, completeness, and ubiquity.
Enterprise Suitability
The ability of SQL Server to handle the most demanding enterprise applications has been a key philosophy since SQL Server 7.0 development began. The focus has been on classic attributes such as quality, performance and scalability, availability, platform integration, and manageability as well as on key functionality required by enterprise customers. With SQL Server 2005 the enterprise suitability push is particularly strong, most notably in the focus on high availability.
Developer Friendliness
While earlier versions of SQL Server focused on developers, with a special emphasis on independent software vendor (ISV) application developers, SQL Server 2005 is a developer’s paradise. Deep integration of the Microsoft .NET common language runtime (CLR), extensive XML support, enhanced Transact-SQL (T-SQL) support, new development tools, and many other enhancements make SQL Server 2005 the database system for developers.
Cost of Ownership
SQL Server has always been inexpensive compared with the competition. Since SQL Server 7.0, Microsoft has focused on lowering the overall cost of ownership. While 7.0 was somewhat revolutionary in that regard, SQL Server 2005 is more evolutionary. SQL Server 2005 continues the tradition of reducing or automating most of the traditional tuning requirements of database systems. New management tools, supportability enhancements, and platform integration further address lowering the cost of ownership.
Completeness
Prior to SQL Server 7.0, a database management system product generally consisted of a relational database engine, a client/server communications mechanism, and some rudimentary tools. Good management tools, powerful data-loading tools, replication, analytic tools, and so on were extra-cost options, if available at all. SQL Server 7.0 changed that by including most of what was necessary to build and deploy database applications in the base product. SQL Server 2005 continues this philosophy through the inclusion of an end-user component for Reporting Services, better integration of Analysis Services and the relational engine, fleshed out data mining support, and the new Service Broker.
Ubiquity
One of the goals of SQL Server 7.0 was to replace the need for multiple different data stores with a single technology base. This required SQL Server to be capable of running while embedded within an application on a desktop as well as being a standalone database server running on the largest enterprise server hardware. It also required compatible technology to run on handheld devices. This resulted in the creation of MSDE and SQL Server CE. SQL Server 2005 makes a further push toward ubiquity by replacing those earlier technologies with SQL Server 2005 Express Edition and SQL Server 2005 Mobile Edition.
History
After a late 1993 decision to become a mainstream database management system vendor, Microsoft began work on its first in-house–developed version of SQL Server. Prior to that point, Microsoft was a porting partner for Sybase SQL Server, adapting it to Microsoft operating systems and enhancing it with tools more suitable for the workgroup and departmental environments that used Microsoft-based servers. In 1995 Microsoft shipped SQL Server 6.0, and followed it up with SQL Server 6.5 in 1996. These two releases, developed entirely in-house, based on the Sybase architecture and code, allowed Microsoft to push beyond its original workgroup and departmental markets into some enterprise applications. But even before SQL Server 6.0 shipped, work had begun on a revolutionary new SQL Server. A new query processor, cursor engine, database API (OLE DB), and other database management system components were already under development. And initial discussions to acquire OLAP technology had already taken place.
SQL Server’s transformation was planned as three releases. The first release would focus on the basic re-architecture of SQL Server and making it suitable for enterprise use. The second release would be a quick-turnaround release to address key customer concerns and plug any critical competitive holes that had emerged. And the third release would be the one where Microsoft could fully exploit the new architecture to address a broad range of new customer requirements.
With the first release, SQL Server 7.0, Microsoft successfully ripped out the internals it had inherited from Sybase and replaced them with the new technology it had developed in-house. This approach, rather than building a new database system completely from scratch, was employed to retain compatibility for the thousands of applications already running on SQL Server. Customers experienced major improvements in reliability, scalability, performance, and manageability. SQL Server also became the first database product to incorporate all the tools necessary for building data warehouses when it included both extraction, transformation, and loading (ETL) tools and an online analytical processing (OLAP) engine. With its Distributed Query feature and OLE DB architecture, it also became the first database system to make heterogeneous data access a standard integrated capability. Replication, a SQL Server strong-suit since version 6.0, underwent a major upgrade in capabilities. Perhaps most importantly, SQL Server 7.0 focused on automating or eliminating many of the tuning and management demands that had historically been required of database systems.
The second release, SQL Server 2000, was a refinement of SQL Server 7.0. XML was just emerging as an interesting topic in computing, and the SQL Server team became convinced that adding support for XML was critical. Materialized (a.k.a. indexed) views, originally envisioned as a feature for the third release, had become a hot requirement as a result of the then-popular TPC-D benchmark. And numerous modest performance, scalability, and availability improvements were incorporated to enable SQL Server to continue to meet enterprise requirements. Subsequently to the initial release of SQL Server 2000, SQL Server was enhanced with better support for mainframe-class servers such as the HP Superdome, Unisys ES/7000, and 64-bit Intel Itanium-based servers; a major security update; new XML capabilities; and new Reporting Services and Notification Services modules. The new Reporting Services feature, along with major OLAP upgrades and new data mining capabilities included in the initial SQL Server 2000 release, and various server upgrades such as materialized views, made SQL Server 2000 a leading data warehousing platform.
Work on SQL Server 2005 began late in 2000, with a goal of delivering on most of the remaining capabilities envisioned in the original three-release plan. Leading the priorities, the original vision of “Visual Basic stored procedures” had evolved to be full support for the Microsoft .NET CLR (and thus all CLR languages). As anticipated with SQL Server 2000, XML had indeed become a major force in the application development world, and it would become core to nearly all new work in SQL Server 2005. Along with these technology shifts, new features such as direct HTTP access and subsystems such as Service Broker would enable a greatly enhanced SQL Server application model. On the data warehousing front, Analysis Services would receive a major overhaul, and SQL Server’s Data Transformation Services would be replaced by a new enterprise-class ETL tool. At the same time, nearly every other area of SQL Server would receive major upgrades.
SQL Server 2005 and Database Administrators
Initially, many DBAs will see SQL Server 2005 as little more than an evolutionary release with many new features and management capabilities that they will need to master. Over time, most DBAs will come to see SQL Server 2005 as a new application platform that dramatically changes how the applications are built and how they work, as well as the skills DBAs must master to support these applications. In the following sections, we’ll look at SQL Server 2005 from the evolutionary standpoint and then concentrate on it as a revolutionary new application platform.
One Small Step for a DBA
You, the DBA for several SQL Server databases, are suddenly handed the DVD for SQL Server 2005 and told to prepare for an upgrade. What should you expect? Just upgrading an application from SQL Server 7.0 or SQL Server 2000 doesn’t require much effort on the part of DBAs or developers. After installation, nearly all applications just run as before. Evolving applications to take advantage of new SQL Server 2005 features, or deploying new applications developed specifically for SQL Server 2005, is a different story. But for now, let’s focus on straightforward upgrades of existing systems. In most regards, this is more straightforward than the upgrade that occurred between SQL Server 6.5 and 7.0, but there are a few twists for DBAs.
Unlike with the 6.5 to 7.0 upgrade, SQL Server 2005 retains the same basic on-disk structures as the previous version, and thus there is no requirement for a database migration. And while all feasible measures have been taken to ensure the near-complete application compatibility, with APIs continuing to work and T-SQL compatibility maintained (via a Compatibility Level setting in some cases), you need to be aware that there might be some application compatibility challenges. But while application compatibility generally shouldn’t be an issue, DBAs will find that how they interact with SQL Server 2005 is substantially different than in the past.
The first substantial change that a DBA will notice is that the toolset for SQL Server 2005 has been completely replaced. Gone are the familiar SQL Enterprise Manager and Query Analyzer tools; they have been replaced by SQL Server Management Studio. SQL Server Management Studio also takes on some of the capabilities that were present in Analysis Manager. Although OSQL.exe is still provided, it has been deprecated in favor of the new sqlcmd.exe utility. The Client Network Utility, Server Network Utility, and SQL Service Manager have been replaced by the SQL Server Configuration Manager MMC snap-in. And a new tool, Business Intelligence Development Studio, is introduced for building business intelligence applications. These toolset changes represent a major departure from earlier versions of SQL Server and require immediate attention from DBAs.
Why such a dramatic change in the toolset? There are many reasons, but let’s start with the basic truth about DBAs. While much of the IT world has a clear split between developers and operational personnel, the DBA role encompasses development, system management, and operational activities. The previous SQL Enterprise Manager/Query Analyzer split meant you, as a DBA, had to constantly switch between two different tools with completely different user interface styles to perform your job. The new SQL Server Management Studio allows you to performance all aspects of your job by using one tool. DBAs with a strong development focus will easily embrace SQL Server Management Studio, with its Visual Studio .NET–based user interface, as a superior replacement for the earlier tools. System management–focused DBAs may find the transition less natural and take some time to become fond of the new tools.
Another reason for the toolset change was the growing complexity of the overall SQL Server product. In order to build a business intelligence application, should there be separate tools for the relational database, the analysis engine, the ETL service, and the reporting service? Or should there be one tool for building the business intelligence application and another for managing all the underlying services? With SQL Server’s new toolset, Microsoft chose the latter approach.
Both development- and system management–oriented DBAs will find that the new toolset offers considerable new functionality and a far more powerful DBA environment than was available in previous releases of SQL Server.
In addition to its new toolset, SQL Server 2005 has a number of other new developments. Perhaps the most glaring is that Microsoft has replaced SQL Server 7.0/2000’s Data Transformation Services (DTS) with SQL Server Integration Services (SSIS). Although it is possible to continue to run DTS packages in SQL Server 2005, there are no tools to support further development of those packages. When you need to do new development, you have to migrate your DTS packages to SSIS, with the help of the Data Transformation Services Migration Wizard. It is important to keep in mind that even minor maintenance of existing DTS packages forces a DBA to deal with this change.
As a DBA works with SQL Server 2005, he or she will begin to notice considerable differences in other core aspects of SQL Server 2005. For example, the classic system tables have been largely replaced by the system catalog, whose primary access is through a set of catalog views. For compatibility, the system tables remain, implemented as a set of compatibility views over the system catalog. In addition, usage of the master database has been split in two. The Microsoft-shipped system objects now reside in a read-only (and generally hidden) Resource database. For DBAs used to having the ability to modify the Microsoft-shipped system objects such as system stored procedures, this represents a substantial change.
There are solid reasons for these changes, and DBAs are sure to welcome them once an initial adjustment period has passed. The new system catalog cleans up the metadata mess introduced with SQL Server 7.0. In SQL Server 6.5 and earlier, the system tables exposed considerable information about the implementation of SQL Server. Applications relied on this information. With the re-architecture of SQL Server 7.0, the internals changed in ways that couldn’t be compatibly represented in the system tables. Although the tables were cobbled together to preserve as much application compatibility as possible, Microsoft started to recommend that customers not reference them directly. Various alternatives were suggested, but none fully met the needs of users. Changes in SQL Server 2005, such as the support for partitioning, once again require major changes in the system tables. Therefore, Microsoft came up with the new system catalog. The catalog views isolate new applications from future changes to the system catalog, and the compatibility views isolate existing applications from the new metadata design. This should provide a far superior design moving forward.
Likewise, the Resource database solves a number of problems, most importantly the ability to roll back service packs. This highly requested feature has previously proven impractical to implement because applying a service pack almost always results in changes to system objects, such as system stored procedures. With these changes in the master database, and applied via scripts, there is no straightforward way to undo them (without losing user information stored in the master database). By moving them to the resource database, you can undo them simply by copying the older version of the resource database back onto the system.
So far we’ve focused on changes that are somewhat disruptive, and you may be questioning the wisdom of upgrading to SQL Server 2005. Of course, we haven’t looked at many of the benefits that even a simple upgrade can bring. For example, in addition to the new catalog views, SQL Server 2005 provides dynamic management views, which give a DBA extensive insight into what is happening inside SQL Server 2005 and can be used to troubleshoot performance and other problems. And the Dedicated Administrative Console feature allows a DBA to get into a server that is overloaded or otherwise appears hung, to diagnose and correct problems. And a DBA can take advantage of the new plan guides (sp_create_plan_guide) to use query hints to tune specific queries without modifying any application code. In addition, SQL Server 2005 provides the ability to perform nearly all operations, such as index creation, online. It also provides the ability to easily maintain in your network a mirror copy of a database that’s on another server. These are just a few examples of the many immediate benefits DBAs gain when upgrading to SQL Server 2005.
DBAs will also find SQL Server 2005 a compelling option for new hardware platforms their organizations deploy. SQL Server 2005 has added support for AMD and Intel’s 64-bit extensions to the x86 architecture, full support for Itanium 64-bit systems, and extensions for tuning NUMA (non-uniform memory access, the memory architecture used to build many high-end servers) systems. Microsoft performed modest tuning to SQL Server 2000 for new hardware using service packs, but SQL Server 2005 is the first release really designed for the computer systems of 2005 and beyond rather than for those of 2000. These changes allow for greatly increased scalability with little impact on the DBA. In some cases, the DBA’s workload will decrease as the need to tune around 32-bit memory limitations is eliminated.
Although most applications easily upgrade to SQL Server 2005, you need to invest in upgrading your skills even for straightforward upgrades. And depending on which features of SQL Server applications use, it may make more sense to make substantial changes as part of the upgrade process.
A Giant Leap for DBA-kind
Every aspect of SQL Server 2005 has received new capabilities. Many of the largest changes in SQL Server 2005 reflect new features for application developers. In the database world, when a developer sneezes, it means the DBA is about to catch a cold. But first we’ll explore some of the more significant classic database features that have changed. The following sections focus on the new features in SQL Server 2005 that have a major impact on DBAs.
The More Things Change, the More They Stay the Same
SQL Server 2005 has a true toy-chest of wonderful capabilities for DBAs. One that I’ve already mentioned is database mirroring, which is the ability to maintain an exact (and, if desired, fully synchronous) copy of a database on another server for high availability purposes. This is an alternative to using Microsoft Clustering Services (MSCS), and it offers numerous benefits (for example, easier to set up, no special hardware required, very fast failover time) and some limitations (for example, only individual application databases failover—not the entire environment). Database mirroring is particularly useful in conjunction with MSCS because you can use MSCS to provide high availability within your data center and database mirroring to provide a copy in a different physical location, in case of a disaster (for example, power grid outage, earthquake, terrorist attack). Database mirroring is a more integrated, real-time (or synchronous) version of the log shipping that was available in SQL Server 2000.
One change that will please DBAs (and developers) who work with both SQL Server and Oracle is support for snapshot isolation. Snapshot isolation brings to SQL Server Oraclelike “readers don’t block writers” and “writers don’t block readers” style concurrency. With SQL Server 2005, it is also possible to specify that all read committed transactions (the default transaction isolation mode) should use snapshot isolation. With traditional locking, read committed transactions already have the property that readers don’t block writers. Specifying that read committed transactions should use snapshot isolation for reads also means that writers can’t block readers.
For those with larger databases, particularly data warehouses, SQL Server now supports table and index partitioning. Partitioning is generally used to make maintenance easier. For example, in a data warehouse you could load the data for a new month into a table independently of its final destination and then do a rapid switch of that table into a partition of the larger table. Or you could independently rebuild an index partition rather than an entire index.
As mentioned earlier, DTS has been replaced by SSIS. Whereas DTS was a basic ETL tool, SSIS is an enterprise-class ETL and data integration tool, offering a more powerful programming model and very high performance through extensive parallelization.
The security architecture of SQL Server 2005 has been enhanced. The new endpoint-based authentication mechanism allows you to easily manage and secure access to SQL Server. Windows authentication is still the preferred authentication mechanism, but for applications that require SQL authentication, a lot of work has been done to strengthen the SQL authentication. SQL Server now allows granular permissions, and it has fixed ownership chaining issues, introduced the notion of “real” schemas and separated them from the users, and added cryptography support. Microsoft has gone to great lengths to ensure that a problem the magnitude of W32.Slammer does not happen again. Examples of this initiative include the “off by default” features and the new Surface Area Configuration tool, which lets you protect your SQL Server 2005 environment by reducing the attackable area of a system.
These are just a few of the substantial improvements to classic database functionality in SQL Server 2005 that DBAs will be taking advantage of.
You Call This a Relational Database Management System?
The biggest changes in SQL Server 2005 relate to its new features for developers. These changes are not on the same level as changes in previous versions, such as adding a new client API like OLE DB. These are major fundamental changes to how the database server portion of an application is written. And they affect the DBA just as strongly as they affect developers. These developer features include ability to natively store, index, query, and modify XML data, writing stored procedures and functions using .NET languages, and implementing asynchronous messaging inside the database.
The Ubiquity of XML
In a few short years, XML has gone from being a curiosity that might better enable the interchange of data between disparate applications to being the most important application building block of our time. SQL Server 2000 introduced support of XML at the level of mapping XML documents back and forth to relational tables. SQL Server 2005 treats XML as a fundamental data type. This means that SQL Server 2005 can now natively store, index, and manipulate XML data. SQL Server 2005 handles the XQuery query language, not just SQL. It also means that SQL Server 2005 uses XML itself in places where complex information must be communicated. For example, query notification and event notification both format their notification messages in XML. The new Service Broker returns its errors messages as XML strings. And Analysis Services 2005 uses XML for Analysis as its native protocol.
XML has become a native protocol for talking to the SQL Server 2005 database engine. In earlier versions of SQL Server, the only protocol support by the database engine was Tabular Data Stream (TDS). Microsoft provided several native APIs, all of which spoke TDS to the server. SQL Server 2005 provides an alternative: native HTTP SOAP access to the server. By using HTTP SOAP and without using any client database API, an application can formulate a request to the database server, have it natively handled by the server (without the need for IIS or any other intermediary), and have the data returned formatted as XML. This is particularly useful when a non-Windows system (such as Unix or Linux) for which Microsoft doesn’t provide a client API needs to make a request to SQL Server 2005. But it can be used for any application on any platform, including Windows, and with the broad support for XML documents on most platforms, many applications may choose to bypass traditional database APIs in favor of HTTP SOAP.
DBAs who have already made XML a key part of their environments will take very naturally to these new capabilities in SQL Server 2005. Even if an installation doesn’t yet make use of XML for application data, DBAs (and developers) need to become XML experts in order to use many of the new features in SQL Server 2005.
To .NET or Not to .NET?
No feature of SQL Server 2005 is more powerful or more controversial to the DBA community than its support for the Microsoft .NET CLR. At its basic level, CLR support enables the writing of stored procedures in programming languages such as C# and VB.NET. The SQL Server and .NET teams have gone to great lengths to ensure that these stored procedures run with the same level of safety and integrity as those written in T-SQL. For truly data-intensive processing, T-SQL remains a better choice, but for code that does complex computation or need access to resources external to SQL Server 2005 itself, a CLR language is the appropriate choice.
CLR support is much more than just new languages for writing stored procedures. With SQL Server 2005’s support for user defined types, user defined functions, user defined aggregates, indexes on computed columns, and so on, CLR support is a core means of extending the server’s functionality to support applications not easily handled by purely relational systems. It could be used for something as simple as adding a new Date data type (to handle the Arabic calendar, for example) to integrating support for spatial data into SQL Server.
Another popular use of CLR support is as a replacement for extended stored procedures. Extended stored procedures have a reputation for negatively affecting the reliability of a server and for making it very difficult to diagnose seemingly unrelated server problems. Most extended stored procedures can be replaced by CLR stored procedures running with SAFE or EXTERNAL_ACCESS permissions, neither of which allow the integrity of the server to be compromised. The few that require UNSAFE permission have the potential to cause the same problems as extended stored procedures and require similar care to prevent problems. However, they are written for an environment that makes it easier to find, diagnose, and correct problems than exists for extended stored procedures.
So why all the controversy around CLR support? It all goes back to the fundamental nature of DBAs. Some DBAs are purely responsible for operational aspects of the server. Most are responsible for data modeling and the design and maintenance of the database schema. Many DBAs also write T-SQL stored procedures that are then called by applications. Often, these DBAs are responsible for reviewing T-SQL stored procedures written by developers before putting them into production. And, of course, DBAs are the ones who are called in the middle of the night to diagnose a problem when the database server breaks. But only a few DBAs are themselves developers, expert in programming languages such as C# and VB.NET. If their installation allows—or worse, requires—that database server logic be written using the CLR, these DBAs fear they will be forced to become developers or be unable to carry out their job responsibilities. And even those without this concern wonder if a C# stored procedure really can be as safe to run in the server as T-SQL.
SQL Server isn’t, of course, the first database system to face the question of whether a traditional programming language can be used inside the database system itself. Multiple database products have supported and emphasized the use of Java to write stored procedures for a number of years. So the basic concept is proven.
SQL Server 2005 lets a DBA decide whether the CLR is to be supported at all in an installation, and it can’t slip in behind the DBA’s back. Most conservative organizations are likely to avoid the use of CLR in the database server for now. But given the growing popularity of the CLR in application development, DBAs will eventually find themselves pressured into supporting this environment. And given the choice of allowing an extended stored procedure or a CLR stored procedure into the database server, even the most skeptical DBA should opt for the CLR route.
Are You Being Served?
After XML and .NET CLR support, the most significant developer enhancement to SQL Server 2005 is the new Service Broker. Service Broker is a messaging system, similar to message-oriented middleware, that is fully integrated into the database engine. This gives SQL Server an integrated asynchronous distributed programming model that can work within a single instance or across instances on multiple computer systems. The queues for Service Broker are fully implemented in SQL Server, allowing them to participate in SQL Server transactions without the need for two-phase commit. This yields higher performance and greater scalability than transactional queuing implemented outside the database system. Also, because Service Broker is fully integrated, it is managed using SQL Server 2005 facilities (for example, part of SQL Server’s security model, managed with SQL Server Management Studio, backed up as part of the database backup, and so on). Finally, because Service Broker is an integral part of the database engine, it is also used as a system communication facility for features such as event notifications and query notifications.
As a standalone feature, Service Broker will have a dramatic impact on how SQL Server applications are built in the future. Take a current system in which two applications must communicate information such as a shopping cart application shipping an order to an order processing application. Because the shopping cart application is generally running on one or more servers outside the normal production world, it wouldn’t be possible for those servers to share databases. In traditional application development, the shopping cart application would read the order from its database and use RPC, a web service interface (for modern, synchronous applications), a batch file, or message-oriented middleware (for asynchronous applications) to ship the orders to the order processing application. The order processing application would accept the orders and place them in its database. With Service Broker, this process is greatly simplified. The shopping cart application would simply send to the order processing application a message that contains the order information as part of the SQL transaction finalizing the customer’s order request. SQL Server Service Broker would take care of getting the message to the order processing database, where it would be picked up by the order processing application.
Of course, you can use Service Broker for even simple scenarios, such as communicating work requests between two modules of the same application. And it is a more robust and flexible solution than using two-phase commit for solving the problem of applications that need to update two or more databases (in separate instances) in the same transaction.
One of the most interesting scenarios is Service Broker’s ability to enable very high, even continuous, availability. For example, an application might normally function with requests between systems being completed immediately. However, if a back-end server were to fail (or be taken offline for maintenance), the front end could continue to accept requests and queue them until the back-end server was restored. Further, Service Broker would transparently route requests to a mirror database should it take over as the principal database. This ability to hide transient failures, as well as longer-term planned and unplanned outages, makes Service Broker a key tool in creating systems whose availability exceeds that possible with a single computer.
Because Service Broker doesn’t come into play unless developers build applications that use it, the initial impact on DBAs may be low. On the other hand, because Service Broker takes full advantage of the skills and expertise of the DBA community, DBAs may want to encourage developers to use Service Broker rather than alternatives such as message-oriented middleware. Either way, over time, DBAs should expect Service Broker to become a major means of interconnecting databases.
Where No One Has Gone Before
From the late 1960s though the mid-1980s, database systems were essentially pure data repositories residing on the same computer systems as the applications that used them. In the mid-1980s, client/server computing became popular, with applications residing on desktop computers talking to databases running on servers. For client/server computing to work properly, database systems had to evolve from being pure data repositories to having support for protecting the integrity of the data they held from being damaged by the client. This required database systems to create their own application model. Sybase SQL Server was the first to do so, offering features such as stored procedures and triggers. As this feature set evolved, it became known as TP-Lite because it subsumed many of the capabilities traditionally provided by transaction processing monitors such as CICS, ACMS, and Tuxedo. In the mid-1990s, the client/server model gave way to the n-tier model. In the n-tier model, the database system continues to live on its own server, the application itself resides on a separate tier of servers (often front-ended by a tier of web servers), and the client is yet another tier that interacts through the web tier. The n-tier model is particularly popular for high-performance transaction processing.
In the mid-2000s, the application model is once again evolving based on the notion of Web Services. You could take a very simple view of Web Services and say that it is simply replacing the HTML front end exposed by the web servers with an interface specification that uses XML instead. But it really goes deeper. Web Services encourages the development of applications that are partitioned into a set of services with published service interfaces. This encourages applications that are scalable (because each service can run on a separate system), distributable, reusable, and so on. So there is both a design philosophy difference in how to think about building an exposing application functionality and a change in the technology for exposing that functionality. This affects primarily the application server level, but imagine if you took it to the database level.
SQL Server 2005 encourages taking Web Services to the database server level and implementing databases as database services. The extensive support for XML, a key technology for constructing Web Services applications; the support for native HTTP SOAP access, another key technology for Web Services; and the availability of the CLR, which allows SQL Server 2005 to host complex application functionality make SQL Server 2005 an ideal platform for building Web Services applications. With the addition of Service Broker, with its ability to reliably tie together distributed database services, SQL Server 2005 represents a new-generation database application platform.
The benefits of building applications on database services fall into many areas. For a DBA, two of the major benefits are availability and scalability. As discussed earlier, by using Service Broker to tie together the various database services, it is possible to achieve very high availability across a broad range of unplanned and planned outages. By breaking an application into several Database Services, it is possible to achieve extremely high scalability. For example, an application might initially host database services for order entry, order verification, credit verification, distribution, shipping, and so on—on a single server. As the application outgrows the server, individual database services can be transparently moved to other servers. In the extreme, an installation could allocate a 64-way server to each database service, resulting in the ability to handle several million transactions per minute—more than required for any currently known application.
Is this the future of databases? With SQL Server 2005, that certainly is possible.
Summary
The feature-rich SQL Server 2005 release changes the database paradigm by introducing .NET CLR integration, deep XML support, asynchronous messaging, HTTP SOAP access, a new toolset, enhanced business intelligence capabilities, and more. At the same time, a lot of work has been done to enhance the availability, scalability, security, and reliability of the server. This paradigm shift opens up unlimited opportunities for application developers, enabling them to architect and build applications in a way never done before. On the other side, it brings challenges and a new learning curve for DBAs.
This chapter discusses how SQL Server 2005 continues on SQL Server’s basic philosophy to be a database management system that can handle the most demanding enterprise applications, be developer friendly, reduce the total cost of ownership, be feature-rich and complete, and be able to run as a lightweight database engine and run on handheld devices.
This chapter also details the upgrades and new features introduced in SQL Server 2005, the challenges it brings, and how to deal with them. Chapter 2, “SQL Server 2005 Architecture and Components,” looks at the SQL Server 2005 architecture in more detail.