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 .NETbased user interface, as a superior replacement for the earlier tools. System managementfocused 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 managementoriented 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 failovernot 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 allowsor worse, requiresthat 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 onon 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 minutemore than required for any currently known application.

Is this the future of databases? With SQL Server 2005, that certainly is possible.




Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net