S


SAC (Surface Area Configuration) tool

accessing, 74

enabling CLR integration, 276

for Features, 40, 75–76, 79, 80–81

remote configuration with, 79

for Services and Connections, 39–40, 75, 79

SAFETY FULL option, ALTER DATABASE statement, 549, 558, 581

SAFETY OFF option, ALTER DATABASE statement, 549, 550, 581–582

sample databases, installing, 34–37

SAN (Storage Area Network)

choosing, whether to, 21, 23

configuration of, 370–372

recovery measures for, 594

Sarbanes-Oxley law, 2, 294, 324

scalability, for enterprise data management, 52

scalar function, CLR, 287–288

scaling, licensing for, 16

Scheduled Events and Reports Delivery, SSRS, 75

scheduler, 390, 398

schedules for jobs, 109–110

schemas

definition of, 7–8, 307

example of using for security, 307–310

ownership of, changing, 310

performance affected by, 344

scripted installation, 29–30

scripts

ActiveX scripts, 106

change scripts, 334–337

for SSAS, 186–187

T-SQL, 106, 107

for Upgrade Advisor, 60

secondary server, for log shipping, 630

Secure Sockets Layer (SSL), 129

security

for assemblies, 281–282

authentication, configuring, 88

credentials, 302

for Database Mail, 129

of dynamic SQL in stored procedures, 310–314

encryption

certificate for, 315–316

decrypting data, 317–318

definition of, 314

encrypting data, 316–317

keys for, 315–316

SQL Native Client used by, 5

for transport security, 246–247

for enterprise data management, 51

installation decisions regarding, 27–28

of instant database file initialization, 43

for jobs in SQL Server Agent, 107

permissions

for administrators, configuring, 42

for backup and restore, 601

column-level permissions, 318–319

for dynamic management views and functions, 433

granular permissions, 304–305

for Service Broker objects, 256–257

for SSAS database roles, 208–210

too many, causing database failure, 593

troubleshooting, 314

schemas used for, 307–310

for Service Broker

dialog security, 242–243, 250–254

transport security, 242–250

for SQL Server Agent, 118–122

for SQL Server logins

authentication for, 293–294

BUILTIN\Administrators login, 301–302

conflicts between access rights, 300–301

creating, 294–300

disabling, 299

granting access to objects, 299

granting database rights, 297–298

passwords for, 295–296

server roles for, 297

T-SQL for, 300

unlocking, 299

for SSAS, 186, 206–210

for SSIS, 175–178

for users, 303–306

securityadmin role, 297

seek time, 364

SEND permission, for Service Broker objects, 257

send queue, for database mirroring, 550

separation of power (Sarbanes-Oxley law), 2, 294, 324

Server Dashboard report, 86

Server plus Device CAL (Client Access License) licensing model, 16

Server plus User CAL (Client Access License) licensing model, 16

Server Properties screen, Management Studio, 87–89

server reports, in Management Studio, 85–86

server roles, for SQL Server logins, 297

server triggers, 329–330

server-scoped dynamic management views and functions, 433

server-side traces, 419–424

server-state information, in DMVs, 8

server_triggers view, 330

service accounts

security considerations for, 27

for SQL Server Agent, 118

types of, 28

Service Broker

activation of, 236–240, 272

architecture of, 213–222

asynchronous processing used by, 218

case sensitivity of metadata names in, 222

catalog views of, 254–255, 264–266, 286

code review for applications, 263–264

contracts in

creating, 216–217, 225

definition of, 216

mapping to service, 221

conversation groups in, 240–242

conversations in

ending, 263

not ending, 272–273

copying applications, 262

data for, catalog views of, 265–266

default settings for, 75

definition of, 213

dialogs in

definition of, 214, 219, 227

starting, 227–228

disabled in database, 271

DMVs related to, 266–267

endpoints in

creating, 243–244

definition of, 214, 227

event notifications sent to, 431

examples of, 222–236, 243–254

expired certificates, replacing, 263

GUID, 254

installing applications, 254–256

messages in

definition of, 214

dropped, 269

duplicate routes for, 271–272

no return route for, 269

poison messages, 258–261, 263

problems sending, 269–270

receiving, 232–236

sending, 226–227

type of, altering, 216

type of, creating, 214–216, 224

type of, deleting, 216

type of, not handling, 263

type of, viewing, 216

metadata for, catalog views of, 264–265

monologs in, 227

moving applications, 261–262

performance of, 272–273, 272–273

permissions for, 256–257

Queue Monitor for, 237

queues used by

altering, 220, 221

associated stored procedure with, 236

creating, 219–221, 225

definition of, 218–219

disabled, 271

managing, 258

routing for, 247–250

security for

dialog security, 242–243, 250–254

transport security, 242–250

services in, creating, 221–222, 225–226

SQL Profiler, using with, 267–268

support for, by SQL Server edition, 14

transactions in, 264

troubleshooting applications, 263–272

services

configuring with Configuration Manager, 28, 40–41, 78

configuring with Surface Area Configuration, 39–40, 75, 79

creating, 221–222, 225–226

starting automatically, enabling, 40, 78

Web Services, 75, 81

Services applet, control panel, 78

SET options, affecting query results, 444

SET SHOWPLAN_ALL statement, 455–457

SET SHOWPLAN_TEXT statement, 455–457

SET SHOWPLAN_XML statement, 427, 457–458

SET STATISTICS statement, 459–461, 465, 466

setupadmin role, 297

setup.exe program, installation using, 29–30

SETUSER statement, 65

:SETVAR command, 332, 333–334

shared disk array, for clustering, 658

Showplan

formats for, 455

graphical format for, 455, 458–459

text format for, 455, 455–457

XML format for, 424, 425–428, 455, 457–458

side-by-side installation, 28–29

side-by-side upgrade, 55–56

Simple Mail Transfer Protocol (SMTP), 127, 131

simple recovery model, 597, 597–598

simplification transformation of query, 450–451

single-user mode, enabling at startup, 83

64-bit processors, 348

64-bit system

memory configuration, 359, 362, 392

upgrading 32-bit to, 72

sliding windows, 384, 487

smalldatetime data type, 11–12

smallint data type, 10, 11

SMO (SQL Server Management Objects), 90

SMP (symmetric multi-processor), 353

SMTP (Simple Mail Transfer Protocol), 127, 131

Snapshot Agent, replication using, 531

snapshot isolation, SQL Native Client used by, 5

snapshot replication

definition of, 530

distribution for, 532–534

publication for, 534–538

setting up, 531–532

subscribing to, 538–539

verification of, 539–541

snapshot restore, 616

snapshots

on mirror database, 589–590

support for, by SQL Server edition, 15

socket, in multi-core system, 351

software

errors by, causing database failure, 592

planning requirements for, 24–28

sort order of collation, 25–26

sp_addalias procedure, 65

sp_addextendedproc procedure, 66

sp_addgroup procedure, 65

sp_add_log_shipping_alert_job procedure, 638

sp_add_log_shipping_primary_database procedure, 638

sp_add_log_shipping_primary_secondary procedure, 638

sp_add_log_shipping_secondary_database procedure, 638

sp_add_log_shipping_secondary_primary procedure, 638

sp_add_schedule procedure, 638

sp_attach_schedule procedure, 638

SP:CacheHit event, 440, 446

SP:CacheInsert event, 446

SP:CacheMiss event, 440, 446

SP:CachInsert event, 440

sp_changegroup procedure, 65

sp_check_log_shipping_monitor_alert procedure, 639

sp_configure procedure

activating AWE, 393

allow updates option of, 64

changing and overriding settings, 89–90

displaying current configuration, 89

sp_dbmmonitoraddmonitoring procedure, 573

sp_dbmmonitorchangealert procedure, 574

sp_dbmmonitorresults procedure, 573

sp_dbmmonitorupdate procedure, 573

sp_delete_log_shipping_primary_database procedure, 650

sp_delete_log_shipping_primary_secondary procedure, 649

sp_delete_log_shipping_secondary_database procedure, 650

sp_dropalias procedure, 65

sp_dropextendedproc procedure, 66

sp_dropgroup procedure, 65

sp_helpgroup procedure, 65

sp_help_log_shipping_monitor procedure, 639, 640

SPID (Process ID), monitoring, 92, 94

SP:Recompile event, 446

sp_send_dbmail procedure, 127

SP:Starting event, 446

sp_start_job procedure, 106

SP:StmtCompleted event, 446

sp_trace_create procedure, 420, 421

sp_trace_setevent procedure, 420, 421–422

sp_trace_setfilter procedure, 420, 422

sp_trace_setstatus procedure, 420, 422

sp_update_job procedure, 638

sp_who procedure, 95

sp_who2

procedure, 95

SQL Agent. See SQL Server Agent

SQL Client tools, installing from network share, 48

SQL Express edition

definition of, 12

features for, 14–15

memory used by, 13

operating systems supported by, 13

processors used by, 13

SQL Mail. See also Database Mail

compared to Database Mail, 127

configuring, 81

default settings for, 75

migrating to, 66

SQL Native Client

definition of, 5

removing, 47

SQL OS, 389–390

SQL Profiler. See SQL Server Profiler

SQL Server. See also installation; optimization; upgrading to SQL Server 2005

architecture of, 4–12

backward compatibility of, 64–66

burning in after installation, 38–39

configuring

with Management Studio, 87–90

for performance, 372–374

deprecated database features, 64–65

discontinued features, 64

editions of, 12–16

existing features, behavior changes in, 65–66

features supported by, 14–15

licensing of, 16

memory used by, 13

multiple instances of, 28–29

as .NET runtime host, 273–274

number of processors used by, 13

operating systems supported by, 13

starting, 78, 81

startup parameters for, 81–83

uninstalling, 44–47

SQL Server Agent

alerts in, 113–118

components of, 105

configuring, 123–127

jobs in

categories for, 107–108

logging, 108

notifications from, 108–109

running, methods for, 106

types of, 106–107

log shipping processes using, 631

operators in, 110–113

proxy accounts for, 119–122

replication using, 531

roles having access to, 118–119

scheduling jobs in, 109–110

scheduling SSIS package execution, 173–175

security for, 118–122

service account for, 118

starting automatically, enabling, 105

stopping before running in single-user mode, 83

subsystems, 119–120

troubleshooting, 141–143

SQL Server Analysis Services. See SSAS

SQL Server authentication, 293–294

SQL Server Configuration Manager

compared to Services applet, 78

configuring SSIS using, 153

Network Configuration node, 41–42, 78

Services node, 28, 40–41, 78

SQL Server Engine

installation location of, 27

uninstalling, 47

SQL Server event alerts, 114–115

SQL Server Integration Services. See SSIS

SQL Server Management Objects (SMO), 90

SQL Server Management Studio

Activity Monitor, 92–95

actual execution plan, viewing, 462

backup execution using, 607–612

configuring SQL Server using, 87–90

creating projects, 321–322

database reports in, 86–87

executing sqlcmd program from, 332–334

filtering objects using, 90

log shipping

deploying, 634–637

removing, 649

logs, displaying, 91

managing SSIS packages, 163–164

post-install configuration using, 42

processing SSAS databases, 192–194

recovering database using, 617–620

Server Properties screen, 87–89

server reports in, 85–86

viewing SSIS event logs with, 156

viewing XML Showplan using, 427–428

SQL Server Performance Condition alerts, 115–117

SQL Server Profiler

definition of, 345, 404

granting access to, 302

managing trace data, 424–430

monitoring CLR using, 291

monitoring database mirroring using, 577

monitoring Service Broker using, 267–268

monitoring SSAS using, 198–201

new features in, 424–425

performance of, 430–431

recompilation events, monitoring, 446

templates for, 419

SQL Server Reporting Services. See SSRS

SQL Server Services. See services

SQL Server Upgrade Advisor

definition of, 56

installing, 57

report generated by, 60–61

scripting, 60

using, 58–59

SQL Server Upgrade Assistant, 62–64

SQL Trace

analyzing query plans using, 479

architecture of, 419

creating traces, with T-SQL stored procedures, 419–424

managing traces with SQL Profiler, 424–430

performance of, 430–431

retrieving trace data, 424

retrieving trace metadata, 423

"SQL Trace Terminology" (Books Online topic), 418

SQLAgentOperatorRole role, 118–119

SQLAgentReaderRole role, 118–119

SQLAgentUserRole role, 118–119

sqlcmd program

definition of, 330

executing from command prompt, 330–332

executing from Management Studio, 332–334

initialization files for, 331–332

sqlcmdini environment variable, 332

SQLDiag.exe program, 101–103

(SQLDIR) token, 134

SQLDumper.exe program, 100–101

SQLIO tool, 39

SQLIOStress tool, 39

sqlmonitor.exe program, 572

.sqlplan file extension, 427

SQLscaler, Idera's, 378

sqlserver.exe program, 81

SSAS (SQL Server Analysis Services)

Ad-Hoc Data Mining Queries, 75

aggregations for, designing, 204–206

Anonymous Connections, 75

components of, 182–184

configuring, 75

database roles for, 207–210

databases for

backing up and restoring, 194–197

deploying, 188–191

processing, 191–194

synchronizing, 197–198

definition of, 182

Flight Recorder for, 200–201

installing, 37

jobs executing commands or queries from, 106–107

Linked Objects, 75

lock manager properties for, 184

log properties for, 184

memory properties for, 185

network properties for, 185

OLAP properties for, 185

partition configuration for, 202–204

performance monitoring for, 198–201

profiling of, 424

required services for, 186

scripting language for, 186–187

security for, 206–210

security properties for, 186

server configuration for, 184–186

server roles for, 206–207

storage used by, managing, 201–206

support for, by SQL Server edition, 14

traces for, 199–201

uninstalling, 46–47

User Defined Functions, 75

SSIS (SQL Server Integration Services)

business uses of, 146–147

configuring, 150–154

constraints in, 148

controlflows in, 147

dataflow engine and dataflow components, 149

dataflows in, 147

definition of, 145–146

event logs for, 155–156

failures of, responding to, 152

jobs executing packages from, 106, 107

named instances, handling, 150–151

object model of, 149

packages in

creating, 158–163

definition of, 158

deploying, 166–170

digital signatures for, 178

execution of, 171–175, 178

exporting, 166, 170

importing, 165–166, 170

managing, 163–165

resources used by, 178

saving, 177

scheduling execution of, 173–175

security for, 176–177, 178

templates for, 162–163

performance monitoring for, 156–158

runtime engine and runtime components of, 147–148

security for, 175–178

Service component of, 147, 150

startup properties, 151–152, 153

support for, by SQL Server edition, 15

tasks in, 147–148

upgrading DTS to, 67–71

Windows Firewall modifications for, 153–154

SSL (Secure Sockets Layer), 129

SSRS (SQL Server Reporting Services)

configuring, 75

installation location of, 27

installing, 37–38

Scheduled Events and Reports Delivery, 75

uninstalling, 45–46

Web Services and HTTP Access, 75

Windows Integrated Security, 75

Standard Edition

definition of, 12

features for, 14–15

memory used by, 13

operating systems supported by, 13

processors used by, 13

start sector alignment for disks, 374

startup parameters, SQL Server, 81–83

startup stored procedures, 83–84

(STEPCT) token, 134

(STEPID) token, 134

storage alignment, 387–389

Storage Area Network (SAN)

choosing, whether to, 21, 23

configuration of, 370–372

recovery measures for, 594

storage system design

disk configuration, 366–372

guidelines for, 365–366

for SAN systems, 370–372

stored procedures. See also specific procedures; T-SQL (Transact-SQL)

associated with Service Broker queues, 236

CLR, catalog view of, 287

creating traces with, 419–424

deprecated, 64–65

extended, compared to CLR, 276

in Resource database, 6

run at SQL Server startup, 83–84

security of dynamic SQL in, 310–314

worst performing, finding, 417

striping with mirroring, 369

striping with parity, 369

(STRTDT) token, 134

(STRTTM) token, 134

support

Product Support Services (PSS), 47–49

programs supplying information for, 100–103

Surface Area Configuration (SAC) tool

accessing, 74

enabling CLR integration, 276

for Features, 40, 75–76, 79, 80–81

remote configuration with, 79

for Services and Connections, 39–40, 75, 79

(SVR) token, 134

symmetric multi-processor (SMP), 353

Synchronize Database Wizard, 188

synchronous mirroring mode, 549

synonyms, 8

sysadmin role, 297

sys.database_mirroring view, 561–562

sys.database_mirroring_endpoints view, 562–563

sys.database_mirroring_witnesses view, 562

sys.db_broker_forwarded_messages DMV, 266

sys.dm_broker_activated_tasks DMV, 266

sys.dm_broker_connections DMV, 266

sys.dm_broker_queue_monitors DMV, 266

sys.dm_db_index_physical_stats DMF, 497

sys.dm_exec_cached_plans DMV, 439, 441

sys.dm_exec_connections DMV, 96

sys.dm_exec_query_stats DMV, 97

sys.dm_exec_sql_text DMV, 96–97, 442

sys.dm_io_virtual_file_stats DMF, 453

syslocks table, 65

sysmail_allitems view, 133

sysmail_delete_log_sp procedure, 133

sysmail_delete_mailitems_sp procedure, 133

sysmail_mailattachments view, 133

sys.server_triggers view, 330

sys.sp_check_log_shipping_monitor_alert procedure, 639

sys.syscacheobjects virtual table, 446

system architecture, performance considerations for, 353–354

system databases. See also specific databases

backing up, 601–602

list of, 6–7

rebuilding, 84–85

recovering, 621–622

system files, location of, 27

System Monitor, Windows

monitoring CLR using, 290

monitoring database mirroring using, 570–572

system processes

disconnecting, 93

monitoring, 92, 95–97

system tables, 65

sys.triggers view, 330



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net