Microsoft SQL Server 2012 Administration

(1)

Microsoft® SQL

Server

2012

Administration

Adam

_Jorgensen

Steven Wort

Ross LoForte

Brian

_Knight

®

WILEY

(2)

_Ecosystem

1

New

_Important

Features in 2012 2

Production DBA 2

Development

DBA 2

Business

_Intelligence

DBA and

_Developer

3

SQL Server Architecture 4 Database Files and Transaction

_Log

4

SQLNative Client 5

Standard

System

Databases 6

Schemas 8

Synonyms 8

Dynamic

Management

Objects

9

SQL Server 2012 Data

_Types

10

Editions of SQL Server 17

Edition Overview 17

Licensing

18

Summary

20

CHAPTER 2: INSTALLING SQLSERVER 2012 BEST PRACTICES 21

Planning

the

_System

22

Hardware Options 22

Software and Install Options 27

Installing

SQL Server 29 New Installs 30

Side-by-Side

Installs 30

Upgrades

30 Unattended Installs 30 Attended Installations 36

Installing

Analysis

Services 40

Multidimensional and Data

_Mining

Mode

(UDM

Mode)

41

Tabular Mode 42

Installing

PowerPivot for SharePoint 43

(3)

Post-Install

_{Configuration}

45

Configuring

SQLServer

_Settings

forPerformance 46

tempdb

47

Configuring

SQL Server

Setting

for

Security

49

Best _{Practices Analyzer}

_(BPA)

50 SQL Server

_{Configuration}

_Manager

50

Back It

_Up

51

Uninstalling

SQL Server 51

Uninstalling Reporting

Services 51

Uninstalling Analysis

Services 51

Uninstalling

the SQLServer Database

Engine

52

Troubleshooting

a FailedInstall 52

Summary

52

CHAPTER 3: UPGRADING SOL SERVER 2012 BEST PRACTICES 53

Why Upgrade

to SQL Server 2012? 53

Risk

_Mitigation

—the Microsoft Contribution 54

Independent

Software Vendors and SQL

_Community

Contributions 54

Upgrading

toSQL Server 2012 55

In-Place

_Upgrading

55

Side-by-Side Upgrade

57

In-Place

_Upgrade

versusSide-By-Side

Upgrade

Considerations 58

Pre-Upgrade

Steps

and Tools 58

Pre-Upgrade Steps

58

Pre-Upgrade

Tools 59

Backward

_{Compatibility}

67

Unsupported

and Discontinued Features inSQLServer2012 67 SQL Server 2012

_Deprecated

Database Features 67 Other SQL Server 2012

_{Changes Affecting}

Behavior 68

SQL Server

_Component

Considerations 68

Upgrading

Full-Text

Catalog

68

Upgrading

ReportingServices 68

Upgrading

to 64-Bit 69

Post-Upgrade

Checks 69

Poor

_Query

Performance After

_Upgrade

69

Summary

70

CHAPTER 4:MANAGING ANDTROUBLESHOOTING

THE DATABASE ENGINE 71

Configuration

and

Administration

Tools 71

SQL Server

_{Configuration}

Manager 72

Startup

Parameters ₇₃

(4)

CONTENTS

Startup

Stored Procedures 77

Partially

Contained Databases 78

Troubleshooting

Tools 79

Dedicated AdministratorConnection 79

Rebuilding

the

_System

Databases 81

Management

Studio 82

Reports

82

Configuring

SQL Serverin

_Management

Studio 85

Filtering Objects

90

Error

_Logs

90

Activity

Monitor ₉₁

Monitoring

Processes

in T-SQL 96

sp_who

and sp_who2 96

sys.dm__exec_connections

97

sys.dm_exec_sql_text

97

Multiserver

_Management

98

Central

_Management

Servers and Server

Groups

98 SQL Server

_Utility

99

Trace

_Flags

99

Getting Help

from

Support

101

SQLDumper.exe

101

SQLDiag.exe

102

Summary

104

CHAPTER 5: AUTOMATINGSQL SERVER 105

Maintenance Plans 106

Maintenance PlanWizard 106 Maintenance Plan

_Designer

109

Automating

SQL

Serverwith SQL Server

_Agent

111

Jobs 112

Schedules 117

Operators 118

Alerts 121

SQL Server

_{Agent Security}

126

Service Account 126

AccesstoSQL

_Agent

126 SQL Server

_Agent

Proxies 127

Configuring

SQL Server

_Agent

130

General

Properties

131

Advanced

_Properties

132 Alert

_System

_Properties

133

Job

_{System Properties}

133

(5)

Connection Properties 134 History

Properties

134 Database Mail ₁₃₄ Architecture ₁₃₅

Security

135

Configuration

136

Archiving

140 Multiserver Administration 140

Using

Token

_Replacement

140

Event

_Forwarding

₁₄₃

Using

WMI ₁₄₃

Multiserver Administration —

Using

Master and

Target

Servers 145

Summary

146

CHAPTER 6: SERVICEBROKER IN SQL SERVER 2012 147

Asynchronous Messaging

147

SQLService Broker Overview 148

SQL Server Service Broker Versus Other

_Message

Queues 148

Configuring

SQL Server Service Broker 149

Enabling

149

Message

Types

151 Contracts ₁₅₁ Queues ₁₅₂ Services ₁₅₃ Routes ₁₅₄ Priorities ₁₅₆ Conversation Groups 156

Using

SQL Server ServiceBroker ₁₅₇

Sending

Messages

157

Receiving

Messages

160

Sending

Messages

BetweenDatabases 161

Sending Messages

Between Instances 162

External Activation ₁₆₃

Summary

165

CHAPTER7: SQL_{SERVER CLR INTEGRATION} ₁₆₇

Introduction

to the CLR ₁₆₇

SQLServeras a .NET Runtime Host 169

Application

Domains ₁₇₀

T-SQLversus CLR ₁₇₀

Enabling

CLR

_Integration

₁₇₁

Creating

CLR

Assemblies

172

The Non-Visual Studio

_Way

₁₇₂

(6)

Using

MicrosoftSQL Server Data Tools 174

Securing

CLR 176

Performance

_Monitoring

177

Windows

_System

Monitor 177

SQL Profiler 178

Dynamic Management

Views

_(DMVs)

179

CLR

_{Integration Design}

Goals 180

Summary

180

CHAPTER 8:_{SECURING THE DATABASE INSTANCE} 181

Authentication

_Types

181

SQL Authentication 182

Windows Authentication 183

SQL Versus Windows Authentication 183

Authorizing

Securables 184

Server Securables 185

DatabaseSecurables 189

Permission Chains 190 Cross Database Permission Chains 191

Row Level

_Security

193

Summary

194

CHAPTER 9: CHANGE MANAGEMENT 197

Creating

Solutions and

_Projects

198

Creating

aConnection 199

Creating

a

Project Query

200

Policy-Based Management

200

Policy-Based Management

Overview 201

Policy-Based Management

Step

by

Step 202

Scripting Policy-Based Management

209

Policy-Based Management Implementation

210

DDL

_{Trigger Syntax}

212 Database

Triggers

212 Server

Triggers

217

Trigger

Scripting

Overview 218

sqlcmd

219 PowerShell 223

Creating Change Scripts

225

Data-Tier

_Applications

225

SQL Server Data Tools 229

VersionTables 229

(7)

CHAPTER 10: CONFIGURING THE SERVER FOR OPTIMAL

PERFORMANCE 233

What

_Every

DBA Needsto Know About Performance 234

The Performance

_{Tuning Cycle}

234

Defining

Good Performance 235

Focus onWhat's Most

Important

236

What

the Developer

DBA

Needs

to Know About Performance 237

Users 237

SQL Statements 237

Data

_Usage

Patterns 238

Robust Schema 238

What the Production DBA Needs toKnow About Performance 238

Optimizing

the Server 239

Hardware

_Management

241 CPU 241 x64 242 Cache 242

Hyper-threading

243 Multicore 244

System

Architecture 246

Memory

248

Physical Memory

248

Physical

Address

_Space

248

Virtual

Memory Manager

249

The Page File 249

Page

Faults 250

I/O

251

Network 252

Disks 252

Storage

Considerations 255

Designing

a

Storage System

257

Large Storage System

Considerations:SAN

_Systems

262 Server

_{Configuration}

264

Fragmentation

269

Summary

271

CHAPTER 11: OPTIMIZING SQL SERVER 2012 273

Application Optimization

273

Defining

aWorkload 274

System Harmony

Is the Goal 274

The Silent Killer: I/O Problems 274

(8)

CONTENTS

SQL Server I/OProcessModel ₂₇₅

DatabaseFile Placement ₂₇₅

tempdb

Considerations ₂₇₆

Table and Index

_Partitioning

₂₇₉

Why

Consider

_{Partitioning?}

₂₈₀

Creating

a Partition Function ₂₈₁

Creating

Filegroups

284

Creating

a Partition Scheme ₂₈₄

Creating

Tables and Indexes ₂₈₅

Data

_Compression

₂₉₀

Row

_Compression

₂₉₀

Page Compression

₂₉₁

Estimating

Space

Savings

293

Monitoring

Data

_Compression

₂₉₅

Data

_Compression

Considerations ₂₉₅

CPU Considerations ₂₉₆

Cache

_Coherency

₂₉₇

Affinity

Mask ₂₉₇

Max

_Degree

of Parallelism

_(MAXDOP)

₃₀₀

Affinity

I/O Mask ₃₀₁

Memory

Considerations and

Enhancements

302

Tuning

SQL Server

_Memory

₃₀₂

64-bitVersionsof SQLServer 2012 ₃₀₅

Data

_Locality

₃₀₆

Max Server

_Memory

₃₀₇

Index Creation

_{Memory Option}

₃₀₇ Minimum

_Memory

_per

_Query

₃₀₈

Resource Governor 309

The Basic Elementsof ResourceGovernor 309

Using

Resource Governorfrom SQL Server2012

Management

Studio ₃₁₃

Monitoring

Resource Governor 314

Summary

315

CHAPTER 12: _{MONITORING YOUR SQL SERVER} ₃₁₇

The Goal of

_Monitoring

₃₁₈

Determining

Your

_Monitoring

_Objectives

₃₁₈

Establishing

a Baseline ₃₁₈

Comparing

CurrentMetricsto the Baseline 319

Choosing

the

_{Appropriate Monitoring}

Tools 319 Performance Monitor ₃₂₁

CPU Resource Counters ₃₂₂

(9)

Disk

_Activity

₃₂₄

Memory

Usage

330

Performance

_Monitoring

Tools ₃₃₃

Monitoring

Events ₃₃₅

The Default Trace ₃₃₇

system_health

Session ₃₃₈

SQL Trace ₃₃₈

Event Notifications ₃₅₂

SQLServer Extended Events ₃₅₅

Monitoring

with

_{Dynamic Management}

Views and

Functions

376

What's

_Going

on Inside SQLServer? 377

Viewing

the

_Locking

Information 380

Viewing

Blocking

Information 380

Index

_Usage

in a Database ₃₈₁

Indexes Not Usedin a Database ₃₈₂ View Queries

_Waiting

for

_Memory

Grants ₃₈₃ Connected UserInformation ₃₈₄

Filegroup

Free

Space

384

Query

Plan and

_Query

Text for

_{Currently Running}

Queries 385

Memory Usage

385

Buffer Pool

_Memory

_Usage

₃₈₅

Monitoring

Logs

386 Monitoring

the SQLServer Error

_Log

₃₈₆

Monitoring

the Windows Event

_Logs

₃₈₇

Management

Data

Warehouse

₃₈₇

System

Data CollectionSets ₃₈₈

Viewing

DataCollected

_by

the

_System

Data Collection Sets 388

Creating

Your Own Data Collection Set ₃₉₀

Examining

the Data YouCollected ₃₉₂

SQL

Server

Standard

_Reports

₃₉₃

System

Center

_Management

Pack ₃₉₅

SQL Server Best Practice

_Analyzer

₃₉₆

System

Center Advisor ₃₉₆

Summary

397

CHAPTER 13: PERFORMANCE TUNINGT SQL 399

Physical Query Processing

Part

One:

_Compilation

and

_{Recompilation}

₃₉₉

Compilation

₄₀₀

Recompilation

₄₀₁

Tools and Commands for

_{Recompilation}

_Scenarios ₄₀₈

(10)

CONTENTS

Parser and

_Algebrizer

₄₁₀

Optimization

412

Physical

Query

Processing

Part Two:

Execution

417

Database I/O Information ₄₁₈

Working

withthe

_Query

Plan ₄₁₉

Estimated Execution Plan ₄₂₀ Actual Execution Plan ₄₂₄

IndexAccess Methods ₄₂₇

Fragmentation

438

Statistics ₄₃₉

Join

_Algorithms

₄₄₀

Data Modification

_Query

Plan ₄₄₃

Query

Processing

Enhancementson PartitionedTables and Indexes 444

Gathering Query

Plans for

_Analysis

with SQL Trace 446

Summary

447

CHAPTER 14: INDEXING YOUR DATABASE 449

Noteworthy

Index-Related

Features in SQLServer 449

What's New forIndexes in SQL Server 2012 450 Index Featuresfrom SQLServer

_2008R2,

SQL Server

2008,

and SQLServer 2005 ₄₅₂

Partitioned Tables

and Indexes ₄₅₅

Understanding

Indexes 455

CreatingIndexes ₄₅₈

Why Use Both Partitioned Tablesand Indexes? 459

Creating

PartitionedTables 460

Index Maintenance ₄₆₁

Monitoring

Index

_{Fragmentation}

462

Cleaning Up

Indexes 462

Improving Query

Performance with Indexes 464

Database

_Tuning

Advisor 468

Too

_Many

Indexes? 469

Summary

471

CHAPTER 15: REPLICATION ₄₇₃

Replication

Overview

473

Replication Components

474

Replication Types

476

Replication

Enhancements in SQL Server 2012 478

Replication

Models

₄₇₈

(11)

Multiple Publishers, Single

Subscriber 480

Multiple

PublishersAlso

_Subscribing

481

Updating

Subscriber 482

Peer-to-Peer 483

Implementing Replication

484

Setting Up Snapshot Replication

484

Setting Up

Distribution 484

Implementing Snapshot Replication

487

Implementing

Transactional and

_Merge

Replication 497

Peer-to-Peer

Replication

498

Setting Up

Peer-to-Peer

_Replication

498

Configuring

Peer-to-Peer

_Replication

499

Scripting Replication

502

Monitoring Replication

502

Replication

Monitor 502 Performance Monitor 505

Replication

DMVs 505

sp_replcounters

506

Summary

507

CHAPTER 16: CLUSTERING SQL SERVER 2012 509

Clustering

and Your

_Organization

510

What

_Clustering

Can Do 510

What

_Clustering

Cannot Do ₅₁₁

Choosing

SQLServer 2012

_Clustering

for the

_Right

Reasons 512

Alternatives to

Clustering

512

Clustering:

The

_Big

Picture 514

How

_Clustering

Works 515

Clustering Options

518

Upgrading

SQL Server

_Clustering

520

Don't

_Upgrade

520

Upgrading

Your SQL Server 2012 ClusterIn Place 520

Rebuilding

Your Cluster 521

Back-Out Plan 523

Which

_{Upgrade Option}

Is Best? 523

Getting Prepared

for

_Clustering

523

Preparing

the Infrastructure 523

Preparing

the Hardware 524

Clustering

Windows Server 2008 527

Before

_Installing

Windows 2011

_Clustering

527

Installing

WindowsServer 2008 Failover

_Clustering

528

Preparing

Windows Server 2008 for

_Clustering

531

(12)

CONTENTS

Clustering

Microsoft

_{Distributed Transaction Coordinator}

₅₃₂

Clustering

SQL Server 2012 ₅₃₄

Step by Step

to ClusterSQL Server ₅₃₄

Installing

the Service Pack and Cumulative

_Updates

540

Test,

and Test

_Again

₅₄₀

Managing

and

_Monitoring

the Cluster ₅₄₂

Troubleshooting

Cluster

Problems

₅₄₃

How toApproach WindowsFailover

_Clustering

Troubleshooting

544

Doing

It

_Right

the First Time ₅₄₄

Gathering

Information ₅₄₄

Resolving

Problems ₅₄₅

Working

with Microsoft ₅₄₅

Summary

546

CHAPTER 17: BACKUP AND RECOVERY ₅₄₇

Types

of Failure ₅₄₈

Hardware Failure ₅₄₈

Data Modification Failure ₅₄₈

SoftwareFailure ₅₅₀

Local Disasters ₅₅₀

Making

Plans

551

Backup/Recovery

Plan ₅₅₁

Disaster

_Recovery

_Planning

₅₅₄

Creating

the Disaster

_Recovery

Plan ₅₅₆

Maintaining

the Plan ₅₅₈

Overview of

_Backup

and

Restore ₅₅₉

How

_Backup

Works ₅₅₉

Copying

Databases ₅₆₂

Backup

Compression

570

Comparing

Recovery

Models ₅₇₁

Choosing

a Model ₅₇₃

Switching Recovery

Models ₅₇₄

Backing

Up

History

Tables ₅₇₅

Permissions

_Required

for

_Backup

and Restore ₅₇₆

Backing Up System

Databases ₅₇₇

Full-Text

_Backup

₅₇₈

Verifying

the

_{Backup Images}

₅₇₈

HowRestore Works ₅₇₉

Preparing

for

_Recovery

₅₈₁

Recoverability

Requirements

581

Data

_Usage

Patterns ₅₈₂

(13)

MaintenanceTime Window 583

Other

_{High-Availability}

Solutions 584

Developing

and

_Executing

a

_Backup

Plan 585

Using

SQL Server

_Management

Studio 585

Database Maintenance Plans 589

Using

Transact-SQL

_Backup

Commands 591

Managing Backups

593

Backup

and Restore Performance 594

Performing

Recovery

Restore Process

SQL Server

_Management

Studio Restore 599

T-SQLRestore Command 602

Restoring System

Databases 602

Archiving

Data 604

SQL Server Table

_Partitioning

604

Partitioned View 605

Summary

606

CHAPTER 18: SQL SERVER 2012 LOG SHIPPING 607

Log Shipping Deployment

Scenarios

608

Log

Shipping

to Createa Warm

Standby

Server 608

Log Shipping

as a Disaster

Recovery

Solution 609

Log Shipping

as a

Report

DatabaseSolution 610

Log-Shipping

Architecture 611

Primary

Server 611

Secondary

Server 611

Monitor Server 612

Log

Shipping

Process 612

System Requirements

613

Network 613

Identical

_Capacity

Servers 613

Storage

614

Software 614

Deploying

Log

Shipping

614

Initial

_{Configuration}

₆₁₄

Deploying

with

_Management

Studio 616

Deploying

with T-SQLCommands 624

Monitoring

and

_{Troubleshooting}

624

Monitoringwith

_Management

Studio 625

Monitoring

with Stored Procedures 626

Troubleshooting

Approach

626

(14)

CONTENTS

Managing Changing

Roles 627

Synchronizing

Dependencies

627

Switching

Roles fromthe

_Primary

to

_Secondary

Servers 630

Switching

Between

_Primary

and

_Secondary

Servers 632

Redirecting

Clients to Connect to the

_Secondary

Server 632 Database

_Backup

Plan ₆₃₃

Integrating Log Shipping

with

Other

_{High-Availability}

Solutions

634

SQL Server 2012 Data

Mirroring

634 WindowsFailover

Clustering

635 SQL Server 2012

_Replication

₆₃₅

Removing Log Shipping

636

Removing Log Shipping

with

_Management

Studio 636

Removing

Log Shipping

with T-SQLCommands 636

Log-Shipping

Performance 637

Upgrading

toSQL Server 2012

_{Log Shipping}

638

Minimum Downtime

_Approach

₆₃₈ With Downtime

_Approach

638

Deploy Log

Shipping Approach

639

Summary

639

CHAPTER 19: DATABASE MIRRORING 641

Overview of Database

_Mirroring

641

Operating

ModesofDatabase

_Mirroring

643

Database

_Mirroring

In Action 645

Preparing

the

_Endpoints

646

Preparing

the Databasefor

_Mirroring

652

Initial

_{Synchronization}

Between

_Principal

and Mirror 653

Establishing

the

_Mirroring

Session 653

High-Safety

Operating

ModeWithout AutomaticFailover 655

High-Safety

Operating

Mode with Automatic Failover 655

High-Performance

Operating

Mode 657

Database

_Mirroring

and SQL Server2012 Editions 658 Database

_{Mirroring Catalog}

sys.database_mirroring

658

sys.database__mirroring_witnesses

660

sys.database_mirroring_endpoints

660

Database

Mirroring

Role

_Change

661

Automatic Failover 661

Manual Failover 664

Forced Failover 666

Database

_Availability

Scenarios 667

Principal

Is Lost 667

(15)

Mirror is Lost ₆₆₈

Witness Is Lost ₆₆₉

Mirror and Witness Are Lost ₆₆₉

Monitoring

Database

_Mirroring

₆₇₀

Monitoring Using System

Monitor ₆₇₀

Monitoring

Using

Database

_Mirroring

Monitor ₆₇₂

Setting

Thresholdson Countersand

_Sending

Alerts 676

Troubleshooting

Database

_Mirroring

₆₇₈

Troubleshooting Setup

Errors ₆₇₈

Troubleshooting

RuntimeErrors ₆₇₉

Automatic

_{Page Repair}

₆₈₀

Preparing

the Mirror Serverfor

Failover

681

Hardware,

Software,and Server

_{Configuration}

₆₈₁ Database

_Availability

_During

Planned Downtime ₆₈₂

SQL Job

_{Configuration}

onthe Mirror ₆₈₄ DatabaseTRUSTWORTHY Bitonthe Mirror ₆₈₄

Client Redirection tothe Mirror ₆₈₄

Mirroring Multiple

Databases ₆₈₅

Database

_Mirroring

and Other

_{High-Availability}

_Solutions

₆₈₆ Database

_Mirroring

versus

_Clustering

₆₈₇

Database

_Mirroring

versus Transactional

_Replication

₆₈₇ Database

_Mirroring

versus

Log

_Shipping

₆₈₇

Database

_Mirroring

Versus

_Availability

_Groups

₆₈₈

Mirroring

Event Listener

_Setup

₆₈₈

Database

_Snapshots

₆₉₂

Summary

693

CHAPTER 20: _{INTEGRATION SERVICES}

_{ADMINISTRATION}

AND

_PERFORMANCE

_TUNING ₆₉₅

ATourof

_Integration

Services ₆₉₆

Integration

Services Uses ₆₉₆

The Main Parts of

_Integration

Services ₆₉₇

Project

Management

and

_Change

Control ₆₉₉

Administration

ofthe

_Integration

_Services

Service 699

An Overviewof the

_Integration

_{Services Service} ₇₀₀

Configuration

₇₀₀

Event

_Logs

₇₀₄

Monitoring

Activity

705

Administration

of

_Integration

Services

_Packages

in

Package Deployment

Model

706

Using Management

Studio for

_{Package Management}

₇₀₆

Deployment

₇₀₉

(16)

Administration of

_Integration

Services

_Packages

in

_{Project Deployment}

Model 712

Configuring

the SSIS

_Catalog

712

Deploying Packages

714

Configuring Packages

716

Execution and

_Scheduling

719 Running

Packages

in SQL ServerData Tools 720

Running Packageswith the SQL Server

_Import

and

_Export

Wizard 720

Running Packages

with DTExec 720

Running Packages

with DTExecUl

_(Package

Deployment Model)

721

Running Packages

with the Execute

_Package

Tool

(Project Deployment Model)

722

Scheduling

Execution with SQL Server

Agent

723

Running

Packages

with T-SQL 725

Applying Security

to

_Integration

Services 725

An Overview of

_Integration

Services

_Security

725

Securing Packages

in

_{Package Deployment}

Model 726

Summary

728

CHAPTER 21: ANALYSIS SERVICES ADMINISTRATION

AND PERFORMANCE TUNING 729 Tour of

_Analysis

Services 730

MOLAP Components 731

Tabular ModelComponents 732

Analysis

Services Architectural

_Components

732

Administering Analysis

Services Server 733

Server

_Properties

734

Required

Services 735

Analysis

Services

_{Scripting Language}

736

Administering

Analysis

Services Databases 737

Deploying Analysis

ServicesDatabases 737

Processing Analysis

Services

Objects

741

Backing Up

and

_Restoring

Analysis

Services Databases 745

Synchronizing

AnalysisServices Databases 748

Analysis

Services Performance

_Monitoring

and

_Tuning

749

Monitoring Analysis

Services Events 749

Creating

Traces for

_Replay

750

Using Flight

Recorder for After-the-Fact

_Analysis

751

Management

of

_Analysis

Services MOLAP Model

_Storage

752

Storage

Modes 752

(17)

Partition

Configuration

753

Designing Aggregations

in the MOLAP Model 755

Applying

Security

to

_Analysis

Services in the MOLAP

Model

758

Server Role ₇₅₈

Database Role ₇₅₈

Database Role Permissions ₇₅₀

Applying

Security

to

_Analysis

Services in the TabularModel 762

Summary

763

CHAPTER _{22: SQL SERVER REPORTING SERVICES}

ADMINISTRATION ₇₆₅

SQL Server

_Reporting

Services

_{Configuration Manager}

765

The ServiceAccount ₇₆₈

The Web Service URL ₇₆₉

Reporting

Services Databases ₇₇₁

The

_{Report Manager}

URL ₇₇₃

E-mail

Settings

774

Execution Account ₇₇₅

Encryption

Keys

776

Scale-out

_Deployment

₇₇₇

Report

Server

_Properties

₇₇₈

General

_{Properties Page}

₇₇₉ Execution

_{Properties Page}

₇₈₀

History Properties Page

781

Logging Properties Page

781

Security Properties

Page

782

Advanced

_{Properties Page}

₇₈₃

The

_Report

Execution

_Log

₇₈₄

Report

Builder ₇₈₆

Report

Manager

795

Managing Report

Manager

796

Managing Reports

803

Summary

814

CHAPTER 23: SQL SERVER 2012 SHAREPOINT 2010INTEGRATION 815

Components

of

_Integration

₈₁₅

PowerPivot ₈₁₆

Reporting

Services ₈₁₈

Power View ₈₁₉

Service

_Application

Architecture ₈₂₀

Data Refresh ₈₂₀

(18)

CONTENTS

Using

Data Connections in Excel ₈₂₀

PerformancePoint

Data Refresh 826

Visio ServicesData Refresh ₈₂₇

PowerPivotData Refresh 829

Summary

836

CHAPTER _{24: SQL AZURE ADMINISTRATION}

AND CONFIGURATION 837 Introduction to

SQL

Azure

₈₃₇

SQL Azure

Architecture

838 Client

_Layer

₈₃₈ Services

Layer

838 Platform

Layer

839 Infrastructure

_Layer

₈₃₉

Configuring

SQL Azure ₈₃₉

Serverand Database

_Provisioning

₈₄₀

Throttling

and Load

_Balancing

844

Configuring

SQL Azure Firewalls 845

Connecting

toSQLAzure 847

Administering

SQL

Azure ₈₄₈

Creating Logins

and Users 848

Assigning

Access

_Rights

₈₅₀

Working

with SQL Azure ₈₅₀

Backups

with SQL Azure ₈₅₂

Object

Explorer

for SQLAzure 852

What's

_Missing

in SQL

Azure

854

Summary

855

CHAPTER 25: _{ALWAYSON AVAILABILITY GROUPS} ₈₅₇

Architecture ₈₅₈

Availability

Group Replicas

and Roles 858

Availability

Modes ₈₅₉

Types

of Failover

_Supported

₈₅₉

Allowing Read-Only

Access to

_Secondary

_Replicas

860

Availability Group Example

862

Configure

a New

_{Availability Group}

₈₆₂

Configure

an

Existing Availability

_Group

870

Availability Group

Failover

_Operation

872

Suspend

an

_Availability

Database 873

Resume an

_Availability

Database 874

(19)

Active Secondary

for

_{Secondary Read-Only}

875

Read-Only

Access Behavior 876

Secondary Replica

Client

_Connectivity

876

Performance 878

Backup

onthe

_{Secondary Replica}

879

Evaluate

_{Backup Replicas}

Metadata 880

AlwaysOn Group

Dashboard 881

Monitoring

and

_{Troubleshooting}

883

Summary

884