With the release of SQL Server 2000, Microsoft has carved out a new space in the relational database management system (RDBMS) market. SQL Server 2000 contains a vast array of new features. This article will concentrate on the major enhancements to SQL Server 2000 and introduce you to the key features and enterprise potential of this RDBMS.

SQL Server 2000 versions
First, we’ll touch on the various editions of SQL Server 2000. These editions are available:

  • SQL Server 2000 Enterprise Edition
  • SQL Server 2000 Standard Edition
  • SQL Server 2000 Personal Edition
  • SQL Server 2000 Developer Edition
  • SQL Server 2000 Desktop Engine (MSDE)
  • SQL Server 2000 Windows CE Edition

For a detailed explanation of each, see Features Supported by the Editions of SQL Server 2000 and Operating Systems Supported by the Editions of SQL Server 2000.

SQL Server 2000 and Microsoft .NET
SQL Server 2000 integrates into the Windows 2000 operation system and plays a key role in Microsoft’s .NET strategy. The .NET enterprise servers (Exchange 2000, SQL Server 2000, Commerce Server 2000, etc.) are aimed at creating a robust, scalable, secure infrastructure for building and hosting e-commerce applications. This technology includes:

  • Internet Information Services 5.0 (IIS)
  • Active Server Pages (ASP)
  • COM+ Component Services
  • Distributed transactions
  • Microsoft Message Queuing (MSMQ)
  • Role-based security
  • Network load balancing
  • Active Directory Services (ADS)
  • Extensible Markup Language (XML) support
  • Active Data Objects (ADO)

Where does SQL Server 2000 fit into this picture? Along with XML, it serves as the backbone of the entire strategy. SQL Server 2000 provides a fast way to integrate, manage, and Web-enable data-driven Web sites. SQL Server 2000 is also more scalable than past versions, it is easy to manage, and it has a great support network.

SQL Server 2000 enhancements
Let’s examine some of the SQL Server 2000 enhancements a little more closely.

Memory support
If you have purchased the SQL 2000 Enterprise Edition, you can take advantage of Microsoft’s Address Windowing Extensions (AWE). SQL Server supports 8 GB of memory for Windows 2000 Advanced Server and 64 GB of memory on servers running Windows 2000 Datacenter. However, as you might expect, the Enterprise Edition does not support Windows 2000 Server or Windows 2000 Professional.

Multiple instances of SQL Server
SQL Server now allows you to run as many as 16 instances on the same server. This means you can update test servers before updating your production server. In addition, you can test hotfixes or patches on a different instance of your SQL Server without affecting your production environment. Note that if you use multiple instances of SQL Server, you need to carefully monitor your CPU usage and memory. It is important to set a maximum and minimum for each instance.

Distributed partition views
Another new feature shares the database load across a group of servers by partitioning a table across multiple servers running SQL Server 2000. This approach means strong scalability because your connections are not managed by a single SQL Server. When a client sends a query, the partitioned view determines which server holds the data and retrieves it, as shown in Figure A. If both servers contain the data, both servers are queried. You can have multiple servers involved in this process, which results in scalability gains.

Figure A

Failover clustering
Failover clustering in SQL Server 2000 is easier and more manageable. The installation and maintenance is more accessible in the graphical user interface (GUI), and SQL Server 2000 automatically detects cluster services during the installation. In addition, nodes can be added and removed during setup, and failover clustering works well with replication.

XML support and improved database maintenance
SQL Server 2000 now supports XML by allowing you to access XML through SQL Server via HTTP. In addition, SQL Server 2000 has enhanced database operations by expanding its already-extensive DBCC commands and speeding up differential backups by being able to run on multiple CPUs.

Miscellaneous features
SQL Server 2000 also offers several general improvements, including:

  • Referential integrity
  • Full-text searching
  • Data types
  • Index enhancements

Administration enhancements
In addition to the features we’ve considered so far, SQL Server 2000 offers a number of administration enhancements. Let’s take a look.

SQL Server Profiler and Query Analyzer
SQL Server Profiler provides new ways for you to limit a trace. There are also several new trace events. You can access these by creating a trace and looking at the Events tab, shown in Figure B.

Figure B

The Query Analyzer now includes an object browser that allows you to navigate and explore database objects. There is also an entirely new Tools menu (Figure C), which enables you to manage indexes and statistics and perform object searches.

Figure C

Copy Database Wizard
SQL Server 2000 introduces a new wizard. The Copy Database Wizard (Figure D) allows you to copy and move databases with minimal downtime to your server. You can use this feature to move data between servers or instances of servers, as well as to perform migrations and upgrade SQL 7.0 databases to SQL 2000.

Figure D

Summing up
We’ve covered only a few of the most prominent changes in SQL Server 2000. Replication, dynamic tuning/management, and DTS have also been vastly improved. If you’re interested in reading more about SQL Server 2000, you can download the stand-alone SQL Server 2000 Books Online.

Have a comment or a question?

We look forward to getting your input and hearing about your experiences regarding this topic. Post a comment or a question about this article.