From: www.itworld.com

SQL Server 2000 EE opens platform options

by Ron Talmage

December 15, 2000 —

 

One of the more significant tasks you'll encounter when preparing to deploy SQL Server 2000 is deciding which edition to purchase.

With the previous version of SQL Server, the decision was clear. You purchased the Enterprise Edition (EE) if you wanted your SQL Server 7.0 installation to take advantage of EE's support for additional memory or clustering. Otherwise, you purchased the Standard Edition -- and that's what most buyers did, unless the amount of data they were managing was so small that the even more streamlined Small Business Server Edition would suffice.

Now that Microsoft has further differentiated SQL Server's Enterprise and Standard Editions, you'll need to reconsider the way you choose a SQL Server edition. Your choice will now depend less on memory support and more on factors such as the types and amount of data you'll maintain in your SQL Server 2000 databases -- as we'll see when we inspect the two editions' surprising differences more closely.

SQL Server 2000 Enterprise Edition

If you install SQL Server 2000 Beta 2, you will probably be surprised to learn that SQL Server 2000 Enterprise Edition no longer requires either Windows NT Enterprise Edition or Windows 2000 Advanced Server. In other words, you can install SQL Server 2000 Enterprise Edition both on Windows NT servers and Windows 2000 standard servers. This represents a significant departure from SQL Server 6.5 and 7.0 Enterprise Editions, both of which required the most advanced Windows operating system.

SQL Server 2000 EE includes the following features that are not available in the other SQL Server editions. Only one -- the failover clustering service, Microsoft Cluster Server -- requires an advanced Windows server version.

SQL Server 2000 EE's inclusion of advanced features that are supported on the basic OS will likely spur sales. Indeed, it will probably discourage most DBAs from using SQL Server 2000 Standard Edition except for the smallest and least critical databases.

Microsoft will also make available SQL Server 2000 Enterprise Edition for the developer. This is really the Enterprise Edition with a restricted license; while it includes all the features found in EE, it can be used on development and test servers, but not in production. Because the Developer Edition will likely be less expensive, its availability will take some of the sting out of having to purchase EE for production servers.

It should be noted, however, that Microsoft has not yet announced pricing for the Developer Edition or any of the other SQL Server 2000 editions.

Failover clustering, the sticky wicket

NT Enterprise Edition provided SQL Server with two key feature: failover clustering and expanded memory usage. Introduced in Windows NT EE, failover clustering is the method of using Microsoft Cluster Server (MSCS, also known as Wolfpack) to automate the failover of SQL Server from a primary to a secondary node (or server) in a cluster. A two-node version of MCSC is included in Windows 2000 Advanced Server, and four-node version will be part of Windows 2000 DataCenter Server.

To make use of failover clustering today, SQL Server 2000 must be installed on a version of Windows that supports MSCS -- that is, NT EE, Windows 2000 Advanced Server, or Windows 2000 DataCenter Server. Only the Enterprise Editions of SQL Server 6.5 and 7.0 will support failover clustering.

Along with providing MSCS, Windows NT EE expanded the amount of memory available to applications from two to three gigabytes. Indeed, the Enterprise Editions of SQL Server 6.5 and 7.0, designed specifically to take advantage of this expanded memory capability, would not install on anything other than Windows NT EE.

SQL Server 2000, however, will use any memory spaces the Window operating system delivers to it; no special edition of SQL Server 2000 is required in order to use more than the NT-standard 2GB.

The bottom line: Failover clustering is the only feature that requires that SQL Server 2000 be installed on an advanced Windows operating system.

If you don't need failover clustering, therefore, there's no reason to relegate your SQL 2000 installation to the more advanced, more expensive, Windows 2000 distributions. Now you can benefit from SQL 2000's new Enterprise Edition features on less-advanced versions of Windows servers.

Other Enterprise Edition features

The new features included in SQL Server 2000 are log shipping, parallel DBCC (database consistency checker), parallel CREATE INDEX, enhanced read-ahead and scan, indexed views, and federated servers.

Log shipping automates the process of copying and loading ("shipping") transaction logs to a standby server. The new version included with SQL Server 2000 is meant to replace an earlier, unsupported version for SQL Server 7.0 that is available in the Back Office Resource Kit 4.5.

Many production SQL Servers are not part of a cluster, but instead depend on a "warm standby" server as part of their disaster recovery plan. This second server will have copies of most, and perhaps all, of the server's original data, which has been delivered via log shipping. If a disaster occurs, applications can then point to this warm standby server rather than to the failed server.

A warm standby can even protect a two-node cluster that uses MSCS from disaster should the entire cluster fail. Now that SQL Server EE has automated log shipping built in, there's no reason for any SQL Server that must have a responsive disaster recovery strategy to lack one.

With Parallel DBCC and parallel CREATE INDEX, SQL Server 2000 can make use of additional available processors. The standard versions of both Windows NT and Windows 2000 support SMP machines that have up to four processors. If performance is important -- and improved DBCC and indexing speed could be part of your performance strategy -- you will want to run SQL Server 2000 EE.

In addition, the SQL Server 2000 query engine will use enhanced read-ahead and scan to improve scalability -- another motive to move to EE.

Indexed views are views with a clustered index. When you create the view's index, the data selected by the view is both materialized and kept persistent and up-to-date by SQL Server 2000. Because the data is persistent, further SELECTs from the view can be dramatically faster. Many data warehouses and reporting serves will likely require this feature, and hence, SQL Server 2000 Enterprise Edition.

The last feature, federated servers, refers to a union of distributed views across a set of SQL Servers. You can partition a table's data across those servers, define a view as the union of the tables, and from that point on refer to the distributed view instead of the partitioned tables. Every SQL Server in a partitioned view will have to be Enterprise Edition.

Given these new features, virtually every database that houses any critical or dynamic data -- or that is moderate-to-large in size -- will require SQL Server 2000 Enterprise Edition. This is an interesting conclusion, because it's a complete reversal from SQL Server's history to date. Whereas SQL Server Standard edition has been the standard-bearer in the past, now it may be relegated to databases that are comparatively less important, static, and small.


ITworld.com