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