Professional Microsoft SQL Server 2014 Administration E-Book

Adam Jorgensen

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1354

Veröffentlichungsjahr: 2014

Beschreibung

Learn to take advantage of the opportunities offered by SQL Server 2014 Microsoft's SQL Server 2014 update means big changes for database administrators, and you need to get up to speed quickly because your methods, workflow, and favorite techniques will be different from here on out. The update's enhanced support of large-scale enterprise databases and significant price advantage mean that SQL Server 2014 will become even more widely adopted across the industry. The update includes new backup and recovery tools, new AlwaysOn features, and enhanced cloud capabilities. In-memory OLTP, Buffer Pool Extensions for SSDs, and a new Cardinality Estimator can improve functionality and smooth out the workflow, but only if you understand their full capabilities. Professional Microsoft SQL Server 2014 is your comprehensive guide to working with the new environment. Authors Adam Jorgensen, Bradley Ball, Ross LoForte, Steven Wort, and Brian Knight are the dream team of the SQL Server community, and they put their expertise to work guiding you through the changes. * Improve oversight with better management and monitoring * Protect your work with enhanced security features * Upgrade performance tuning, scaling, replication, and clustering * Learn new options for backup and recovery Professional Microsoft SQL Server 2014 includes a companion website with sample code and efficient automation utilities, plus a host of tips, tricks, and workarounds that will make your job as a DBA or database architect much easier. Stop getting frustrated with administrative issues and start taking control. Professional Microsoft SQL Server 2014 is your roadmap to mastering the update and creating solutions that work.

Leseprobe

Table of Contents

Cover

Chapter 1: SQL Server 2014 Architecture

SQL SERVER 2014 ECOSYSTEM

NEW IMPORTANT FEATURES IN 2014

SQL SERVER ARCHITECTURE

EDITIONS OF SQL SERVER

SUMMARY

Chapter 2: Installing SQL Server 2014 Best Practices

PLANNING THE SYSTEM

INSTALLING SQL SERVER

INSTALLING ANALYSIS SERVICES

INSTALLING PowerPivot FOR SharePoint

BURNING IN THE SYSTEM

POST-INSTALL CONFIGURATION

UNINSTALLING SQL SERVER

TROUBLESHOOTING A FAILED INSTALL

SUMMARY

Chapter 3: Upgrading SQL Server 2014 Best Practices

WHY UPGRADE TO SQL SERVER 2014?

UPGRADING TO SQL SERVER 2014

PRE-UPGRADE STEPS AND TOOLS

BACKWARD COMPATIBILITY

SQL SERVER COMPONENT CONSIDERATIONS

POST-UPGRADE CHECKS

SUMMARY

Chapter 4: Managing and Troubleshooting the Database Engine

CONFIGURATION AND ADMINISTRATION TOOLS

TROUBLESHOOTING TOOLS

SQL SERVER MANAGEMENT STUDIO

MONITORING PROCESSES IN T-SQL

MULTISERVER MANAGEMENT

TRACE FLAGS

GETTING HELP FROM SUPPORT

SUMMARY

Chapter 5: Automating SQL Server

MAINTENANCE PLANS

AUTOMATING SQL SERVER WITH SQL SERVER AGENT

SQL SERVER AGENT SECURITY

CONFIGURING SQL SERVER AGENT

MULTISERVER ADMINISTRATION

SUMMARY

Chapter 6: Service Broker in SQL Server 2014

ASYNCHRONOUS MESSAGING

CONFIGURING SQL SERVER SERVICE BROKER

USING SQL SERVER SERVICE BROKER

SUMMARY

Chapter 7: SQL Server CLR Integration

INTRODUCTION TO CLR

CREATING CLR ASSEMBLIES

CLR INTEGRATION SECURITY

PERFORMANCE MONITORING

SUMMARY

Chapter 8: Securing the Database Instance

AUTHENTICATION TYPES

LOGINS AND USERS

AUTHORIZING SECURABLES

ROW-LEVEL SECURITY

SUMMARY

Chapter 9: In-Memory OLTP

USING AND IMPLEMENTING IN-MEMORY OLTP

CREATING NATIVELY COMPILED STORED PROCEDURES

OVERVIEW OF THE ANALYZE, MIGRATE, AND REPORT (ARM) TOOL

SUMMARY

Chapter 10: Configuring the Server for Optimal Performance

WHAT EVERY DBA NEEDS TO KNOW ABOUT PERFORMANCE

WHAT THE DEVELOPER DBA NEEDS TO KNOW ABOUT PERFORMANCE

WHAT THE PRODUCTION DBA NEEDS TO KNOW ABOUT PERFORMANCE

CPU

MEMORY

I/O

SUMMARY

Chapter 11: Optimizing SQL Server 2014

APPLICATION OPTIMIZATION

THE SILENT KILLER: I/O PROBLEMS

SQL SERVER INTERNALS AND FILE ALLOCATIONS

TABLE AND INDEX PARTITIONING

DATA COMPRESSION

UNDERSTANDING SQL SERVER AND CPUs

MEMORY CONSIDERATIONS AND ENHANCEMENTS

RESOURCE GOVERNOR

SUMMARY

Chapter 12: Monitoring Your SQL Server

THE GOAL OF MONITORING

CHOOSING THE APPROPRIATE MONITORING TOOLS

PERFORMANCE MONITOR

MONITORING EVENTS

MONITORING WITH DYNAMIC MANAGEMENT VIEWS AND FUNCTIONS

MONITORING LOGS

SQL SERVER STANDARD REPORTS

SYSTEM CENTER ADVISOR

SUMMARY

Chapter 13: Performance Tuning T-SQL

OVERVIEW OF QUERY PROCESSING

IDENTIFYING SQL QUERY PERFORMANCE TUNING ISSUES

DATA ACCESS OPERATORS IN QUERY PLANS

JOIN OPERATORS

DATA MODIFICATION QUERY PLAN

ANALYZING QUERY PERFORMANCE IN A PRODUCTION ENVIRONMENT

PUTTING IT ALL TOGETHER

SUMMARY

Chapter 14: Indexing Your Database

WHAT'S NEW FOR INDEXES IN SQL SERVER 2014

ABOUT INDEXES AND PARTITIONED TABLES

INDEX MAINTENANCE

IMPROVING QUERY PERFORMANCE WITH INDEXES

DATABASE TUNING ADVISOR

SUMMARY

Chapter 15: Replication

REPLICATION OVERVIEW

REPLICATION MODELS

IMPLEMENTING REPLICATION

PEER-TO-PEER REPLICATION

SCRIPTING REPLICATION

MONITORING REPLICATION

SUMMARY

Chapter 16: Clustering SQL Server 2014

CLUSTERING AND YOUR ORGANIZATION

CLUSTERING: THE BIG PICTURE

UPGRADING SQL SERVER CLUSTERING

GETTING PREPARED FOR CLUSTERING

CLUSTERING WINDOWS SERVER 2012 R2

CLUSTERING MICROSOFT DISTRIBUTED TRANSACTION COORDINATOR

CLUSTERING SQL SERVER 2014

MANAGING AND MONITORING THE CLUSTER

TROUBLESHOOTING CLUSTER PROBLEMS

SUMMARY

Chapter 17: Backup and Recovery

BACKUP AND RESTORE ENHANCEMENTS

OVERVIEW OF BACKUP AND RESTORE

PREPARING FOR RECOVERY

DEVELOPING AND EXECUTING A BACKUP PLAN

MANAGING BACKUPS

BACKUP AND RESTORE PERFORMANCE

PERFORMING RECOVERY

ARCHIVING DATA

SUMMARY

Chapter 18: SQL Server 2014 Log Shipping

LOG-SHIPPING DEPLOYMENT SCENARIOS

LOG-SHIPPING ARCHITECTURE

LOG-SHIPPING PROCESS

SYSTEM REQUIREMENTS

DEPLOYING LOG SHIPPING

MONITORING AND TROUBLESHOOTING

MANAGING CHANGING ROLES

DATABASE BACKUP PLAN

INTEGRATING LOG SHIPPING WITH OTHER HIGH-AVAILABILITY SOLUTIONS

REMOVING LOG SHIPPING

LOG-SHIPPING PERFORMANCE

UPGRADING TO SQL SERVER 2014 LOG SHIPPING

SUMMARY

Chapter 19: Database Mirroring

OVERVIEW OF DATABASE MIRRORING

OPERATING MODES OF DATABASE MIRRORING

DATABASE MIRRORING IN ACTION

DATABASE MIRRORING AND SQL SERVER 2014 EDITIONS

DATABASE MIRRORING ROLE CHANGE

MONITORING USING DATABASE MIRRORING MONITOR

PREPARING THE MIRROR SERVER FOR FAILOVER

DATABASE MIRRORING AND OTHER HIGH-AVAILABILITY SOLUTIONS

DATABASE SNAPSHOTS

SUMMARY

Chapter 20: Integration Services Administration and Performance Tuning

A TOUR OF INTEGRATION SERVICES

ADMINISTRATION OF THE INTEGRATION SERVICES SERVICE

ADMINISTRATION OF INTEGRATION SERVICES PACKAGES IN PACKAGE DEPLOYMENT MODEL

ADMINISTRATION OF INTEGRATION SERVICES PACKAGES IN PROJECT DEPLOYMENT MODEL

EXECUTION AND SCHEDULING

APPLYING SECURITY TO INTEGRATION SERVICES

SUMMARY

Chapter 21: Analysis Services Administration and Performance Tuning

TOUR OF ANALYSIS SERVICES

ADMINISTERING ANALYSIS SERVICES SERVER

ADMINISTERING ANALYSIS SERVICES DATABASES

ANALYSIS SERVICES PERFORMANCE MONITORING AND TUNING

SUMMARY

Chapter 22: SQL Server Reporting Services Administration

SQL SERVER REPORTING SERVICES CONFIGURATION MANAGER

THE REPORT EXECUTION LOG

REPORT MANAGER

SUMMARY

Chapter 23: SQL Server 2014 SharePoint 2013 Integration

COMPONENTS OF INTEGRATION

DATA REFRESH

SUMMARY

Chapter 24: SQL Database Administration and Configuration

GETTING TO KNOW WINDOWS AZURE SQL DATABASE

SQL DATABASE ARCHITECTURE

CONFIGURING SQL DATABASE

ADMINISTERING SQL DATABASE

WORKING WITH SQL DATABASE

WHAT'S MISSING IN SQL DATABASE

SUMMARY

Chapter 25: AlwaysOn Availability Groups

ARCHITECTURE

AVAILABILITY GROUP EXAMPLE

ACTIVE SECONDARY FOR SECONDARY READ-ONLY

BACKUP ON THE SECONDARY REPLICA

ALWAYSON GROUP DASHBOARD

MONITORING AND TROUBLESHOOTING

SUMMARY

Introduction

Who This Book Is For

How This Book Is Structured

What You Need to Use This Book

Conventions

Source Code

Errata

p2p.wrox.com

Advertisement

End User License Agreement

Pages

xxxvii

xxxviii

xxxix

xl

xli

xlii

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

61

60

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

101

100

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

181

179

180

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

207

205

206

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620