Download Designing SQL Server 2000 Databases. for .Net ™ Enterprise Servers PDF

TitleDesigning SQL Server 2000 Databases. for .Net ™ Enterprise Servers
File Size52.3 MB
Total Pages726
Document Text Contents
Page 1

We would like to acknowledge the following people for their kindness and support
in making this book possible.

Richard Kristof, Duncan Anderson, Jennifer Gould, Robert Woodruff, Kevin
Murray, Dale Leatherwood, Rhonda Harmon, and Robert Sanregret of Global
Knowledge, for their generous access to the IT industry's best courses, instructors
and training facilities.

Ralph Troupe, Rhonda St. John, and the team at Callisma for their invaluable
insight into the challenges of designing, deploying and supporting world-class
enterprise networks.

Karen Cross, Lance Tilford, Meaghan Cunningham, Kim Wylie, Harry Kirchner,
Bill Richter, Kevin Votel, Brittin Clark, and Sarah MacLachlan of Publishers Group
West for sharing their incredible marketing experience and expertise.

Mary Ging, Caroline Hird, Simon Beale, Caroline Wheeler, Victoria Fuller,
Jona than Bunkell, and Klaus Beran of Harcourt International for making certain
that our vision remains worldwide in scope.

Anneke Baeten, Annabel Dent, and Laurie Giles of Harcourt Australia for all their

David Buckland, Wendi Wong, Daniel Loh, Marie Chieng, Lucy Chong, Leslie Lim,
Audrey Gan, and Joseph Chan of Transquest Publishers for the enthusiasm with
which they receive our books.

Kwon Sung June at Acorn Publishing for his support.

Ethan Atkin at Cranbury International for his help in expanding the Syngress

Joe Pisco, Helen Moyer, and the great folks at InterCity Press for all their help.

Page 2

From Global Knowledge
At Global Knowledge we strive to suppor t the multiplicity of learning styles
required by our s tuden t s to achieve success as technical professionals. As
the world's largest IT training company, Global Knowledge is uniquely
positioned to offer these books. The expertise gained each year from pro-
viding inst ructor- led training to h u n d r e d s of t hous ands of s tuden t s world-
wide has been captured in book form to enhance your learning experience.
We hope tha t the quality of these books demons t ra tes our commi tment to
your lifelong learning success. Whether you choose to learn th rough the
writ ten word, computer based training, Web delivery, or ins t ructor- led
training, Global Knowledge is committed to providing you with the very
best in each of these categories. For those of you who know Global
Knowledge, or those of you who have jus t found us for the first time, our
goal is to be your lifelong competency partner.

Thank your for the opportuni ty to serve you. We look forward to serving
your needs again in the future.

Warmes t regards,

D u n c a n Anderson
President and Chief Executive Officer, Global Knowledge


Page 363

352 Chapter 7 �9 SQL Server Backup and Recovery





RESTORE VERIFYONLY With this command, two functions can be per-
formed. The first one is to check for validity and correctness of a backup
set, resul t ing in a message stat ing tha t the backup set is valid or invalid.
The second function is to load the information on the verified backup set
into the backup history in the msdb database . In this way, you can con-
s t ruc t a history of backups tha t were never par t of the history recorded
in the msdb database . Example:




FILE = i,



Database Options and Settings
From within the Enterpr ise Manager, a few da tabase sett ings and SQL Server
da tabase sett ings are directly related to the backup and restore process. To be
able to back up the t ransac t ion log, you should prevent it from being t runca ted
automatically. This is done by taking care tha t the '~rruncate log on checkpoint"
sett ing is set to false. You can change the value of this option this way:

1. Select the app rop r i a t e d a t a b a s e in the En t e rp r i s e Manager .

2. Right-click the Properties option. This will bring the Properties dialog
box on screen (see Figure 7.26).

3. Select the tab Options.

4. Verify whether or not the option 'q ' runcate log on checkpoint" is checked
in Settings.

5. If the option is checked, uncheck it.

This options needs to be set or unse t for every da tabase tha t is accessible
from the da tabase server.

Within the properties of the SQL Server, you can set a few options tha t have
an effect on all da tabases available on the da tabase server. The first setting has
to do with the a m o u n t of time the SQL Server waits for a tape to become avail-
able from the tape device, after a backup or restore operation is started. The
t ime-out period (see Figure 7.27) can have three values:

Page 364

SQL Server Backup and Recovery �9 Chapter 7 353

Figure 7.26 The Northwind Properties Options tab.

Figure 7.27 SQL Server Properties Database Settings tab.

Wait indefinite ly This is the default value and tells the b a c k u p / r e s t o r e
process to keep checking to see if a tape is inserted. This option locks up
sys tem resources and other operat ions from accessing the tape device.
This option is very convenient for an a t tended backup or restore.

Try once then quit The b a c k u p / r e s t o r e process will check for a tape to
be ready for use. If no tape is ready, it aborts the operation and will lock
the failure. You can use this option for una t t ended backups , where you
expect a tape to be present .

Page 725

Index 715

backups, 318, 338-339
disk directory, specifica-

tion, 177
plan, specification, 176
size/growth rate, 307

files, 675
growth, defining, 157
naming, 156-157

scheduling intervals, 307
Transact ion Processing

Council (TPC), 96, 652
benchmarks , 97-98
TPC-C, 98
TPC-H, 98

Transact ion Server, 637
Transactional replication,

309, 550, 560-561,

backup, 590
log shipping, usage,

performance, enhance-

ment process,

restoration, 590
Transactions, 298

start ing point, 306
Transact-SQL (T-SQL), 151,

321-322, 624-633
command, 333
execution, 140
OPENXML statement,

statement, 159, 160, 230,

275. See also CHECK-

usage, 555
stored procedures, 231
usage, 209. See also

Databases; Fixed serv-
er role; Windows

Transfer Tasks, 27
Transformations, 41 4-415
Triggers, 131

enhancements , 1 6-17,

size, 173
Trigger-specific inserted

table, 616
TRN, 340
Truncation, 302

Trusted Computer System
Evaluation Criteria
(TCSEC), 193

T-SQL. See Transact-SQL
Tuple functions, 484
Two-click deployment, 362

UDT. See User-defined data

Uncommitted replication

transaction, 306
Uniform Resource Locator

(URL), 494, 510-513
usage, 500, 517

UNION, 655
deleting, 672
operator, 653


column data type, usage,

data type, 555
Universal, 551
Universal Resource Locator

(URL), 261
Unused space, removal. See


action, 131
query, 660
statement, 113-114, 121,

525, 560, 576
usage, 613, 626-627,

Updategrams, 534-535

support. See Structured
Query Language
Server 2000

understanding, 535
UPDATETEXT statement,

630, 631
Updating. See Queued

updat ing
UPS protection, 101
URL. See Uniform Resource

Locator; Universal
Resource Locator

URL query support. See
HyperText Transfer

Usage Analysis Wizard,
495-496, 644

Usage-Based Optimization
Wizard, 496, 644

User-defined data type
(UDT), 130, 131

User-defined functions, 18,
130, 167, 609-613.
See also Inline user-
defined functions;
Scalar user-defined
functions; Table user-
defined functions

usage, 201
User-defined roles, 225,

User-definition function,

Users, 492. See also Active

users; Databases;
Enterprise-level user

accounts. See Guest user

adding. See Enterprise

changing, 66-67
authentication, 232-234
connections, 687

management , 140
databases, 30, 335
grouping, 200-201
permissions, assignation,

222-224, 235
questions, understanding,


Variable-length column stor-

age requirements, 149
VARIANCE statistical func-

tions, 619
VB. See Visual Basic
View mapper. See

Structured Query

Views, 131. See also
Distributed partitioned
views; Distributed
view; eXtensible
Markup Language;
Indexed views;
Partitioned view

creation, 659
referencing, 619

Page 726

716 Index

usage, 201
Virtual cube, 487
Virtual dimension, 481
Virtual names, 512
Virtual Private Network

(VPN), 202, 236, 571
usage. See Replicating

Virtual server, 75, 102
Visual Basic (VB), 209, 376,

409-413, 579, 599
DTS, programmatic pack-

age execution,

references, 642
sample, 636-637
script file, DTS package

saving, 429
Visual C++, 376, 599
Visual Studio 6.0, 5
Visual Studio 6.0 integra-

tion, 362
Visual Studio.NET, 7
VPN. See Virtual Private


W3C. See World Wide Web

Consort ium, 510
WAN. See Wide Area

Web. See World Wide Web
Weekly (occurrence), 329
WHERE clause, 484, 611,

654, 664
Wide Area Network (WAN),

254, 255, 408, 483
link, 553

Windows 98, 38
Windows 2000, 67, 233-234

cluster service component,
installation, 104-105

IPSec, usage, 239
MSCS setup, 104-105

Windows 2000 Active
Directory, 262-265

integration, 24, 244-261
Windows 2000 Advanced

Server, 36, 38
Windows 2000 Datacenter

Server, 36, 38
Windows 2000 Professional,

Windows 2000 Server, 6, 36
Windows 2000 settings,

Windows authentication, 81,

mode, understanding,

Windows CE

SQL Server 2000, 2
subscribers, 581-582

Windows DNA, future, 4-8
Windows logins

adding, 210-216. See also
Enterprise Manager

Transact-SQL, usage,

grant ing/revoking/deny-
ing, 211

Windows Millennium
Edition, 38

Windows NT, 67
authentication, 279
fibers, usage, 80

Windows NT 4.0 Server, 36,

Windows operating system
files, 675

Windows paging files, 675
Windows-only authentica-

tion mode, 210

WITH MEMBER statement,

option, 122, 612, 613

Word documents, 381
Word occurrences. See

Workflow. See Packages

restarting, 416
World Wide Web Consort ium

(W3C), 500, 5 0 1 , 5 1 0
World Wide Web (WWW /

Analysis Services, access,

browser, 483
support, 31-32
Web-based English Query

applications, implemen-
tation, 376-377

solution, creation,

XML resources, 509-510
Write-enabled dimension

type, 22
WRITETEXT statement , 630,

W W W . See World Wide Web

X.500, 246
XDR. See eXtensible

Markup Language
XML. See eXtensible

Markup Language, 510
XPath. See eXtensible

Markup Language
xp_sendmail, 291
XSL. See eXtensible

Stylesheet Language

Similer Documents