Among the most significant changes introduced in the areas
of high availability and scalability in SQL Server 2005 are the following:
database mirroring - allows running hot-standby system closely
synchronized with the primary source. This provides an extension of log
shipping functionality, which existed in SQL Server 2000, with a number of
additional enhancements, such as low-latency, automatic failover and failback,
and two-way synchronization.
online restore - provides the ability to restore data without
taking a database offline, which was the case in earlier versions of SQL
Server. Users are only prevented from accessing data that is being restored.
failover clustering - even though this is not a new feature, its
SQL Server 2005 implementation offers significant improvements, such as
eight-node clustering (in combination with Windows 2003 Server Enterprise
Edition) and support for failover of Notification Services, Analysis Services,
and a number of SQL Server Agent related tasks (such as replication or job
management and processing).
online indexing - indexes can now be created, dropped, and
rebuilt (performed typically in order to eliminate index fragmentation) at the
same time that the underlying table data is being queried or modified. In SQL
Server 2000, rebuilding a non-clustered index places a shared lock on the
underlying table, which restricts operations on it to SELECT statements. When
rebuilding a clustered index, SQL Server 2000 places an exclusive lock on the
table, preventing access to it altogether until the operation is completed.
support for both 32- and 64-bit Windows 2003 Server platforms,
including both Intel and AMD (Opteron with Direct Connect Architecture)
processors.
table partitioning - provides the ability to partition tables
across file groups in a database, which optimizes operation on large tables.
database snapshot and snapshot isolation - snapshots generate a
read-only view of the underlying database, which can be used, for example, to
quickly recover data after unintentional or erroneous change. Note that a
snapshot is different from a copy, since it occupies only the space required to
contain changes applied to the database after it has been created, greatly
limiting storage requirements. Snapshot isolation provides parallel access to
the last committed row in a database, which can be used to eliminate blocking
issues when dealing with users operating simultaneously on the same data set.
replication - its SQL Server 2000 implementation has been
enriched by the introduction of a new peer-to-peer topology, the ability to
replicate via HTTP and HTTPS (to accommodate secure communication over the
Internet), and cross-platform replication from Oracle databases.
SQL Service Broker - provides functionality of asynchronous
message routing and guaranteed delivery, intended primarily for scenarios
involving complex, simultaneous, distributed, and interdependent data
processing tasks (common in e-commerce applications). In essence, this is a
message queuing mechanism native to SQL Server 2005, which can be configured
and managed using extensions to the T-SQL data manipulation language.
fast recovery - allows connections to a database when bringing it
on-line as soon as its transaction log has been rolled forward (in previous
versions of SQL Server, connections were permitted only after incomplete
transactions had been rolled back).
With the surging wave of virus threats and the rising rate of
vulnerabilities, database administrators (as well as computer professionals in
other fields) have been devoting more and more of their time and attention to
the area of security. This process has been further accelerated by increasing the
number of regulatory requirements (such as Sarbanes-Oxley Act or Health
Insurance Portability and Accountability Act) enforced in various sectors of the
market dealing with large quantities of data. Microsoft's commitment in this
area has greatly improved since the announcement of the Secure Computing
Initiative and resulted in the following security-related changes in SQL Server
2005:
native data encryption, protected with passwords or certificates,
authorization enhancements.
In the area of data management, changes are also significant, encompassing
new extraction, transform, and load (ETL) features as well as analytical and
data mining processing enhancements:
SQL Server Integration Services - is a revamped implementation of
SQL Server 2000-based Data Transformation Services (for more information on DTS
in SQL Server 2000, refer to our series of articles),
with performance, usability, and manageability improvements. In its new form,
SQL Server Integration Services contains Business Intelligence Workbench and
SQL Server Workbench utilities, which further simplify extracting data from
various sources and distilling it for use in data-warehousing and analytical
applications.
Analysis Services - offering better performance of OLAP and data
mining processing.
built-in support for both relational and XML-structured data -
available through the addition of the XML data type, allowing storing XML
fragments and documents in SQL Server databases (for more information on XML in
SQL Server 2000, you can refer to our
series of articles on the Database Journal Web site). It is also worth
mentioning that SQL Server 2005 has new VARCHAR(MAX) data type - along with
NVARCHAR(MAX) and VARBINARY(MAX) - with the ability to store up to 2GB of data,
supplementing TEXT, NTEXT, and IMAGE data types.
A number of administrative and maintenance tasks have been eliminated or
simplified, by either automating them or introducing new\improved management
utilities. Functionality in this area has also been extended through reporting
and notification services (although note that corresponding products are
available on SQL Server 2000 platform):
self-tuning capabilities have been enhanced,
SQL Server Management Studio - replacing a number of SQL Server
2000 management utilities, including SQL Server Enterprise Manager, SQL Query
Analyzer (replaced by SQL Server Management Studio Query Editor - with extra
features such as statement auto-completion or results presented in XML form),
SQL Server Analysis Services, Reporting Services, and Notification Services, as
well as providing management for SQL Server Mobile Edition databases.
Reporting Services - enhanced from its recently released, SQL
Server 2000-based version, offers the ability to create, manage, and view
reports. Integrating it with SQL Server 2005 eliminates the need for such
external tools as Crystal Reports (or similar third party products).
Notification Services - provides the ability to generate and send
custom subscription-based notifications, (triggered by data changes or
according to a pre-determined schedule), via a variety of messaging mechanisms,
such as e-mail, phone, or instant messenger.
Last, but definitely not least, there are significant enhancements in the area
of development, such as the following:
more powerful programming model,
close integration with Visual Studio 2005, Web Services, and
Common Language Runtime (reflected by the dependency on Microsoft .NET
Framework 2.0) - provides the ability to use .NET-based stored procedures,
functions, and triggers. This way, it is possible to perform SQL development
with .NET programming languages, taking advantage of functionality present in
the .NET framework. At the same time, this helps consolidate application and
database development tasks, making Transact-SQL, and .NET programming languages
interchangeable.
In our next article, we will start reviewing the changes outlined above in
more detail.
» 
Comment and Contribute
