What’s New in Microsoft SQL Server 2000

  • Written By:
  • Published:

What’s New in Microsoft SQL Server 2000
M. Reed - June 21, 2000

Product Background

In 1987, Sybase released the SQL Server database management system on the UNIX platform. The product was ported to IBM OS/2 by Microsoft, Sybase, and Ashton-Tate (of DBase fame) in 1988-89. Ashton-Tate dropped out of the consortium in 1990. Between 1991 and 1993, Microsoft and IBM divorced in the OS/2 arena, and SQL Server 4.2 for Windows NT and the Windows NT 3.1 operating system were released.

In 1994, Microsoft and Sybase ended their co-development efforts (for a period in the early 1990's, SQL Server 4.x for OS/2 was actually labeled Microsoft on the manuals and Sybase on the diskettes.) Microsoft carried forward on Windows NT, and Sybase carried forward on UNIX. Microsoft SQL Server version 6.0 was released in 1995, version 6.5 in 1996, and version 7 in 1998. The rest is basically history, the fate of IBM OS/2 and Sybase's database compared to Microsoft's offerings is well known.

Microsoft has developed a number of versions of the successor to SQL 7.0, known as SQL Server 2000, including the Enterprise Edition, which includes distributed partitioned views and failover clustering to improve database uptime; Standard Edition, which should be appropriate for small to mid-sized businesses; Personal Edition, which will run on non-server platforms including Windows 98; Developer Edition, which includes a development and test end-user license precluding production deployment; and a Windows CE Edition, which will not be available until the second half of 2000.

The product has historically been used at the departmental data mart level. Microsoft is now trying to penetrate the Enterprise Data Warehouse market. Their largest competitors are IBM's DB2 Universal Database and Oracle's 8i. The database market is growing at approximately 20% per year, and since Microsoft will continue to thrive with mid to low-end customers, they should profit greatly in these markets.

In addition, Microsoft has announced that they are running 20 critical internal systems, including their worldwide sales data warehouse, on SQL Server 2000. In the vendor world, this is referred to as "eating your own dog food", and their commitment to the product is clear.

The product is based on Microsoft's Windows DNA (Distributed Internet Architecture), and includes many interesting components, some of which are also present in SQL Server 7.0 and many of which have been enhanced:

Web Related

Internet Information Services 5.0 (IIS): Web services integrated with the operating system to allow web hosting.

Active Server Pages (ASP): A web server scripting language.

HTTP Connectivity: A DLL is supplied which allows users to define virtual roots in IIS that are associated with an instance of SQL Server. Security can be set to anonymous access, using SQL Server id/password (in conjunction with the secure sockets layer), or with Windows Access Control Lists (ACLs).

Work Sharing

COM+ Component Services: This feature was formerly known as the Microsoft Transaction Server (MTS). This service allows for remote object calls and many other programming language features similar to Remote Procedure Calls (RPCs).

Distributed Transaction Support: The ability for multiple computers to participate in a coordinated unit of work.

Message Oriented Middleware: MSMQ (Microsoft Message Queuing) is similar to IBM's MQSeries product, and coordinates the communications across networks and systems.

XML Support: The Microsoft XML Parser helps create applications that exchange data formatted in the Extensible Markup Language (XML). Most newly released products from data warehouse vendors support XML in some form.

ActiveX Data Objects: Software modules based on Microsoft's Component Object Model (COM) architecture which make it possible to call ready-made components that blend in and appear as normal parts of the program.


Security: Microsoft has enabled role-based security to simplify the programming effort.

Improved Security: Analysis Services includes security features all the way down to the cell level within a cube.


English Query: A method for issuing natural language queries in English that are turned into the appropriate SQL statements to answer the question posed. "Authoring" of English Query applications is performed in Microsoft Visual Studio version 6, which is included with SQL Server. In its prior incarnation, English Query could use only ODBC, it is now enabled with OLE DB, and can therefore access heterogeneous data sources.

Analysis Services: (Formerly OLAP Services). The capability to create On-Line Analytical Processing multi-dimensional databases (MDBs or "cubes") within SQL Server. Also provides roll-up (aggregation) capabilities. Includes support for Multidimensional, Relational, and Hybrid OLAP.

Integrated Data Mining: The ability to perform such tasks as clickstream analysis for web sites. Analysis services also provides the ability to link cubes (multiple cubes, either inside and/or outside the firewall appear in a single "view", essentially a "virtual cube") and access cubes via HTTP (web-enabled analysis). The features are part of the OLE DB for Data Mining specification (see "Microsoft Says OLE for Data Mining: Is It Bull?", March 28, 2000, for additional details). The data mining algorithms supported were developed by Microsoft and consist of Decision Trees (four different algorithms based on classification theory), and Clustering (based on the nearest neighbor method). Third party algorithms are also supported.

Full Text Search: The ability to run queries on text documents in relational tables and search across all indexed columns in a table. For Full-Text Search, documents are stored in Binary Large Objects (BLOBs) and registered for full-text indexing, including the ability to filter out "noise words" such as "a" and "the". This capability is similar to the full text indexing in Lotus Notes. Microsoft has also added the capability to update the full-text index in near real-time instead of batch mode, if desired.

Distributed Partitioned Views: The ability to share database processing across a group of servers. The data is "horizontally partitioned" across the servers, but the partitioning is transparent to the application. The servers distribute scans and updates as needed. The technology used is known as "shared-nothing clustering".

Sub Indexing: On Symmetric Multiprocessing (SMP) hardware, the product has the ability to multi-thread index creation. Separate processors and threads create "sub-indexes" of large databases in parallel (including parallel database scans to gather the index information), and then a "coordinating thread" combines them into a complete index. This can be extremely valuable in a very large database that is frequently updated, since it can save a great deal of time.

Materialized Views: The product allows the creation of a unique clustered index on a database view. This can result in great performance gains, and the query optimizer will even be able to use the view if it is not referenced in the query's FROM clause. This feature is important in that existing queries do not have to be re-coded to force the optimizer to use the view index.

Directory Services

Directory Services: The new Active Directory Services process centralizes administrative information about resources.

Active Directory: Databases can be centrally managed using the Active Directory service.


Network Load Balancing: On the datacenter and advanced server products, Microsoft has enabled load balancing of TCP/IP traffic across multi-node clusters.

Multi-Instance Support: The ability to have multiple installations of SQL Server on the same machine. This was previously impossible due to the way NT registry entries were made. This inability in prior versions was a serious problem, since many customers wanted to be able to run a test and a production instance on the same (typically very expensive) machine.

Database Maintenance

Database Programmability Enhancements:

  • Cascading Referential Integrity: In previous releases of major databases, if an attempt was made to delete or update a key that had foreign keys, the operation had to be handled programmatically. This ability, most often used to perform a "cascade delete" (delete a parent and all its children), is now built into the product.

  • New data types: SQL Server now supports BIGINT (an 8-byte integer), and a variant datatype that can store data values of different data types.

Product Strategy and Trajectory

Microsoft is attempting once again to make inroads into the enterprise space with the Enterprise Edition of the product, specifically to compete with IBM's DB2 Universal Database and Oracle's 8i. It is questionable whether they will be successful, since they only support Microsoft operating systems, but the product's ease of use and breadth of offering is compelling.

Microsoft is attempting to create a product that is "all things to all people", by including data movement technology, data mining, transaction services, OLAP support, and ease of use - all "out-of-the-box". Microsoft will also leverage its DNA initiative to lock customers into its solutions, if it can get them to run Windows 2000 on some or all of their servers. TEC predicts that Microsoft will have not succeeded at the Enterprise level (60% probability) and will have to provide further enhancements in the next major release (already in development, code-named "Yukon"). The timing of the Yukon release is unknown.

Product Strengths

  • The product has a lower total cost of ownership (TCO) than many of its competitors, and runs on less expensive x86 hardware (compared to the price of a mainframe or high-end UNIX machine).

  • SQL Server contains many features not currently contained in its competitors' out-of-the-box database product.

  • Customers will be intrigued at the concept of an almost-complete data warehouse or data mart solution from a single vendor, in a single box.

  • Very broad feature set.

  • Improved Data Transformation Services (DTS) that allows the product to access data sources other than Microsoft databases.

Product Challenges

  • The product is not supported on any non-Microsoft operating system. Customers which run UNIX and mainframe operating systems will be less inclined to move to SQL Server, especially at the enterprise-wide high end of the database spectrum. In this space Microsoft will be challenged by IBM, Oracle, and to a lesser degree, Informix and Sybase.

  • It will be difficult for Microsoft to dispel the belief in the IT industry that they are not ready for Enterprise level applications. IBM and Oracle will continue to control this space (80% probability).

  • It will be difficult to dislodge competing products from non-Microsoft SQL Server shops. Companies with large hardware installations of other (non-NT or 2000) operating systems, such as IBM OS/390 and Sun E10000 enterprise UNIX systems, will not be willing to let that hardware cost go to waste (90% probability).

  • The product is not as efficient at handling multimedia data as Informix, Oracle, or IBM.

  • Tuning distributed partitioned views (which potentially access data across many servers to answer a single query), will be very tricky, and if not properly handled, will cause the network to suffer due to traffic between the SQL Servers that contain the appropriate data.

  • Microsoft will suffer somewhat in the market, at least in the near term, due to the dust raised over the Department of Justice anti-trust suit.


Vendor Recommendations

  • Continue to improve the product and scale it up to the Enterprise level even further. A 64-bit release brought to market on time would help them in this regard.

  • Improve multimedia support.

  • Bring Host Integration Server 2000 (for SNA access to mainframe data) to market as quickly as possible to improve your marketing message with legacy database shops (the product is currently in Beta 1).

User Recommendations

Customers in the Small-to-Medium Enterprise space ($500 million to $2 Billion) should include Microsoft SQL Server 2000 on a short list of candidates for data marts and small to mid-sized data warehouses. The list of product enhancements is impressive, and Microsoft is obviously committed to the success of this product.

When licensing is discussed, customers should be careful to have Microsoft delineate how licensing is going to be structured, and what happens as they scale up to larger numbers of servers and/or users.

As always, production rollout should be delayed until the first Service Pack for the General Availability product has been released, and regression tested by the customer's IT and QA staffs.

Glossary of Terms

Distributed Internet Architecture: Microsoft's term (originally invented for Windows NT) to describe the components that make up its enterprise network architecture built into Windows 2000. It includes software that collectively provides a Web-enabled infrastructure for an organization.

ActiveX: A software module based on Microsoft's Component Object Model (COM) architecture. It enables a program to add functionality by calling ready-made components that blend in and appear as normal parts of the program. It adds componentization and modularity, but is based on proprietary Microsoft standards.

comments powered by Disqus