An Overview of SQL Server 2005 for the Database Administrator

  • Article

By Eric Brown

Summary: This document provides an overview of new features in Microsoft® SQL Server™ 2005 for database administration and database availability, scalability, and security.

On This Page

Introduction
High Availability and Scalability
Extending High Availability to All Database Applications
Minimizing the Maintenance Window
Database Development, Deployment, and Management: A Revolutionary Tool Set
Deploying SQL Server 2005
Tools for Management and Performance Tuning
Conclusion
Appendix

Introduction

Microsoft® SQL Server™ 2005 provides the Microsoft® Windows Server System™ integrated server software with a database platform for the next generation of connected, scalable, and reliable enterprise applications. The breadth and depth of innovation in this version is in response to the needs of customers. This white paper is targeted to database administrators, to give you an understanding of the new features in and capabilities of SQL Server 2005. From many enhancements of existing features, to an entirely new security model, database administration is now more productive and in tune with the needs of the administrator.

The world of the DBA is changing. DBAs now increasingly are asked by the organizations they work for to manage more systems with larger amounts of data, while at the same time organizations are trying to keep IT costs the same or even to lower costs. Simultaneously, DBAs are being asked to give more of their time to help corporate developers to understand data models and the best data access paradigms for building reliable applications that perform to the scale that the corporation requires. With these tensions, DBAs need more from their database technologies in terms of enterprise manageability, scalability, and availability. SQL Server 2005 provides the bedrock foundation that DBAs can bet on to build their next generation IT infrastructure.

At the core of SQL Server 2005 are new infrastructure application capabilities. SQL Service Broker is a distributed application framework that provides a new form of scalability and reliability for asynchronous message delivery. Though not new, Microsoft® SQL Server™ Notification Services, Reporting Services, and SQL Server Mobile Edition (formerly called SQL Server CE) are all greatly enhanced in SQL Server 2005.

SQL Service Broker

Over the last 10 years, the proliferation of e-commerce applications has created a need for increased process management across database applications. If you've ever built an order entry system designed for online customer ordering, you're familiar with the complexities of the process at the back end. For example, when a customer places an order for a book, the order needs to commit transactions into the inventory, shipping, and credit card systems of the database, and must also send an order confirmation using another Web application. Waiting for each of these processes to happen in order doesn't scale well. SQL Server 2005 provides a new scalable architecture for building asynchronous message routing.

The Service Broker technology allows internal or external processes to send and receive guaranteed, asynchronous messages by using extensions to normal Transact-SQL data manipulation language (DML). Messages are sent to a queue in the same database as the sender, to another database in the same instance of SQL Server, or to an instance of SQL Server either on the same server or on a remote server. Service Broker brings the best of asynchronous messaging with the best transactional support available, the SQL Server relational engine.

Reporting Services

With the release of SQL Server 2005, Microsoft extends a major new component of its integrated business intelligence (BI) platform. SQL Server Reporting Services expands the Microsoft business intelligence vision by making it easy to get the right information to the right people, in any business environment.

Reporting Services is a complete, server-based platform for creating, managing, and delivering traditional and interactive reports. It includes everything you need "out of the box" to create, distribute, and manage reports. At the same time, the modular design and the extensive application programming interfaces (APIs) of Reporting Services enable software developers, data providers, and enterprises to integrate reporting with legacy systems or third party applications.

Reporting Services ships with SQL Server 2005 and includes:

  • A complete set of tools for creating, managing, and viewing reports.

  • An engine for hosting and processing reports.

  • An extensible architecture and open interfaces for embedding reports or integrating the solution in diverse IT environments.

Notification Services

Notification Services is a platform for developing and deploying applications that generate and send notifications to users. Notifications are personalized, timely messages that can be sent to a wide variety of devices.

Notifications reflect the preferences of the subscriber. The subscriber enters a subscription to express an interest in information. For example, "notify me when the stock price of Adventure Works reaches $70.00," or "notify me when the strategy document my team is writing is updated."

A notification can be generated and sent to the user as soon as a triggering event occurs, or can be generated and sent on a predetermined schedule that the user specifies. The user's subscription specifies when the notification should be generated and sent.

Notifications can be sent to a wide variety of devices. For example, a notification can be sent to a user's mobile phone, personal digital assistant (PDA), Microsoft Windows® Messenger, or e-mail account. Because these devices often accompany the user, notifications are ideal for sending high-priority information.

SQL Server Mobile Edition

Microsoft® SQL Server™ 2000 shipped with Microsoft SQL Server 2000 Windows CE Edition. This is now called Microsoft SQL Server Mobile Edition, and can be developed and managed through the new SQL Server Management Studio. You can now create a SQL Server Mobile Edition database on the desktop or on the device, directly from SQL Server Management Studio. You can also manipulate the schema of the SQL Server Mobile Edition database directly from Management Studio, regardless of whether the database resides on the mobile device or on the desktop. You can create subscription databases and pull data from a SQL Server publication database through Management Studio into a SQL Server Mobile Edition database either on the desktop or on the device. Lastly, you can run queries through Management Studio that will target a SQL Server Mobile Edition database on the device or on the desktop, and you can take advantage of new SQL Server Mobile Edition features that include an XML showplan rendered in a GUI format just like native SQL Server, and the ability to use query hints to override the query optimizer. SQL Server Mobile Edition is integrated with Data Transformation Services (DTS). You can now access non-SQL Server data sources and bring data directly into a SQL Server Mobile Edition database on the desktop by using DTS objects in Microsoft® Visual Studio® 2005 applications. SQL Server Mobile Edition also takes direct advantage of new merge replication performance improvements that greatly increase the scalability of a single server with thousands of simultaneously connected SQL Server Mobile Edition subscription databases.

As you can see, there are many changes to the infrastructure of the SQL Server platform. The database engine provides a mature and reliable infrastructure for scalability, reliability, and security. The frameworks mentioned above participate in failover clustering, backup and restore, and transactional logging, and you can administer them from SQL Server Management Studio.

High Availability and Scalability

SQL Server 2005 makes significant strides in database availability and reliability. Downtime falls into two categories, planned and unplanned. Unplanned downtime is really disaster recovery, and requires special planning and infrastructure. Failover clustering can be used to keep the system online during planned maintenance, and this technology is enhanced in SQL Server 2005. Additionally, SQL Server 2005 introduces database mirroring, a new technology that provides an easy to use option for database availability.

Failover Clustering

SQL Server 2005 failover clustering provides high availability support for server-wide failure. With failover clustering, the operating system and SQL Server work together to provide failure protection by providing redundant hardware and an automated mechanism to move the database server to secondary hardware in the event that the primary fails. Failover clustering supports up to eight nodes, depending on which edition of Microsoft® Windows Server™ 2003 is running on the server.

SQL Server has extended the abilities of failover clustering to SQL Server Analysis Services, Notification Services, and SQL Server replication. With SQL Server 2000, SQL Server Agent and other job management and processing capabilities were not covered by failover clustering. Now, the technologies are clustering aware; SQL Server failover clustering is now a more complete server-level redundancy solution.

Extending High Availability to All Database Applications

SQL Server 2005 extends the data availability frontier in new directions with copy-on-write technology. SQL Server 2005 introduces a new high-performance transaction transmission technology for database-level availability—database mirroring. With database mirroring, transactions changes are shipped to another server as the logs are written. All logged changes to the database instance are quickly available for copying to another location.

Database Mirroring

SQL Server 2005 significantly enhances the capabilities of log shipping by providing you with a database mirroring option. Database mirroring allows continuous streaming of the transaction log from a source server to a single destination server. In the event of a failure of the primary system, applications can reconnect to the database on the secondary server almost immediately, without waiting for recovery to finish. The secondary instance detects failure of the primary server within several seconds and accepts database connections almost immediately after detecting a failure. Unlike failover clustering, the mirrored server is fully cached and ready to accept workloads because of its synchronized state.

A database mirroring system requires three servers that are running SQL Server, and each server has a specific architectural role: the "principal," the "mirror," and the "witness." The principal server is the server on which applications connect and where transactions are processed. The mirroring server is the target of transaction log records, which can be applied either synchronously or asynchronously. The mirroring server exists in a state that does not allow direct read access to the data. As transaction log records are generated on the principal server, they are continuously replayed on the mirroring server, producing a state in which the mirroring server is normally behind the principal server only by the time it takes to replay the log written in a single log write. This provides a duplicate of the data at a point in time.

The witness server is an arbiter within the architecture, providing the tie-breaking "vote" in determining which server is "the principal" and which is "the mirror". Two servers in the architecture must agree in order for a server to be designated the principal and thus the target of all transactions. From a client application point of view, the failover from one server to the next is automatic and nearly instantaneous. The server that is "the witness" is only needed in instances were automatic failover is needed.

One of the most important things about database mirroring, besides the extremely low latency provided during a failover from the principal to the mirror, is the ability to synchronize changes in both directions. In the event that the principal goes offline and the application fails over to the mirror, the mirror then becomes the principal server in the architecture. When the failed server comes back online, it is designated the mirror and transaction log records from the principal are applied to it to bring it into synchronization with the state of the database at that point in time.

Database mirroring works on standard server hardware and requires no special storage or controllers.

While database mirroring provides continuous support, there are many scenarios where a "snapshot" of the database is needed: as a "warm to cold" standby; as a test and development database; or as a reporting database. Copy-on-write technology provides this capability in the form of a database view.

Database Snapshot

SQL Server 2005 introduces the capacity for administrators to generate and use a read-only, stable view of a database. The database snapshot provides this capability without the overhead of creating a complete copy of the database or storage requirement. As the primary database diverges from the snapshot, the snapshot gets its own copy of original pages when they are modified. The snapshot may be used to recover an accidental change to a database by simply reapplying the pages from the snapshot to the primary database.

Replication

Replication is designed to increase data availability by distributing the data across multiple databases. Availability is increased by allowing applications to scale up out the SQL Server read workload and across databases, or by synchronizing offline data with mobile user databases that occasionally connect. SQL Server 2005 enhances the availability options in these two spectrums by using a new peer-to-peer model that provides a new topology in which databases can be synchronized transactionally with any identical peer database. This increases database availability by enabling a higher "read" workload against the distributed data while also providing the fault tolerance of the redundant distributed databases. A new option for replicating through http:// and https:// increases the availability of data for mobile scenarios, which enables synchronization over the Internet. The new Oracle Publishing feature enables replication from Oracle databases into SQL Server, improving the availability of heterogeneous data within the enterprise.

Minimizing the Maintenance Window

One of the biggest headaches for database administrators is having to restart the database server for changes in SQL Server system parameters. SQL Server 2005 has substantially reduced the number of parameters that require restart. You can now alter the database server affinities for CPU and RAM memory without being required to restart. As part of the development cycle, every feature that previously required a restart now no longer requires a restart. When planned or unplanned downtime does happen, the time that it takes to completely recover has been reduced by the "on the fly" database capabilities in SQL Server 2005 that use rolling restores. Additionally, SQL Server provides the ability to create and manage indexes online, so that you no longer have to take the database offline for these tasks.

Online Index Operations

The ability to create, rebuild, or drop an index online is an enhanced feature of SQL Server 2005 that augments the indexing capabilities of earlier versions of SQL Server. The online index option allows concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language (DDL) execution. For example, while a clustered index is in the process of being rebuilt, you can continue to make updates to the underlying data and can perform queries against the data. In earlier versions of SQL Server, index DDL operations such as rebuilding held exclusive locks on the underlying data and the associated indexes, preventing modifications or queries until the index operation was complete. With support for online index operations, you can add the indexes without interfering with access to the tables or other existing indexes. Additionally, the server workload allows for index operations to take advantage of parallel processing.

Online Restore

SQL Server 2005 introduces the ability to perform a restore operation while an instance of SQL Server is running. Online restoration capabilities improve the availability of SQL Server because only the data that is being restored is unavailable. The rest of the database remains online and available. Earlier versions of SQL Server require that you bring a database offline before you perform a restoration.

You can choose between two options when you use online restore:

  • An online file-level restore of an entire database file

  • An online restore of a filegroup, because a filegroup is nothing more than a collection of files.

Fast Recovery

SQL Server 2005 improves the availability of SQL Server databases with a new faster recovery option. Users can reconnect to a recovering database after the transaction log has been rolled forward. Earlier versions of SQL Server required users to wait until incomplete transactions had rolled back, even if the users did not need to access the affected parts of the database.

Read/write operations can continue in parallel with the undo phase of recovery. However, operations that access the locked data for the undo phase will experience normal blocking within the database until the undo operation releases locks on that data.

Mirrored Backups

Earlier versions of SQL Server supported only a single copy of a given backup. If backup media was damaged, roll-forward would take longer, or would fail. In SQL Server 2005, backup media can now be mirrored. For example, you can set up four tape devices to back up two media families, with a mirror for each media family. The corresponding volumes in each mirror have identical content, making them interchangeable at restore time. You can implement up to four mirrored backup sets.

Database Development, Deployment, and Management: A Revolutionary Tool Set

While many features in SQL Server are evolutionary, there is one area of SQL Server that is revolutionary—the tool set. SQL Server Management Studio is the first database administration suite that allows users to connect to and work with SQL Server, Analysis Services, and a SQL Server Mobile Edition database. Moreover, you can also manage DTS, Reporting Services, and Notification Services applications. Whether you're a serious Transact-SQL developer or are new to SQL Server, the SQL Server tool set provides the flexible and intelligent architecture that you need to accomplish database tasks.

The SQL Server 2005 tool set is focused on three primary usage scenarios: authoring, management, and operations. Each of these scenarios has unique tools and features. Let's start with a look at the new tools for development.

SQL Server Management Studio Query Editor

SQL Server 2005 replaces SQL Query Analyzer with the Query Editor, a component of SQL Server Management Studio and the primary tool for designing and testing Transact-SQL statements, queries, batches, and scripts interactively. With the Query Editor, you can write new scripts in Transact-SQL and Multidimensional Expressions (MDX). You can also edit scripts that are created from files or that are automatically generated from SQL Server Management Studio dialog boxes or from SQL Object Explorer.

The Query Editor offers:

  • Statement auto-completion, a list of possible objects that might follow certain keywords for system stored procedures.

  • Parameter tipping for system stored procedures to provide a list of parameters that follow stored procedures and user-defined functions.

  • Color-coding of Transact-SQL and MDX syntax to improve the readability of complex statements.

  • Templates to speed development of the Transact-SQL statements for creating SQL Server objects.

  • Editing of execute and parse queries with SQLCMD extensions.

  • Results presented in either a grid or a free-form text window, or in XML form, when using the XML column or XML functions for Transact-SQL.

  • Graphical representation of the showplan information, presenting the logical steps built in to the execution plan of a Transact-SQL statement.

Deploying SQL Server 2005

Tools for Deployment and Configuration

Many database administrators need to have greater control of SQL Server during the configuration and set up stages of database application deployment. The ability in SQL Server 2005 to alter basic parameters and locations of files provides greater flexibility for application security and resource management. The following sections present an overview of some of the changes in SQL Server 2005 that relate to setup and configuration.

Microsoft Windows Installer

Setup for SQL Server 2005 uses Microsoft Windows Installer to completely integrate the installation of SQL Server features in a single feature tree. Minimum and typical installation modes are no longer implemented. Instead, Setup displays a feature tree with default options selected; you can then customize installation by selecting and clearing items on the feature tree and specifying installation paths. This version of Windows Installer also supports remote setup and multiple instance configurations.

Consistency Checker

Setup for SQL Server 2005 provides a Setup Consistency Checker (SCC), a new feature that checks and validates the target computer before setup begins. Using Microsoft Windows Management Instrumentation (WMI) technology, the consistency checker prevents setup failures that are due to unsupported configurations on local, remote, or clustered target computers. If Setup can fix failed check items, you can allow Setup to take the necessary action. Otherwise, the consistency checker will guide you to a solution for each blocking issue before Setup can continue.

Failure Reporting

Setup for SQL Server 2005 includes improved failure reporting and extensible alerts. If an error occurs during installation, Setup determines a failure exit code, provides a descriptive error message, recommends corrective actions to take for resolution, and points you to the setup log. Setup also saves the log from each installation.

Computer Manager

Computer Manager is a replacement for the Client Network Utility, SQL Server Network Utility, and SQL Server Service Manager. Computer Manager in SQL Server 2005 provides the ability to work with Analysis Services, Reporting Services, previous versions of SQL Server, and full-text search and Microsoft Search. From within Computer Manager, you can define network protocols for client and server network access. Computer Manager is supported by WMI.

Tools for Management and Performance Tuning

SQL Object Explorer

The new SQL Server Management Studio integrates the functionality of Enterprise Manager and Analysis Services from earlier versions of SQL Server. SQL Object Explorer provides all of the same functionality as Enterprise Manager. Enhancements include greater scalability when you explore database objects, and the integration of scripting across OLTP and online analytical processing (OLAP) databases. SQL Server 2005 and SQL Server Mobile Edition include support for Analysis Services, DTS, Reporting Services, and Notification Services. SQL Object Explorer provides all of the administration capabilities that you expect from Enterprise Manager, including Security, SQL Server Agent, SQL Mail, and the other SQL Server sub systems, which include DTS, Notification Services, and Reporting Services.

Maintenance Plans

In earlier versions of SQL Server, you create database maintenance plans by using a single wizard, and they create multiple agent jobs as an output. These agent jobs themselves contain command-line parameters that indicate the function to perform. Although the Database Maintenance Wizard answers a few key customer scenarios (such as full database backups), it is not possible to either define other scenarios such as differential backups or to easily obtain exactly which Transact-SQL command was actually being executed for the maintenance task.

SQL Server 2005 provides a completely renovated design that provides the ability for you to define almost any SQL Server related maintenance task. The Maintenance Plan feature in SQL Server 2005 utilizes the power of the design surfaces in DTS Designer, allowing you to define workflow within your maintenance plan.

SQL Profiler and Database Tuning Advisor

SQL Server 2005 introduces a new index tuning tool called the Database Tuning Advisor (DTA). The DTA has the ability to suggest index and table partition changes to a database structure. Additionally, SQL Profiler has undergone extensive revision in SQL Server 2005, resulting in greater performance and providing deeper visibility into the server. Following are the biggest changes to SQL Profiler:

  • Profiling of Analysis Services. SQL Profiler now supports capturing events that are raised by Analysis Services.

  • Saving of traced Showplan as XML. New XML-based Showplan events have been added and you can now save Showplan results in an XML format. Results that are saved in this manner can later be loaded for graphical Showplan display in SQL Server Management Studio. Deadlock events also use XML text in returning deadlock information. As with Showplan events, deadlock events may also be persisted to disk as XML and later re-graphed in SQL Server Management Studio. This includes a graphical deadlock statement.

  • Saving trace results as XML. You can save trace results in an XML format in addition to the standard formats of ANSI, UNICODE, and OEM. Once saved in XML format, trace results can be edited and loaded back into SQL Profiler for replay.

  • Aggregating view. You can choose an aggregate option and select a key for aggregation. This will enable you to see a view that shows the column on which the aggregation was performed, along with a count for the number of rows that make up the aggregate value.

  • SQL Profiler can correlate performance monitor counters with SQL Server or Analysis Services events. You can select from a pre-defined set of performance monitor counters and save them at specified time intervals, while also independently collecting a SQL Server or Analysis Services trace. Trace and performance data that overlap in time will be correlated by relating the graphically represented counters to trace results (which are presented in tabular form).

Showplan Enhancements

Microsoft SQL Server 2005 introduces enhancements to Showplan and Deadlock Traces that provide you with additional capabilities for tuning database servers. These enhancements include:

  • Showplan results are saved in an XML format, which later can be loaded for graphical display in the Query Editor.

  • The ability to save Showplan results in an XML format provides a number of benefits for performance tuning: Showplans can be saved, transferred to another location, and viewed without the need to have an underlying database.

You can use an exported Showplan to aid in identifying discrepancies between different in-house or remote databases.

SQLCMD

SQLCMD is the new command prompt utility for working with SQL Server 2005. It replaces the OSQL command prompt utility. The SQLCMD utility allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt. SQLCMD supports the functionality of OSQL or ISQL, but also introduces a richer set of commands allowing for better operation in application scripts, such as Microsoft® Visual Basic® for Applications (VBA) scripts.

Dedicated Administrator Connection

SQL Server 2005 introduces a dedicated administrator connection to access a running server even if the server is not responding or is otherwise unavailable. This allows you to execute diagnostic functions or Transact-SQL statements in order to troubleshoot problems on a server. The connection is activated by members of the sysadmin fixed server role and is only available through the SQLCMD command prompt utility locally, or from a remote machine.

In earlier versions of SQL Server, it is not possible to connect to a server that is not responding to a SQL Server connection. To use the dedicated administrator connection, execute the SQLCMD utility at the command prompt by using the -A switch to specify a dedicated connection. From there, you can diagnose and repair the problem.

SQL Management Objects

SQL Management Objects (SMO) is a new set of programming objects that expose the functionality of the SQL Server database. SMO is implemented as a Microsoft® .NET Framework assembly. You can use SMO to automate repetitive or common SQL Server administrative tasks, such as programmatically retrieving configuration settings, creating new databases, applying Transact-SQL scripts, creating SQL Server Agent jobs, and scheduling backups.

The SMO object model extends and replaces Distributed Management Objects (DMO), which was included with earlier versions of SQL Server. SMO includes many improvements over DMO, including a .NET Framework object model, advanced scripting operations, offline management of SQL Server configuration settings, partial or delayed instantiation of objects, capture mode execution, delegated execution, and releasable object state. Although SMO is a .NET Framework class library, its functionality is still available through Component Object Model (COM) (late bound only). Some examples of SMO utilization are:

  • An Internet service provider (ISP) needs to provide delegation of SQL Server administration to its clients in a shared database environment. The ISP wants to provide a Web interface and limit the types of tasks that can be performed. The development team at the ISP develops an ASP.NET application by using the SMO object model to build a simple administrative interface, which the ISP provides to its customers.

  • A database administrator wants to delegate basic administration tasks to the Help desk, such as adding new users for the company's SQL Server databases. An IT developer creates a simple Web interface built with the SMO object model, and deploys the interface on a secure area of the company's intranet.

SQL Server Agent

In SQL Server 2000, SQL Server Agent jobs run under the account that created the job. A proxy account can be specified to run CmdExec and COM-based jobs. SQL Server 2005 introduces the following changes to SQL Server Agent security:

  • The creation of an Agent user role. Only users that are explicitly added will be able to add, edit, or execute SQL Server Agent jobs.

  • The ability for system administrators to create one or more proxy accounts and include proxies for other SQL Server subsystems, including DTS.

  • The ability to assign users or groups (SQL Server groups) to use proxy accounts.

Every SQL Server 2005 management action can be scheduled to be run by SQL Server Agent from SQL Server, using Transact-SQL or SQL Management Objects (SMO). SQL Server 2005 provides new objects and counters for SQL Server Agent that Windows System Monitor can use to monitor the activity of SQL Server Agent, including enabled jobs, enabled alerts, enabled schedules, active jobs, active alerts, percentage of job success, and so on. The counters also enable the ability to configure alerts that are triggered by SQL Server Agent performance counter conditions.

Transact-SQL Enhancements

Enhancements to Transact-SQL in SQL Server 2005 reflect greater conformity to the ANSI-99 SQL specification, as well as response to requests from customers. This section highlights some of the changes to Transact-SQL, many of which focus on greater expressiveness in queries.

The new common table expression (CTE) functionality provides the ability to generate query batches in cases where a derived table would be used today. A CTE is a temporary named result set that can be referred to by a defining statement. In its simple form, you can think of a CTE as an improved version of a derived table. You refer to a CTE in the FROM clause of a query, similar to the way you refer to derived tables and views. CTE functionality provides a flexible and expressive way to reorganize normalized data.

One of the biggest additions to Transact-SQL is the recursive query, which furthers the ability to manipulate relational data. A recursive query is a new query type that provides generation of a bill of materials or a hierarchical result set. There are new PIVOT and UNPIVOT operators. These operators perform a manipulation on an input table-valued expression and produce an output table as a result set. The PIVOT operator rotates rows into columns, and may perform aggregations or other mathematical calculations along the way. It widens the input table expression based on a given pivot column, generating an output table with a column for each unique value in the column. The UNPIVOT operator performs an opposite operation to PIVOT, rotating columns into rows. It narrows the input table expression based on a pivot column. These new recursive queries provide a way to reshape data.

Several Transact-SQL enhancements focus on the ability to formulate results based on the application of an operator. The new APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression. You specify APPLY in the FROM clause of a query, similar to the way you use the JOIN relational operator. With the APPLY operator, SQL Server 2005 allows you to refer to a table-valued function in a correlated subquery. APPLY comes in two forms: CROSS APPLY and OUTER APPLY. CROSS APPLY invokes a table-valued function for each row in an outer table expression. You can refer to columns of the outer table as arguments to the table-valued function. CROSS APPLY returns a unified result set out of all of the results that are returned by the individual invocations of the table-valued function. If the table-valued function returns an empty set for a given outer row, that outer row is not returned in the result. OUTER APPLY is very similar to CROSS APPLY, with the addition that it also returns rows from the outer table for which the table-valued function returned an empty set. For more information about the capabilities in SQL Server 2005 for reshaping and manipulating data, see SQL Server Books Online.

SQL Server 2005 modernizes the transactional capabilities of queries by adding an error handling capability. SQL Server 2005 introduces a simple but powerful exception-handling mechanism in the form of a TRY/CATCH Transact-SQL construct. Transaction abort errors that cause a batch to terminate in earlier versions of SQL Server can be caught and handled in SQL Server 2005. For example:

SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO T1 VALUES(1)
/* perform other activity */
PRINT 'After INSERT.'
COMMIT
END TRY
BEGIN CATCH TRAN_ABORT
ROLLBACK
/* perform corrective activity */
PRINT 'INSERT failed.'
END CATCH

Additionally, there are new language constructs in SQL Server 2005 for security, replication, Notification Services, XML, and all of the .NET Framework features. To learn more about all of the new functionality in SQL Server 2005, see SQL Server Books Online.

Security Enhancements

SQL Server 2005 makes significant enhancements to the security model of the database platform, with the intention of providing more management and security options to both developers and administrators. A considerable investment has been made in a number of features covering a broad spectrum; for example, enforcing policies for SQL Server login passwords (in the authentication space), providing for more granularity in terms of specifying permissions at various scopes (in the authorization space), and allowing for the separation of owners and schemas (in the security management space).

Furthermore, keeping in mind core security tenets like "reduction in surface area" and "principle of least privileges," and sound principles like "secure defaults and designs," certain product-wide changes are also being prepared for the final version of the product.

Two Special Scalability Features

There are two scalability features in SQL Server 2005 that are worth mentioning for very large database scenarios. Snapshot isolation level allows users to access the last row that was committed, by using a transitionally consistent view of the database. This new isolation level provides the following benefits:

  • Increased data availability for read-only applications

  • Non-blocking read operations allowed in an OLTP environment

  • Automatic mandatory conflict detection for write transactions

  • Simplified migration of applications from Oracle to SQL Server

For example, locking can cause blocks between applications that are reading and writing the same data simultaneously. If a transaction changes a row, another transaction cannot read the row until the write commits. With snapshot isolation, the reader can access the previous committed value of the row.

The second special scalability feature is table partitioning. While the concept of partitioning data across tables, databases, and servers is not new to the world of databases, SQL Server 2005 provides a new infrastructure capability for partitioning of tables across filegroups in a database. Horizontal partitioning allows division of a table into smaller groupings based on a partitioning scheme. Table partitioning is designed for very large databases, from hundreds of gigabytes to terabytes and beyond. Very large database (VLDB) query performance is improved with partitioning. By partitioning across a range of partitioning column values, subsets of data can be managed and reassigned to other tables quickly and efficiently. To learn more about partitioning, see SQL Server Books Online.

Conclusion

SQL Server 2005 provides the technologies and capabilities that organizations and database administrators count on. The spectrum of new and enhanced functionality that affect database administration is immense. This paper provides only an introduction to all of the new functionality in SQL Server 2005.

Appendix

New Data Types

  • XML data type. An XML data type that allows you to store an XML fragment or document in SQL Server databases. Instances of an XML data type can be columns in a table, functions, or stored procedure arguments, or variables in a function or stored procedure. In addition, the XML data type can be specialized by indicating an associated XML schema that provides both a validation constraint and type information for the data for the XML instance.

    You perform operations on an instance of an XML data type by using built-in XML query methods. These methods accept queries and data manipulation statements that are appropriate for XML data. You can then specify queries (XQuery) against the XML that is stored in the XML data type variable or column, and apply updates (using insert, update, or delete) to the XML instance. You can also use an XSD to create an index for the XML column, which will improve query performance.

  • Varchar(max). The nvarchar(max) and varbinary(max) data types hold up to 2 gigabytes (GB) of data and are useful alternatives to the text, ntext, and image data types.