Register your product to gain access to bonus material or receive a coupon.
Essential SQL Server 2000: An Administration Handbook
- By Buck Woody
- Published Dec 27, 2001 by Addison-Wesley Professional.
Book
- Sorry, this book is no longer in print.
Description
- Copyright 2002
- Dimensions: 7-3/8" x 9-1/4"
- Pages: 624
- Edition: 1st
- Book
- ISBN-10: 0-201-74203-9
- ISBN-13: 978-0-201-74203-9
Microsoft markets SQL Server 2000 as the easiest enterprise database to manage -- and increasingly, professionals with no DBA experience are being called upon to manage it. Now, there's an SQL Server 2000 book for them. In Essential SQL Server 2000: An Administration Handbook, Buck Woody gives them the skills they need to survive -- and achieve their goals. Essential SQL Server 2000 begins with an overview of SQL Server 2000's capabilities and versions, and the role of a DBA in managing SQL Server 2000. Next, Woody walks through SQL Server 2000 installation, and the efficient management and operation of SQL Server 2000 systems. The book includes step-by-step coverage of maintenance, including backup; and offers practical, easy-to-follow guidance on recovering from problems with SQL Server 2000 systems and databases. For every technical professional called upon to manage SQL Server 2000 databases, regardless of experience with database administration or SQL Server.
Extras
Related Articles
My Baby Done Wrote Me a Letter (Using SQL Mail)
Whats Going On? Using SQL Server Web Wizard to Display Maintenance Results
Sample Content
Online Sample Chapters
Backing Up and Restoring in SQL Server
Downloadable Sample Chapter
Click below for Sample Chapter related to this title:
woodych07.pdf
Table of Contents
Preface.
1. Introduction.
Chapter at a Glance.
Overview.
How You Can Use This Book (You Really Should Read This Part).
The DBA Role.
Duties of a DBA.
SQL 2000. Overview.
Versions.
SQL Server Licensing.
Clients.
Resources.
2. Installing/Upgrading SQL 2000.
Chapter at a Glance.
Overview.
Detail. Setup.
Requirements.
Hardware.
Environment.
Graphical. Setup.
Detail Upgrade.
Requirements.
Graphical Upgrade.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
3. Tools.
Chapter at a Glance.
Overview.
Detail.
Reference.
Administration.
Data Access.
Monitoring.
Network Configuration.
Graphical.
Books Online.
Enterprise Manager.
Command Line.
osql.
bcp.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
4. Accessing Data.
Chapter at a Glance.
Overview.
Detail.
Databases.
Accessing Data.
Graphical.
Creating and Managing a Database.
Using the SQL Tools to Access Data.
ODBC.
Command Line.
Creating and Managing a Database.
Using the SQL Tools to Access Data.
ODBC.
Examples.
Finding Information about a Database.
Microsoft Access Linked Tables.
The Web Assistant.
A Simple Active Server Page.
The Sample Visual Basic Programs.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
5. Importing and Exporting Data.
Chapter at a Glance.
Overview.
Detail.
Data Transformation Services.
The bcp Utility.
BULK INSERT.
SELECT INTO.
XML Bulk Insert.
Which Tool to Use?
Graphical.
Data Transformation Services.
Command Line.
bcp.
BULK INSERT.
SELECT INTO.
Examples.
Creating a Scheduled Export of Data with DTS.
Using bcp to Export Data and Create a Format File.
Using SELECT INTO to Import From a Table Based on a Condition.
Using BULK INSERT to Import Only Five Rows of Data.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
6. Automation (The SQL Server Agent).
Chapter at a Glance.
Overview.
Detail.
Operators.
Jobs.
Alerts.
Graphical.
Operators.
Jobs.
Alerts.
Command Line.
Operators.
Alerts.
Jobs.
Examples.
Finding Out When You Need More Ram.
Back Up a Database, Zip It, Copy It.
Rosetta Stone.
Resources.
7. Maintenance.
Chapter at a Glance.
Overview.
Detail.
Physical Structure.
The Maintenance Wizard.
DBCC Commands.
Graphical.
The Maintenance Wizard.
Command Line.
Recovering a Suspect Database.
DBCC Commands.
Examples.
Checking the Success of a Maintenance Plan.
Correcting a Suspect Database.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
8. Backing Up and Restoring.
Chapter at a Glance.
Overview.
Detail.
Backing Up Databases.
Recovery Models.
Full Backups.
Log Backups.
Large Backups.
Differential Backups.
Backup Locations.
Backup Devices.
Recovery Process.
Graphical.
Creating Backup Devices.
Backing Up a Database.
Restoring a Database.
Backing Up a Log.
Recovering a Database Using a Log Backup.
Command Line.
Creating a Backup Device.
The Backup Command.
The Restore Command.
A Backup and Restore with Logs.
Examples.
Backing Up a Database to a Network Share.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
9. Monitoring and Optimization.
Chapter at a Glance.
Overview.
Detail.
Hardware Optimizations.
Index Tuning Wizard.
SQL Profiler.
Current Activity.
sp_monitor.
sp_who.
sp_lock.
Graphical.
Windows Performance Monitor.
Enterprise Manager.
SQL Profiler.
Command Line.
Query Analyzer.
Query Analyzer. sp_monitor.
Query Analyzer. sp_who.
Query Analyzer. sp_lock.
Examples.
Who's Logged On.
Kill a Process.
How Busy Is My Server.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
10. Security.
Chapter at a Glance.
Overview.
Detail.
Security Types.
Permissions.
Graphical.
Working with Server Logins.
Working with Database Users.
Working with Roles.
Working with Permissions.
Command Line.
Working with Server Logins.
Working with Database Users.
Working with Roles.
Working with Permissions.
Examples.
Setting Up a Database Permissions Schema.
Recreate Permissions after a Move.
Rosetta Stone.
Oracle.
Microsoft Access.
Resources.
SQL Server 2000 Advanced Features.
11. Replication.
Chapter at a Glance.
Overview.
Detail.
Types of Replication.
Replication Topology.
Graphical.
Setting Up Replication Using the Wizard.
Command Line.
Replication Stored Procedures.
Examples.
Setting the Distributor.
Setting the Publisher.
Setting the Database.
Setting the Publication.
Setting the Articles.
Setting the Agent.
Activating the Replication.
Setting the Subscriber.
Resources.
12. Analysis Services (OLAP).
Chapter at a Glance.
Overview.
Detail.
Analysis Services Evolution.
Uses.
Analysis Services Concepts.
Impacts.
Client Access.
Preparing for Analysis Services.
Cubes.
Graphical.
Installation.
Running the Tutorial.
Command Line.
MDX Sample Application.
Examples.
Viewing Cube Data with Microsoft Excel.
Resources.
13. eXtensible Markup Language.
Chapter at a Glance.
Overview.
Detail.
Structured Documents.
Tags.
XML Documents.
Creating XML.
Graphical.
Setting Up For XML.
Accessing SQL Server 2000 From a Browser URL.
Accessing SQL Server 2000 From a Template Document.
Formatting XML Data with a Style Sheet.
Command Line.
Creating an XML Document with Query Analyzer.
Opening an XML Document As Data.
Examples.
A Simple Visual Basic Program to Create XML Documents From SQL Server 2000.
Resources.
14. English Query.
Chapter at a Glance.
Overview.
Detail.
English Query Uses.
Understanding the Data Structure.
Understanding the Data Content.
Developing the Questions.
The Semantic Model.
Installing the Product.
Visual Studio.
Clients.
Regression Testing.
Graphical.
Installing English Query.
Creating the Project.
Command Line.
Creating a Regression Test.
Examples.
Deploy Completed Model to Web.
Resources.
15. Working with Instances.
Chapter at a Glance.
Overview.
Detail.
What Are Instances?
Reasons.
Properties.
Impacts.
Connecting to Multiple Instances.
Graphical.
Installing a New Instance.
Command Line.
Connecting to the Instance Using OSQL.
Examples.
Connecting to a Named Instance Using ODBC.
Resources.
Appendix A. A Sample Server Sizing Exercise.
RAM.
Storage.
Appendix B. Hardware for SQL Server.
RAM.
CPU.
Storage Devices (Hard Drives).
NIC.
Index. 0201742039T12042001
Preface
This book is written for the technical professional. It's a mix of a little theory and a lot of practical hands-on examples and real-world explanations.
The book is divided into two parts. The first part (Chapters 1-10) deals with the day-to-day things you need to know as a database administrator (DBA).
- Chapter 1 explains how to use the book. It introduces databases in general and SQL Server in particular. This chapter also explains what a DBA does, and details the various versions of SQL Server 2000.
- Chapter 2 displays an install of SQL Server 2000 as well as an overview of upgrading a SQL Server 7 or 6.5 database server to SQL Server 2000.
- Chapter 3 familiarizes you with the tools available for SQL Server 2000. This chapter also shows the tools' changes from earlier versions.
- Chapter 4 introduces you to methods of accessing data, both from internal SQL Server 2000 tools and by using other programs. This chapter also covers the connection methods for SQL Server 2000.
- Chapter 5 explains the methods used to import and export data for SQL Server 2000, and how to automate data transfer from existing sources, such as text files or other databases, to another destination.
- Chapter 6 introduces you to automating SQL and other tasks such as operating system commands to full-scale programming with conditional branching.
- Chapter 7 focuses on the maintenance of your databases. This is the most important chapter in the book.
- Chapter 8 explains backing up and restoring SQL Server 2000 databases to disk or tape as well as the recovery models SQL Server uses for its databases and how they affect backups and restoration.
- Chapter 9 shows how to tune SQL 2000 Server, both with hardware and the nondesign options.
- Chapter 10 details SQL Server 2000 security.
The second part (Chapters 11-15) contains introductions and overviews to the more advanced features in SQL Server™ 2000. I've also included a sizing exercise for your servers as well as a discussion about hardware.
- Chapter 11 describes replication and shows a simple replication schema.
- Chapter 12 gives a basic understanding of Microsoft's Analysis Services. Here you'll see a start-to-finish example of how OLAP cubes work, as well as the process to use Microsoft Excel as an OLAP client.
- Chapter 13 provides an overview of the XML technology in SQL Server 2000.
- Chapter 14 shows how to ask plain-English questions from SQL Server 2000 databases.
- Chapter 15 explains named instances and how to set them up and connect to them.
The book concludes with two appendixes.
- Appendix A gives a sample server sizing exercise.
- Appendix B details hardware for SQL Server 2000.
0201742039P12042001
Index
A
Active Server Pages (ASPs), 488, 509
in data access, 148-152
for IIS, 148, 150-152, 544
ActiveX Data Objects (ADO), 13, 104
ActiveX Script, 172, 180, 201-202, 206, 213
Adjectives in English Query, 501, 506-507
Administration, 45. See also Enterprise Manager
Service Manager, 57-58
ADO. See ActiveX Data Objects
ADO.NET method, 13, 107
Agents, replication and, 362, 397
Alerts in automating with SQL Server Agents, 202, 220-223, 225
Alias, 555
security issues and, 341
AMD, 578
Analysis Manager, 408-453
Analysis Services, using OLAP for, 401-468
APIs. See Application Programming Interfaces
AppleTalk network libraries, 555
Application Programming Interfaces (APIs), 13, 107
Application roles, 326, 328, 351-352
Arcserv, 259
Articles, 362
setting, 397
ASPs. See Active Server Pages
Authentication, 326
Automation, SQL Server Agent in, 199-232
Alerts in, 202, 220-223, 225
Jobs in, 201-202, 210-219, 225-229
Notifications in, 201
Operators in, 200, 205, 208-210, 224-225
Axes, 459
B
BACKUP command, 231, 289
Backup Exec, 259
Backups, 229-232, 238, 259-294. See also Recovery models
devices for, 264, 265, 271-272, 274, 289
differential, 263
full, 262
large, 262-263
locations for storing, 263-264
log, 262, 277-283
with Microsoft Access, 293
to network share, 292-293
with Oracle commands, 293
Banyan VINES, 555
bcp (bulk copy program)
in data access, 99, 156
in data transfer, 156, 185-187
exporting data and creating format file, 157, 191-196
Binaries, 26-27
Books Online, 16, 37, 38, 44, 50, 69, 123, 152
Contents tab, 70-71
Favorites tab, 71, 73
Index tab, 70-71, 72
menu page for, 69
Search tab, 71, 72
using, 69-73
Boolean search, 71
Building the project, 508-509
bulk copy program. See bcp
Bulk insert, 180
BULK INSERT statement, in data transfer, 156, 158, 187, 197
Bulk Logged Recovery model, 105, 260, 262, 263
C
C#, for writing programs to control DTS, 157
Catalogs, full-text, 54-55
Central processing unit (CPU), 5, 297-298, 578
CE version of SQL Server 2000, 5-6
reasons for using, 6
CGI script, 488
Checkpoint, 105, 262, 579
Client access, 405
Client Network Utility, 68, 75
Clients
connecting to SQL Server 2000, 12-14
in English Query, 509
setting up ODBC connection from, 115-120
Clusters, 20
CmdExec, 206
Collation, 30
Command line, 109
Commands in English Query, 502, 507
Component Checker, 25
Concurrency, 4
Conditions, using SELECT INTO to import data from table based on, 196
Connections, 156, 157, 178-179
Copy SQL server objects, 180
Counters, in performance monitoring, 299-300
CPU. See Central processing unit
CREATE, 326
CScript, 132
Cubes, 402, 404, 405-407
viewing data, with Microsoft Excel, 460-468
Cube tables, 402
Current activity, monitoring, 55, 302
D
DAO. See Data Access Objects
Data, 472, 487
bcp in exporting, 191-196
BULK INSERT in importing, 197
creating scheduled export of, with DTS, 188-191
importing, 134
in linked table, 134
opening XML document as, 493-495
querying, from a table, 88-90
SELECT INTO command in importing from table, 196
viewing cube, with Microsoft Excel, 460-468
Data access, 58, 103-154. See also Databases
with ASP, 148-152
with bcp, 99
with Microsoft Office, 108
with OLE-DB, 107-108
with Open Database Connectivity (ODBC), 107
with osql, 58, 97-98, 129-132, 157
interactive mode, 129, 130, 131
query mode, 129, 130
script mode, 129, 131
with Query Analyzer, 58-61, 97, 114, 125-129, 130, 131
with SQL tools, 106, 114, 124-132
with Visual Basic, 108
with Web Assistant Wizard, 134, 140-148, 149
with Web pages, 108
Data Access Objects (DAO), 13
Data analysis, 403
Data architect, 3
Database administrators (DBAs)
concerns of, 103
design, 503
duties of, 3-4
impact of ODBC drivers, 107
maintenance, 3
tasks of, 155
types of, 3
Database Consistency Check (DBCC) commands
DBCC CHECKDB, 238, 249-252, 253-255
DBCC CHECKTABLE, 239
DBCC DBREINDEX, 239, 252
DBCC SHOWCONTIG, 237
Database developer, 3
Database Diagram, 38, 52-53
creating, 81-87
Database groups, 329-330
Databases, 50-52, 104-109. See also Data access
accessing data from, 106-109
backing up, 229-232, 261, 266-271
to a network share, 292-293
correcting suspect, 253-256
creating and managing, 109-114, 121-124
Enterprise Manager in, 109-114
Query Analyzer in, 121-124
DBCC commands for checking tables and indexes in, 238-239
defined, 104
Enterprise Manager in getting information about, 133-134
growth settings for, 106
integrity of, 237-238
log backup in recovering, 284-288
maintenance plans for, 55
objects in, 52-55
physical storage process for, 104-105
recovering suspect, 248-249
using log backup, 284-288
recovery settings for, 105
replication and, 396
restoring, 271-277
roles of, 350-351
setting up permissions schema for, 354-356
statistics on, 237
suspect
correcting, 253-256
recovering, 248-249
Database selection drop-down menu, 59
Database users, 53
security for, 328, 340-342, 348-349, 348-352
application roles, 326, 328, 351-352
database roles, 329-330, 343-345, 350-351
Data content in English Query, 503-504
Data Definition Language (DDL) statements, 343-344
Data driven query, 180
Data Manipulation Language (DML) statements, 343
Data mining, 403
Data Source Name (DSN), 107
File, 107
System, 107
User, 107
Data structure in English Query, 503
Data transfer, 155-198
bulk copy program (bcp) for, 156, 157, 185-187
in exporting data and creating format file, 191-196
BULK INSERT statement for, 156, 158, 187, 197
DTS for, 156, 157, 159-184, 185, 188-191
deciding on tool to use, 158-159
with Microsoft Access, 198
with Oracle, 197
SELECT INTO statement for, 156, 158, 187-188
in importing data from table based on conditions, 196
XML software in, 156, 158
Data Transformation Services (DTS), 40, 55
for data transfer, 156, 157, 159-184, 185
connections in, 157, 178-179
creating a scheduled export of data with, 188-191
DTS Designer in, 159, 174-185
Import/Export Wizard in, 161-174
packages in, 157, 174, 177
settings in, 156
tasks in, 157, 179-181
Transformations panel in, 172, 173
workflows in, 156, 157, 181-184
Data types, user-defined, 54
db_accessadmin, 329, 330
db_backupoperator, 329
db_datareader, 329, 343
db_datawriter, 329, 343
db_ddladmin, 329, 343
db_denydatareader, 329, 330
db_denydatawriter, 329, 343
DB-Library, 13
db_owner, 329, 330, 342, 343
db_securityadmin, 329, 330
DBAs. See Database Administrators
DBCC commands. See Database Consistency Check commands
DDL statement. See Data Definition Language statements
Decision support system, 402-403
Defaults, 54
DELETE, 326, 327, 352
DELETE permission, 329, 330
Delphi, 405
DENY, 327, 352, 353
Design DBAs, 503
Differential backups, 263
Dimensional naming, 426
Dimensions, 404
Dimension tables, 405, 406
Direct Memory Access (DMA), 580-581
Distributed partitioned views, 11
Distributed Transaction Coordinator, 57
Distributors, replication and, 362, 363, 366, 395
DLLs. See Dynamic Link Libraries
DMA. See Direct Memory Access
DML statements. See Data Manipulation Language statements
Domain User account, 29
DSN. See Data Source Name
DTS. See Data Transformation Services
DTS Designer, 178
in accessing DTS, 159, 174-185
tasks of, 179-181
Dynamic Link Libraries (DLLs), 108, 472
Dynamic properties, 181
E
EIDE format. See Enhanced Integration Disk Electronics format
Elements, 472
E-mail notification method, 201, 209
E-mail pager notification, 201, 209
Enable Multi-Protocol Encryption, 32
End-User License Agreement (EULA), 22
English Query, 10, 499-500
adjectives in, 501, 506-507
clients in, 509
commands in, 502, 507
creating project in, 511-537
data content in, 503-504
data structure in, 503
deploying completed model to the Web, 545-549
developing questions in, 504, 511-512
entities in, 501, 505
mapping to physical objects, 515
installing, 508, 510-511
logical model in, 500
measurement in, 501
phrasings in, 501-502
physical model in, 512-515
prepositions in, 501, 506
regression testing in, 509, 538-544
relationships in, 505-507, 515-516
semantic model in, 504-507, 515-516, 523
subject-verb-object mode in, 528
synonyms in, 505, 526-528
traits in, 501, 506
uses, 503
verbs in, 502, 507, 516
Visual Studio in, 501, 508-509
creating project with, 516-537
Enhanced Integrated Disk Electronics (EIDE) format, 579
Enterprise Manager
in accessing DTS, 159
in accessing tables, 87-97, 167-168
in administration, 44, 45-50, 73
backup and restore procedures in, 259, 260-261
Console root in, 88, 89, 91
in controlling database options, 109, 112-114
Data Files tab, 112, 113
Options tab, 112, 114
in creating a scheduled export of data, 188-189
creating backup device with, 265
creating Database Diagram in, 81-87
in creating Operators, 224
in data access, 114
in examining data using Web browser, 134, 141
in finding information about database, 133-134
generating script in, 91-97
in monitoring, 296, 302, 306-307
opening Alerts object in, 220
recovering database with, 264
registering SQL server in, 73-76
security issues and, 325
setting SQL Server configuration in, 77-81
in setting up Replication, 368
Enterprise version of SQL Server 2000, 10-12
reasons for using, 11-12
Entities in English Query, 501, 505
mapping, to physical objects, 515-516
Entity Relationship Diagrams (ERDs), 52-53
EULA. See End-User License Agreement
Event Categories, 302
Event classes, 302
EXECUTE, 326, 327, 352
Execute package, 180
EXECUTE permissions, 331
Execute process, 180
Execute SQL, 180
Export List, 48
Extensible Markup Language (XML). See XML
Extensible Stylesheet Language (XSL) document, 471
F
Fact table, 405, 406
Fail-Safe Operator, 205
File Allocation Table (FAT), 104
File DSN, 107
File transfer protocol, 180
Firewall, 363
Fixed server role, 328, 329
Format file, 157
importing data with, 186
using bcp to create, 191-196
Free-form tags, 471
Full backups, 262
Full Recovery model, 105, 260, 262, 263, 279, 291
Full-Text Catalogs, 54-55, 57
Full-Text Search object, 57
Functions, user-defined, 54
G
GRANT, 327, 352, 353
H
Handle, 493
Hardware, for SQL server, 577-581
Hardware optimizations, 297
CPU, 297-298
memory, 298
NIC, 299
storage, 298-299
Hardware RAID, 298
HOLAP. See Hybrid OLAP
HTML, 469
HTTP, in accessing ASP, 148
Hybrid OLAP (HOLAP), 402, 444
I
IDEs. See Integrated Development Environments
IIS. See Internet Information Server
Import/export object wizards, 166-174
Indexed views, 11
Indexes, 300-301
DBCC commands for checking, in database, 238-239
Index Tuning Wizard, 300-302
Information, finding, about databases, 133-134
INSERT, 326, 327, 352
INSERT INTO command, 156
INSERT permission, 329, 330
Instances, 22, 551-570
connecting to multiple, 555-556
defined, 552-553
impacts, 554-555
installing new, 556-557
named, 552
names, connecting with ODBC, 567-770
properties, 554
protocols for, 555-556
reasons for implementing, 553-554
Integrated Development Environments (IDEs), 405
Intel, 578
Internet Information Server (IIS)
ASPs for, 148, 150-152, 544
configuring, 475-484
isql, 58
J
Java programming language, 132
JavaScript, 172
programming commands in, 201
Jobs in automating with SQL Server Agent, 201-202, 210-219, 225-229
Join, 429
L
Large backups, 262
Levels, 423
Linked servers, 188
Linked tables, 134
in Microsoft Access, 134, 135
Log backups, 262, 277-283
recovering a database using, 284-288
Log files, 27
recovery of, 276-277
Logical design, 405
Logical model in English Query, 500
Logs, 261
backup and restore with, 290-292
M
Mail application programming interface (MAPI), 80, 201
Maintenance, 233-257
DBCC commands in, 235, 238-239, 249-252
Maintenance Wizard in, 236-238, 239-248
Microsoft Access and, 257
Oracle commands for, 256-257
physical structure in, 234-235
suspect database in, 248-249
correcting, 253-256
recovering, 248-249
tasks in, 233
Maintenance DBA, 3
Maintenance plan
checking on success of, 252-253
creation of, by Maintenance Wizard, 236
Maintenance Wizard, 236-238, 239-248, 259, 268
for checking on database backups, 238
for checking on database integrity, 237-238
to create scheduled backups, 260, 268
optimizations, 236-237
MAPI. See Mail application programming interface
Master database, 50
MDAC. See Microsoft Database Access Components
MDX. See Multidimensional Expressions
Measurement adjective in English Query, 501, 507, 516
Measures, 404
Memory, 298, 577-578. See also RAM
Menu bar, 59
Merge replication, 362, 363, 365
Message queue, 180
Meta Data, 57
Microsoft
Analysis Services, 401-468
Desktop Engine in, 6-7
Developer Network in, 9
IIS
ASPs on, 148, 150-152, 544
configuring, 475-484
Ownership Chain in, 327
Repository feature in, 156
Microsoft Access, 410
backup and restore commands with, 293
comparison to SQL Server 2000, 101
data access with, 152, 154
data transfer with, 198
linked tables in, 134, 135
maintenance and recovery in, 257
monitoring in, 322
security commands in, 358-359
upsizing to SQL Server 2000, 40-41
Microsoft Database Access Components (MDAC), 24-25, 104
Microsoft Data Link connection object, 179
Microsoft Excel, 401
in copying data from SQL Server 2000 into spreadsheets, 134
Pivot Table Services, 405
viewing cube data with, 460-468
Microsoft Management Console (MMC), 303
Microsoft Office, in database access, 108
Microsoft Word, 471
in creating labels from SQL Server, 134
Migration, 155
Mixed Mode, 29-30
MMC. See Microsoft Management Console
Model database, 50
Model test tool, 508
MOLAP. See Multidimensional OLAP
Monitoring, 61-66, 295-323. See also SQL 2000 Profiler
of current activity, 302
Enterprise Manager for, 296, 302, 306-307
Index Tuning Wizard in, 300-302
in Microsoft Access, 322
NIC in, 17, 299, 580-581
in Oracle, 322
Query Analyzer in, 317-320
sp_lock in, 303, 319-320
sp_monitor in, 302-303, 318, 321
sp_who in, 303, 318-319, 321
SQL Server Profiler for, 61, 64-66, 296, 302, 307-316
Windows Performance Monitor for, 296, 299-300, 303-306
Msdb database, 50
Multidimensional Expressions (MDX), 402, 405
sample application in, 454-460
Multidimensional OLAP (MOLAP), 402, 405, 443, 444, 447
Multi-Protocol, 32
N
NAS. See Network Attached Storage
Named Pipes, 32, 37, 38, 552, 555
Name/ID, 501, 505-506
Native file, 186
Natural Language Processing, 10
NDS. See NetWare Directory Services
NET, 405
NetWare Directory Services (NDS), 17
Network Attached Storage (NAS), 11-12
Network configuration, 66
Client Network Utility in, 68
Server Network Utility in, 66-68
Network Interface Card (NIC), 17, 299, 580-581
Network share, backing up a database to, 292-293
NIC. See Network Interface Card
Normalization, 503
Northwind (sample database), 51, 52
Notifications methods, in automating with SQL Server Agent, 201
e-mail notification with, 201, 209
E-mail pager notification, 201, 209
Windows NT NET SEND, 201, 208-209
Novell network, 17
NTBACKUP command, 264
NT File System (NTFS), 104, 299
O
Object Linking and Embedding Database (OLE-DB), 12, 13
in accessing data, 13, 107-108, 157
Object permissions, 326, 330-331
ODBC. See Open Database Connectivity
OLAP. See Online Analytical Processing (OLAP)
OLE-DB. See Object Linking and Embedding Database
OLTP/OLAP hybrid databases, 298
Online Analytical Processing (OLAP), 10, 11, 19, 297, 401-468
Analysis Services
concepts in, 404
evolution of, 402
installation of, 407-408
MDX sample application, 454-460
preparing for, 405
tutorial for, 408-453
client access, 405
cubes in, 402, 404, 405-407
MDX in accessing, 402, 405
viewing data, 460-468
data for, 57
hybrid, 402
impacts, 404
multidimensional, 402, 405, 443, 444, 447
relational, 402, 405, 443-444
uses of, 403
Open Database Connectivity (ODBC), 12-13
in accessing data, 107, 132-133
setting up connection from client, 115-120
Visual Basic in creating, 132-133
connecting to a named instance using, 567-770
drivers in, 24, 107
MDAC in upgrading, 104
Operating System Command (CmdExec), 201, 211
Operators, in automating with SQL Server Agent, 200, 208-210, 224-225
Optimization
hardware, 297-299
Maintenance Wizard for, 236-237
Oracle
backup commands with, 293
compared to SQL Server 2000, 100
data access with, 152, 153
data transfer with, 197
maintenance commands in, 256-257
monitoring in, 322
security commands in, 358, 359
upgrading database, to SQL Server 2000, 40
osql, 58
in data access
interactive mode, 129, 130, 131
query mode, 129, 130
script mode, 129, 131
in data transfer, 97-98, 129-132, 158, 185, 187
BULK INSERT statement in, 158
OUTPUT qualifier, 494
Ownership Chain, 327
P
Pages, 234, 574
Performance tuning, 299
Perl, 132
Permissions, 327, 328, 330-332, 346-347, 352-354
object, 326, 327, 330-331
re-creating after move, 357-358
statement, 326, 327, 331-332
working with, 346-347, 352-354
Per Processor licensing, 12, 33
Per Seat licensing, 12, 32-33
Phrasings, 501
Physical filename boxes, 276
Physical model, defining, 512-515
Physical objects, mapping entities to, 515
Physical storage, 405
pkzip compression program, 231
Port 1433, 556
Preaggregate, 444
Prepositions in English Query, 501, 506
Profile, 358
Profiler. See SQL 2000 Profiler
Profile Traces, 38
Programmatically, 13
Progress bar, 164
Project, 501
creation of, in English Query, 511-537
Project Wizard, 508, 516-517, 521-522
Protocols, 555-556
Publications, replication and, 362, 367, 396-397
Publishers, replication and, 362, 363, 364, 366, 395-396
Pubs database, 51-52, 65
Pull subscription, 364
Push subscription, 364
Q
Query, against local servers, 98
Query Analyzer, 44, 260, 317, 328
color codes in, 61, 63
in creating and managing database
Log Settings, 123-124
Options, 122-123
in creating an XML document, 491-492
in creating backup device, 289
in creating script, 127
in creating table, 167
in data access, 58-61, 97, 114, 125-129, 130
in data transfer, 185
BULK INSERT statement in, 158, 187
hotkeys available in, 61, 62
menu items for, 63
in monitoring
sp_lock, 319-320
sp_monitor, 318, 321
sp_who, 318-319, 321
recovering database with, 264
sp_help in, 512-513
Query plan, 577-578
Questions in English Query
categorizing, 512
developing, 504, 511-512
R
RAID. See Redundant Array of Independent Disks
RAM, 573, 577-578
memory ant, 298
need for additional, 229
Rapid Application Development (RAD), 405
RDBMS. See Relational Database Management System
RDO. See Remote Data Objects
READ permissions, 330
Recovery models, 260, 261-262
Bulk Logged option, 105, 260, 262, 263
Full option, 105, 260, 262, 263, 279, 291
simple option, 105, 112, 123, 260, 262, 266
Recovery process, 264
Redundant Array of Independent Disks (RAID)
level 1, 298, 579-580
level 5, 298, 575, 577-580
Reference, 44. See also Books Online
REFERENCES, 326, 327, 331
Regression test ing in English Query, 509, 538-544
Relational Database Management System (RDBMS), 4-5, 493
implementation of security in, 325
Relational OLAP (ROLAP), 402, 405, 443-444
Relationships in English Query, 505-507, 515-516
Remote Data Objects (RDO), 13
REPLDATA, 395
repl_freq option, 394
Replication, 361-399
activating, 397-398
agent for, 397
articles in, 362, 367, 397
database in, 396
distributors in, 362, 366, 395
merge, 362, 363, 365
publications in, 362, 367, 396-397
publishers in, 362, 366, 395-396
setting up, using the wizard, 368-393
snapshot, 362, 363, 364
stored procedures in, 394
subscribers in, 362, 367, 398-399
subscription in, 362, 368
transactional, 362, 363, 364-365
two-way, 365
Replication Wizard, setting up replication using, 368-393
Restoration of databases, 271-277
RESTORE command, 289-290
REVOKE, 330, 352, 353-354
ROLAP. See Relational OLAP
Roles, 53
working with, 343-345, 349-352
Rolling back, 7
Root, 473, 487
Rules, 53-54
S
SAN. See System Area Network
Schema, 473
Script, 91
generating, 91-97
Query Analyzer in creating, 127
SCSI format. See Small Computer System Interface format
Security, 325-359
database users in, 328, 340-342, 348-349, 348-352
application roles, 351-352
database roles, 329-330, 343-345, 350-351
in Microsoft Access, 358-359
in Oracle, 358, 359
permissions in, 330-332, 346-347, 352-354
object, 330-331
re-creating, after move, 357-358
statement, 331-332
as reason for implementing instances, 553-554
Server Logins in, 328, 332-340, 348
server groups (roles), 328, 350
setting up database permissions schema, 354-356
SELECT, 326, 327, 352, 353
SELECT INTO statement, 157
for data transfer, 156, 158, 187-188
in importing data from a table based on a condition, 196
SELECT permission, 330
Semantic model in English Query, 501, 504-507, 523
creating relationships, 515-516
mapping entities to physical objects, 515
Send mail, 180
Sequential data, 579
Sequential log file, 103
Server Logins, 328, 332-340, 348
server groups (roles), 328, 350
Server Network Utility, in network configuration, 66-68
Server/per seat (CAL) licensing, 12
Servers, query against local, 98
Server Settings tab, 79, 80
Server users, 53, 56
Service Manager, 57-58
Shortcut keys, 62
Simple Network Management Protocol (SNMP) services, 300
Simple recovery models, 105, 112, 123, 260, 262
Slicers, 459
Slipstreaming, 24
Small Computer System Interface (SCSI) -format, 579
snapshot, 394
Snapshot replication, 362, 363, 364
SNMP services. See Simple Network Management Protocol services
Software RAID, 298
sp_addapprole, 326, 351
sp_addArticle, 394
sp_addDistributor, 394
sp_addlogin, 326
sp_addpublication, 394
sp_addpublication_snapshot, 394
sp_addrole, 350
sp_addrolemember, 326
sp_change_users_login, 357
sp_defaultdb, 349
sp_denylogin, 349
sp_droplogin, 349
sp_droprole, 351
sp_droprolemember, 351
sp_grantdbaccess, 326, 348
sp_grantlogin, 348
sp_help, 512-513, 514
sp_helpdb, 123
sp_helpdbfixedrole, 351
sp_helplogins, 349
sp_helprole, 351
sp_helprolemember, 351
sp_helpuser, 349
sp_lock, 303, 319-320
sp_monitor, 302-303, 318, 321
sp_password, 349
sp_replicationddboption, 394
sp_revokedbaccess, 349
sp_revokelogin, 349
sp_setapprole, 326, 352
sp_validatelogins, 349
sp_who, 303, 318-319, 321
Spreadsheets, using Microsoft Excel to copy data from SQL Server 2000 into, 134
SQL development. See Structured Query Language development
SQL Mail, 201, 203
SQL 2000 Profiler
monitoring tasks of,