|
|
|
SQL Server 2000 Components
and Features
SQL Server 2000 is more than just the database engine.
While the database engine is at the core of the product, and obviously the
most important component, there are a number of additional applications
bundled with the database engine, such as the tools and utilities used to
manage the SQL Server environment, as well as other components and server
applications that extend the capabilities and features of SQL Server 2000.
This section provides an overview of the components and features that are
included with the SQL Server 2000 product. Each of these components is
subsequently explored in greater detail in the rest of this book.
SQL Server Database Engine
SQL Server's database engine is
the primary server application in the SQL Server package. Following are
the main responsibilities of the database engine:
 |
Provide reliable storage for data sent to the
engine
|
Provide a means to rapidly access this data
|
Provide consistent access to the data
|
Control access to the data through security
|
Enforce data-integrity rules to ensure the data is
accurate and consistent
|
| | | |
Each of these points will be examined in detail later
in the book. I will touch on each of these points to show how Microsoft
SQL Server fulfills these core responsibilities.
Reliable Storage
Reliable storage starts at the hardware
level. This isn't the responsibility of the database engine, but it's a
necessary part of a well-built database. Although you can put an entire
SQL database on an old IDE drive (or even burn a read-only copy on a CD),
it is preferrable to maintain the data on RAID arrays. The most common
RAID arrays allow hardware failures at the disk level without losing data.
To learn more about RAID arrays and database hardware
planning, check out Chapter
39, “Database Design and Performance.”
Using whatever hardware you have decided to make
available, the database engine handles all the data structures necessary
to ensure reliable storage of your data. Rows of data are stored in pages,
each 8KB in size. Eight pages
make up an extent, and the database
engine tracks which extents are allocated to which tables and indexes.
For an in-depth discussion of data structures and how
they factor into performance and reliability, see Chapter
33, “SQL Server Internals.”
|
Page: An 8KB
chunk of a data file, the smallest unit of storage available in
the database.
Extent: A
collection of eight 8KB pages.
|
Another key feature the engine provides to ensure
reliable storage is the transaction log. The transaction log makes a
record of every change that is made to the database for rollback and
recovery purposes. See Chapter
31, “Transaction Management and the Transaction Log.”
|
It is not strictly true that the transaction
log records all changes to the
database—some exceptions exist. Binary Large Objects—data of
type image and text—can be excepted from
logging, and inserts generated by bulk table loads can be
non-logged to get the fastest
possible performance.
|
Rapidly Accessing Data
SQL Server provides rapid access to data by utilizing
indexes and storing frequently accessed data in memory.
SQL Server allows the creation of
clustered and nonclustered indexes, which speed access to data by using
the index pointers to find data rows rather than having to scan all the
data in the table each time. See Chapter
34, “Indexes and Performance,” for an in-depth discussion of
indexes and how they are used to improve query performance.
Memory is allocated by SQL Server database to be used
as a data cache to speed access to data by reducing the number of required
physical I/Os to the disks. When pages are requested from the database,
the server checks to see if they are already in the cache. If not, it
reads them off the disk and inserts them into the data cache. With
sufficient memory, the next time the data needs to be accessed, it should
still be in cache, avoiding the need to access the disk drive(s). A
separate process runs continuously and attempts to keep frequently
accessed information in memory by pushing old pages that haven't been
accessed recently out of the cache to make room for newly accessed pages.
If the pages contain modifications, they are written to disk first before
being removed from cache, otherwise the old pages are simply discarded.
|
With sufficient memory, the entire database can
fit completely into memory.
|
More information on the data cache and how it is
managed is available in Chapter
33.
Providing Consistent Access to Data
Getting to your data quickly doesn't mean
much if the information you receive is inaccurate. SQL Server follows a
set of rules to ensure that the data you receive back from queries is
consistent.
The general idea with consistent access is to allow
only one client at a time to change the data, and to prevent others from
reading data from the database while it is undergoing changes.
Transactional consistency has several levels of
conformance, each of which provides a trade-off between accuracy of the
data and concurrency. These levels of concurrency are examined in more
detail in Chapter
38, “Locking and Performance.” In particular, check out the
sections on “SQL
Server Lock Types” and “Transaction
Isolation Levels in SQL Server.”
Controlling Access
The database server provides security
at multiple levels. Security is enforced at the server level, the database
level, and at the database object level. Access to the server is verified
by either a username and password, or through integrated network security.
Integrated security uses the client's network login credentials to
establish identity.
SQL Server security is examined in more detail in Chapter
15, “Security and User Administration.”
Enforcing Data Integrity Rules
Some databases have to serve the
needs of more than a single application. A corporate database that
contains valuable information might have a dozen different departments
wanting to access portions of the database for different needs.
In this kind of environment, it is impractical to
expect the developers of each application to agree on an identical set of
standards for maintaining data integrity. For example, one department
might allow phone numbers to have extensions, whereas another department
does not need that capability. One department might find it critical to
maintain a relationship between a customer record and a salesman record,
whereas another might care only about the customer information.
The best way to keep everybody sane in this
environment—and to ensure that the data stays consistent and usable by
everyone—is to enforce a set of rules at the database level. This is
accomplished through the database objects, including rules, defaults,
triggers, stored procedures, and data-integrity constraints. See Chapter
14, “Implementing Data Integrity,” for details.
SQL Server Enterprise Manager
The Enterprise Manager is the
central console from which most SQL Server database-management tasks can
be coordinated. SQL Enterprise Manager (hereafter referred to as SQL-EM)
provides a single interface from which all servers in a company can be
managed.
The SQL-EM is examined in more detail in Chapter
4, “SQL Server Enterprise Manager.”
The following lists some of the tasks that can be
performed with SQL-EM. Most of these are discussed in detail later in the
book:
|
Completely manage many servers in a convenient
interface
|
Set server options and configuration values, such
as the amount of memory and processors to use, the default language,
and the default location of the data and log files
|
Manage logins, database users, and database roles
|
Schedule automated jobs through the SQL Agent
|
Back up and restore databases and develop
maintenance plans
|
Create new databases
|
Browse table contents
|
Manage database objects, such as tables, indexes,
and stored procedures
|
Configure and manage replication
|
Import and export data
|
Transfer data between servers, SQL Server, and
otherwise
|
Monitor SQL Server activity and error logs
|
Provide a convenient centralized front end to other
applications, such as the Data Transformation Services designer, Query
Analyzer, Full Text Search Services, and SQL Mail
|
| | | | | | | | | | | |
|
SQL Enterprise Manager interacts with SQL
Server using standard Transact-SQL commands. For example, when you
create a new database through the SQL-EM interface, behind the
scenes it generates a CREATE DATABASE SQL command.
Whatever you can do through SQL-EM, you can do with the Query
Analyzer or even the command line ISQL or
OSQL programs.
If you're curious how EM is accomplishing
something, you can run SQL Profiler to trap the commands that SQL-EM
is sending to the server. I've used this technique to discover
some interesting internals information. You can also use this
tactic to capture SQL scripts, and then repeat tasks using the
script instead of a few dozen interface
clicks.
|
SQL Service Manager
The Service Manager is a small applet that allows
easy control of several key SQL services:
|
SQL Server, the database engine
|
SQL Agent, a job scheduler
|
SQL Search, a full-text search engine
|
Distributed Transaction Coordinator
|
OLAP Server, a separate service used for
warehousing
|
| | | |
The Service Manager can be used to control or monitor
these services on any machine on the network. It will poll each service
every few seconds (configurable through Options) to determine its state. A
check box also exists for each service, which allows automatic starting of
the service when Windows starts. Most servers automatically start the SQL
service on system startup, but in some maintenance situations, it's
important to be able to disable this property.
All services can be stopped and started. Some can also
be paused. When SQL Server is paused, it continues operating normally
except that new login connections are not
accepted.
SQL Server Agent
The SQL Server Agent is an
integrated scheduling tool that allows convenient execution of scheduled
scripts and maintenance jobs. It is required to use replication services.
The agent also handles automated alerts (for example, if the database runs
out of space).
The Agent is a Windows service that runs on the same
machine as the SQL Server engine. The agent service can be controlled
through either SQL-EM, the SQL Service Manager, or the ordinary Windows
service manager. The Agent is configured through SQL-EM by drilling down
through the SQL Server instance | Management | SQL Server Agent.
SQL jobs can be complex. Branching is possible
depending on the outcome of a query or job return status.
The Agent also handles alerting. The alert system can
watch for a particular event, and then respond to this event by paging an
operator, sending an e-mail, running a predefined job, or any combination
of these. In the previous example of the database running out of space, an
alert could be defined to watch the free space, and when it got to less
than 5 percent, the database could be expanded by 100MB and the on-call
DBA could be paged.
In enterprise situations in which many SQL Server
machines need to be managed together, the SQL Agent can be configured to
distribute common jobs to multiple servers through the use of Multiserver
Administration. This is most helpful in a wide architecture scenario, in
which many SQL Server databases are performing the same tasks with the
databases. Jobs are managed from a single SQL Server machine. This machine
is responsible for maintaining the jobs and distributing the job scripts
to each target server. The results of each job are maintained on the
target servers, but can be observed through a single interface.
If you have 20 servers that all need to run the same
job, you can check the completion status of that job on a single server in
moments, instead of logging into each machine and checking the individual
status 20 times.
The SQL Agent Event also handles forwarding. Any system
events that are recorded in the Windows System Event Log can be forwarded
to a single machine. This gives the busy admininstrator a single place
to look for errors.
More information about how to accomplish these tasks,
and other information on the Agent, is available in Chapter
18, “SQL Server Scheduling and Notification.”
SQL Query Analyzer
The Query Analyzer is the easiest
place to run SQL scripts. Each window in the Query Analyzer represents a
connection to a database. It's possible to have connections to many
different servers.
Some wonderful changes have been made to the 2000
version of Query Analyzer, most notably the addition of an object
browser/template manager and an integrated stored procedure debugger.
The Query Analyzer tool is discussed in Chapter
6, “SQL Server Query Analyzer and SQL Debugger,” along with a
discussion on using its built-in SQL Debugger. See Chapter
36, “Query Analysis,” for details on using the Query Analyzer to
troubleshoot long-running or complex queries.
SQL Profiler
The SQL Profiler is a client tool
that captures the queries and results flowing to and from the server. It
is analogous to a network sniffer, although it does not operate on quite
that low a level. The Profiler has the ability to capture and save a
complete record of all the traffic passed to the server. A series of
filters is useful for paring down the results when you want to drill down
to a single connection or even a single query.
The SQL Profiler can be used to perform these helpful
tasks:
|
Capture the exact SQL statements sent to
the server from an application for which source code is not available
(third-party applications, for example).
|
Capture all of the queries sent
to SQL Server for later playback on a test server. This is extremely
useful for performance testing with live query traffic.
|
If your server is encountering recurring
Access Violations (AVs), the profiler can be used to reconstruct what
happened leading up to the AV.
|
The profiler shows basic performance data about
each query. When your users start hammering your server with queries
that cause hundreds of table scans, the profiler can easily identify
the culprits.
|
For complex stored procedures, the tool can
identify which portion of the procedure is causing the performance
problem.
|
Audit server activity in real time.
|
| | | | |
The profiler is a versatile tool; it provides
functionality that is not duplicated elsewhere in the SQL Server Tools
suite. More information on the Profiler is available in Chapter
7, “Using the SQL Server Profiler.”
Data Transformation Services
Data Transformation Services (DTS) is
a powerful tool used primarily to move data from one source to another. In
the early days of client/server databases, the only way to move data from
the mainframe to your shiny, new SQL Server box was to get a text file
from the mainframe.
Then the fun really started. If the file was relatively
uniform and straightforward, you might have been able to use a
command-line BCP program to import, or bulk-copy, your data in. If not,
you might have had to resort to a simple VB program to parse out each line
and insert it, ever so slowly, into SQL Server.
DTS offers a simple interface, the DTS Designer, which
is accessed through the SQL Enterprise Manager. DTS provides a simple
means to import text files quickly, using a fast bulk insert process, and
little development time. Following are some of the tasks you can do with
DTS:
|
Quickly import a text file into SQL Server.
|
Using OLEDB or ODBC, connect to a different
database (Oracle, DB2, and so forth) to use as either the source or
target of the data transformations.
|
Write custom scripts to cleanse/transform data to
your specifications. The scripts can be written in VBScript or JScript.
|
New to the 2000 version of DTS, connect to an ftp
site and download files.
|
Send a mail message to someone with the results of
an error message or a query.
|
Hook into an MS Message Queue to send or receive
and process MSMQ messages.
|
Read information from an ini file to drive any DTS
tasks, such as the server name to which to connect, the database in
which to execute specific tasks, the table name to use, or the stored
procedures to call.
|
Perform any of these tasks on a scheduled,
recurring basis with full error control and workflow capabilities.
|
| | | | | | |
DTS is a wonderful tool for moving data between two
places. Figure 1.4 shows a DTS
Designer window inside SQL Enterprise Manager. It shows the multistep
process to run a scheduled, daily import. The ini file (first step on the
left) determines the name of the file to be imported and the database and
table names for the destination. The truncate step empties out the table
to prepare it to receive new data. If this step succeeds, the first path
(colored green in the interface, though not in this screenshot) continues
by running the data transformation from the text file (shown by the icon
in the upper-right corner) to SQL Server (icon in the lower-left corner).
If the step fails, the Log Failure step runs, which records the error and
aborts the job. Finally, the GetFileDate script runs. This is a custom
ActiveX script written with VBScript. It determines the date and time the
file was imported, along with the date and time the file was “dropped
off,” and logs these details in the final step.
Replication
Replication is a server-based tool that
allows data to be synchronized between two or more databases. Replication
can send data from one SQL Server to another, or it can include Oracle,
Access, or any other database that is accessible via ODBC or OLEDB.
SQL Server supports three kinds of replication:
|
Snapshot replication
|
Transactional replication
|
Merge replication
|
| |
The replication functionality available may be
restricted depending on the version of SQL Server 2000 that you are
running.
Snapshot Replication
To perform snapshot replication, the server
takes a picture, or snapshot, of the data in a table at a single point in
time. Usually, if this operation is scheduled, the target data is simply
replaced at each update. This form of replication is appropriate for small
data sets, infrequent update periods (or for a one-time replication
operation), or for management simplicity.
Transactional Replication
Initially set up with a snapshot,
the server maintains downstream replication targets by reading the
transaction log at the source and applying each change at the targets. For
every insert, update, and delete, the server sends a copy of the operation
to every downstream database. This is appropriate if low latency replicas
are needed. Keep in mind that transactional replication does not guarantee
identical databases at any given point in time. Rather, it guarantees that
each change at the source will eventually be propogated to the targets.
Transactional replication can keep databases in-synch within about five
seconds of each other, depending on the underlying network infrastructure.
If you need to guarantee that two databases are transactionally identical,
look into two-phase commit possibilities.
Transactional replication might be used for a Web site
that supports a huge number of concurrent browsers, but only a few
updaters, such as a large and popular messaging board. All updates would
be done against the replication source database, and would be replicated
in near-real-time to all of the downstream targets. Each downstream target
could support several Web servers, and each incoming Web request would be
balanced among the Web farm. If the system needed to be scaled to support
more read requests, you could simply add more Web servers and databases,
and add the database to
the replication scheme.
Merge Replication
With snapshot and transactional replication,
a single source of data exists from which all the replication targets are
replenished. In some situations, it might be necessary or desirable to
allow the replication targets to accept changes to the replicated tables,
and merge these changes together at some later date.
Merge replication allows data to be modified by the
subscribers and sychronized at a later time. This could be as soon as a
few seconds or a day later.
Merge replication can be helpful for a sales database
that is replicated from a central SQL Server box out to several dozen
sales laptops. As the sales personnel make sales calls, they can add new
data to the customer database or change errors in the existing data. When
the salespeople return to the office, they can synchronize their laptops
with the central database. Their changes are submitted, and the laptop
gets refreshed with whatever new data was entered since the last sycnhronization.
|
Replication will copy the data from your tables
and indexed views and will even replicate changes to multiple
tables caused by a stored procedure, but it will not normally
re-create indexes or triggers at the target. It is common to have
different indexes on replication targets rather than on the source
to support different application and query requirements.
|
Immediate Updating
Immediate update allows a
replication target to immediately modify data at the source. This is
accomplished by using a trigger to run a two-phase commit transaction.
Immediate updating is performance-intensive, but it does allow for updates
to be initiated from anywhere in the replication architecture.
More details on replication are available in Chapter
22, “Data Replication.”
Microsoft Full Text Search Services
The Microsoft Full Text Search Services provides
full text searching capabilities. This is useful for searching large text
fields, such as movie reviews, book descriptions, or case notes.
Full text searching works together with the SQL
database engine. You specify tables or entire databases that you want to
index. The full text indexes are built and maintained outside the SQL
engine in special full text indexes. You can specify how often the full
text indexes are updated to balance out performance issues with timeliness
of the data.
|
The Full Text Search Server is a separate
service from the database engine. You have the option of
installing it as an add-on feature when you install SQL Server.
You can add it to an existing server later by running SQL Setup.
|
The SQL engine supports basic text searches against specific
columns. For example, if you wanted to find all the rows where a text
column contained the word guru, you might
write a SQL statement like that in Listing
1.1.
Listing 1.1 A SQL
Query That Searches for the Word Guru in
the Resume Table
select *
from resume
where description like '%guru%'
This will find all the rows in the resume table where
the description contains the word guru.
This method has a couple of problems, however. First, the search will be
slow. Because text columns can't be indexed by the database engine, a full
table scan will be done to satisfy the query. Even if the data were stored
in a varchar column instead of a text column, an index wouldn't help
because you're looking for guru anywhere
in the column, not just at the beginning. (More information on avoiding
situations like this one are discussed later in the book, in Chapter
34.) What if you
wanted to search for the word guru
anywhere in the table, not just in the description column? What if you
were looking for a particular set of skills, such as “SQL” and
“ability to work independently?”
Full text indexing addresses these problems. To perform
the same search with full text indexing, you would use a query like that
in Listing 1.2.
Listing 1.2 A Full
Text Query to Find the Results from Listing
1.1
select *
from resume
where contains(description, 'guru')
To perform the last search mentioned, use a query like
that shown in Listing 1.3 in any
free-text indexed column in the
table:
Listing 1.3 A More
Complex Full Text Query Demonstrating Features Not Available Through the
SQL Server Database Engine's Indexing Methods
select *
from resume
where contains(*, 'SQL and "ability to work independently"')
|
Two commonly used functions are available for
free text searches: CONTAINS and FREETEXT. CONTAINS
is a better performing function and returns more exact results. FREETEXT
will return looser results based on the meaning of the search
phrase you enter. It does this by finding alternate word forms in
your queries. For example, FREETEXT(*, 'work independently')
would match 'Work Independently', 'Independent work
preferred', and
'Independence in my work'.
|
SQL Server Analysis Services
SQL Server Analysis Services provides essential services
for using a data warehouse. These services are often referred to as OLAP,
which stands for On Line Analytical Processing.
SQL Server Analysis Services connects to one or more
SQL Server databases containing a data warehouse. Based on the criteria
you select, data cubes are created on this data to allow powerful
searches, called data
mining. The SQL Server Analysis Services client tool assists in the
addition of new data to the warehouse and allows scheduling of cube
updates.
|
SQL Server Analysis Services can be leveraged
even if you do not have your data warehouse in a classical star or
snowflake schema.
|
OLAP is commonly used to perform the following
tasks:
|
Perform trend analysis to predict
the future. Based on how many widgets I sold last year, how many will
I sell next year?
|
Combine otherwise disconnected variables to gain
insight into past performance. Was there any connection between widget
sales and rainfall patterns? Searching for unusual connections between
your data points is a typical data mining exercise.
|
Perform offline summaries of commonly used data
points for instant access via Web or custom interface. For example, a
relational table might contain one row for every click on a Web site.
OLAP can be used to summarize these clicks by hour, day, week, and
month, then further categorize these by business line.
|
| |
You can access OLAP services through the Analysis
Services front-end application. For more in-depth analysis, make use of a
SQL-like query language called Multidimensional Expressions, or MDX. MDX
uses similar language to SQL, but instead of operating on tables in the
from clause, it operates on data cubes.
SQL Server Analysis Services is a complex topic. For
more information on MDX, data cubes, and how to use data warehousing
analysis services, see
Chapter 42,
“Microsoft SQL Server Analysis Services.”
Distributed Transaction Coordinator
With the increasing proliferation of distributed
systems comes the need to access and modify data that is often in separate
physical locations and in varying types of datasources. These distributed
transactions need to be treated as a single logical transaction (one
“Unit of Work”). You need a way to ensure that the distributed
transaction operates in the same way that a local transaction does, and
that it adheres to the same ACID properties of a local transaction, across
multiple servers. Microsoft provides this capability with the Distributed
Transaction Coordinator.
Microsoft has implemented its distributed transaction
processing capabilities using the industry standard two-phase
commit protocol. The Distributed Transaction Coordinator Service
(MS DTC) provides the means of managing distributed transactions within
the SQL Server 2000 environment. Typically, each Microsoft SQL Server will
have an
associated distributed transaction coordinator on the same machine with
it. MS DTC runs as a separate service and can be started using the SQL
Sevice Manager, SQL Enterprise Manager, or it can be started via the
Windows Services Control Panel.
The MS DTC allows applications to extend transactions
across two or more instances of MS SQL Server and participate in
transactions managed by transaction managers that comply with the X/Open
DTP XA standard. The MS DTC will act as the primary coordinator for these
distributed transactions. The specific job of the MS DTC is to enlist
(include) and coordinate SQL Servers and remote servers (linked servers)
that are part of a single distributed transaction. The MS DTC coordinates
the execution of the distributed transaction at each participating
datasource and makes sure the distributed transaction completes. It
ensures that all updates are made permanent in all datasources
(committed), or makes sure that all of the work is undone (rolled back) if
it needs to be. At all times, the state of all datasources involved are
kept intact.
For more information on distributed transactions and
the MS DTC, see Chapter
32, “Distributed Transaction Processing.”
Notification Services
SQL Server Notification Services is a platform for
the development and deployment of notification applications. Notification
applications send messages to users based upon subscriptions made to the
notification application. Depending on how the subscriptions are
configured, messages can be sent to the subscriber immediately or on a
predetermined schedule. The messages sent can be personalized to reflect
the preferences of the subscriber.
The Notification Services platform is a reliable,
high-performance, scalable server that is built on the .NET Framework and
SQL Server 2000 and runs on the Microsoft Windows Server family of
operating systems. Notification Services was designed for extensibility
and interoperability with a variety of existing applications.
SQL Server primarily serves as the storage location for
the subscription information for Notification Services. The subscriber and
delivery information is stored in a central Notification Services
database, and individual subscription information is stored in
application-specific databases.
For more information on the Notification Server
architecture and configuring and using SQL Server Notification Services,
see Chapter 45,
“SQL Server Notification Services.”
English Query
English Query is a framework
that provides the tools to develop and deploy English Query applications,
which allow end users to pose questions against your relational database
in English instead of forming a query with a SQL statement.
Developing an English Query application involves
creating, refining, testing, compiling, and deploying a model based on a
normalized SQL database or an OLAP cube. In general, it is easiest to
create English Query applications against normalized SQL databases and the
resulting applications are typically more flexible and powerful than those
developed against databases that are not normalized.
The English Query Model Editor runs within the Visual
Studio 6.0 development environment. You can begin the creation of your
English Query applications using either the SQL Project Wizard or the OLAP
Project Wizard to automatically create an English Query project and model.
After the basic model is created, you can refine, test, and compile it
into an English Query application and then deploy it (to the Web, for
example).
The basic steps for developing and deploying an English
Query application are as follows:
-
Determine the questions that end users are most
likely to ask. Determining the questions to be answered prior to
creating a model helps you to create the appropriate entities and
relationships and test your application.
-
Create a basic model using the SQL Project Wizard
or OLAP Project Wizard. The wizards create a basic model by bringing
in the schema of the data source (database or cube) and automatically
creating entities and relationships based on the tables, columns,
joins, or OLAP objects.
-
Refine the model to address any questions that
cannot be answered using the basic model. This is done by adding
additional entities and relationships.
-
Test and refine the model until it properly returns
the answers to the questions posed.
-
Build the application and deploy it. An English
Query application can be deployed as a Visual Basic or Microsoft
Visual C++ application, or on a Web page running on the Microsoft
Internet Information Services (IIS) as a set of Microsoft Active
Server Pages (ASP).
SQL Server 2000
Environments
SQL Server, in addition to being available in a
number of editions, also runs on a number of Windows platforms.
However, certain editions will only run on certain platforms. This
section takes a look at the platforms on which SQL Server runs and
the editions it supports.
Windows 98 and Windows Me
Windows 98 and Windows Me are designed
primarily for the home PC user. Windows 98 is still in use on the
desktop in many corporate shops as well. Windows 98 and Windows
Me are intended to support mobile users who are disconnected from
the network but need to run applications that require SQL Server
data storage, or for users who need to run local applications that
require local SQL Server data storage on the computer.
For these purposes, the Windows 98 and Windows
Me platforms support the SQL Server 2000 Standard Edition and
the SQL Server 2000 Desktop Engine. Although the Full Text Search
and Analysis Services are
included with the Personal Edition of SQL Server, they cannot be
installed on Windows 98 or Windows Me. Also, because Windows 98
and Windows Me do not support applications running as services,
SQL Server and SQL Task Manager run as standard
executables on Windows 98 and Windows Me rather than as services,
as they do under Windows NT or Windows 2000.
The Windows 98 and Windows Me environments also
support the installation of the SQL Server client tools, such
as Query Analyzer, Enterprise Manager, and SQL Profiler. The
connectivity components needed to connect to any SQL Server 2000
edition running on a Windows NT or Windows 2000 workstation or
server can be installed on Windows 98, Windows
Me, or Windows 95.
Windows NT 4.0
Windows NT is the original platform
for Microsoft SQL Server versions 6.0 and later. SQL Server 2000
is still supported on all Windows NT 4.0 platforms, including
Workstation, Server, and Enterprise Server. However, Windows NT
4.0 Service Pack 5 or later must be installed to run SQL Server
2000.
Windows NT 4.0 Workstation is the platform
intended for the corporate desktop. It sports a Windows 95–like
interface with the underlying reliability and security of Windows
NT. Windows NT 4.0 Server is the server edition of Windows NT 4.0,
which also sports a Windows 95-like interface as well as increased
scalability. Windows NT 4.0 Server includes Internet Information
Server for tighter integration with Internet and Web-based
applications. The Enterprise Edition of Windows NT 4.0 Server is a
more high-powered version of NT 4.0 Server, providing greater
scalability (up to eight processors), clustering capability, and
multinode load balancing. Windows 4.0 Server Enterprise Edition is
the platform to use when deploying largescale distributed
applications.
All Windows NT 4.0 platforms support the
Developer, Personal, and Desktop Engine editions of SQL Server
2000. The Standard and Enterprise editions can be installed on
Windows NT 4.0 Server and Windows
NT 4.0 Server Enterprise Edition.
Windows 2000
Although SQL Server 2000 runs in
the Windows 98, Windows Me, and Windows NT 4.0 environments, it is
best suited for the Windows 2000 environments. Windows 2000 is the
evolution of Windows NT 4.0 and sports a Windows 98–like user
interface. Windows 2000 is available in a Professional Edition and
Server Edition.
Windows 2000 Professional
Windows 2000 Professional is the
version of Windows 2000 geared toward the corporate desktop and
laptop. Windows 2000 provides better power management and hardware
support for use on notebooks than Windows NT 4.0. Windows 2000
Professional is a more reliable and stable platform than Windows
98 or Windows Me, and even more reliable and stable than Windows
NT 4.0 Workstation. Windows 2000 Professional offers high system
uptime, dynamic system configuration, and resilience to
application failures. It is an ideal environment for application
development.
Windows 2000 Professional supports SQL Server
2000 Personal, Desktop Engine, and Developer
Editions.
Windows 2000 Server Family
The Windows 2000 family supports three
server platforms:
|
Windows 2000 Server
|
Windows 2000 Advanced Server
|
Windows 2000 Datacenter Server
|
| |
Windows 2000 Server
Windows 2000 Server is the
next generation of Windows NT 4.0 Server, providing improved
reliability and scalability. Windows 2000 Server supports up to
4GB of memory and up to four processors. Windows 2000 Server
supports the installation of the Enterprise, Developer, Standard,
Personal, and Desktop Engine Editions of SQL Server 2000.
Windows 2000 Server Advanced Server
Windows 2000 Advanced Server is
the next generation of Windows NT 4.0 Server Enterprise Edition,
providing improved reliability and scalability with support for up
to 8GB of memory and eight processors. Like NT 4.0 Enterprise
Edition, Windows 2000 Advanced Server provides support for
clustering and multinode load balancing.
Windows 2000 Advanced Server supports the
installation of all editions of SQL Server 2000, except SQL Server
Windows CE Edition.
Windows 2000 Datacenter Server
The Windows 2000 Datacenter Server is
a new addition to the Windows Server family. It is intended for
largescale Enterprise-wide business applications. Windows 2000
Datacenter Server is like a pumped-up version of Windows 2000
Advanced Server with support for 32-way symmetric multiprocessing
and up to 64GB of physical memory on systems that support it. It
also provides 4-node clustering and up to 32-node network load
balancing.
Windows 2000 Datacenter Server supports the
installation of all editions of SQL Server 2000, except SQL Server
2000 Windows CE Edition.
Windows XP
Windows XP is the continued evolution
of Windows 2000 and was designed to bridge the gap between the
home and business user by being one operating system that meets
the needs of both types. Windows XP is based upon the proven,
reliable Windows 2000 Professional platform with improvements to
the user interface for ease-of-use, home networking, and enhanced
online capabilities.
Windows XP is available in two editions:
Windows XP Home Edition for home use and Windows XP Professional
for power users and business users. Windows XP Professional
includes all the capabilities of Windows XP Home Edition plus
important features to meet the demanding needs of business and
power users, such as enhanced networking, security, management,
and support capabilities. These features provide functionality
that is critical for businesses, but might not be needed by most
home users.
The advanced features of Windows XP include the
following:
|
Networking—Ability to belong
to a domain and be centrally managed, roaming user profiles,
client service for Netware, IPSecurity (IPSec)
|
Corporate Security—Domain authentication,
encrypting file system, group security
policies, auditing tools, advanced file and folder sharing
|
Corporate management—Group policy
support, software management technology, improved scripting
support, and management interfaces
|
Mobile computing—Offline files
and folders, encrypting file system, log on using dial-up
connection
|
Advanced/power user features—Backup
utility, two-processor support, Internet Information Services,
remote desktop
|
| | | |
Both Windows XP Home Edition and Windows XP
Professional support SQL Server 2000 Personal, Desktop Engine, and
Developer
Editions.
Windows CE
Windows CE is the modular real-time embedded
operating system used in small footprint and mobile devices, such
as Pocket PCs. Windows CE provides a Windows-like interface and
Windows compatibility for consumer electronic devices, Web
terminals, Internet access appliances, specialized industrial
controllers, mobile data acquisition handhelds, and embedded
communication devices. Windows CE allows developers to build
applications for small footprint and mobile 32-bit devices that
can integrate more seamlessly with Windows and the Internet.
Windows CE versions 2.11 and later support
running SQL Server 2000 Windows CE Edition.
SQL Server 2000 Edition/Platform
Matrix
Are you confused yet? Keeping track of which
editions of SQL Server run on which Windows platform can be
difficult. To help you, Table
2.1 summarizes the various platforms and the editions of SQL
Server that are supported.
Table 2.1. SQL Server Editions
Supported on Windows Platforms
| Windows
98 and Windows Me |
✓ |
|
|
|
✓ |
|
| Windows
NT 4.0 Workstation |
✓ |
|
|
✓ |
✓ |
|
| Windows
NT 4.0 Server |
✓ |
✓ |
✓ |
✓ |
✓ |
|
| Windows
NT 4.0 Enterprise Server |
✓ |
✓ |
✓ |
✓ |
✓ |
|
| Windows
2000 Professional |
✓ |
|
|
✓ |
✓ |
|
| Windows
2000 Server |
✓ |
✓ |
✓ |
✓ |
✓ |
|
| Windows
2000 Advanced Server |
✓ |
✓ |
✓ |
✓ |
✓ |
|
| Windows
2000 Datacenter Server |
✓ |
✓ |
✓ |
✓ |
✓ |
|
| Windows
XP Home Edition |
✓ |
|
|
✓ |
✓ |
|
| Windows
XP Professional |
✓ |
|
|
✓ |
✓ |
|
| Windows
CE |
|
|
|
|
|
✓ |
|
SQL Server 2000 Editions
You can choose from several editions of SQL
Server 2000. The edition you choose will depend on your database
and data processing needs, as well as the Windows platform on
which you want to install it.
For actual deployment of SQL Server in a live
deployed server environment, you can only choose from two editions
of SQL Server: Standard Edition and Enterprise Edition. In
addition, SQL Server also is available for special uses in a
Developer Edition, Personal Edition, Desktop Engine Edition, or
Windows CE Edition.
This chapter will examine the different
editions of SQL Server and discuss their features and
capabilities. Using this information, you will be better able to
choose which edition provides the appropriate solution.
SQL Server 2000 Standard Edition
The Standard Edition of SQL Server 2000 is the
version intended for the masses—those running small- to
medium-sized systems that don't require the performance,
scalability, and availability provided by Enterprise Edition. The
Standard Edition runs on any of the Windows 2000 or Windows NT 4.0
Server platforms with scalability limited to up to four processors
and 2GB of memory. Standard Edition includes the following
features:
|
Analysis Services
|
Data Transformation Services
|
Full Text Search
|
English Query
|
Built-in XML support
|
SQL Profiler and performance analysis tools
|
Graphical DBA and Developer tools
|
Replication
|
Data mining tools
|
| | | | | | | |
As shown in Table
2.1, the Standard Edition can be installed on any of the
Windows NT 4.0 and Windows 2000 Server platforms.
The Standard Edition should meet the needs of
most departmental and small- to mid-sized applications. However,
if you need more scalability, availability, advanced performance
features, or comprehensive analysis features, you will want to
implement the Enterprise Edition of SQL
Server 2000.
SQL Server 2000 Enterprise Edition
The Enterprise Edition of SQL Server 2000 is
the most comprehensive and complete edition available. It provides
the most scalability and availability of all editions and is
intended for systems that require high performance and
availability, such as large volume Web sites, data warehouses, and
high throughput online transaction
processing (OLTP) systems.
SQL Server 2000 Enterprise Edition supports up
to 64GB of memory and up to 32 processors when it is installed on
Windows 2000 Datacenter Server. It supports up to 8GB of memory
and 8 processors when it is installed on Windows 2000 Advanced
Server. To provide these capabilities, SQL Server 2000 Enterprise
Edition can be installed only on Server versions of Windows 2000
and Windows NT.
Additionally, SQL Server 2000 Advanced Server
provides performance enhancements such as parallel query, indexed
views, and enhanced read-ahead scanning.
Which version is right for you? The next
section explores the feature sets of Enterprise and Standard
Editions so you can decide which one
provides the features you need.
Differences Between Enterprise and
Standard Editions of SQL Server
For deploying SQL Server 2000 in a server
environment, either the Standard or the Enterprise Edition of SQL
Server is a logical choice. To help decide between the two
editions, Table 2.2
compares the major features supported by each edition.
Table 2.2. SQL Server 2000 Feature
Comparison Between Enterprise and Standard Editions
| Distributed
partitioned views |
Yes |
No |
| Parallel
index creation |
Yes |
No |
| Parallel
scan |
Yes |
No |
| Parallel
DBCC |
Yes |
No |
| Failover
clustering |
Yes |
No |
| Failover
cluster management |
Yes |
No |
| Indexed
views |
Yes |
Yes (See
Note) |
| Log
shipping |
Yes |
No |
| Differential
backups |
Yes |
Yes |
| Replication |
Yes |
Yes |
| Graphical
DBA and development tools |
Yes |
Yes |
| XML
support |
Yes |
Yes |
| Full Text
Search |
Yes |
Yes |
| English
Query |
Yes |
Yes |
| Integrated
data mining |
Yes |
Yes |
| Multiple
instance support |
Yes |
Yes |
| Data
Transformation Services |
Yes |
Yes |
| Active
Directory integration |
Yes |
Yes |
|
While all editions of SQL Server 2000
allow the creation of indexed views, only
the Developer and Enterprise Editions of SQL Server 2000
will automatically consider using an indexed view in query
plans. To force the optimizer to consider indexed views in
other SQL Server editions, the NOEXPAND hint must be
included in the query. For more information on using
indexed views and how they are handled by the query
optimizer, please see Chapters
27, “Creating and Managing Views in SQL Server,” 34,
“Indexes and Performance,” and 35,
“Understanding Query Optimization.”
|
Another main difference between Enterprise and
Standard Editions is scalability. Table
2.3 lists the differences in amount of memory supported by
both editions depending on the platform on
which it is installed.
Table 2.3. Maximum Number of
Processors Supported by the Standard and Enterprise Editions
by Platform
| Windows
2000 Datacenter |
32 |
4 |
| Windows
2000 Advanced Server |
8 |
4 |
| Windows
2000 Server |
4 |
4 |
| Windows
NT 4.0 Server Enterprise Edition |
8 |
8 |
| Windows
NT 4.0 Server |
4 |
4 |
The amount of memory supported by each edition
on the various platforms is limited, as shown in
Table 2.4.
Table 2.4. Maximum Amount of Memory
Supported by the Standard and Enterprise Editions by Platform
| Windows
2000 Datacenter |
64GB |
2GB |
| Windows
2000 Advanced Server |
8GB |
2GB |
| Windows
2000 Server |
4GB |
2GB |
| Windows
NT 4.0 Server Enterprise Edition |
3GB |
2GB |
| Windows
NT 4.0 Server |
2GB |
2GB |
Other SQL Server 2000 Editions
The Standard and Enterprise Editions of SQL
Server 2000 are intended for server-based deployment of
applications. In addition, the following editions are available
for other specialized uses:
|
Developer Edition
|
Personal Edition
|
Desktop Engine Edition
|
Windows CE Edition
|
| | |
Developer Edition
The Developer Edition of SQL Server
2000 is a full-featured version intended for development and
end-user testing only. It includes all features and functionality
of Enterprise Edition, at a much lower cost, but the licensing
agreement prohibits production deployment of databases using the
Developer Edition.
To provide greater flexibility during
development, the Developer Edition can be installed in any of the
following environments:
|
Windows 2000 Datacenter
|
Windows 2000 Advanced Server
|
Windows 2000 Server
|
Windows NT 4.0 Server Enterprise Edition
|
Windows NT 4.0 Server
|
Windows 2000 Professional
|
Windows XP Professional
|
Windows XP Home Edition
|
Windows NT 4.0 Workstation
|
| | | | | | | |
Personal Edition
The Personal Edition of SQL Server 2000 is
intended for users who are running
applications that require a locally installed database, often on
mobile systems, and who spend at least some of the time
disconnected from the network. The Personal Edition, intended for
use in a single-user environment, includes all the tools and
features of the Standard Edition with the following exceptions:
|
It contains a workload governor that
cripples performance when more than five Transact-SQL commands
are executed simultaneously.
|
It is limited to using a maximum of two
processors (only one processor on Windows 98 and Windows Me).
|
It can participate in replication but only
as a subscriber.
|
| |
The Personal Edition can be installed in any of
the Windows 2000 or Windows NT 4.0 environments, Windows XP
Professional and Home Edition, as well as Windows 98/Me. However,
the Full Text Search and Analysis Services are not available under
Windows 98/Me.
Desktop Engine Edition
The Desktop Engine Edition of
SQL Server 2000 is a small-footprint, redistributable,
database-engine-only version of SQL Server intended for
distribution with applications that require an embedded or offline
data store. Software developers can include it as a module for
Windows Installer so it is installed with the rest of the
application.
The Desktop Engine is just that—a database
engine only. No administrative tools are installed. Administration
must be handled through the custom application, or with graphical
tools installed with another edition of SQL Server 2000. The
Desktop Edition supports the same feature set as the Personal
Edition with the exception of Analysis Services. The maximum
database size is limited to 2GB.
Like all other editions of SQL Server 2000, the
Desktop Engine Edition supports installation of multiple instances
to decrease potential conflicts between instances installed by
other applications or vendor products.
The Desktop Engine Edition can be installed in
any of the Windows 2000 or Windows NT 4.0 environments, Windows XP
Professional and Home Edition, as well as
Windows 98/Me.
Windows CE Edition
The Windows CE Edition of
SQL Server 2000 runs only on platforms that are running the
Windows CE environment. The Windows CE Edition is a scaled-down
version of SQL Server 2000 that provides Transact-SQL
compatibility and a cost-based query optimizer. Developers who are
familiar with SQL Server 2000 should feel comfortable developing
for Windows CE Edition.
Windows CE Edition requires about a 1MB
footprint on a Windows CE device, and has a maximum database size
limit of 2GB. Windows CE Edition supports replication with SQL
Server 2000 databases as a merge replication subscriber so that
data can be accessed and manipulated offline and synchronized
later with a Server version of SQL Server 2000.
SQL Server 2000 64-Bit Version
At the time of this writing, Microsoft was
in the process of beginning beta testing for a 64-bit version of
SQL Server 2000, code-named “Liberty.” The SQL Server 2000
64-bit beta is built to take advantage of hardware enhancements of
the 64-bit Itanium platform from Intel. It will offer higher
levels of single-system scalability for memory-intensive data
applications, such as largescale e-commerce, data warehousing, and
analytics. Currently, the planned hardware offerings support up to
64GB of physical linear memory for the 64-bit version of SQL
Server 2000, with up to 4 terabytes of physical linear memory
planned for future hardware.
The increased memory support of the 64-bit
platform will benefit database applications with memory-sensitive
workloads that require working data sets larger than 4GB to be
loaded in memory. In addition, the 64-bit platform may result in
improved I/O performance due to larger memory buffer pools. For
low-end implementations with 4-processor servers or less, some
performance improvement can be expected on a 64-bit implementation
over a 32-bit implementation, but higher performance gains are
expected for higher-scale implementations that utilize 8 to 32
processors.
The 64-bit version of SQL Server 2000 will
include a 64-bit database server, a 64-bit server agent, and
64-bit analysis server for OLAP and data mining. The 64-bit
version of SQL Server 2000 will require 64-bit hardware running
the 64-bit version of Microsoft Windows .NET Server Beta3.
|
|
|
