SQL Server 2005 Part 1 Introduction

Friday Nov 12th 2004 by Marcin Policht

SQL Server 2000 will be soon reaching its five-year mark, which in terms of software life-cycle translates into fairly advanced maturity. While this is still far from retirement age, the name of its successor, SQL Server 2005, suggests that it might be time for you to start looking into what the new generation has to offer.

SQL Server 2000 will be soon reaching its five-year mark, which in terms of software life-cycle translates into fairly advanced maturity. While this is still far from retirement age, the name of its successor, SQL Server 2005, suggests that it might be time for you to start looking into what the new generation has to offer. The release of SQL Server 2005, originally introduced as Yukon, has already been postponed, but its current Beta 2 implementation (with several incremental Community Technical Previews expected before Beta 3 becomes available early next year) brings promise of a timely RTM stage (planned for summer next year). In this series of articles, we will look into functional highlights of the new incarnation of the Microsoft database management system, focusing on those that are likely to remain unchanged in the final product.

Improvements to the database engine, the details of which are not published by Microsoft, and the corresponding changes to the main infrastructure components are reflected by a substantial number of new features as well as enhancements to existing ones. The most relevant ones can be grouped into several categories, such as high availability and scalability, security, data management, administration and maintenance, and development.

The demand for high availability is becoming increasingly common and is no longer limited to major corporate and governmental clients. This results not only from a growing level of customer expectations, but also from the new political climate associated with more stringent legislative and regulatory requirements, in which disaster recovery and business continuity are more relevant then ever. However, businesses are also, at the same time, extremely interested in keeping their costs to a minimum. Microsoft tries to address these expectations by implementing scalability enhancements, which ensure that SQL Server can perform equally well in environments of any size, and by the introduction of several versions of SQL Server 2005 (geared towards more specialized needs) such as:

  • SQL Server Standard Edition - offering the most diverse set of features and intended for the majority of clients.
  • SQL Server 2005 Express Edition - serving as the replacement for Microsoft Data Engine (MSDE) and available for download from the Microsoft Dowload Center. Like its predecessor, it was designed with developers in mind, however, unlike the previous version, it also includes a Web based management interface.
  • SQL Server 2005 Mobile Edition - as a successor to SQL Server 2000 Windows CE Edition, it is intended for Windows mobile-based devices, such as Tablet PCs, Pocket PCs, and Smartphones

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.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved