MCSE: SQL Server 2000 Administration Study Guide

SYBEX Book Index

MCSE: SQL Server™ 2000

Administration Study Guide

Lance Mortensen

Rick Sawtell

Joseph L. Jorden

Index

Copyright © 2001 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. No part of this publication may be stored in a retrieval system, transmitted, or reproduced in any way, including but not limited to photocopy, photograph, magnetic or other record, without the prior agreement and written permission of the publisher.

ISBN: 0-7821-2921-8

SYBEX and the SYBEX logo are either registered trademarks or trademarks of SYBEX Inc. in the USA and other countries. TRADEMARKS: Sybex has attempted throughout this book to distinguish proprietary trademarks from descriptive terms by following the capitalization style used by the manufacturer. Copyrights and trademarks of all products and services listed or described herein are property of their respective owners and companies. All rules and laws pertaining to said copyrights and trademarks are inferred. This document may contain images, text, trademarks, logos, and/or other material owned by third parties. All rights reserved. Such material may not be copied, distributed, transmitted, or stored without the express, prior, written consent of the owner.

The author and publisher have made their best efforts to prepare this book, and the content is based upon final release software whenever possible. Portions of the manuscript may be based upon pre-release versions supplied by software manufacturers. The author and the publisher make no representation or warranties of any kind with regard to the completeness or accuracy of the contents herein and accept no liability of any kind including but not limited to performance, merchantability, fitness for any particular purpose, or any losses or damages of any kind caused or alleged to be caused directly or indirectly from this book.

Index

Note to the reader: Throughout this index boldfaced page numbers indicate primary discussions of a topic.

Italicized page numbers indicate illustrations.

Symbols and Numbers

* operator in SELECT statement, 128–129 2PC (two-phase commit), 516

A

Access

fully qualified name for linking to, 112 linking to database, 118–120

replication to, 537

security configuration, 125–126, 128 account delegation, 276

Action menu (Enterprise Manager), 95
New SQL Server Group, 90
New SQL Server Registration, 91
New Web Assistant Job, 492 Active Directory, 648

Active Directory Users and Computers, 480 Active Script jobs, 457

Active Server Pages, 501–502

ActiveX Data Objects (ADO), 500, 501–502, 648 ad hoc queries, 396–397, 648

Add Counters dialog box, 600, 600

Add Destination Database dialog box, 320

Administrative Tools menu,
Data Sources (ODBC), 71

administrator tasks in SQL Server, 15–16

Advanced Connection Properties dialog box, 405

Advanced Data Connector, 501

Advanced DTS Run dialog box, 425, 426

Advanced Server version of Windows 2000, 10 Agent History Clean Up: Distribution job, 576 alerts, 467–477, 648

creation, 467–472

based on performance condition counter, 471–472

based on SQL Server error messages, 468–470 editing, 472

fail-safe operator for, 488 operator notification about, 478 in Windows System Monitor, 598 aliases, 648

for database tables, 112 to Oracle database, 121

ALTER DATABASE statement, 168 to create filegroup, 197–199 to expand database, 184–185 MODIFY NAME parameter, 191 to remove filegroup, 199–202 syntax, 180–181

anonymous subscribers, 559

ANSI NULL Default option for database, 177 ANSI settings, configuration, 67–69

AppleTalk, SQL Server support, 32 application role, 264–266, 648 archive, 625–626, 648

684 ascending sort order for SELECT statement results – BCP (Bulk Copy Program)

ascending sort order for SELECT statement results, 134

ASCII text

from bulk copy, 390 SQL scripts as, 102

attaching database to server, 192–194 audit, event class security, 278–284 authentication mode, 226–230, 648

Mixed Mode, 228 setting, 229

setting when registering server, 92 Windows Authentication mode, 227–228 Authentication Mode screen (Installation

Wizard), 42

Auto Close option for database, 178

Auto Create Statistics option for database, 178 Auto Shrink option for database, 178

Auto Update Statistics option for database, 177 automatic recovery, 649

autonomy for replicated data, 518 autowrap for log, 68

Average Disk Queue counter, 628

B

B-Tree format, 164, 649 BackOffice, 8, 649

BACKUP DATABASE statement, 335, 349 for full backup, 341–342

backup device, 649

backup or restoring to, 337–340 information, 355–356

backup domain controller (BDC), 649 Backup folder, 54

BACKUP TRANSACTION statement, 346

backups, 334–356. See also Database Maintenance Plan Wizard; restoring databases

to backup device, 337–340 differential backups, 335, 348–350 filegroup backups, 335, 350–352 to files, 340 full backup, 334, 341–344 location in system, 157 of Msdb database, 451

parallel striped backup, 344–345, 668 plan development, 307–309

frequency, 307–308 media, 308

nonlogged operations backup, 309 staff responsibility, 308

verification, 309 reasons for, 306–307 replication, 572 scheduling, 352–354

transaction log backup, 334–335, 345–348 baseline for performance measurement, 625, 634 batch files

to run BCP, 391

for unattended installs, 49

BCP (Bulk Copy Program), 141, 384–391, 649 and backups, 309

vs. BULK INSERT statement, 392 to create output file, 391

BDC (backup domain controller) – client/server 685

disadvantages, 385 parameters, 387–389 syntax, 386

BDC (backup domain controller), 649 BEGIN TRANSACTION statement, 326 binary sort order, 31

Binn folder, 54

Books Online, 141, 675

broken ownership chain, 250, 273, 274

avoiding, 274 browser, 680

Buffer Cache Hit Ratio counter, 606 and memory requirements, 629

Buffer Manager -- Free Buffers Windows System Monitor counter, 628

Builtin\Administrators, 244

builtin groups, Windows login for, 234–235 Bulk Copy Program (BCP). See BCP

(Bulk Copy Program)

BULK INSERT statement, 391, 392–397, 649 to load data, 394–395

parameters, 393–394 syntax, 392

Bulk-Logged recovery mode, 177, 333, 649 bulkadmin role, 245

C

C2 auditing, 284 caching, 650

pages and, 160

caching controller, and RAID, 158

Cartesian products, 133–134, 134

case sensitivity in sort order, 31

catalog, name for remote server link, 114

CD-ROM, SQL Server 2000 Enterprise Evaluation version, 37

central publisher/central distributor, 532–533, 533

central publisher/remote distributor, 533, 534

central subscriber/multiple publishers, 534, 534–535

CGI (common gateway interface), 500 Char datatype, 162

character mode for bulk copy, 390 character sets, 30, 650 CHECKALLOC command (DBCC), 139, 325 CHECKCATALOG command (DBCC), 139, 325 CHECKDB command (DBCC), 139, 325 CHECKFILEGROUP command (DBCC), 139 checkpoints, 326–328, 650

Choose a Data Source screen (DTS Import/Export Wizard), 405, 415

Choose a Destination screen (DTS Import/Export Wizard), 406, 416

Choose Destination Database screen (Pull Sub-scription Wizard), 563

Choose Licensing Mode screen (Installation Wizard), 44

Choose Publication Database screen (Create Publication Wizard), 551

Choose Publication screen (Pull Subscription Wizard), 562

cleanup job, 576

Client Network utility, 136, 136–137, 650 client/server, 650

686 client/server application – Create and Manage Publications window

client/server application, 650 clients, 650

installing SQL Server, 69–74 for Oracle, 120

storing queries on, 206 troubleshooting tools on, 141 closing database, auto close option, 178 clustered index, 205, 631, 651

CmdExec jobs, 457 collation, 30–31, 167, 651

Collation Settings screen (Installation Wizard), 43

column, 651

Column Mappings and Transformations dialog box Column Mappings tab, 410

Transformation tab, 411

Column object, 5

COM (Component Object Model), 651 objects, 398

commit, 651

COMMIT TRANSACTION statement, 326 common gateway interface (CGI), 500 Compatibility Level option for database, 178 Component Object Model (COM), 651

OLE-DB provider as object, 109 Computer Management, 309 computer name, 651

Computer Name screen (Installation Wizard), 38

concurrency, 651

configuration utilities, 135–137. See also Enterprise Manager

SQL Client Network utility, 136, 136–137, 650 SQL Setup program, 135–136

Configure Publishing and Distribution Wizard Customize the Configuration screen, 544

Enable Publication Databases screen, 545–546, 546

Enable Publishers screen, 544, 544 Enable Subscribers screen, 546

Provide Distribution Database Information screen, 544

Select Distributor screen, 542, 542–543 Specify Snapshot Folder screen, 543, 543 Welcome screen, 541

configuring SQL Server 2000, 58–67 ANSI settings, 67–69

internal settings, 59–65

network protocol settings, 65–67 conflict resolution in merge replication, 527 connection objects, 401

connections, 63 constraints, 651

and bulk copy, 390 creation, 204–205 precedence, 400, 401 Control Panel, 651 convergence, 522, 651

Copy Database Wizard, 329–333 Database File Location screen, 331 Schedule the DTS package screen, 332 Select Databases to Move or Copy screen, 331 Select Related Objects screen, 332

Welcome screen, 330

copying. See also BCP (Bulk Copy Program) database using Transfer Manager, 414–419 counter logs in Windows System Monitor, 598 counters, 651

in Windows System Monitor, 606–607 Create and Manage Publications window, 549

CREATE DATABASE statement – Data Transformation Services 687

CREATE DATABASE statement, 165–170 syntax, 165–166

CREATE INDEX statement, 206 Create New Data Source dialog box, 72 CREATE PROCEDURE statement, 207–208 Create Publication Wizard, 548–560

Choose Publication Database screen, 551 Customize the Properties of the Publication

screen, 556 Filter Data screen, 557

Filter Table Columns screen, 557 Filter Table Rows screen, 558

Select Publication Name and Description screen, 555, 556

Select Publication Type screen, 551

Set Snapshot Agent Schedule screen, 559, 559 Specify Articles screen, 554

Specify Subscriber Types screen, 553 Transform Published Data screen, 553 Updatable Subscriptions screen, 552 Welcome screen, 550

Create Table SQL Statement dialog box, 411 CREATE TABLE statement, 203

to create constraints, 204 CREATE TRIGGER statement, 209 CREATE VIEW statement, 211 cross join, 133–134, 134, 652 .csv file extension, 385 cursor

closing after completed transaction, 179 creating local, 180

custom database role, 260–264 in security plan, 287 custom setup routine, 36

Customize the Configuration screen (Configure Publishing and Distribution Wizard), 544 Customize the Properties of the Publication screen

(Create Publication Wizard), 556

D

Data Access Objects (DAO), 652 data archiving, 625–626

data cache, 652

data control language, 652

Data Definition Language (DDL), 9, 652 data file connection object, 401

data files, 155, 652 data flow, 402 Data folder, 54

Data Manipulation Language (DML), 9 data mart, 652

Data menu (Excel),
Get External Data,
Create New Query, 103

data objects, packages as, 401 data pages, 160. See also pages data pump object, 402 data source

Access parameter, 118 for MSQuery, 104, 104

name for remote server link, 114 data source connection object, 401 Data Source Name (DSN), 652 data streams, packages as, 401

Data Transformation Services, 397–426, 652 DTS Designer, 421–422

688 data warehousing – Database Maintenance Plan Wizard

DTS package, 399–402 connection objects, 401 data flow, 402

data pump object, 402 step objects, 400–401 task objects, 399

DTS storage and security, 403 DTSRUN, 422–426

Import and Export Wizards, 404–413 Transfer Manager, 414–420

wizards, 94 data warehousing, 652

vs. transaction processing, 7 database, 4–6. See also filegroup

access in security plan, 285 altering, 180–191

expansion, 181–185 shrinking, 185–191

attaching and detaching, 192–194 common objects, 5

copying using Transfer Manager, 414–419 creation

using Enterprise Manager, 170–173 using T-SQL, 165–170

data manipulation from multiple, 108 dropping, 194

files, 155–156, 653 attempt to rename, 336 location, 156–157 real world scenario, 158 granting access, 242

information gathering, 173–176

with Enterprise Manager, 173–174, 174 with stored procedure, 174–175 management with options, 176–180

name change, 191–192 non-client/server system, 2, 3 offline, 180

planning, 154–156 relational vs. flat-file, 4–6

storage requirement estimates, 161–165 index requirements, 164–165 pages needed calculation, 164 row estimate for table, 163 row size calculation, 161–162 rows per page calculation, 163 setting maximum, 169 storage structures, 159–160

extents, 159, 656 pages, 4, 159–160, 668 upgrading, 57

warehousing vs. transaction processing, 7 Web pages to display, 7

database connectors, installing for Office, 103 database consistency checker (DBCC), 325–326,

653. See also DBCC (database consistency checker)

Database File Location screen (Copy Database Wizard), 331

database files, 653 attempt to rename, 336

Database Maintenance Plan Wizard, 357–361, 458, 653

for backup scheduling, 357–361 Database Integrity Check screen, 358

Initialize the Destination Databases screen, 321 for log shipping, 316

Log Shipping Schedules screen, 322 Log Shipping Thresholds screen, 322 Maintenance Plan History screen, 360

database management system (DBMS) – DBO (Database Owner) 689

Select Databases screen, 318, 357

Specify Backup Disk Directory screen, 359 Specify the Database Backup Plan screen, 359 Specify the Log Shipping Destinations screen, 321 Specify the Log Shipping Monitor Server

Information screen, 323

Specify the Transaction Log Share screen, 319 Specify Transaction Log Backup Disk Directory

screen, 319

Update Data Optimization Information screen, 358

database management system (DBMS), 653 Database Object Owner (DBOO), 654 database objects constraints, 204–205 indexes, 205–206 stored procedures, 206–208 tables, 202–203 triggers, 208–209 views, 210–211

Database Owner (DBO), 653 database Properties dialog box

Data Files tab, 172, 182 General tab, 174, 183

Options tab, 177–178, 317, 334 Transaction Log tab, 173

Database Role Properties dialog box - New Role, 261 database roles, 257–266

custom database role, 260–264 fixed database role, 257–260 database servers

heterogeneous, 536–539 installing, 38

database tables creation, 202–203

importing with DTS Import Wizard, 404–413 referencing on linked server, 112

database user, 653

database user accounts, 247–250

Database User Properties - New User dialog box,

248

database wizards, 94

Datacenter version of Windows 2000, 10 datatype, 653

storage requirement estimates, 161 Datatype object, 5

Datetime datatype, 162 DB-Library, 97, 653 db_accessadmin role, 258 db_backupoperator role, 258

DBCC (database consistency checker), 325–326, 653

commands, 138–139

DBREINDEX command, 634 SHOWCONTIG command, 633

SHRINKDATABASE command, 185–189 TRUNCATEONLY and NOTRUNCATE

options, 187, 187 SHRINKFILE command, 188 dbcreator role, 177, 244 db_datareader role, 258 db_datawriter role, 258 db_ddladmin role, 258 db_denydatareader role, 258 db_denydatawriter role, 258

DBMS (database management system), 653 DBO (Database Owner), 249, 653

690 DBOO (Database Object Owner) – distribution server, installing

DBOO (Database Object Owner), 654 db_owner role, 177, 258

DBREINDEX command (DBCC), 634 db_securityadmin role, 258

DDL (Data Definition Language), 9, 652 deadlocks, 632

decision support systems, 652, 654 DecNet, SQL Server support, 32 default, 654

default database, for login, 242 default filegroup, 195

default instances, 34, 654 Default object, 6

default SQL Server groups, opening, 90 default Unicode support, 31

defragmentation, 633–634 DELETE object permission, 254 deleting

jobs, 467 queries, 103

Web publishing job, 498 DENY SELECT statement, 272 DENY state, 267–273

descending sort order, for SELECT statement results, 134

Desktop, 654

detaching database from server, 192–194 developer tasks in SQL Server, 14–15 Developer version of SQL Server, 12 DHTML (Dynamic HTML), 501 Dictionary sort order, 31

differential backups, 335, 348–350, 654 restoring, 366

disabling jobs, 467

disaster recovery plan. See also backups restoring databases, 362–370

SQL Server for data protection, 324–370 backup modes, 333–334

Copy Database Wizard, 329–333 DBCC (database consistency checker),

325–326

transactions and checkpoints, 326–328 Windows and hardware for SQL Server

protection, 309–324 clustering, 315

RAID (Redundant Array of Inexpensive Disks), 310–315

standby servers, 315–324 disconnected recordset, 501 disk drives

counter and value, 599

space for database tables, 161–165 disk duplexing, 310–311, 311 disk mirroring, 156, 310, 310, 671 disk striping, 156

diskadmin role, 244

distributed query, 108, 276, 654 access to multiple servers, 275 what it is, 109–113

Distributed Transaction Coordinator (DTC), 52, 654, 655

distributed transactions, 520 vs. replication, 515 distribution agent, 524, 530

Distribution Clean Up: Distribution job, 576 Distribution database, rebuilding, 369 distribution server, installing, 541–548

distributor – Enable Publishers screen (Configure Publishing and Distribution Wizard) 691

distributor, 516, 571, 655 backups, 572

divide-by-zero error, 180 DLL (dynamic link library), 655 DML (Data Manipulation Language), 9 documentation, 141

domain, 655

domain controller, 655

DRI (Declarative Referential Integrity) object permission, 255

drives for duplexing, 311

DROP DATABASE statement, 194, 363 dropping database, 194

DSN (Data Source Name), 652

DTC (Distributed Transaction Coordinator), 52, 654, 655

DTS. See Data Transformation Services DTS Designer, 398, 421–422, 655 DTS Import/Export Wizard, 404–413

Choose a Data Source screen, 405, 415 Choose a Destination screen, 406, 416 Finish screen, 413

Save DTS Package screen, 413

Save, schedule, and replicate package screen, 412 Select Columns screen, 408

Select Objects to Copy screen, 416–417, 417 Select Source Tables and Views screen, 410 Specify Query Criteria screen, 409

Specify Sort Order screen, 408

Specify Table Copy or Query screen, 406 Type SQL Statement screen, 407, 409 DTS Operator password, 401

DTS Owner password, 401

DTS package, 398, 399–402, 655 connection objects, 401 data flow, 402

data pump object, 402

DTS Designer to create, 421–422, 422 DTSRUN utility, 422–426

syntax, 422–423 step objects, 400–401 DTS storage and security, 403

DTSRUN utility, 141, 398, 422–426, 655 DTSRUNUI utility, 141, 424–426, 655 DTSWIZ utility, 141, 398

DUMP DATABASE statement, 342 dump device. See backup device dynamic backup, 655

dynamic link library (DLL), 655 dynamic pages, from Internet Database

Connector, 499

E

e-mail, testing, 485–487

Edit Recurring Job Schedule dialog box, 353, 462 Edit Schedule dialog box, 354

Edit SQL Server Message dialog box, 474 electronic mail, 656

Enable Publication Databases screen (Configure Publishing and Distribution Wizard), 545–546, 546

Enable Publishers screen (Configure Publishing and Distribution Wizard), 544, 544

692 Enable Subscribers screen (Configure Publishing and Distribution Wizard) – events

Enable Subscribers screen (Configure Publishing and Distribution Wizard), 546

English Query, 74–75

Enterprise Manager, 88, 89–93, 656 to access remote Access database, 126 Action menu, 95

to assign fixed database role to user, 258–259 to assign fixed server role to user, 245–246 to attach and detach database, 193 for backup device information, 356 to create and test standard login, 231–232 to create application role, 264–266 to create backup device, 338–339 to create constraints, 204

to create custom database role, 261–262 to create database, 170–173, 171 to create database table, 203

to create database user account, 247–249 to create filegroup, 196–197

to create index, 205–206 to create job, 459–462

to create link to Access database, 119 to create linked server, 115–116, 118

to create server group and register to SQL Server, 90–92

to create stored procedure, 207 to create triggers, 208–209 to create view, 210–211

to create Windows-to-SQL login, 235–240 for database information, 173–174, 174 for database options, 176, 176

for differential backup, 349–350, 349–350 to drop database, 194

error log, 327

to examine backup devices, 356 to expand database, 181–183 for filegroup backup, 351–352 for full backup, 343–344

to grant, revoke and deny permissions, 268–271 to grant statement permissions, 251–253 job history in, 465, 465–466

Management folder, 458, 459 Web Publishing folder, 492

to map login IDs to remote server, 125–126 to monitor log shipping, 324, 324

refresh to display database changes, 179 Replication Monitor, 548

to restore database, 363, 364–366 to restore transaction log, 366–368 to run jobs manually, 464

to set authentication mode, 229 to shrink databases, 189–191 to test installation, 50–51, 51 Tools menu, 96

for transaction log backup, 347–348 wizards, 93, 93–94

enterprise network, 656

Enterprise version of SQL Server, 12 Evaluation version, 37

.err file extension, 385

error log for automatic recovery entries, 327–328 error messages

alert based on, 468–470 editing and creating, 473–475 event classes, 278, 656

security audit, 278–284 events, 656

exam objectives – filter 693

monitoring, 278 trace for, 611 exam objectives

configuring network libraries, 66, 136 data import and export, 397

database configuration for performance, 155 database manipulation, 165, 195

database permissions, 250 database user management, 247

disaster recovery operations, 316, 335, 362 hardware resource usage, 597

IIS (Internet Information Server) virtual direc-tories, 427, 490 installing SQL Server, 28 integrity checks, 138, 325, 357 linked servers, 113, 275 login management, 230 object management, 202 security auditing, 277 security management, 273 security modes, 226

security role management, 243, 257 SQL Server Agent, 467, 478 SQL Server Agent jobs, 456

SQL Server system troubleshooting, 611 troubleshooting SQL Server, 626 upgrading to SQL Server 2000, 54 Exchange (Microsoft), 663

exclusive locks, 632

EXECUTE object permission, 255 Execution plan for query, 102, 102

forcing creation of new, 630–631 expanding database, 181–185

with Enterprise Manager, 181–183

with T-SQL, 184–185

Expired Subscription Clean Up job, 576 Explorer, 656

Export Wizard. See Import and Export Wizards exporting with BCP, 385

extended stored procedure. See stored procedures eXtensible Markup Language (XML), 656 extents, 159, 656

calculating requirements, 164 extranet, 656

F

fail-safe operators, defining, 488 Fast BCP, 657

FAT (file allocation table), 657 field in database table, 202–203, 657 file allocation table (FAT), 657

file extensions, for BCP export results, 385 filegroup, 185, 195–202, 657

advantages and disadvantages, 195 backups, 335, 350–352, 657 creation

with Enterprise Manager, 196–197 with T-SQL, 197–199

removing, 199–202 restoring, 366

files, backup to and restoring from, 340 fill factor, 163, 657

filter, 657

in MSQuery, 106 for trace data, 615–618

694 Filter Data screen (Create Publication Wizard) – I/O (input/output)

Filter Data screen (Create Publication Wizard), 557 Filter Table Columns screen (Create Publication

Wizard), 557

Filter Table Rows screen (Create Publication Wizard), 558

Finish screen (DTS Import/Export Wizard), 413 Finish screen (Index Tuning Wizard), 624 fixed database role, 257–260, 657 fixed server roles, 230, 243–246, 657

assigning user to, 245–246 in security plan, 285 flat-file database, 657 relational database vs., 4–6 Float datatype, 162 .fmt file extension, 385 folders

for prebuilt SQL scripts, 102 for SQL Server, 53–54, 94–95, 95 forwarding events, 475–477

fragmentation, 633

frequency of backup, 307–308 full backup, 334, 341–344, 657 Full recovery mode, 333, 658 full recovery model for database, 177 fully qualified name for database tables, 112

G

Generate SQL Script dialog box, 578 Global Allocation Map pages, 160 GRANT SELECT statement, 271 GRANT state, 267–273

GRANT statement, 253

GRANT UPDATE statement, 263

group Properties dialog box, Members tab, 237 groups, 658

Windows login for, 234–235 growth of database, planning for, 163 guest user, 249, 658

removing permissions from, 252, 252 in security plan, 286

GUID, 527

H

hardware RAID, 158 vs. software RAID, 314

hardware requirements for SQL Server, 29 HDR (Host Data Replicator), 537 heterogeneous database servers, 536–539 heterogeneous queries, 396–397, 658 historical data, data warehousing for, 7 Hkey_Local_Machine/Software/Microsoft, 53 Hkey_Users key, 53

horizontal partitioning, 130, 517, 517, 658 Host Data Replicator (HDR), 537

.htx file extension, 500

Hypertext Markup Language (HTML), 658 Hypertext Transfer Protocol (HTTP), 658

I

IBM DRDA – installing SQL Server 695

IBM DRDA (Distributed Relational Database Architecture) data protocol, 537

IBM SEQUEL, 8 .idc file extension, 500 IDE, 659

IIS (Internet Information Server). See Internet Information Server (IIS)

immediate updating subscribers, 552, 571 snapshot replication with, 522 transactional replication with, 521 Import and Export Wizards, 398, 404–413 importing with bulk copy, 390

Index Allocation Map pages, 160 index pages, 160

Index Recommendations screen (Index Tuning Wizard), 624

Index Tuning Wizard, 621–624, 659 Finish screen, 624

Index Recommendations screen, 624 Select Server and Database screen, 622 Select Tables to Tune screen, 623 Specify Workload screen, 622 Welcome screen, 621

Indexed Sequential Access Method (ISAM), 659 indexes, 6, 659

creation, 205–206

estimating storage requirements, 164–165 optimization, 631

WHERE clause and, 131

Industry Standard Architecture (ISA), 659 initialization files for unattended installs, 49 Initialize Subscription screen (Pull Subscription

Wizard), 564

inner join, 131, 131–132, 659

input/output (I/O), 660 INSERT object permission, 254 Install folder, 54

Installation Definition screen (Installation Wizard), 40

installation path for SQL Server, 34

Installation Selection screen (Installation Wizard), 39, 39

Installation Wizard

Authentication Mode screen, 42, 47 Choose Licensing Mode screen, 44 Collation Settings screen, 43, 47 Computer Name screen, 38 Installation Definition screen, 40 Installation Selection screen, 39, 39

for named instance, 46, 46 Instance Name screen, 40, 47, 47 for named instance, 45–48

Network Libraries screen, 43, 48, 48 Select Components screen, 41, 47 Services Accounts screen, 42, 47 Setup Type screen, 41, 47 Start Copying Files screen, 44 User Information screen, 39 installing

distribution server, 541–548 linked servers, 113–114

MAPI support for Windows NT and 2000, 479, 480–485 installing SQL Server, 28–35 clients, 69–74 path, 53–54 preparations collation, 30–31

696 Instance Name screen (Installation Wizard) – jobs

component selection, 35 default and named instances, 34 hardware requirements, 29 installation path, 34 license mode, 30 network libraries, 32 software requirements, 29–30 sort order, 31 Unicode support, 31

upgrade vs. side-by-side install, 35 user accounts, 33

Registry settings, 53 second instance, 45–48 steps, 37–45

testing and troubleshooting, 50–51 running query, 51–52

unattended, 49

Instance Name screen (Installation Wizard), 40 instances

default and named, 34

installing second for SQL Server, 45–48 Instcat.sql query, 117

Instmsdb.sql script, 451 Int datatype, 161 integrity checks, 138 interactive Web pages

creation, 498–502

with ActiveX Data Objects and Active Server Pages, 501–502

with Internet Database Connector, 499–500 with Remote Data Services, 501

vs. static, 489–490 Internet, 659

and SQL Server, 489–498

interactive Web page creation, 498–502

SQL Web Assistant, 491–498

static vs. interactive Web pages, 489–490 XML support configuration, 490–491 Internet Database Connector, 499–500 Internet Explorer, 660

Internet Information Server (IIS), 660

Configure SQL XML Support, 427, 428–434 virtual directory for SQL Server, 490 Internet Protocol (IP), 660

Internet Services Provider (ISP), 660 Internetwork Packet eXchange (IPX), 660 interprocess communication channel (IPC), 660 intranet, 660

IP (Internet Protocol), 660 IP address, 661

IPC (interprocess communication channel), 660 IPX (Internetwork Packet eXchange), 660 IPX/SPX, SQL Server support, 32 ISA (Industry Standard Architecture), 659 ISAM (Indexed Sequential Access Method), 659 ISAPI (Internet Server Application Programming

Interface), Internet Database Connector as, 499 ISP (Internet Services Provider), 660

ISQL (Interactive SQL), 97 ISQLW utility, 117

J

JavaScript, 502 Jet data engine, 661 Job Wizard, 458 jobs, 456–467, 661

Jobs folder – log shipping 697

deleting, 467 disabling, 467 editing, 463

multiserver jobs, 457

operator notification about, 478 running manually, 464

scheduling, 458–459 viewing history, 465–466 for Web pages, 497 Jobs folder, 54

joining tables in SELECT statement, 131–134 cross joins, 133–134, 134, 652 inner join, 131, 131–132, 659 outer join, 133, 133, 667

K

L

latency of replicated data, 518 LazyWriter, 628–629, 662 .LDF file extension, 155 left outer join, 133 LFN (long filename), 662 license mode, 30

linked login, 277

Linked Server Properties dialog box Security tab,

124, 124

for unsecured Access database, 126

Linked Server Properties - New Linked Server dialog box

for Access database, 119 for Oracle connection, 122 for remote server link, 116 linked servers, 110, 662

for heterogeneous queries, 396 installing and configuring, 113–114 referencing tables on, 112

security, 123–128

login mapping with Enterprise Manager, 123–128

login mapping with Query Analyzer, 127–128 setup to Access database, 118–120

setup to Oracle database server, 120–123 setup to remote SQL Server 2000 database server,

115–117

setup to remote SQL Server 6.5 database server, 117–118

what it is, 108–109

LOAD DATABASE statement, 362 LOAD TRANSACTION statement, 362 local group, 662

local queries, 96–107

local server, for server group creation, 89 location, name for remote server link, 114 lock escalation, 662

locks, 662

optimization, 631–632 log autowrap, 68

log files, created by transfer process, 419–420 Log folder, 54

698 logging – Money datatype

logging, 662

with Windows System Monitor, 603–606 login, 662

common items, 242–243 server login, 230–243 Windows login, 234–242 Logon as a Service right, 33 logreader agent, 524, 529, 530 long filename (LFN), 662

Look for Publications screen (Pull Subscription Wizard), 562

M

mailbox for SQL user account, 481 Makepipe, 139–140, 663

Management folder, 458, 459 Management wizards, 94

MAPI (Messaging Application Programming Interface), 663

enabling for e-mail, 478–485

installing support on SQL Server, 480–485 testing configuration, 485–487

Master database, 663 frequency of backup, 307 and replication, 531

restoring with valid backup, 368–369 restoring without backup, 370 master server, for multiserver jobs, 457 Max Async I/O, 628, 663

max server memory, 630, 663 .MDF file extension, 155

MDI (Multiple document interface), 99 measurement baseline, 625, 634, 663 media for backup, 308

memory

counters and values, 598–599 manual configuration, 630 optimization, 629–630

merchant services, interactive Web pages for, 490 merge agent, 524

merge replication, 522–523, 525–527, 526 conflict resolution in, 527

Messaging Application Programming Interface (MAPI), 663

Meta Data Services, DTS package storage in, 401 Microsoft Distributed Transaction Coordinator

(MS-DTC), 516 Microsoft Exchange, 663

Microsoft Management Console (MMC), 664 Microsoft Outlook Setup Wizard, 482, 482–483 Microsoft Query, 663

Microsoft Search, 53

Microsoft Transaction Server, 490 min server memory, 630

Miscellaneous Agents folder, 576 mission-critical systems, RAID for, 157 mixed extents, 159

Mixed Mode, 228, 663 mixed security, 663

MMC (Microsoft Management Console), 664 Model database, 155, 664

frequency of backup, 308 rebuilding, 369

and replication, 531 Money datatype, 162

monitoring SQL Server – nonclustered index 699

monitoring SQL Server, 596. See also Windows System Monitor

with Query Analyzer, 607–610 security, 277–284

with SQL Profiler, 610–624 tips and techniques, 625–626

data archiving and trend tracking, 625–626 measurement baseline, 625–626

MS-DTC (Microsoft Distributed Transaction Coordinator), 516

Msdb database, 450–451, 664 DTS package storage in, 403 frequency of backup, 308 rebuilding, 369, 451 and replication, 531 MSQuery, 103–107 filter in, 106 MSSQLSERVER, 35, 53

Multiple document interface (MDI), 99 multiprocessing, 664 Multiprotocol standard, 664 SQL Server support, 32 multiserver jobs, 457 multitasking, 665 preemptive, 669 multithreaded, 665

N

real world scenario, 49 Named Pipes, 665

SQL Server support, 32 Windows 9x and, 89 names

for remoted SQL Server 2000 computer, 115 for server group, 90

native API, 665

Native mode for bulk copy, 390 .NDF file extension, 155 nesting roles, 261, 666 .NET server applications, 8 network libraries, 32

Network Libraries screen (Installation Wizard),

43, 48, 48

network operating system, 666

network segment, counter and value, 599 New Alert Properties dialog box

General tab, 469 Response tab, 470 New Job Step dialog box

Advanced tab, 461 General tab, 460

New Object - User dialog box, 480 New Operator Properties dialog box, 486 New SQL Server Message dialog box, 474 New Virtual Directory Properties dialog box

Advanced tab, 433 Data Source tab, 430, 430 General tab, 428

Security tab, 429, 429 Settings tab, 431, 431, 491 Virtual Names tab, 432 nonclustered index, 205, 631, 666

700 nonlogged operations backup – Oracle

nonlogged operations backup, 309 nontrusted connection, 228, 228, 666 normalizing the database, 4, 666

NT Authentication, mapping SQL Server login ID, 127

NT Event Viewer, 666 NTFS, 666

numbers, error from loss of precision, 180

O

Object Linking and Embedding DataBase (OLE-DB), 500, 667

object permissions, 254–257, 666 objects, 4, 666

monitoring, 598

ODBC (Open Database Connectivity), 667 ODBC connections, 500

configuration, 71–74 and named instances, 45 for replication, 537 ODBC Data Sources utility, 70 Office 97, MSQuery with, 103–107 OLAP (Online Analytical Processing) server

installing, 75

snapshot replication for, 521

OLE-DB (Object Linking and Embedding DataBase), 500, 667

OLE-DB provider

to access linked server, 108–109 name for remote server link, 114 for Oracle, 121

for replication, 537 testing, 114

OLTP (Online Transaction Processing), 7, 667 server performance, 533

on demand synchronization, 565

Online Transaction Processing (OLTP), 7, 667 server performance, 533

Open Database Connectivity (ODBC), 667 OPENDATASOURCE statement, 396 OPENROWSET statement, 396–397 operator Properties dialog box, Notifications

tab, 487 operators, 667

creating and managing, 478–488 creating and testing e-mail, 485–487 defining fail-safe, 488 optimization, 626–634, 667 defragmentation, 633–634 indexing, 631 LazyWriter, 628–629 locks, 631–632 Max Async I/O, 628 memory, 629–630

queries and stored procedures, 627 Query Governor, 627–628

RAID (Redundant Array of Inexpensive Disks), 629

stored procedures, 630–631 Tempdb database, 627 Oracle

communicating with server, 121 developer version for download, 122 fully qualified name for linking to, 112 linking to database, 120–123

OSQL (ODBC SQL) utility – Provide Distribution Database Information screen 701

OSQL (ODBC SQL) utility, 97–98, 667 outer join, 133, 133, 667

overhead for database table row size estimate, 162 ownership chain, 273–275, 668

P

Page Free Space page, 160 Page Reads/Sec counter, 607 Page Writes/Sec counter, 607 pages, 4, 159–160, 633, 668 paging services, 478

parallel striped backup, 341, 344–345, 668 parity checksums, 157

parity, striping with, 157, 312–314, 313, 671, 676 password

for DTS Owner and DTS Operator, 401 to log in to SQL server, 97

login for remote server, 123 for SQL Server Agent, 454 for SQL Server service account, 33 PCI (Peripheral Connection Interface), 668 PDC (primary domain controller), 669 PDK (Program Developers' Kit), 669 per-processor licensing, 30

per-seat licensing, 30, 668 per-server licensing, 668

performance. See also optimization baseline for measurement, 625 replication and counters for, 580

Performance Condition counter, for alert, 468, 471 Performance Monitor, 668

Peripheral Connection Interface (PCI), 668 permissions, 250–257, 669

for BCP, 385

GRANT, REVOKE, DENY states, 267–273 object permissions, 254–257

in security plan, 286

statement permissions, 250–254 Personal version of SQL Server, 11

Physical Disk: Average Disk Queue Windows System Monitor counter, 629

point-in-time recovery, 346, 368 precedence constraints, 400, 401 preemptive multitasking, 669

primary domain controller (PDC), 669 primary filegroup, 195

primary key, in central subscriber/multiple publisher model, 535

priority for SQL Server, 61 proactive troubleshooting, 626 procedure cache, 669

process, 669

processadmin role, 244

Processor object, counter and value, 598 product name, for remote server link, 113 Professional Windows 2000, 10

Profiler utility, 141, 278

to monitor login failures, 280–283, 283 Program Developers' Kit (PDK), 669 protocols

for client/server communication, 136 configuring SQL network settings, 65–67 Provide Distribution Database Information screen

(Configure Publishing and Distribution Wizard), 544

702 provider string, name for remote server link – RAID

provider string, name for remote server link, 114 public access in security plan, 286

public role, 669

removing permissions from, 251 publication, 516, 670

publication database, enabling, 546 publications in replication, 517–518 publisher, 516, 670

backups, 572

central with central distributor, 532–533, 533 central with remote distributor, 533, 534 database as, 180

multiple, with central subscriber, 534, 534–535 multiple, with multiple subscribers,

535–536, 536

publisher/subscriber metaphor for replication, 515–518

articles, 516–517 publications, 517–518

Pubs database, frequency of backup, 308 pull page, 498, 670

Pull Subscription Properties sheet, 567 Pull Subscription to... screen, 561 Pull Subscription Wizard, 560–567

Choose Destination Database screen, 563 Choose Publication screen, 562

Initialize Subscription screen, 564 Look for Publications screen, 562

Set Distribution Agent Schedule screen, 565 Snapshot Delivery screen, 564

Start Required Services screen, 566 pull subscriptions, 524, 571

merge agent for, 525 over the Internet, 538 for snapshot replication, 528 push page, 492, 670

push subscriptions, 523 merge agent for, 525 over the Internet, 538 for snapshot replication, 528

Q

queries, 670

English Query for converting to SQL, 74–75 heterogeneous, 396–397 local, 96–107 optimization, 627 remote, 108–114 Query Analyzer, 97, 98–103, 608, 610, 670 for analysis, 607–610

to create linked server, 116–117 to link SQL Server 6.5, 118 to link to Access database, 120 login screen, 99

to map login IDs to remote server, 127–128 results display, 100

to test installation, 51–52, 52 Query Governor, 627–628, 670

Query menu,
Show Execution Plan, 102 query optimizer, 670

R

RAID (Redundant Array of Inexpensive Disks), 156–157, 310–315, 629, 670–671

hardware-based solution, 158 optimization, 629

Raiserror command – replication 703

Raiserror command, 473 RAM

settings for SQL Server, 60 SQL Server requirements, 29

RDBMS (relational database management system), 672

RDO (Remote Data Objects), 671 RDS DataFactory, 501

RDS (Remote Data Services), 501 read-ahead, 671

Read-only option for database, 177 Readpipe, 139–140, 671

Real datatype, 161 Rebuildm.exe, 370, 671

record in database table, 203, 671 recovery model for database, 177 recurring job, 459

Recursive Triggers option for database, 177 Redundant Array of Inexpensive Disks (RAID). See

RAID (Redundant Array of Inexpensive Disks) REFERENCES object permission, 255

referential integrity, 517 Register SQL Server Wizard, 91

Registered SQL Server Properties dialog box, 92 registration of server, 91–92

Registry, 89, 671

copying settings to create server group, 89 SQL Server settings, 53

Reinitialize Subscriptions Having Data Validation Failures job, 576

relational database, 672 vs. flat-file database, 4–6

relational database management system (RDBMS), 672

Remote Agent Activation, 524 Remote Data Objects (RDO), 671 Remote Data Services (RDS), 501 remote distributor, 571

remote installation, 36

Remote Procedure Calls (RPC), 672 remote queries, 108–114

remote server. See also linked servers creating link to, 113–114 removable media database, 672 Replay SQL Server dialog box, 619 Repldata folder, 54

replication, 514, 672 database options for, 180 on demand synchronization, 565 distribution types, 519, 519–523 distributed transactions, 520 merge replication, 522–523 snapshot replication, 521–522

snapshot replication with immediate updating subscribers, 522

transactional replication, 520

transactional replication with immediate updating subscribers, 521 factors, 518–519 installation adding publication, 548–560 creating subscription, 560–567 distribution server, 541–548 SQL Server configuration, 540–541 testing, 567–570 internals, 523–532 agents, 524 merge replication, 525–527, 526

704 Replication Agents Checkup job – scheduling snapshot replication, 528, 529 transactional replication, 529–532, 530 management, 570–580 administrative issues, 571–572 backups, 572

and performance counters, 580 Replication Monitor, 573–576

replication of stored procedures, 579–580 scripts, 577–579

models, 532–536

central publisher/central distributor, 532–533 central publisher/remote distributor, 533, 534 central subscriber/multiple publishers, 534,

534–535

determining which to use, 538

multiple publishers/multiple subscribers, 535–536, 536

over the Internet, 536–539 heterogeneous replication, 537 Internet replication, 538 overview, 515 publisher/subscriber metaphor, 515–518 articles, 516–517 publications, 517–518 real world scenario, 539 of stored procedures, 579–580 Replication Agents Checkup job, 576

Replication Component Script Preview screen, 579 Replication jobs, 457

Replication Monitor, 573–576 Snapshot Agents in, 573 Replication wizards, 94

Restore Database dialog box, 365

RESTORE DATABASE statement, 362, 363–364

RESTORE HEADERONLY stored procedure, 355 RESTORE LOG statement, 362, 366

restoring databases, 362–370 from files, 335

Master database, 368–369

Master database without backup, 370 REVOKE SELECT statement, 271 REVOKE state, 267–273

right outer join, 133 roll back, 672

ROLLBACK TRANSACTION statement, 326 row, 672

Row object, 5

RPC (Remote Procedure Calls), 672 rule, 672

Rule object, 6

running jobs manually, 464

S

SA (System Administrator), 672 SAM (Security Accounts Manager), 673 Save DTS Package screen (DTS Import/Export

Wizard), 413

Save, schedule, and replicate package screen (DTS Import/Export Wizard), 412

saved SQL scripts, opening, 102 saving queries, 103

Schedule the DTS package screen (Copy Database Wizard), 332

scheduling, 673 backups, 352–354

scripts – SEQUEL, versions 705

jobs, 458–459 replication, 547

scripts, 673. See also SQL scripts

SCSI (Small Computer Systems Interface), 674 for disk duplexing, 311

security, 673

application role, 264–266 authentication mode, 226–230

Mixed Mode, 228 setting, 229

Windows Authentication mode, 227–228 database roles, 257–266

custom database role, 260–264 fixed database role, 257–260 database user accounts, 247–250 for DTS package, 401

fixed server role, 243–246 for linked servers, 123–128 login common items, 242–243 server login, 230–243 Windows login, 234–242 monitoring, 277–284 n-tier, 275–277 ownership chain, 273–275 permissions, 250–257

GRANT, REVOKE, DENY states, 267–273 object permissions, 254–257

statement permissions, 250–254 plan development, 285–287 roles in database options, 177 for SQL Server, 62, 62

SQL Server system accounts, 284–288 Security Accounts Manager (SAM), 673

security identifier (SID), 673 securityadmin role, 244

Select Columns screen (DTS Import/Export Wizard), 408

Select Components screen (Installation Wizard), 41 Select Databases to Move or Copy screen (Copy

Database Wizard), 331

Select Distributor screen (Configure Publishing and Distribution Wizard), 542, 542–543

SELECT INTO commands, and backups, 309 SELECT object permission, 254, 269

Select Objects to Copy screen (DTS Import/Export Wizard), 416–417, 417

Select Publication Name and Description screen (Create Publication Wizard), 555, 556 Select Publication Type screen (Create Publication

Wizard), 551

Select Related Objects screen (Copy Database Wizard), 332

Select Server and Database screen (Index Tuning Wizard), 622

Select Source Tables and Views screen (DTS Import/ Export Wizard), 410

SELECT statement, 128–135, 673 * operator, 128–129

to create report, 111–112

to display name of SQL Server, 97–98 joining tables, 131–134

ordering results, 134–135 syntax, 128

WHERE clause, 130–131

Select Tables to Tune screen (Index Tuning Wizard), 623

706 server groups – sp_detach_db stored procedure

server groups, 89–90 Server Groups dialog box, 91

Server Name, for remote server link, 113 server process ID (spid), 675

Server version of Windows 2000, 10 serveradmin role, 244

servers, 673

attaching and detaching database, 192–194 optimization, 634–635

standby, 315–324

log shipping to configure, 317–324 storing stored procedures on, 206–207 troubleshooting tools on, 140

service, 673–674

service accounts, 284, 674 service packs, 674

for MAPI support, 478, 479

Services Accounts screen (Installation Wizard), 42 Set Distribution Agent Schedule screen (Pull

Subscription Wizard), 565

Set Snapshot Agent Schedule screen (Create Publication Wizard), 559, 559

set working size, 630, 674

Setup Type screen (Installation Wizard), 41 setupadmin role, 244

severity level, 674 shared locks, 632

"shared memory" net library, 89 SHOWCONTIG command (DBCC), 633 shrinking databases, 185–191

with DBCC SHRINKDATABASE command, 185–189

TRUNCATEONLY and NOTRUNCATE options, 187, 187

with Enterprise Manager, 189–191 SHRINKFILE command, 188 SID (security identifier), 673

Simple recovery mode, 333–334, 390, 674 simple recovery model for database, 177 Single User option for database, 177

Small Computer Systems Interface (SCSI), 674 Small-datetime datatype, 162

SmallInt datatype, 161 Smallmoney datatype, 162 snapshot agent, 524

Snapshot Agent History screen, 574 Snapshot Delivery screen (Pull Subscription

Wizard), 564

snapshot replication, 521–522, 528, 529 with immediate updating subscribers, 522 software RAID, hardware RAID vs., 314 software requirements, for SQL Server, 29–30 sort order, 674

for SELECT statement results, 134 and SQL Server install, 31 sorting, in MSQuery, 107

sp_addlinkedserver stored procedure, 114, 276 sp_addlinkedsrvlogin stored procedure, 114, 123,

127, 277

sp_addlogin stored procedure, 233 sp_addrole stored procedure, 263

sp_addrolemember stored procedure, 260, 263 sp_addsrvrolemember stored procedure, 246 sp_addumpdevice stored procedure, 337–338 sp_attach_db stored procedure, 193, 329, 370, 674 sp_configure stored procedure, 59

sp_dboption stored procedure, 176, 178–180 sp_detach_db stored procedure, 193, 329, 675

Specify Articles screen (Create Publication Wizard) – SQL Server 707

Specify Articles screen (Create Publication Wizard), 554

Specify Filter dialog box, 558

Specify Query Criteria screen (DTS Import/Export Wizard), 409

Specify Snapshot Folder screen (Configure Pub-lishing and Distribution Wizard), 543, 543 Specify Sort Order screen (DTS Import/Export

Wizard), 408

Specify Subscriber Types screen (Create Publication Wizard), 553

Specify Table Copy or Query screen (DTS Import/ Export Wizard), 406

Specify Workload screen (Index Tuning Wizard), 622 sp_grantdbaccess stored procedure, 249

sp_grantlogin stored procedure, 241

sp_helpdb stored procedure, 174–175, 175, 179 sp_helpdevice stored procedure, 355

sp_helpdistributiondb stored procedure, 579 sp_helpdistributor stored procedure, 579 sp_helpfile stored procedure, 175 sp_helpfilegroup stored procedure, 199 sp_helppublication stored procedure, 579 sp_helpsubscriberinfo stored procedure, 580 sp_helpsubscription stored procedure, 579 spid (server process ID), 675

sp_lock stored procedure, 632 sp_recompile stored procedure, 631 sp_rename stored procedure, 57

sp_renamedb stored procedure, 191–192 sp_repldone stored procedure, 530 sp_setapprole stored procedure, 264, 266 SQL Client Network utility, 136, 136–137, 141 SQL Compilations/Sec counter, 607

SQL-DMO (SQL Server Distributed Management Objects), 537, 675

.sql file extension, 102 SQL Profiler, 610–624, 676

filtering trace data, 615–618 Index Tuning Wizard, 621–624 replaying trace file, 618–620, 618–620 SQL scripts

created by transfer process, 419–420 opening previously saved, 102

Query Analyzer to retrieve and store, 99 to rebuild Msdb database, 451

for replication, 577–579

SQL Server. See also installing SQL Server administrator tasks, 15–16

background, 8–9

as client/server database engine, 2–3, 3 configuration for replication, 540–541 developer tasks, 14–15

English Query Support, 74–75

error messages, editing and creating, 473–475 folders, 94–95, 95

fully qualified name for linking to, 112 history, 9

impact of interactive Web pages on performance, 498

and Internet, 489–498

interactive Web page creation, 498–502 SQL Web Assistant, 491–498

static vs. interactive Web pages, 489–490 XML support configuration, 490–491 registration, 89

708 SQL Server 6.5, installing and configuring as linked server – SQL Trace

upgrading 6.5 and 7 to 2000, 54–57 database, 57

issues, 57–58

Windows 9.x vs. 2000 installations, 14

SQL Server 6.5, installing and configuring as linked server, 117–118 SQL Server 2000 clients support, 12–13 configuration, 58–67 ANSI settings, 67–69 internal settings, 59–65

network protocol settings, 65–67 features, 13–14

versions, 11–12

SQL Server Agent, 53, 451–455, 675 alerts, jobs and events, 454–455, 456 configuration, 452–454

user account setup, 452–453 jobs, 456–467 creation, 458–462 deleting, 467 editing, 463 multiserver jobs, 457 running manually, 464 viewing history, 465–466 password change, 454

starting service automatically, 453 SQL Server Agent Properties dialog box

Advanced tab, 477 Alert System tab, 488 General tab, 484 Job System tab, 466 SQL Server Backup dialog box

for differential backup, 350

for filegroup backup, 352 for full backup, 342 General tab, 343 for striped backup, 345 for transaction log backup, 348 SQL Server Books Online, 141, 675

SQL Server Distributed Management Objects (SQL-DMO), 675

SQL Server engine, 675

SQL Server Login Properties - New Login dialog box, 238

SQL Server Network Utility, General tab, 66 SQL Server ODBC Data Source Test dialog box, 74 SQL Server Properties (Configure) dialog box

Connections tab, 63, 63

Database Settings tab, 65, 65, 328 General tab, 59

Memory tab, 60, 60 Processor tab, 61, 61 Security tab, 62, 62, 229 Server Settings tab, 64, 64

SQL Server Properties dialog box, Security tab, 483 SQL Server Select Components dialog box, 135 SQL Server system accounts, 284–288

SQL Server user account, to register server, 93 SQL Server Web Assistant, 458

SQL Server Web Wizard, 675 SQL Service Manager, 140 SQL Setup program, 135–136

SQL (Structured Query Language). See also T-SQL (Transact-SQL)

history, 8–9

SQL Web Assistant – system table 709

SQL Web Assistant, 491–498 Finish screen, 496

Publish the Web Page screen, 495 Schedule the Update Interval screen, 494 Schedule the Web Assistant Job screen, 494 Select a Table and Columns screen, 493 Specify Titles screen, 495

Start a New Web Assistant Job screen, 493 SQLAgent, 140

SQLDiag, 140 Sqlisapi.dll, 675 SQLMaint, 140, 676

SQL*Net software (Oracle), 120–121

SQLPERF(LOGSPACE) command (DBCC), 139 SQLPERF(LRUSTATS) command (DBCC), 139 SQLServer, options for troubleshooting, 140 staff responsibility for backup, 308

standard login, Enterprise Manager to create and test, 231–232

Standard version of SQL Server, 12 standby servers, 315–324

log shipping to configure, 317–324

Start Copying Files screen (Installation Wizard), 44 Start Required Services screen (Pull Subscription

Wizard), 566

statement permissions, 250–254, 676 static Web pages

vs. interactive, 489–490

SQL Web Assistant and, 491–498 step objects, 400–401

Stored procedure object, 6

stored procedures, 676. See also sp_... stored procedure

to create linked server, 114 creation, 206–208

for database information, 174–175 for Internet Database Connector, 499 optimization, 627, 629, 630–631 WITH RECOMPILE clause, 630 on remote servers, 108

replication of, 579–580

stripe set, 156, 311–312, 312, 671, 676 striped backup, 341, 344–345

striped drive array, 315 striping, 155

striping with parity, 157, 312–314, 313, 671, 676 Structured Query Language (SQL). See SQL

(Structured Query Language)

Structured Storage File, DTS package storage as, 401 subscriber, 676

Subscriber Properties dialog box, Schedules tab,

547, 547

subscribers, 516 anonymous, 559 backups, 572

central with multiple publishers, 534, 534–535 immediate updating, 552, 571

snapshot replication with, 522 transactional replication with, 521

multiple publishers with multiple, 535–536, 536 subscriptions, creation, 560–567

suspect database, 677 Sybase Corporation, 9

sysadmin role, 177, 243–244, 249 sysalerts table, 467

System Monitor (Windows). See Windows System Monitor

system stored procedure. See stored procedures system table, 676–677

710 T-SQL (Transact-SQL) – TinyInt datatype

T

T-SQL (Transact-SQL), 9, 96–107, 678. See also SELECT statement

ALTER DATABASE statement, 168 to create filegroup, 197–199 to expand database, 184–185 to remove filegroup, 199–202 syntax, 180–181

to assign fixed database role to user, 260 to assign fixed server role to user, 246 to assign object permissions, 256 BULK INSERT statement, 392–397 to create and test standard login, 233 to create application role, 266

to create custom database role, 263–264 to create database, 165–170

to create database user account, 249 CREATE INDEX statement, 206

CREATE PROCEDURE statement, 207–208 CREATE TABLE statement, 203

to create constraints, 204 CREATE TRIGGER statement, 209 CREATE VIEW statement, 211

to create Windows-to-SQL login, 240–242 DROP DATABASE statement, 194 for full backup, 341–342

to grant, revoke and deny permissions, 271–272 to grant statement permissions, 253–254 to link to Access database, 120

Table Article Properties dialog box, General tab, 554, 555

Table object, 5

table scan, 205 tables, 677

joining in SELECT statement, 131–134 cross joins, 133–134, 134, 652 inner join, 131, 131–132, 659 outer join, 133, 133, 667 tape for backup, 308

target server, for multiserver jobs, 457 Task Manager, 677

task objects, 399 Taskbar, 677

TCP/IP (Transmission Control Protocol/Internet Protocol), 678

for Internet replication, 538 SQL Server support, 32

TCP (Transmission Control Protocol), 678 TechNet, 677

Tempdb database, 627, 677 frequency of backup, 308 and replication, 531 terminator for BCP fields, 390 Test Alert dialog box

Action tab, 603 General tab, 602 Test Log dialog box General tab, 604 Log Files tab, 605 testing e-mail, 485–487 replication, 568–570, 570 SQL Server install, 50–51 text/image pages, 160 thread, 677 TinyInt datatype, 161

Tools menu (Enterprise Manager) – two-phase commit 711

Tools menu (Enterprise Manager), 96
Replication

Configure Publishing, Subscribers and Distribution, 541

Create and Manage Publications, 549
Generate SQLScript, 577

Pull Subscription, 560
SQL Query Analyzer, 100

SQL Server Configuration Properties, 59
SQL Server,
Query Analyzer, 51 Torn Page Detection option for database, 178 Total Server Memory counter, 607

trace, 278, 611, 678 filtering data, 615–618

logs in Windows System Monitor, 598 replaying file, 618–620

Trace Properties dialog box Data Columns tab, 614 Events tab, 282, 613 Filters tab, 617 General tab, 281, 612 Transact-SQL (T-SQL), 9, 96–107, 678 transaction backup, 678 transaction log, 155, 678 of distribution database, 542 location, 156

for Publishing database, 572 restoring, 366–368

transaction log backup, 334, 345–348 during restore process, 362

transaction processing, data warehousing vs., 7 transactional consistency, 518

transactional replication, 520, 529–532, 530 with immediate updating subscribers, 521

transactions, 326–328, 678 automatic recovery, 327–328 distributed, 520

vs. replication, 515 per synchronization cycle, 543 Transfer Manager, 398, 414–420, 678 Transform Published Data screen (Create

Publication Wizard), 553

Transmission Control Protocol/Internet Protocol (TCP/IP), 678

for Internet replication, 538 SQL Server support, 32

Transmission Control Protocol (TCP), 678 trend tracking, 625–626, 678

triggers, 6, 679 and bulk copy, 390 creation, 208–209 for merge replication, 527 troubleshooting proactive, 626 SQL Server install, 50–51 troubleshooting tools, 137–141 on client, 141 DBCC commands, 138–139 Makepipe, 139–140 Readpipe, 139–140 on server, 140

Windows Event Viewer, 137–138, 138 TRUNCATEONLY and NOTRUNCATE options

for SHRINKDATABASE command, 187, 187 for transaction log backup, 346–347

trusted connection, 227, 227, 679 TSQL jobs, 457

712 .txt file extension – virtual directory on IIS server

.txt file extension, 385

Type SQL Statement screen (DTS Import/Export Wizard), 407, 409

U

unattended installations, 49

UNC (Universal Naming Convention), 679 unhandled event forwarding server, 475

designating, 476–477 Unicode support, 679

and SQL Server install, 30, 31 uniform extents, 159

Uniform Resource Locator (URL), 679 Universal Naming Convention (UNC), 679

Updatable Subscriptions screen (Create Publication Wizard), 552

UPDATE object permission, 254

updating subscribers, immediate, 552, 571 snapshot replication with, 522

transactional replication with, 521 Upgrade folder, 54 Upgrade Wizard, 36 upgrading database, 57 vs. side-by-side install, 35, 55–56 SQL Server 6.5 and 7 to 2000, 54–57 database, 57 issues, 57–58

URL (Uniform Resource Locator), 679

Use Quoted Identifiers option for database, 178 user, 679

user accounts

assigning fixed server role to, 245–246 database, 247–250

login for remote server, 123

mapping with Enterprise Manager, 125–126 mapping with Query Analyzer, 127–128 permissions to restrict database activities,

250–257 to register server, 93 in security plan, 285

for SQL Server Agent, 452–453, 541 for SQL Server install, 33

for SQL services, 480 mailbox for, 481 User Connections counter, 607

user database, frequency of backup, 308 user-defined filegroups, 195

User Information screen (Installation Wizard), 39 user Properties dialog box, General tab, 236 username, 679

V

viewing history of jobs, 465–466 views, 6, 680

creation, 210–211

Visual Basic, for DTS package – World Wide Web (WWW) 713

Visual Basic, for DTS package, 401 VSWITCH, 140

W

Web Assistant Wizard. See SQL Web Assistant Web browser, 680

Web pages, 680

for database display, 7 static vs. interactive, 489–490

web sites, developer version of Oracle, 122 Web.sql script, 451

Welcome screen (Configure Publishing and Distribution Wizard), 541

Welcome screen (Copy Database Wizard), 330 Welcome screen (Create Publication Wizard), 550 Welcome screen (Index Tuning Wizard), 621 WHERE clause, in SELECT statement, 130–131 Win16, 680

Win32, 680 Windows 2000, 680

history, 10 versions, 10

Windows 2000 Active Directory Users and Computers utility, 33, 33

Windows 95/98/ME, 680

Windows Authentication mode, 227–228, 680 Windows CE version of SQL Server 2000, 11 Windows clustering, 315

Windows Event Viewer, 137–138, 138 Windows for Workgroups 3.11, 680 Windows login, 234–242

Windows NT, 680–681 MAPI support, 478–479

Windows System Monitor, 597–607, 681 alerts, 598

counter logs, 598

counters and values, 598–599 logging, 603–606

SQL counters, 606–607 trace logs, 598

wizards

Configure Publishing and Distribution Wizard, 542–546

Copy Database Wizard, 329–333 Create Publication Wizard, 548–560

Database Maintenance Plan Wizard, 357–361, 458, 653

in Enterprise Manager, 93, 93–94

Import and Export Wizards, 398, 404–413 Index Tuning Wizard, 621–624, 659 Installation Wizard, 45–48

Job Wizard, 458

Management wizards, 94

Microsoft Outlook Setup Wizard, 482, 482–483 Pull Subscription Wizard, 560–567

Register SQL Server Wizard, 91 Replication wizards, 94 SQL Server Web Wizard, 675 Upgrade Wizard, 36

Web Assistant Wizard. See SQL Web Assistant workflow, step objects to structure, 400

workgroup, 681

714 Visual Basic, for DTS package – XML (eXtensible Markup Language)

X

XML (eXtensible Markup Language), 489, 656 configuring support in SQL Server 2000,

490–491