Index

A

American National Standards Institute (ANSI), 127

American Standard Code for Information Interchange (ASCII), 127

Attach database method, 215–216

Auditing

compliances, 283

database audit specification object, 280–282

hardware-based auditing devices, 273

objects

abstract object, 274

database audit specification, 273

scopes and server audit, 273

server audit specification, 273

server -instance-scoped audit specification, 274

server audit object

audit creation, 274

audit destination, 276

DDL, 276–277

extended events, 275

repudiation attack, 276

system center operations manager, 276

windows audit collection services, 276

server audit specification object

context menu, 277–278

log files, 279–280

third-party auditing solutions, 273

track events, 272

user-defined audit event, 283–284

B

Backups

automating process

advanced page, 173–174

drop-down menu, 174–175

general page window, 170

object explorer window, 178–179

schedule window, 176–178

SQL Server agent, 169–170

step window, 172–173

steps page window, 171

visible, step page, 175

compression, 180–182

database properties window, 164–165

differential backups

files/device, 163

resoring database, 200–201

syntax, 163–164

encrypting backup files, 179–180

file and filegroups, 164–166

file backups, restore strategies, 205–206

full backup

backup mirroring, 161

database window, 158–159

destination, 159

files window, 160

options page, 161

overview, 158

RESTORE command, 162

SQL Server management studio, 158–162

T-SQL, 162–163

logs

checkpoint, 166

database, 169

database restore strategies, 201–205

fully recovery mode, 167

management studio, 168

transactions, 166

T-SQL, 169

physical backup device, 158

plan design

implementation, 182

OLTP system, 183

requirements, 182

SLA, 183

types, 183

server properties window, 181

TDE, 179

testing backups, 207

types, 157–158

Bulk Copy Program (BCP), 227–229

Business Intelligence Development Studio (BIDS), 70, 96

C

Cannon, J. C., 271–272

Central management server (CMS)

DBA, 295–296

multiple server, 53

New Query option, 296

production folder, 296

Clapper, Ed, 9–11

Cloud computing, DBA. See also SQL Azure

3M Visual Attention Service, 370–371

definition, 365

OCCMundial.com, 369

service models

infrastructure as a service (IaaS), 366

platform as a service (PaaS), 366–367

software as a service (SaaS), 366

Windows Azure, 367–369

Command-line tools

DTEXEC.exe, 70

GUI, 70

PowerShell, 70, 73–74

SQLCMD

overview, 71

passing variables, 72–73

SQL Server connection, 71–72

Community Technology Preview 3 (CTP3), 96

D

Data collector

baseline, 307

data view

details report, Queries, 317–318

disk usage summary report, 314–316

Query statistics history report, 316–318

reports, 311

Server activity history, 311–314

system CPU, 312

setting up data

configuration task, 308–310

data warehouse, 309

management folder, 308

Data Control Language (DCL), 110

Data Definition Language (DDL), 109

Data gathering, 1–2

Data Manipulation Language (DML)

DELETE statement, 135

INSERT

identity and sequences, 133–135

parameters, 133

queries, 133

SELECT statement

FULL OUTER JOIN, 131

INNER JOIN, 131

JOIN statement, 131

LEFT OUTER JOIN, 131

ORDER BY clause, 130

paging queries, 131–133

queries, 129–131

RIGHT OUTER JOIN, 131

syntax, 128

WHERE clause, 129

T-SQL language, 109–110

UPDATE statement, 135

Database administrator (DBA)

authorization, 250

Clapper, Ed, 9–11

database maintenance, 2–3

definition, 2–4

disaster recovery, 3

documentation, 4

Esposito, Michael, 11–12

Fritchey, Grant, 5–7

need for, 1–2

Pyne, Charlie, 8–9

Rehak, Roman, 7–8

salary information, 4

schema design and performance improvement, 4

security and compliance, 3

Database backup. See Backups

Database Console Command (DBCC), 229–230

Database encryption key (DEK), 290

Database Engine Tuning Advisor (DTA), 299, 331–333

Database maintenace tasks

database backup and restore, 213

database integrity, 229–230

distribution statistics

properties, 236–237

SSMS, node, 235–236

sys.stats and sys.indexes, 234–235

updating statistics, 237

indexes

clustered index, 231

defragmentation, 233–234

detecting fragmentation, 231–233

logical scan fragmentation, 233

nonclustered index, 231

page and extent, 232

scan density, 232

log files, 245–246

maintenance plan

additional tasks, 243–244

backup task, 239–241

context menu, 242

designer, 242–243

integrity task, 240

properties, 237–238

tasks page, 238–239

moving data

advanced tab, 222–223

BCP, 227–229

columns tab, 221–222

configure destination, 218–219

copy database wizard, 216–220

data source, 220–221

data types, 223–224

database, 217–218

destination, 224–225

detach and attach method, 214–216

execution status, 226–227

import and export wizard, 220–227

mapping data, 226

package configuration, 219–220

SMO method, 217

transfer method, 216–217

scheduling maintenance tasks, 244

Database restore strategies. See also Full disaster recovery plan

differential backups, 200–201

disaster recovery plan, 185

file backups, 205–206

log backups, 201–205

management studio GUI

backup devices, 187–189

device contents window, 190–191

error and explanation, 192

locate backup file, 188–189

media selection, 191

object explorer window, 186

RestoreTest database, 186–187

options page, GUI, 192–194

testing backups, 207

T-SQL

cleaning up, 200

database copy creation, 195–196

display general information, 197–199

execution, 194–195

FILELISTONLY command, 197

logical and physical file names, 196–197

RESTORE FILELISTONLY command, 199

RESTORE HEADERONLY command, 199

Database security

contained database, 267–269

covering permissions, 264

database permissions, 263–265

database users, 259

fixed database roles, 262–263

flexible database roles, 265–266

GRANT statement, 265

RUNAS command, 268

schemas, 259

default schema, 261

four-part naming convention, 261

reassigning schema ownership, 262

right way, 260

wrong way, 259–260

security functions

fn_my_permissions() function, 266

HAS_PERMS_BY_NAME function, 267

SQLCMD, 268

user-defined database, 267

Deadlocks, 137–139

Detach database method, 214–215

Differential backups

files/device, 163

restoring database, 200–201

syntax, 163–164

Dynamic management functions (DMFs), 306

Dynamic management objects (DMOs), 305–307

Dynamic management view (DMV), 305–307

E

Encrypting backup files, 179–180

Encryption

asymmetric key, 285

Caesar shift cipher, 284

certificate-based encryption

EXECUTE AS statement, 289–290

master key and private key, 288

REVERT statement, 288

symmetric key, 288, 289

ciphertext, 285

compliance requirements, 284

concepts, 285

cryptographic API, 285

password-based encryption

AUTHORIZATION parameter, 286

ContosoBank database, 285

DecryptByKey function, 288

EncryptByKey function, 286–287

SYMMETRIC KEY statement, 286

plain text, 285

primer, 285

private and public key, 285

symmetric keys, 285

transparent data encryption, 290

Esposito, Michael, 11–12

Evaluation edition

download, 20

Installation Center

Advanced tab, 26–27

Installation tab, 22–23

Maintenance tab, 23–24

Options tab, 28

Planning tab, 20–22

Resources tab, 25–26

Scripted install, 27

system configuration checker, 22

Tools tab, 24–25

instance

Database Engine Configuration page, 33–35

Disk Space Requirements page, 31

error reporting page, 35

Features Selection page, 29–30

Installation Rules page, 35

Instance Configuration page, 30–31

Ready to Install page, 35–36

Server Configuration page, 32–33

setup role page, 29

shared components, 30

SQL logins, 34

tempdb database, 35

F

Fault tolerance

AlwaysOn Availability Group

databases page, 359

listener, 361

replicas, 360

SQL Server instance, 355, 358

SQL-WEST instance, 359

synchronization, 362

Windows Server failover cluster, 356–357

database mirroring configuration

accounts details, 353–354

BACKUP DATABASE statement, 350–351

database properties, 351–352

motivation and benefits, 350

operation modes, 350

server instance, 352–353

features

AlwaysOn Availability Group, 355

AlwaysOn Failover Clustering, 354–355

backup and restore, 341

database mirroring, 349–354

differential backup, 341

full database backup, 341

log shipping, 341–348

replication, 348–349

geoclusters, 339

log shipping configuration

backup folders, 343–345

copy files, 346

Pros and Cons, 348

restore transaction log, 346–347

secondary database, 345–346

status report, 347

transaction log shipping, 342–343

UsedCars database, 342

replication

distributor, 348

merge replication, 349

publications, 348

snapshot replication, 349

subscription, 348

transactional replication, 349

service level agreement

definition, 340

metrics, 340–341

nines, 340

uptime, 339

File and filegroup backups, 164–166

fn_my_permissions function, 266

Fritchey, Grant, 5–7

Full backup

backup mirroring, 161

files window, 160

options page, 161

RESTORE command, 162

SQL Server management studio

database window, 158–159

destination, 160

T-SQL, 162–163

Full disaster recovery plan

business information, 208F

downtime, 208

establishment

large commercial insurance company, 209

small online retail business, 209

testing, 210

G

Graphical user interface (GUI), 70

H

HAS_PERMS_BY_NAME function, 267

Health Insurance Portability and Accountability Act (HIPAA), 3

Hyper-V, 403

I

Infrastructure as a service (IaaS), 366

Itanium/(IA64), 14

M

3M Visual Attention Service (VAS)

architecture, 371

graphical design, 370

Microsoft Assessment and Planning (MAP) toolkit, 17, 389–395

Microsoft Desktop Engine (MSDE), 16

Microsoft management console (MMC), 66

Microsoft Online Services Customer Portal (MOSCP), 374

Microsoft SQL Server Upgrade Advisor (Advisor), 395–396

Microsoft System Center Operations Manager (SCOM), 55

Microsoft TechNet, 402

Microsoft Virtual PC 2007, 403–405

N

National Institute on Standards and Technology (NIST), 365

Network interface card (NIC), 300

N

Online transaction processing (OLTP) system, 183

P

Payment Card Industry Data Security Standards (PCI DSS), 3

Performance Data Collector (PDC)

data collection option, 57

management data warehouse, 57

query statistics report, 59–60

Server report, 57–58

subreport, 58–59

Performance measurment

counters

full scans, 301

instance and objects, 300

NIC, 300

SQL Server, 300–301

data collector

baseline, 307

configuration task, 308–310

data view, 311–318

data warehouse storage page, 309

management folder, 308

security, 308–311

dynamic management view (DMV), 305–307

Monitor tool

add counters window, 303–304

counter selection, 304–305

initial window, 302–303

perfmon, 302

Platform as a service (PaaS), 366–367

Policy-based management (PBM)

central management server (CMS), 295–296

evaluation, 294–295

multiple server, 55

need for, 291

policy creation, 291–293

Server configuration, 291

PowerShell, 73–74

Professional Association for SQL Server (PASS), 402

Pyne, Charlie, 8–9

R

Rehak, Roman, 7–8

S

Sarbanes-Oxley (SOX), 3, 271

Service level agreement (SLA), 85, 182, 339

Single-user mode

database, 153

DBCC CHECKDB command, 152

ROLLBACK IMMEDIATE option, 153

SQL Server

command-line argument, 153

configuration, 153–154

properties, 154–155

SQLCMD, 156

Software as a service (SaaS), 366

SQL Azure

administer databases, 372

backup and restore, 385

data hubs, 373

database management

connection string, 378–379

Object Explorer tree, 379–380

SLA, 379

DBA, 372

departmental applications, 373

firewall rules, 376

migrating data

bulk copy utility, 380

CodePlex, 380

results summary page, 381–382

server connection, 383

SQLAzureMW.exe, 380–381

SSSIS, 380

Target Server Connection, 383

MOSCP, 374

properties, 377

relational data, 385

relational database service, 372

server creation, 375–376

service level agreement, 378

software vendors, 373

syntax, 372

system views, 384–385

Tabular Data Stream, 377

web applications, 372

Windows Azure, 373–374, 375

SQL Management Object (SMO) method, 217

SQL Server

32-bit and 64-bit architectures, 14

ALTER TABLE, 114

Books Online, 401

certification, 405

clustered index, 120

conferences, 402

constraints

CHECK, 114

foreign key, 116–117

NULL, 114

primary key, 115

Referential integrity, 116

UNIQUE, 115–116

deadlocks, 137–139

Deployment Wizard, 63

dropping tables, 117–119

evaluation edition, 19

download, 19

Installation Center, 20–28

instance, 28–37

fault tolerance

AlwaysOn Availability Groups, 355–362

AlwaysOn Failover Clustering, 354–355

backup and restore, 341

database mirroring, 349–354

log shipping, 341–348

replication, 348–349

hands-on labs, 403

indexes creation, 119–222

installation

editions, 18

environmental assessment, 17–18

hardware/software requirements, 19

installation and configuration, 13

internals (See also single-user mode)

agent service, 151

Analysis Services, 152

Browser service, 151

Client service, 152

corrupt system databases, 149

data warehouse, 152

database files, 149–150

Distributed Replay Controller service, 152

filegroup, 150

Full-Text Engine Filter Daemon Launcher service, 151

Integration Services, 151

master database, 147

model database, 147

MSDB database, 148

query optimization, 147

query tree, 147

Reporting Services, 152

resource database, 149

resources, 156

Secondary data files, 149

services, 150–52

tempdb database, 147

Volume Shadow Copy Service (VSS), 152

Itanium/IA64, 14

lab system and practice, 403

local users groups, 402

nonclustered index, 120

performance measurment

counters, 300–301

data collector, 307–318

DMOs, 299

dynamic management view (DMV), 305–307

execution plan, 299

Monitor tool, 302–305

resources, 40

resources management

index and data compression, 336

pools, 334

Resource Governor, 334

usage, 334–335

workload, 335

Server editions

Business Intelligence edition, 15

Enterprise edition, 15

features, 14–15

specialized editions, 15–16

Standard edition, 15

setting up, 13

specialized editions

Developer edition, 15

evaluation edition, 16

Express edition, 16

LocalDB, 16

table creation

Ad hoc scripting options, 112

ANSI_NULL statement, 113

CREATE TABLE statement, 112–113

designer, 110–112

QUOTED_IDENTIFER, 113

terminology, 16

tuning queries

Database Engine Tuning Advisor, 331–333

execution plans, 319–326

extended events, 326–331

T-SQL query, 319

upgrading servers, 37

features, 38

hardware, 37

sp_renamedb stored procedure, 39

strategies, 38

Windows Performance Monitor tool, 39–40

virtual machine (VM)

hard disk location, 404

Hyper-V, 403

ISO image, 405

Microsoft Virtual PC 2007, 403–405

time-bombed environment, 403–405

websites

Microsoft TechNet, 402

SQL Server Central, 401

SQL Server Customer Advisory Team, 402

SQL Server World Wide Users Group, 401

SQL Skills site, 402

SQL Server development

data-tier applications

dacpac file, 85–86

definition, 85

deploying, 89–90

existing database, 90

LocalDB, 102

managed instances, 93

management data warehouse, 91

monitoring, 90–95

set properties, 86–87

SLA, 85

SQL Server instance, 92

utility control point (UCP), 90–92

utility node, 93–94

utility node report, 94–95

validation, 87–89

SQL Server Management Studio (SSMS)

IntelliSense, 77–78

need for, 77

Query designer, 78–81

templates, 81–84

T-SQL debugging, 84

SQL Server Development Tools (SSDT)

components, 95–96

connected mode

database updates, 98–99

server explorer, 96–97

table definition, 97–98

offline mode

import database dialog, 99

schema comparison, 100–101

table designer, 99–100

version application, 102

SQL Server instance security

dedicated administrator connection, 257

endpoints, 256–58

fixed server roles, 253–54

login creation, 252–253

network protocols, 258

principal, 252

Securables, 256

server permissions, 255–256

terminology

authentication, 249

authorization, 250

database roles, 250

database users, 250

Mixed mode, 250

representation, server login, 251

Server authentication, 250

server instance vs. database, 250–51

Windows authentication mode, 250

transport protocols, 257

user-defined server roles, 258

SQL Server Management Studio (SSMS). See also Transact-SQL (T-SQL)

Ad hoc scripting options, 112

droping table, 117

execution plan, 121

IntelliSense, 77–78

monitoring server activity

Activity Monitor (AM), 56

Performance Data Collector(PDC), 57–60

reports, 60–61

SCOM, 55

multiple servers, 53

policy-based management (PBM), 55

queries, 55

Registered Servers, 53–54

need for, 77

Object Explorer

context menu, 47–48

database, 48–49

menu options, 50

multiple connections, 46–47

Object Explorer tree, 105–106

products, 43

properties tab, 45

Queries

components, 46

document window, 52

Object Explorer, 46–50

Query Editor, 50–52

results pane, 53

Query designer

Add Table dialog, 78

table relationship, 79–81

Query menu, 120

scripting actions, 106–108

SQL Server connections, 43–45

table designer, 111

templates

explorer, 81

parameters dialog box, 82–83

stored procedure, 81–84

T-SQL debugging, 84

SQL Server private cloud, 387

Advisor tool, 395–396

architecture, 387

Assistant tool, 396–397

control and customization, 399

elasticity, 398–399

Hyper-V Dynamic Memory, 399

implementation options, 399–400

MAP tool

DatabaseInstance tab, 393–394

discovery and readiness, 389–390

discovery methods, 391–392

excel file tab, 394–395

inventory and assessment wizard, 390–391

summary tab, 392–393

resource pooling, 389

self-service, 399

stages, 387–388

Sysprep, 398

virtual machines, 398

virtualization, 389

SQL Server Upgrade Assistant (Assistant), 396–397

SQLCMD

defined, 70

overview, 71

Passing Variables, 72–73

SQL Server connection, 71–72

Sysprep, 398

T

Tabular Data Stream (TDS), 377

TechNet, 402

Toolbox. See also Command-line tools, SQL Server Management Studio (SSMS)

Analysis Services (SSAS), 63

Client Protocols node, 67–68

CodePlex, 68

Configuration Tools folder

Reporting Services Configuration Manager, 64–65

SQL Server Configuration Manager, 66–68

SQL Server Error and Usage Report, 63–64

SQL Server Installation Center, 63

Documentation and Community folder

hyperlink, 68

Microsoft SQL samples overview, 68

SQL Server Books Online, 68

Import and Export Data Wizard, 70

Integration Services folder

Data Profiler Viewer, 69

ETL tool, 68

Execute Package utility, 69

Project Conversion wizard, 69

Performance Tools folder, 69

Database Engine Tuning Advisor, 69

SQL Trace, 70

protocols node, 66–67

services node, 66

SQL Server 2012, 61–62

SQL Server Data Tools (SSDT), 70

Transact-SQL (T-SQL). See also Data Manipulation Language (DML)

aggregate functions, 144

BEGIN TRANSACTION statement, 136

Built-in functions, 144

character set, 127

COMMIT TRANSACTION statement, 137

data types, 126–127

DCL, 110

DDL, 109

deadlocks, 137–139

DML, 109–110

function creation, 142–143

function invoking, 143

NULL, 128

primer, 109

ROLLBACK statement, 136

Scalar functions, 144

script, 175

security-related functions, 144

SQL Server Books Online Articles, 146

stored procedure

ALTER STORED PROCEDURE statement, 142

CREATE PROCEDURE statement, 139

modifying, 142

system folder, 142

templates, 139–142

transaction execution, 136

transaction isolation, 136–137

trigger, 144–145

Unicode vs. ANSI, 127

user-defined function, 144

VetClinic database, 125–126

Transparent data encryption (TDE), 179

ContosoBank database, 290

database encryption key, 290

encrypting backup files, 179

Trigger, 144–145

Tripp, Kimberly, 402

Tuning queries

DTA, 331–333

execution plans

actual execution plan, 321–322

data flow tooltip, 324

estimated plan, 320–321

graphical format, 320

Hash Match operator, 322

Nested Loops tooltip, 323

operator properties, 324–325

query optimization, 319

SQL Editor toolbar, 320

text execution, 320

XML, 325–326

extended events

action and session, 326

data storage window, 330

event filter, 329–330

graphical user interfaces, 327

output window, 330–331

selection, 328

session wizard, 327

template, 327–328

T-SQL query, 319

U

Unicode vs. ANSI, 128

Utility control point (UCP), 90–92

V

Virtualization, 389

Volume Shadow Copy Service (VSS), 152

W, X, Y, Z

Windows Azure

AppFabric, 369

platform, 367

roles, 368

storage, 369