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:
- "secure by default" settings,
- enforceable SQL Server-based login strong password policies,
- 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.