Azure SQL Revealed: The Next-Generation Cloud Database with AI and Microsoft Fabric. 2 Ed

Table of Contents....5

About the Author....16

About the Technical Reviewer....17

Acknowledgments....18

Foreword....20

Introduction....23

Chapter 1: SQL Server Rises to the Clouds....26

CloudDB....27

The Red Dog....28

The Azure Services Platform....29

The Road to SQL Azure....33

SQL Data Services....33

SQL Azure Is Born....37

The SAWA Project....40

The Virtual Machine Initiative....40

Becoming Azure SQL Database....42

The Sterling (SAWAv2) Project....44

New Editions, the DTU, and Previews....49

Intelligent Performance and the MDCS....50

Advanced Data Security and the ILDC Team....51

A Pane for the Future Called Ibiza....52

A New Engineering Model for Azure....53

Bending Azure SQL Database....54

Lifting Customers to the Cloud....55

Project Socrates Goes Hyper....56

Azure SQL Today....58

Summary....59

Chapter 2: What Is Azure SQL?....60

The Azure Ecosystem....61

Azure Accounts, Tenants, and Subscriptions....61

The Azure Portal....63

The Azure Marketplace....66

Azure API and CLI....67

Azure Resource Manager (ARM)....68

Azure Monitor....71

Azure Regions and Data Centers....72

Azure Trust, Privacy, and Compliance....73

Azure Service-Level Agreement (SLA)....73

What Is Azure SQL?....74

IaaS vs. PaaS....74

The Azure SQL Lineup....74

SQL Virtual Machines....76

Managed Instances....76

Databases....76

Azure SQL Managed Instance....76

Managed Instance Capabilities....77

Managed Instance Options and Limits....79

General Purpose....80

Business Critical....81

Managed Instance Pools....81

Managed Instance vs. SQL Server on Azure Virtual Machine....82

Customers Using Managed Instance....83

Azure SQL Database....83

Azure SQL Database Capabilities....83

Azure SQL Database Options and Limits....85

Elastic Pool or Single Database....86

DTU vs. vCore....86

General Purpose Service Tier....87

Serverless....87

Business Critical....88

Hyperscale....88

Elastic Pool Databases....90

Azure Database Fleet Manager....91

Azure SQL Database vs. Azure SQL Managed Instance....92

Customers Using Azure SQL Database....93

Interfaces for Azure SQL....93

Azure Portal....93

az CLI....97

PowerShell....97

REST API....98

TDS and T-SQL....98

SQL CLI....99

SQL Server Management Studio (SSMS)....99

Visual Studio Tools....100

Beyond RDBMS....101

Summary....102

Chapter 3: SQL Server on Azure Virtual Machine....103

Deploying....103

Pricing....104

SQL Server Gallery Images....104

Resource Group, Region, and Availability....108

Resource Group....108

Virtual Machine Name....108

Region....108

Availability Options....108

Security Type....109

Image....109

VM Architecture....109

Spot Instance....110

Virtual Machine Sizes....111

Accounts, Port, and OS Licensing....114

Making Configuration Choices As Part of Deploy....115

OS Disks....116

Networking....117

Management....118

Monitoring....120

Advanced....122

SQL Server Settings....124

Tags....132

Deploy!....132

Navigating an Azure Virtual Machine in the Portal....135

Connecting to Your VM....137

Exploring the SQL Server Installation....139

What Is Installed....139

What Is Configured....140

Deploy on Your Own....141

Using a CLI with ARM Templates, Bison, and Terraform....142

Reserved Instances, Dedicated Hosts, and Capacity Reservations....143

Migration to Azure....144

Azure Arc Migration Assessment....144

Restoring a Database....144

Azure Migrate and Azure Database Migration Service....145

Using Azure Migrate Server Migration....146

Deploying SQL Server on Linux with Azure Virtual Machine....146

Deploying SQL Server Containers....147

SQL Server IaaS Agent Extension....147

Settings....149

Configure....149

Storage....149

Updates....150

Backups....151

High Availability....151

SQL Best Practices Assessment....151

Security....151

Security Configure....151

Microsoft Defender for Cloud....152

Configuring an Azure Virtual Machine....153

Stopping vs. Deallocating....153

Resizing....153

Security (RBAC)....154

Other Config Options....154

Maximizing Storage Performance....155

Best Practices....155

Premium SSD v2....156

Learn More....158

Performance Monitoring....158

Azure Metrics....159

Insights....160

Networking....161

HADR....161

Azure Storage....162

Backups....162

Go Further with Azure Availability....163

Always On Failover Cluster Instance....164

Always On Availability Groups....165

SQL Server and Linux High Availability....167

Microsoft Innovations and Azure Boost....167

Summary....168

Chapter 4: Deploying Azure SQL....169

Pre-deployment Planning....170

New Deployment or Migration....170

Making Deployment Choices....171

Deploying for Free....172

The Azure SQL Managed Instance Free Trial....172

The Azure SQL Database Free Offer....172

Deployment Methods....173

Deployment Option....175

Region....175

Purchasing Model....176

Service Tier (SLO)....176

Hardware....178

vCore and Storage Sizes....179

Price....179

Consider Resource Limits....180

Deploying Azure SQL Managed Instance....181

Walking Through a Deployment....181

Basics....182

Networking....187

Virtual Network....189

Connection Type....189

Public Endpoint....189

Security....190

Additional Settings....192

Tags....194

Deploy!....195

Deploying with a CLI....195

Implementation Details....196

Connecting and Verifying Deployment....198

Connect to a Managed Instance....199

bwsqlminextgen.b243ea7f888c.database.windows.net....200

Verify the Deployment....201

Examining the ERRORLOG....201

Verification Queries....202

Migrating to Azure SQL Managed Instance....205

Deploying an Azure SQL Database....206

Deployment and Options....207

Basics....207

Service Tier....213

Compute Tier....213

Hardware Configuration....213

vCores....213

HA Replicas and Zone Redundancy....213

Other Choices....213

Networking....214

Security....216

Additional Settings....218

Tags....220

Deploy It!....220

Deploying Serverless....221

Deploying an Elastic Pool....224

Deploying with a CLI....226

Implementation Details....227

Dedicated Rings and Instances....228

The Logical Server....228

Storage, Compute, and Gateways....229

Serverless....229

Hyperscale....230

Resource Governance....232

SQL Server Resource Governor....232

Engine enhancements....232

Windows Job Objects....233

File Source Resource Manager (FSRM)....233

Connecting and Verifying Deployment....233

Connecting to Azure SQL Database....233

Verifying Deployment....238

Migrating to Azure SQL Database....240

Summary....241

Chapter 5: Configuring Azure SQL....243

Configuring Azure SQL Managed Instance....244

sp_configure....244

Trace Flags....244

Tempdb....245

Master and Model....246

Configuring Edition....246

Compute and Storage....246

Networking Configuration....247

Maintenance....247

Start and Stop....248

Configuring Databases....249

Configuring Azure SQL Database....251

Creating New Databases....251

Altering Databases....253

Network Configuration....254

Configuring Serverless....255

Configuring Elastic Pools....257

Configuration Restrictions....257

Azure SQL Managed Instance Restrictions....258

Start and Stop Services....258

Instant File Initialization....258

Locked Pages....258

FILESTREAM and Availability Groups....259

Server Collation....259

Startup Parameters....259

ERRORLOG Configuration....259

Error Reporting and Customer Feedback....260

ALTER SERVER CONFIGURATION....260

“Mixed Mode” Security....260

Logon Auditing....261

Server Proxy Account....261

Database Restrictions....261

Azure SQL Database Restrictions....261

Azure SQL Space Management....262

Azure SQL Managed Instance Space Management....262

Azure SQL Database Space Management....263

Loading Data....264

Keep These in Mind....264

bcp....265

BULK INSERT and OPENROWSET....266

SQL Server Integration Services (SSIS)....267

Azure SSIS....268

BACPAC....269

Database Copy....270

RESTORE to Managed Instance....271

Spark Connector....272

Azure Data Factory (ADF)....272

Updating Azure SQL....272

Maintenance of Azure SQL....273

Update Policy for Azure SQL Managed Instance....274

New Features and Capabilities in Azure SQL....275

Summary....275

Chapter 6: Securing Azure SQL....277

Network Security....279

Azure SQL Managed Instance Network Security....279

Public Endpoint....280

VNet-Local Endpoint....280

Private Endpoint....281

Other Considerations....281

Azure SQL Database Network Security....282

Using the Public Endpoint....282

Using Private Link....285

Authentication and Access....289

Azure Role-Based Access Control (RBAC)....289

SQL DB Contributor....290

SQL Server Contributor....290

SQL Security Manager....290

SQL Managed Instance Contributor....290

Authentication for Azure SQL Managed Instance....290

Microsoft Entra Authentication....291

Windows Authentication....291

Authentication for Azure SQL Database....292

Using Contained Users....293

Microsoft Entra Authentication....293

Managed Identities....295

Set Up and Configure Access....301

Microsoft Purview....301

Data Protection....302

Encrypting Connections....302

Transparent Data Encryption (TDE)....303

Bring Your Own Key (BYOK)....304

Always Encrypted....306

Dynamic Data Masking (DDM)....307

SQL Ledger....308

Security Management....310

Monitoring Activities Outside of SQL....310

Auditing Azure SQL Managed Instance....311

Tracking Logins....312

SQL Server Audit....312

Auditing Azure SQL Database....313

Tracking Connections....313

SQL Database Auditing....314

Microsoft Defender for SQL....318

Vulnerability Assessment....322

SQL Threat Protection....324

Data Classification....326

Summary....328

Chapter 7: Monitoring and Tuning Performance for Azure SQL....329

Performance Capabilities....331

Max Capacities....331

Indexes....333

In-Memory OLTP....333

Partitions....334

SQL Server 2022 Enhancements....335

Intelligent Performance....336

Configuring and Maintaining for Performance....336

Tempdb....336

Database Configuration....337

Files and Filegroups....337

Max Degree of Parallelism....338

Resource Governor....339

Maintaining Indexes....339

Maintaining Statistics....340

Monitoring and Troubleshooting Performance....340

Monitoring Tools and Capabilities....340

Azure Monitor....341

Dynamic Management Views (DMVs)....341

Extended Events (XEvent)....341

Lightweight Query Profiling....342

Query Plan Debugging....342

Query Store....342

Performance Visualization in Azure Portal....343

Database Watcher....343

Microsoft Copilot Skills in Azure SQL Database....344

Dive into DMVs and Extended Events....349

DMVs Deep Dive....349

Azure SQL Managed Instance....350

Azure SQL Database....350

DMVs You Will Need....350

DMVs for Deep Troubleshooting....351

XEvent at Your Service....352

Extended Events for Azure SQL Managed Instance....352

Extended Events for Azure SQL Database....352

Performance Scenarios....354

Running vs. Waiting....355

Azure Portal/PowerShell/Alerts....355

sys.dm_db_resource_stats....355

sys.server_resource_stats....356

Using Copilot to Get Started....356

Running....358

Query Store....358

sys.dm_exec_requests....358

sys.dm_exec_query_stats....358

sys.dm_exec_procedure_stats....359

Microsoft Copilot Skills in Azure SQL Database....359

Database Watcher....362

Waiting....363

sys.dm_os_wait_stats....364

sys.dm_exec_requests....365

sys.dm_os_waiting_tasks....365

Query Store....365

Microsoft Copilot Skills in Azure SQL Database and Database Watcher....365

Performance Example....368

Azure SQL–Specific Performance Scenarios....380

Log Governance....380

Worker Limits....380

Business Critical (BC) HADR Waits....381

Hyperscale Scenarios....381

Accelerating and Tuning Performance....382

Scaling CPU Capacity....382

Tuning I/O Performance....388

Increasing Memory or Workers....388

Improving Application Latency....389

Tune like It Is SQL Server....390

Intelligent Performance....390

Intelligent Query Processing....390

Automatic Plan Correction....393

Automatic Tuning....394

Summary....401

Chapter 8: Availability for Azure SQL....403

HADR Capabilities....405

Automatic Backups and Point-in-Time Restore....405

Built-In Availability....405

Azure Redundancy for High Availability....406

Geo-replication and Failover Groups....406

Database Availability and Consistency....406

Disaster Recovery with Managed Instance Link....407

Free Passive DR Replicas....407

SQL Server Replication....407

Backup and Restore....408

Automatic Backups....408

Locally Redundant Storage (LRS)....409

Zone-Redundant Storage (ZRS)....409

Geo-redundant Storage (GRS)....409

Geo-Zone-Redundant Storage (GZRS)....409

Backup Retention....410

Automated Backups....412

Backup Storage Consumption and Costs....412

Point-in-Time Restore (PITR)....414

Geo-restore of Databases....419

Restore Backups from Deleted Databases....419

Restore in Azure SQL Managed Instance....420

Built-In Availability....421

General Purpose Availability....422

Business Critical Availability....423

Hyperscale Availability....427

Extend HADR with Azure....430

Zone Redundancy....431

Geo-replication....435

Failover Group....440

Disaster Recovery with Managed Instance Link....446

Offline Disaster Recovery....447

Online Disaster Recovery....448

Azure SQL SLA....448

Database Availability and Consistency....449

Database Availability....450

Accelerated Database Recovery (ADR)....450

Database Consistency....450

Monitoring Availability....451

Instance, Server, and Database Availability....452

Backup and Restore History....453

Region, Data Center, and Service Availability....456

Availability Metrics....458

Replica Status....461

Failover Reasons....463

Summary....464

Chapter 9: Extending Your Knowledge of Azure SQL....466

Surface Area of Azure SQL....467

Linked Servers and Cross-Database Queries....467

External Tables....468

Database Mail....471

Service Broker....472

Full-Text Search....472

Machine Learning Services....473

Distributed Transactions (DTC)....474

xp_cmdshell....474

Job Management....475

SQL Server Agent....475

Elastic Jobs....477

Azure Automation....479

Supporting Azure SQL....479

Handling Errors....479

Stack Dumps....481

Troubleshooting Resources in the Azure Portal....482

UserVoice....490

Azure SQL Best Practices....490

Security Playbook....490

Best Practices for Performance....491

Azure Advisor....491

Keep Up to Date with New Features....493

Stay in Touch with Our Team....493

Summary....495

Chapter 10: Beyond RDBMS....496

“It’s in the Box?”....497

JSON....498

JSON_ARRAYAGG....500

JSON_OBJECTAGG....501

RegEx....501

Spatial and Graph....503

Columnstore and Memory Optimized....505

Ledger....505

Query Intelligence....506

REST API Integration....506

Data API Builder and GraphQL....509

DevOps, GitHub, and Containers....510

Database Projects....511

GitHub Actions....511

Containers....512

SQL and AI....513

GenAI and SQL....515

Use AI Services....517

Use T-SQL for Hybrid Search....519

Use Frameworks....522

What About Chat History?....523

Where Do You Store Your Text Data?....523

Resources....524

Natural Language to SQL Query....524

SQL and Microsoft Fabric....529

Azure Arc....533

Futures....534

Summary....535

Access detailed content and examples on Azure SQL, a set of cloud services that allows for SQL Server to be deployed in the cloud. This book teaches the fundamentals of deployment, configuration, security, performance, and availability of Azure SQL from the perspective of these same tasks and capabilities in SQL Server. This distinct approach makes this book an ideal learning platform for readers familiar with SQL Server on-premises who want to migrate their skills toward providing cloud solutions to an enterprise market that is increasingly cloud-focused.

If you know SQL Server, you will love this book. You will be able to take your existing knowledge of SQL Server and translate that knowledge into the world of cloud services from the Microsoft Azure platform, and in particular into Azure SQL. This book provides information never seen before about the history and architecture of Azure SQL. Author Bob Ward is a leading expert with access to and support from the Microsoft engineering team that built Azure SQL and related database cloud services. He presents powerful, behind-the-scenes insights into the workings of one of the most popular database cloud services in the industry. This book also brings you the latest innovations for Azure SQL including Azure Arc, Hyperscale, generative AI applications, Microsoft Copilots, and integration with the Microsoft Fabric.

What You Will Learn

• Know the history of Azure SQL
• Deploy, configure, and connect to Azure SQL
• Choose the correct way to deploy SQL Server in Azure
• Migrate existing SQL Server instances to Azure SQL
• Monitor and tune Azure SQL’s performance to meet your needs
• Ensure your data and application are highly available
• Secure your data from attack and theft
• Learn the latest innovations for Azure SQL including Hyperscale
• Learn how to harness the power of AI for generative data-driven applications and Microsoft Copilots for assistance
• Learn how to integrate Azure SQL with the unified data platform, the Microsoft Fabric

Who This Book Is For

This book is designed to teach SQL Server in the Azure cloud to the SQL Server professional. Anyone who operates, manages, or develops applications for SQL Server will benefit from this book. Readers will be able to translate their current knowledge of SQL Server―especially of SQL Server 2019 and 2022―directly to Azure. This book is ideal for database professionals looking to remain relevant as their customer base moves into the cloud.