Essential SQL Server 2000: An Administration Handbook

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

SQL Profiler

SQL Server Replication

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,



Updates

Submit Errata



More Information

