What’s New in Microsoft SQL Server 2000
Written By: M. Reed
Published On: June 21 2000
What’s New in Microsoft SQL Server 2000
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.
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.
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.
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.
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.
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:
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).
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
Transaction Support: The ability for multiple computers to participate
in a coordinated unit of work.
Oriented Middleware: MSMQ (Microsoft Message Queuing) is similar to
IBM's MQSeries product, and coordinates the communications across networks
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.
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.
Microsoft has enabled role-based security to simplify the programming
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.
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.
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.
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.
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
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.
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
Directory Services: The new Active Directory Services process centralizes
administrative information about resources.
Directory: Databases can be centrally managed using the Active Directory
Load Balancing: On the datacenter and advanced server products, Microsoft
has enabled load balancing of TCP/IP traffic across multi-node clusters.
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.
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.
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.
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.
- 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
will be intrigued at the concept of an almost-complete data warehouse
or data mart solution from a single vendor, in a single box.
broad feature set.
Data Transformation Services (DTS) that allows the product to access
data sources other than Microsoft databases.
- 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
- The product
is not as efficient at handling multimedia data as Informix, Oracle,
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.
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.
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.
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).
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.
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.
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.
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.
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.