Index

A

AAD. See Azure Active Directory

access policies 215–216

Active Directory Federation Services (ADFS) 23

Active Directory Integrated 25

Active Directory Password Authentication 25

Active Directory Universal Authentication 25

ACUs. See Azure Compute Units

adaptive joins

batch mode 9

Add-AzureRmSqlServerKeyVaultKey cmdlet 139

ADFS. See Active Directory Federation Services

agent jobs 37

Agile methodology 72–73

alerts 163–164, 166–171

associating with operators 169–170

creating 166, 170

events generating 166

severity of 166–167, 171

strategy for 171

Alerts and Operators nodes 163–164

ALTER DATABASE statement 138, 174

ALTER INDEX statement 271

Always Encrypted

benefits of 106

configuration of 106–123

implementing 120–124

keys 106, 110–112, 116, 119, 123

management considerations 123–124

permissions 113–114

AlwaysOn Availability Groups 36, 70, 85–87

application logs 166

application permissions 107

ARM. See Azure Resource Manager

ASR. See Azure Site Recovery

ASYNC_NETWORK_IO wait type 224

auditing

in Azure SQL Database 16–17

authentication 23

Always Encrypted 107

multi-factor 23

Windows Authentication 24, 25

authenticators 127

automatic failover 22–23

automatic tuning 7–9

automation

of database maintenance tasks 258–263

availability

management of 36–38

Availability Groups (AGs) 36–38

AlwaysOn 36, 70, 85–87

availability sets 91–92

Azure

availability sets in 91–92

backup and restore of 81–84

disks in 48–49

storage account 81–82

virtual machines. See virtual machines (VMs)

Azure Active Directory (AAD) 22–25

Azure Blob service 212–216

Azure Command Line Interface (CLI) 11, 13, 208

firewall rules 102–103

setting firewall rules using 15

Azure Compute Units (ACUs) 43

Azure Files 191

Azure Key Vault 80, 107, 111–112, 115, 118, 120

Azure Portal 5, 29, 208

firewall rules 97–99

Azure PowerShell

firewall rules 100–101

installation 14–15

Azure Recovery Services 19

Azure Recovery Vault 19

Azure Resource Manager (ARM) 47–48, 70–71

Azure Site Recovery (ASR) 87

Azure SLQ Database

deployment of

elastic pool configuration 25–31

Azure SQL Database

auditing in 16–17

automatic tuning 7–9

Azure Active Directory and 23–25

backups 18–20

benchmark information 7

changing service levels 10–11

compatibility levels 8–11

connecting to, from inside Azure 16

deployment of 1–31

choosing service tier 2–12

creating servers and databases 12–22

sysadmin role 22–25

disaster recovery in 20–21

high availability in 20–21

performance tuning 7–9

secure access to 95–137

Always Encrypted configuration 106–123

cell-level encryption 123–127

Dynamic Data Masking 127–133

firewall rules 96–105

transparent data encryption 135–139

service tiers 218–223

sizing 2

Threat Detection 17–18

Azure storage

access keys 211

account overview 211

architecture 207–208

blobs 212–216

components 207–208

configuration 207–218

creating 209

files 216–217

overview of 207–208

B

backups 18–20

automated 79–80

Azure 81–84

database 239–240, 270–271

differential 239, 270

frequency of 240

full 239, 270

location 56

restoring 19

storage 20

strategy for 239–240, 270

system databases 270

to Azure Blob storage 58

transaction log 239, 270

Bash Shell 102

basic service tier 2, 3, 219

batch mode adaptive join 9

batch mode memory grant feedback 9

benchmark data 87–89

binary collations 117

binary files 55

blobs 212–216

Blob Service 82

Blob Storage Account 210

Bring Your Own Key (BYOK) 136–137

bring your own license (BYOL) 33–34

BYOK. See Bring Your Own Key

C

caching policy 58

cardinality estimation 162–163

catalog views

Query Store 150

CEK. See column encryption key

cell-level encryption (CLE) 123–127

central processing unit (CPU) 41

certificates 136

creation of 124

Check Database Integrity Task 267–268

CHECKDB function 239, 268–270, 272

checksum 270

CLE. See cell-level encryption

cloud computing 72

CMK. See column master key

cold data 195, 196, 205

collations 117

column encryption key (CEK) 106

column master key (CMK) 106

column statistics 264

compatibility level 140 8–11

Compatibility Level setting 180

compliance practices 16

configuration

Always Encrypted 106–123

Azure storage 207–218

cell-level encryption 123–127

databases 179–181

database scope 157–163

Dynamic Data Masking 127–133

elastic pools 25–31

file shares 193

max server memory 154–156

operators and alerts 163–171

Power Plan 142

Query Store 149–150

tempdb 183–186

transparent data encryption 135–139

configuration files 68

contained database users 37

continued standard service tier 219

core based licensing model 32

corruption, database 239, 269–272

Cost Threshold for Parallelism 144–146

CPU 41

CPU performance 140–142

Create Certificate statement 124

Create New Database dialog 172

CREATE STATISTICS statement 264

credit card masking 130

Cumulative Update (CU) 158

custom text masking 130–131

CXPACKET wait type 223

D

DACPAC. See Data-Tier Application Package

database encryption key (DEK) 135

Database Engine Tuning Advisor 249, 256–259

database files 171–174

database-level firewall rules 104–105

database maintenance 241–273

automation of 258–263

Database Engine Tuning Advisor 256–259

indexes 249–258

maintenance plans 259–261, 267–268

Maintenance Plan Wizard 249–255

monitor DMVs 241–249

PowerShell for 262–263

recovery from database corruption 269–272

update statistics 263–266

verifying database integrity 266–269

database master key 124–125

Database Overview page 97

database performance settings 140–154

missing index DMVs 151–153

parallelism 143–146

Power Plan 140–142

Query Store 146–151

Database Read-Only setting 179

databases. See also Azure SQL Database

automated deployment of 73–77

backups 56, 84, 239–240, 270–271

bottlenecks 73

changing service levels 11–12

compatibility levels 8–11

configuration 179–181

corruption 239, 269–272

creating 12–22, 177–181

firewall rules 14–16, 96–105

integrity of, verifying 266–269

isolation of 218

recovery models 180–181

shrinking 56

size of 11, 26

stretch 195–205

system database files 181–182

workload utilization 26

database scope

configuration 157–163

Legacy Cardinality Estimation configuration setting 162–163

Max DOP 157–158

parameter sniffing 160–162

Query Optimized Fixes configuration setting 158–159

Database Transaction Units (DTUs) 2, 4–7, 219–223

data compression 55

data definition language (DDL) 73

data disks

maximum number of 42

data encryption. See encryption

data file management 56

data files 58

Data-Tier Application Package (DACPAC) 73–76

DBCC CHECKDB function 239, 268–270, 271, 272

DecryptByKey function 126

default masks 130

defragmentation 250–251

delayed durable transactions 179

deterministic encryption 116

developer edition 32

differential backups 239, 270

disaster recovery 20, 36–38

Azure Site Recovery 87

disk caching 55

DiskSpeed 39–40

disk usage 40

Distributed Replay 87–89

DMFs. See dynamic management functions

DMVs. See dynamic management views

DS series 44–45

DS_V2 series 45

DTUs. See Database Transaction Units

Dynamic Data Masking (DDM)

components 132

configuration 127–133

using PowerShell 133–134

using T-SQL 133

dynamic management functions (DMFs) 40

dynamic management views (DMVs) 40, 223, 224–226

database 241–242

execution 242–245

index and I/O 245–247

missing index 151–153

monitoring 241–249

transaction 247–249

E

elastic Database Transaction Unit (eDTU) 2, 25

elastic jobs 30–31

elastic pools

changing sizes of 30

configuration 25–31

geo-replication and 31

sizing 27–28

when to choose 26–27

email masking 130

enclosure awareness 233

EncryptByKey function 125

encryption

Always Encrypted 106–123

Bring Your Own Key 136–137

cell-level 123–127

deterministic 116

randomized 116, 117

symmetric 127–128

transparent data 127, 135–139

Enterprise Edition 32, 40

error messages 166–167

ES_v3 series 44

existing applications

migration of 34–36

Express Edition 32

ExpressRoute 84

F

failed storage

recovery from 238–240

failover

automatic 22–23

Availability Groups 85

groups 22, 22–23

process 21

Failover Cluster Instances 36–38

fatal errors 166

File and Storage Services 229

filegroups 174–176, 177

files 171–174, 177–178

Azure 191, 216–217

primary data 172

secondary data 172

system database 181–182

virtual log 178

file shares

configuration 193

creating 193

mounting 193–194

viewing 194

file size 177–178

firewalls

rules 14–16, 96–105

database-level 104–105

server-level 96–103

troubleshooting 105–106

fixed provisioning 234

fragmentation 177, 235, 250–251

Free space in tempdb (KB) counter 168

Front-Ends 207

full backups 239, 270

FULL CONTROL permissions 191, 195

G

geo-redundant storage (GRS) 48

geo-replication 20–21, 22, 135–136

elastic pools and 31

Get-AzureRmSqlDatabaseDaaMaskingRule cmdlet 133

Get-AzureRmSqlDatabaseDataMaskingPolicy cmdlet 133

Get-AzureRmSqlDatabaseTransparentDataEncryptionActivity cmdlet 139

Get-AzureRmSqlDatabaseTransparentDataEncryption cmdlet 138

Get-AzureRmSqlServerFirewallRule cmdlet 100

Get-AzureRmSqlServerKeyVaultKey cmdlet 139

Get-AzureRmSqlServerTransparentDataEncryptionProtector cmdlet 139

GitHub 72

GracePeriodWithDataLossHours parameter 22

GS series 46

H

hardware reliability 238

HashBytes function 127

high availability 20

management of 36–38

hot data 195

hotfixes 158

hybrid network connections 84–85

I

IaaS. See Infrastructure as a Service

indexes 7, 90–91

analysis of 257–258

DMVs 245–247

fragmentation 250–251

maintaining 249–258

missing 151–153

rebuilding 252–255

reorganization of 252, 255

index statistics 264

Infrastructure as a Service (IaaS) 2

installation of SQL Server on 63–68

performance tuning 89–91

INSERT INTO statement 134

instances. See SQL Server instances

instant file initialization 56

interleaved execution 9

Internal Load Balancer 86

internal objects 183

I/O Operations Per Second (IOPs) 39

I/O-related indexes 245

IP addresses 105

Is Read Committed Snapshot On setting 179

J

“just-in-time” allocation 235

L

latency 39

LCK_* wait type 223

Legacy Cardinality Estimation configuration setting 162–163

licensing 32–34

Location Service 207–208

locked pages in memory 57

Locked Pages In Memory 154

log files 17, 56, 58, 178

logical file names 172

logical servers 13–14

login-azurermaccount cmdlet 86

login issues 105

logins 24

M

maintenance plans 259–261, 267–268

Maintenance Plan Wizard 249–254, 259, 267

managed disks 48, 227–228

masking functions 132

masking policy 133–134

masking rules 128–130, 132, 134

master keys 118, 119, 124–125

Maximum Degree of Parallelism (MaxDOP) 143–146, 157–158

maximum file size 177–178

Max plans per query option 150

max server memory

configuration 154–156

memory 40–41

batch mode memory grant feedback 9

locked pages in 57

max server memory 154–156

VM 42

missing indexes 151–153

multi-factor authentication 23

N

Net Use command 193

network address translation (NAT) 105

network bandwidth 43

network connections

hybrid 84–85

New-AzureRmSqlDatabaseDataMaskingRule cmdlet 133

New-AzureRmSqlServerFirewallRule cmdlet 100, 101

New-AzureRmStorageAccount cmdlet 208

NTFS allocation unit size 55

NTFS permissions 191, 195

NUMA (Non-Uniform Memory Access) 143

O

object-based storage 47

OLEDB wait type 224

online analytical processing databases (OLAP) 41

online transaction processing (OLTP) 7

on-premises environments

deployment of SQL Server instances in 59–63

operating system file names 172

operators

associating alerts with 169–170

configuration 163–165

creating 164–165

optimize-storagepool cmdlet 54

O/S disks 58

Overall Resource Consumption view 148

over-indexing 247

P

PaaS. See Platform as a Service

PAGEIOLATCH_* wait type 223

PAGELATCH_* wait type 224

page life expectancy (PLE) 40

pages 174

Page Verify setting 179

paired regions 20

parallelism 143–146

parameter sniffing 160–162

Partition Layer 207

pass-through authentication 23

password issues 105

peak workloads 26

performance

benchmark data 87–89

CPU 140–142

DTUs and 219–223

Query Store and 90–91

settings 139–170

database scope 157–163

max server memory 154–156

missing index DMVs 151–153

operators and alerts 163–171

parallelism 143–146

Power Plan 140–142

Query Store 146–151

tuning, on Azure IaaS 89–91

performance analysis of logs (PAL) tool 36

performance conditions 167–168

performance levels 2–4

performance management 38–39

Performance Monitor 34–36, 40, 87, 141, 168

performance tuning 7–9

permissions

Always Encrypted 113–114

application 107

cell-level encryption 124

FULL CONTROL 191, 195

NTFS 191, 195

SELECT 131

physical file names 173

plan change regression analysis 10–11

Platform as a Service (PaaS) 1, 12

PLE. See page life expectancy

Point to Site VPNs 84

portals

building VM using 68–70

Power Plan 140–142

PowerShell

Azure Key Vault configuration with 115

DDM using 133–134

deploying Azure VM using 70–72

firewall rules 100–101

maintenance automation using 262–263

setting firewall rules using 15

TDE using 138–139

premium RS service tier 2, 4

premium service tier 2, 3, 220

premium storage 39, 48, 89

primary data files 172

primary filegroups 174

primary servers 22

provisioning types 234–235

Q

Queries with Forced Plans view 148

Queries with High Variation view 148

query execution plans 146

Query Optimized Fixes configuration setting 158–159

query optimizer 151, 153

statistics 263–266

Query Performance Insight 6, 7

query plans 151, 153

Query Store 6, 7, 10, 90–91, 146–151

built-in views 148–149

catalog views 150

configuration 149–150

enabling 146–147

folders 148–149

R

RAID (Redundant Array of Independent Disks) 47

randomized encryption 116, 117

random number masking 130

read-access geo-redundant storage (RA-GRS) 18, 48

recovery

from database corruption 269–272

from failed storage 238–240

Recovery Model setting 180

regression analysis 10–11

Remove-AzureRmSqlDatabaseDataMaskingRule cmdlet 133

Remove-AzureRmSqlServerFirewallRule cmdlet 100

Remove-AzurermSqlServerKeyVaultKey cmdlet 139

REPAIR_ALLOW_DATA_LOSS parameter 269

replicas 37

replication options 210

Resource Manager deployment model 210

resource manager model 72

Rest API 11

Restrict Access setting 179

retention policy 19

row versions 183

S

SaaS. See Software as a Service

SAS tokens 83, 84

secondary data files 172

secondary servers 22

security. See also encryption

Dynamic Data Masking 127–133

firewall rules 14–16

Threat Detection 17–18

security patches 57

SELECT INTO statement 134

SELECT permissions 131

SELECT statement 243, 246

Server/Client Access License (CAL) model 32

server-level firewall rules 96–103

Server Management Objects (SMOs) 262

Server Manager 229

Server Overview page 97

servers

creating 12–22

firewall rules 14–16

logical 13–14

max server memory 154–156

primary 22

secondary 22

service level agreement (SLA) 10

service tiers 2–12

changing 11–12, 218–223

choosing 218

choosing initial 10

Database Transaction Units 4–7

DTUs and 219–223

performance levels 2–4, 219–223

Set-AzureRmSqlDatabaseDataMaskingPolicy cmdlet 133

Set-AzureRmSqlDatabaseDataMaskingRule cmdlet 133

Set-AzureRmSqlDatabaseTransparentDataEncryption cmdlet 138, 139

Set-AzureRmSqlServerFirewallRule cmdlet 100

Set-AzureRmSqlServerTransparentDataEncryptionProtector cmdlet 139

severity 23, 166

shared access signatures 83

shared access signatures (SAS) 215

Sign-On URLs 107

Site to Site VPNs 84

SMB files shares 190–195

SMB (Server Message Block) protocol 190–195

social security number masking 130

Software as a Service (SaaS) 14, 26

Software Assurance 32

SOS_SCHEDULER_YIELD wait type 224

sp_delete_database_firewall_rule 104

sp_delete_firewall_rule 99

sp_set_database_firewall_rule 104

sp_set_firewall_rule 99

SqlConnectionStringBuilder class 120

SQL Database Benchmark 7

SQL Injection 17–18

SqlParameter objects 120

SQL Server

availability management 36–38

Azure Site Recovery and 87

Azure VMs for 44–45

databases

bottlenecks 73

deployment of 72–76

deployment to VMs 81–92

migrating to VMs 81–87

deployment planning 31–41

editions 32

events 166

installation 31–59

best practices for 55–57

manual, on Azure VMs 63–68

requirements 38–39

instances

changing name of 63

database creation 177–181

deployment 59–81

files and filegroups 171–176

in IaaS and on-premises 59–63

managing 171–186

provisioning VM to host 68–72

system database files 181–182

tempdb 183–186

licensing 32–34

memory 40–41

migration of existing applications to 34–36

performance settings 139–170

database 140–154

database scope 157–163

max server memory 154–156

missing index DMVs 151–153

operators and alerts 163–171

parallelism 143–146

Power Plan 140–142

Query Store 146–151

performance tuning 89–91

Query Store 146–151

settings 55–57

size selection 41–46

SMB file shares 190–195

storage pools 47–55

storage requirements 39–40

templates

building 59–63

deployment using 76–80

wait statistics 89, 223–227

SQL Server Agent 163–164

SQL Server Authentication 25

SQL Server Data Tools 73

SQL Server Management Studio 66, 83, 172

max server memory configuration in 155

SQL Server Reporting Services 64

Standard Edition 32

standard performance tier 3

standard service tier 2, 219

statistics

column 264

index 264

update 263–266

STATS_DATE function 266

storage

Azure storage account 81–82

backups 20

configuration 78–79

failed 238–240

geo-redundant 48

layout 57–58, 233–234

management 189–238

Azure 207–218

changing service tiers 218–223

pools 227–238

SMB file shares 190–195

stretch databases 195–205

wait statistics 223–227

object-based 47

premium 48, 89

read-access geo-redundant 48

requirements 39–40

size 12

spaces 48–54, 78

standard 48–49

storage pools 227–238

planning, based on performance requirements 47–55

Storage Stamps 207

stored procedures

parameter sniffing and 161–162

Stream Layer 207

Stretch Database

benefits of 195

disabling 204–205

enabling 196

identifying databases and tables for 205

limitations of 206–207

management of 195–204

Stretch Database Monitor 203–204

symmetric encryption 127–128

symmetric keys 135

creation of 124

sysadmin role 22–25

sys.database_files 173

sys.database_firewall_rules 104

sys.database_query_store_options 150

sys.dm_db_column_store_row_group_physical_stats 154

sys.dm_db_file_space_usag 241

sys.dm_db_index_operation_stats 154

sys.dm_db_index_physical_stats 154, 246

sys.dm_db_index_physical_stats system function 250

sys.dm_db_index_usage_stats 154, 246

sys.dm_db_missing_index_columns 151, 152, 246

sys.dm_db_missing_index_details 151, 152, 153, 246

sys.dm_db_missing_index_groups 151

sys.dm_db_missing_index_group_stats 151, 152

sys.dm_db_operation_stats 242

sys.dm_db_partition_stats 242

sys.dm_db_resources_stats 242

sys.dm_db_session_space_usage 242

sys.dm_db_stats_properties 265

sys.dm_db_wait_stats 227, 242

sys.dm_exec_connections 243, 244

sys.dm_exec_query_plan 243

sys.dm_exec_query_stats 243

sys.dm_exec_requests 224, 243, 244

sys.dm_exec_session 244

sys.dm_exec_sessions 243

sys.dm_exec_session_wait_stats 243

sys.dm_exec_session_wait_status 223

sys.dm_exec_sql_text 224, 243, 244

sys.dm_exec_text_query_plan 225, 243

sys.dm_io_pending_io_requests 246

sys.dm_io_virtual_file_stats 246

sys.dm_os_waiting_tasks 223

sys.dm_os_wait_stats 223

sys.dm_tran_active_transactions 247

sys.dm_tran_current_transactions 247

sys.dm_tran_database_transactions 247

sys.dm_tran_locks 247–248

sys.dm_tran_session_transactions 247

sys.firewall_rules 99

sysprep 59

sys.query_context_settings 150

sys.query_store_plan 150

sys.query_store_query 150

sys.query_store_query_text 150

sys.query_store_stats 150

sys.query_store_wait_stats 150

System Admin role 22–25

system backups 270

system database files 181–182

T

table valued functions (TVFs) 9

TDE. See transaction data encryption

tempdb 179, 183–186

TempDB workloads 58

templates

for Azure features 72

SQL

building 59–63

deployment using 76–80

temporary disks 58

temporary user objects 183

temp storage 42

thin provisioning 234–235

THREADPOOL wait type 224

Threat Detection 17–18

Top Resource Consuming Queries view 148

trace files 56

Tracked Queries view 148

transaction DMVs 247–249

transaction log backups 239, 270

transaction log files 172

Transactions object 168

Transact-SQL (T-SQL) 96

creating alerts with 170

DDM using 133

enabling Query Store via 147

firewall rules 99–100, 104–105

max server memory configuration in 156–157

operator creation with 165

Query Store configuration 150

TDE using 138

transparent data encryption (TDE) 127

configuration 135–139

using PowerShell 138–139

using T-SQL 138

troubleshooting

database firewall 105–106

SQL Server 163–164

TVFs. See table values functions

U

Unwrap Key permission 113

update statistics 263–266

USE PLAN query hint 146

user-defined filegroups 174

V

vCPU 42

virtual disks

creating 233–234

provisioning types 234–235

selecting 236–237

size of 236

storage layout 233–234

Virtual Log Files (VLFs) 178

virtual machines (VMs)

adding disks to 49–50

building, using portal 68–70

components 42–43

compute units 43

deployment

SQL Server databases to 81–92

using PowerShell 70–72

for SQL Server 44–45

manual installation of SQL Server on 63–68

migration of on-premises SQL Server database to 81–87

performance tuning 89–91

provisioning to host SQL Server instances 68–72

sizing 41–46, 87

storage layout for 57–58

types of 42–43

W

wait statistics 89, 223–227

wait types 223–227

warm data 195

Windows Authentication 24, 25

Windows Azure Service Management API 108

Windows Power Plan 140–142

Windows Server Failover Cluster (WSFC) 36

Wrap Key permission 113

WRITELOG wait type 224