twitter
facebook 
| SQL Server 2005 Improves Management, Enterprise Features |
| Nov. 14, 2005 |
A new management suite and other manageability enhancements in SQL Server 2005 offer efficiency gains for database administrators. In addition, new high-availability features in the product's high-end edition and improved replication could close the gap between SQL Server and rivals Oracle and IBM in the enterprise database market. Although SQL Server leads the database market in unit sales, it trails in enterprise revenue share and many of the product's high-availability features already exist in market leader Oracle's products. Furthermore, one of SQL Server 2005's most anticipated high-availability features, database mirroring, is not supported in the product's initial Nov. 2005 release. New Tool Set Leads Management Changes SQL Server 2005 offers several manageability enhancements that will simplify and streamline many day-to-day database maintenance tasks and activities. These enhancements will benefit SQL Server database administrators and could further boost one of SQL Server's main advantages over competitors such as Oracle and IBM in the database market for small and midsize companies: ease of use. Microsoft's continued focus on SQL Server's management features could thwart Oracle's attempts to move downmarket. Management Studio Consolidates Tools SQL Server 2005 introduces a consolidated management suite called the SQL Server Management Studio. (For a screen shot of the new management tool, see the illustration "SQL Server Management Studio".) The Management Studio subsumes the functions of many stand-alone tools from previous versions of the product, including the following:
Key features of the Management Studio include the following: Improved usability, administrator efficiency. Management Studio offers a more organized and less cluttered user interface, relieving administrators of the inconvenience of navigating multiple administrative tools and interfaces. For example, Management Studio provides a tabbed interface for organizing and navigating through multiple windows, each of which might contain a different database query or query result. (Enterprise Manager and Query Analyzer allowed multiple windows but provided no way to organize them.) Management Studio also improves the manageability of complex multidatabase, multiserver environments. For example, from a single query window, an administrator or developer can easily change the database connection to run the query on a different database or database server. Previous versions of SQL Server required multiple query windows for such operations, one window for each database connection. Full query language support. Management Studio includes a query editor that supports the multiple languages used by SQL Server administrators and developers. For example, the editor can create scripts or queries in T-SQL, the Multi-Dimensional Expressions (MDX) language (used to query Analysis Services data sources or cubes), and the Data Mining Extensions (DMX) language, used for working with Analysis Services data mining models. SQL Server 2000's Query Analyzer provided support only for T-SQL. In addition, Management Studio supports offline development—developers or administrators can write scripts or queries without a live database connection. Better organization. Management Studio introduces a project structure for organizing and managing the scripts (and other related details, such as connection information) associated with database administration or maintenance tasks. Instead of being based on Microsoft Management Console (MMC) technology, Management Studio is hosted within the Visual Studio shell and inherits that product's project management features. For example, administrators can group all scripts and connection information related to a database maintenance task, such as a data backup process, and manage them under source-code control, which is built into Visual Studio. These features could prove particularly interesting to administrators in larger corporations, whose management responsibilities often span a range of maintenance tasks across multiple databases and database servers. Express Edition, the SQL Server 2005 replacement for the Microsoft Desktop Engine (MSDE) is due for a stripped-down version of the Management Studio, which should make the edition more useful for beginning developers; novice database administrators; testing purposes; and small, simple, production databases. (If they are working in environments that include other SQL Server 2005 editions, administrators can also use the full Management Studio to manage Express Edition databases.) The new management utility will supplement the command-line management tools in MSDE. However, the management tools for Express were cut from the product's initial launch and will ship separately in the first half of 2006. Safer Backups, Better Control Beyond the new Management Studio, SQL Server 2005 includes numerous enhancements aimed at improving the reliability of maintenance operations and simplifying database configuration and administration tasks. Highlights include the following: Mirrored backup. Previous versions of SQL Server included a built-in utility for backing up and restoring databases and associated files and information. SQL Server 2005 Enterprise Edition addresses a limitation in this tool by allowing administrators to back up SQL Server data to more than one set of media (such as a tape drive), which guards against the loss of a backup due to failure of the media. For mission-critical data, the feature will offer a more viable alternative to third-party backup software than the built-in backup utility of earlier SQL Server versions. A command-line diagnostic connection, referred to as a dedicated administrator connection (DAC), allows administrators to execute diagnostic queries and troubleshoot problems when SQL Server is not responding to standard connection requests. SQL Server sets aside limited resources and a dedicated port for the DAC, which allows administrators to access SQL Server when it is otherwise unresponsive. For example, an administrator could use the DAC to check for database locking problems, examine and clear memory caches, or kill SQL processes. Recovery from user-blocking problems in previous SQL Server versions often required restarting SQL Server, which extended downtime. Dynamic memory configuration. SQL Server 2005 supports Address Windowing Extensions (AWE), which allows the database to access up to 64GB of memory on 32-bit editions of Windows. Running on Windows 2003, SQL Server 2005 can dynamically allocate (or free) AWE memory as needed. With SQL Server 2000, which also supports AWE, memory is allocated at start-up and cannot be adjusted without stopping and restarting SQL Server. High-Availability Features Target Enterprises The introduction of SQL Server 2000 Enterprise Edition marked Microsoft's first serious foray into the enterprise database market. That edition significantly improved support for the Microsoft Cluster Services (MSCS) failover technology and introduced a feature called log shipping, both of which reduce downtime in the event of a database or database server failure. SQL Server 2005 Enterprise Edition expands these capabilities and adds several new features that could reduce maintenance times and speed recovery from a database failure. The continued emphasis on high-availability features demonstrates Microsoft's determination to make gains in the enterprise database market, where the company continues to trail IBM and Oracle in terms of total revenue. As the feature gap between SQL Server and its competitors' database management systems closes, Microsoft hopes to further SQL Server's reputation as a credible solution for the largest, mission-critical database applications. Redundancy Mitigates Failures Failover strategies, such as clustering, improve database availability by quickly bringing a redundant copy of a database or a second instance of SQL Server online in the event of a system or device failure. SQL Server 2005 enhances redundancy and failover capabilities of previous versions. SQL Server 2005 continues support for Microsoft Cluster Service (MSCS), a quick restart solution that relies on clusters of machines; when one machine in the cluster fails, its applications restart on another. Machines in the cluster share storage (such as on a storage area network volume) and use specially designed and certified network and server hardware. SQL Server 2005 Enterprise Edition supports eight-node failover clusters on 32-bit systems and four-node clusters on 64-bit systems; SQL Server 2000 Enterprise Edition supported only four-node clusters. In addition, SQL Server 2005 will eventually get a database mirroring feature that enhances SQL Server log shipping. Log shipping maintains a copy of a production database on a standby machine by periodically copying the production database's transaction logs (which record database changes, such as data inserts or deletes) to the standby machine and applying the changes recorded in those logs to the standby database. However, log shipping requires operator intervention to bring a standby database online in the event of a failure of the production database. Database mirroring, on the other hand, supports automatic failover between a production database (referred to as the primary database) and the copy of that database on the standby server (the mirror database). Database mirroring offers customers a simpler, less costly alternative to failover support using MSCS—for example, database mirroring does not require special hardware, shared storage, or additional services. Originally planned for the initial release of SQL Server 2005, database mirroring will not be an officially supported feature until the first half of 2006; in the interim, however, customers will be able to access the feature for testing and evaluation. (For a discussion of database mirroring and failover clustering similarities and differences, see the sidebar "Database Mirroring Versus Failover Clustering".) Shortening Maintenance Windows, Crash Recovery A second approach to improving availability involves minimizing planned downtime; that is, the downtime associated with routine maintenance operations, which often require servers or databases to be unavailable to users or applications for the duration of the operation. SQL Server 2005 Enterprise Edition increases database availability by allowing applications and users to access databases during certain common maintenance activities. SQL Server 2005 Enterprise Edition supports the following online operations:
Compared to its predecessors, SQL Server 2005 Enterprise Edition also speeds recovery of databases after server or disk failures. Recovering a database after a failure involves several steps: administrators reconstruct the database's structure and populate it with data (if necessary) from the most recent backup, reapply all transactions recorded in the database's transaction log, and finally roll-back (or undo) transactions that did not complete (or were uncommitted) at the time of the database failure. With SQL Server 2000, databases are not available to users and applications until all recovery steps are completed. SQL Server 2005, on the other hand, allows a database to be brought online while the final roll-back step is under way (with the caveat that rows in the database with uncommitted transactions will be inaccessible during the roll-back step). Replication Gets Incremental Updates SQL Server's replication feature includes tools and services that developers and administrators use to automatically copy and synchronize data among two or more database servers. Replication is a central component of many database scaling architectures—for example, it can help administrators reduce per-server query load by distributing queries across multiple, identical database servers. Replication is also commonly used to roll up information across servers (e.g., collect financial data from branch offices for central reporting) and to support mobile users who need to access databases while offline (e.g., sales representatives who input orders on a laptop). SQL Server 2005 will allow administrators to more efficiently manage existing replication systems and help developers support new replication scenarios with less custom code. Among numerous enhancements, SQL Server 2005 replication gets the following updates: Replication of database schema changes. SQL Server 2000 replication provided limited support for replicating database schema changes. For example, administrators could add or delete columns in a replicated table using special, built-in stored procedures but could not change a column's data type, add or drop constraints, or make other schema changes. In SQL Server 2005, a wider range of schema changes can be replicated without using or having to create special stored procedures. Administrators use SQL commands to change schema on the master copy (publisher) of a replicated database; those changes are then automatically propagated to replicated copies (subscribers). Oracle to SQL Server. SQL Server 2005 supports replication of Oracle databases to SQL Server. Although SQL Server 2000 supported replication from SQL Server to DB2 or Oracle, publishing data from other databases to SQL Server required developers to write custom code. The new feature will ease implementation of what could be an attractive scaling architecture for large companies, whose online transaction systems and data warehouses are often built on databases other than SQL Server. Specifically, it could help those companies offload queries from production databases or central data warehouses running on Oracle to servers running Microsoft's less expensive database engine. Web-based replication. SQL Server 2005 supports Web-based replication using Hypertext Transfer Protocol over Secure Socket Layer (HTTPS), which could help businesses better support remote users by allowing them to synchronize data without having to dial in to a corporate network or establish a virtual private network (VPN) connection. Better manageability. SQL Server 2005 simplifies and streamlines the wizards and tools administrators use to set up and manage replication projects. For example, the wizard used to create new SQL Server 2005 replicated databases (publications) contains fewer pages and dialog boxes than its SQL Server 2000 predecessor. Furthermore, when using SQL Server 2005's subscription wizard (used to define the publishers, schedules, subscribers and other configuration for a replication system) administrators can create multiple subscriptions without having to rerun the wizard; SQL Server 2000 administrators needed to run the wizard each time they created a new subscription. Caveats and Considerations SQL Server 2005 simplifies and streamlines many routine maintenance activities and could improve the availability of databases and applications accessing those databases. Nonetheless, certain caveats and considerations apply. No debugging in Management Studio. Although many developers and administrators will use Management Studio's query editor for the script, query, and stored procedure development previously done in SQL Server 2000's Query Analyzer, these users will find one major omission: unlike Query Analyzer, Management Studio does not include a T-SQL debugger. To develop and debug complex scripts, queries, or stored procedures, SQL Server developers and administrators will need to use Visual Studio, which supports debugging T-SQL code. No backup compression. Support for mirrored backup addresses one major limitation in SQL Server's backup utility. However, another limitation is not addressed—the utility does not provide built-in data compression. As a result, administrators will likely find SQL Server's backup utility to be slower, less efficient, and potentially less cost-effective than commercial backup products such as Quest Software's SQL Litespeed and Idera's SQL Safe. Database mirroring and Express Edition management not shipping. As noted above, neither database mirroring nor Express Edition's new management interface made SQL Server 2005's Nov. 2005 release date. Microsoft has not provided an availability date or identified a ship vehicle for these features, but has said that both will be available to SQL Server 2005 customers in the first half of 2006. Catching Up Microsoft has work to do to catch Oracle and IBM in the enterprise database market, since many of the high-availability features in SQL Server 2005 currently exist in its competitors' products. For example, Oracle supports features similar to SQL Server's failover clustering, database mirroring, and online maintenance operations, and Oracle's features are more mature in most cases. Oracle's database redundancy technology (called Data Guard) purports to be more reliable and consume a fraction of the network traffic of a mirroring solution, for instance. Although SQL Server 2005 Enterprise Edition addresses many of the gaps, Microsoft still has progress to make before it can claim functional superiority to its competitors. Finally, customers should note that most high-availability features are only available in the product's Enterprise Edition. SQL Server 2005 Standard Edition gets a subset of Enterprise Edition's database mirroring capabilities and supports only two-node failover clustering. Moreover, SQL Server Standard, Workgroup, and Express Editions do not support online maintenance operations. Resources and Availability SQL Server 2005 was released to manufacturing in Oct. 2005 and was officially launched on Nov. 7, 2005. The product will be available through most Microsoft sales channels by early Dec. 2005. The SQL Server 2005 site is www.microsoft.com/sql. For more information on SQL Server 2005's high-availability features and capabilities, see www.microsoft.com/sql/technologies/highavailability. For more on the Windows Server 2003 hot-add memory feature, see www.microsoft.com/whdc/system/pnppwr/hotadd/hotaddmem.mspx. SQL Server 2005's new database partitioning feature is outlined in "SQL Server 2005 Partitions Large Databases" on page 14 of the Oct. 2005 Update. Analysis Services in SQL Server 2005 is described in "Analysis Services Matures in SQL Server 2005" on page 3 of the Feb. 2005 Update. Integration Services in SQL Server 2005 is described in "Data Integration Improved in SQL Server 2005" on page 13 of the Dec. 2004 Update. Updates to Reporting Services in SQL Server 2005 are outlined in "Reporting Services Updated for SQL Server 2005" on page 15 of the July 2005 Update. New features for developers in SQL Server 2005 are outlined in "SQL Server Gets .NET, XML" on page 5 of the Jan. 2004 Update. |
