Practical Azure SQL Database for Modern Developers : Building Applications in the Microsoft Cloud

Here is the expert-level, insider guidance you need on using Azure SQL Database as your back-end data store. This book highlights best practices in everything ranging from full-stack projects to mobile applications to critical, back-end APIs. The book provides instruction on accessing your data from any language and platform. And you learn how to push processing-intensive work into the database engine to be near the data and avoid undue networking traffic. Azure SQL is explained from a developer's point of view, helping you master its feature set and create applications that perform well and delight users.Core to the book is showing you how Azure SQL Database provides relational and post-relational support so that any workload can be managed with easy accessibility from any platform and any language. You will learn about features ranging from lock-free tables to columnstore indexes, and about support for data formats ranging from JSON and key-values to the nodes and edges in the graph database paradigm. Reading this book prepares you to deal with almost all data management challenges, allowing you to create lean and specialized solutions having the elasticity and scalability that are needed in the modern world. **What You Will Learn** * Master Azure SQL Database in your development projects from design to the CI/CD pipeline * Access your data from any programming language and platform * Combine key-value, JSON, and relational data in the same database * Push data-intensive compute work into the database for improved efficiency * Delight your customers by detecting and improving poorly performing queries * Enhance performance through features such as columnstore indexes and lock-free tables * Build confidence in your mastery of Azure SQL Database's feature set **Who This Book Is For**Developers of applications and APIs that benefit from cloud database support, developers who wish to master their tools (including Azure SQL Database, and those who want their applications to be known for speedy performance and the elegance of their code Table of Contents 5 About the Authors 12 About the Technical Reviewer 14 Acknowledgments 15 Foreword by Rohan Kumar 18 Foreword by Mark Souza 19 Introduction 21 Chapter 1: A Database for the Modern Developer 22 Why did we write this book? 22 The growth mindset 23 Reviewing old beliefs 25 Continuous learning 26 A better developer 27 Not just a passive container of data 27 Gatekeeper of data 28 Where should business rules go? 29 Tell me about Polyglot persistence 30 Batteries included 31 Is Azure SQL a developer tool? 31 Keep it super simple 31 Be a generalized specialist 32 If you want to know more 33 Chapter 2: Azure SQL Kickstart 35 Creating Azure SQL Database 38 Tools to use and manage Azure SQL Database 42 Choices, choices, choices 44 A quick recap 44 DTU or vCores 44 Service tiers 45 Hardware generations 47 Networking 48 Connection methods 49 Collations 50 Time zones 50 Advanced data security 50 Using sample databases 51 If you want to know more 54 Chapter 3: Connecting and Querying Azure SQL 55 Driver and libraries 56 What Azure SQL driver works best for me 57 Getting started 58 Connectivity aspects 65 Resilient connection and query execution 67 Retry logic 67 Transient and persistent errors 68 Custom code or reusable libraries 69 Connectivity best practices 75 Handling exceptions 77 Frameworks, ORM, and MicroORM 79 Data access frameworks 80 ORMs 82 MicroORMs 86 Using read-only replicas 87 If you want to know more 88 Chapter 4: Developing with Azure SQL – Foundations 89 Pushing compute to data 90 Declarative vs. imperative 91 Query and data manipulation 92 Retrieving data 93 Two-part names 95 Aliasing 96 Quoted identifiers 96 Joins 96 Filtering 100 Subqueries 100 Common Table Expressions 101 Union 103 Semicolon 104 Unicode strings 104 Adding data 104 Modifying data 105 Removing data 106 Merging data 107 Additional useful features 109 Output clause: inserted and deleted virtual tables 109 Identity and sequences 111 Top and Offset/Fetch 112 Aggregations 113 Grouping data 114 Multiple grouping 115 Windowing Functions 117 Bulk operations 121 If you want to know more 122 Chapter 5: Developing with Azure SQL – Advanced 124 Programmability 124 Variables 124 Temporary Tables 126 Views 127 Functions 129 Stored Procedures 132 Triggers 134 Non-scalar parameters 136 Table-Valued Parameters 136 JSON 138 CSV 139 Monitoring data for changes 139 Change Tracking 140 Change data capture 142 Protecting data assets 142 Permissions 142 Row-Level Security 145 Dynamic Data Masking 147 Always Encrypted 149 If you want to know more 150 Chapter 6: Practical Use of Tables and Indexes 152 Designing good tables 152 Creating tables 155 Determine the right column types 157 Define your column types 157 Collations 159 Computed columns 161 Complex-type columns 162 Declarative constraints 163 Making your tables secure 166 Improve performance with indexes 167 B-Tree indexes 168 When to use B-Tree indexes? 170 When to create indexes? 171 Maintaining the indexes 173 Retiring the indexes 175 If you want to know more 175 Chapter 7: Scalability, Consistency, and Performance 177 Transactions 178 Local transactions 181 Distributed transactions across cloud databases 192 Locking and non-locking options 194 Natively compiled procedure 200 Optimizing database roundtrips 202 Leveraging transactions 203 Table-valued parameters 204 Bulk copy 206 Multiple-row parameterized INSERT statements 207 Recommendations and best practices 207 If you want to know more 208 Chapter 8: Multi-model Capabilities 210 Leveraging multi-model capabilities in database design 211 Classifying domain models 212 Why would you choose Azure SQL for non-relational models? 214 JSON support 215 Formatting query results as JSON document 217 Storing JSON documents 218 Querying JSON data 219 JSON paths 220 Collation awareness 221 Ensuring data integrity in JSON documents 222 Indexing JSON values 222 Importing JSON documents 223 Graph structures 225 Loading graph data 227 Querying graph data 228 Spatial data 229 Querying spatial data 231 Spatial indexes 232 Geometry vs. Geography 234 XML data 235 Querying XML data 236 XPath and XQuery languages 238 XML indexes 240 Key-value pairs 241 How to handle unstructured text? 242 Indexing unstructured text 243 Querying unstructured text 244 How to leverage unstructured indexes on semi-structured data? 247 Multi-model in Azure SQL: why and when 249 If you want to know more 250 Chapter 9: More Than Tables 251 Columnstore format 251 Columnstore in Azure SQL 254 Updating values in Columnstore 257 Querying Columnstore structure 258 Clustered Columnstore indexes 260 Nonclustered Columnstore indexes 261 Memory-optimized tables 263 A story about optimistic concurrency 264 Should we be pessimists? 266 Memory-optimized tables 266 Accessing memory-optimized tables 270 Natively compiled code 272 Temporal tables 275 Querying temporal data 276 Configuring temporal tables 279 If you want to know more 280 Chapter 10: Monitoring and Debugging 282 Dynamic Management Views (DMVs) 284 Execution plans 287 Query store 291 Query Store at work 296 Raising and catching exceptions in SQL 303 Keep it simple! 307 Integration with application insights 308 If you want to know more 314 Chapter 11: DevOps with Azure SQL 316 CI/CD: Definitions and concepts 316 CI/CD and Azure SQL 317 GitHub Actions 317 Azure DevOps 318 Database migrations 319 Synthetic environment 320 Integration environment 321 Database migration tools 322 Code First 322 Database First 325 DACPAC 326 Comparison tools 327 DbUp 328 Database testing 329 Putting everything together 331 If you want to know more 331 Index 333 Here is the expert-level, insider guidance you need on using Azure SQL Database as your back-end data store. This book highlights best practices in everything ranging from full-stack projects to mobile applications to critical, back-end APIs. The book provides instruction on accessing your data from any language and platform. And you learn how to push processing-intensive work into the database engine to be near the data and avoid undue networking traffic. Azure SQL is explained from a developer's point of view, helping you master its feature set and create applications that perform well and delight users. Core to the book is showing you how Azure SQL Database provides relational and post-relational support so that any workload can be managed with easy accessibility from any platform and any language. You will learn about features ranging from lock-free tables to columnstore indexes, and about support for data formats ranging from JSON and key-values to the nodes and edges in the graph database paradigm. Reading this book prepares you to deal with almost all data management challenges, allowing you to create lean and specialized solutions having the elasticity and scalability that are needed in the modern world. What You Will Learn Master Azure SQL Database in your development projects from design to the CI/CD pipeline Access your data from any programming language and platform Combine key-value, JSON, and relational data in the same database Push data-intensive compute work into the database for improved efficiency Delight your customers by detecting and improving poorly performing queries Enhance performance through features such as columnstore indexes and lock-free tables Build confidence in your mastery of Azure SQL Database's feature set Who This Book Is For Developers of applications and APIs that benefit from cloud database support, developers who wish to master their tools (including Azure SQL Database, and those who want their applications to be known for speedy performance and the elegance of their code Front Matter ....Pages i-xxv A Database for the Modern Developer (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 1-13 Azure SQL Kickstart (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 15-34 Connecting and Querying Azure SQL (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 35-68 Developing with Azure SQL – Foundations (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 69-103 Developing with Azure SQL – Advanced (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 105-132 Practical Use of Tables and Indexes (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 133-157 Scalability, Consistency, and Performance (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 159-191 Multi-model Capabilities (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 193-233 More Than Tables (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 235-265 Monitoring and Debugging (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 267-300 DevOps with Azure SQL (Davide Mauri, Silvano Coriani, Anna Hoffman, Sanjay Mishra, Jovan Popovic)....Pages 301-317 Back Matter ....Pages 319-326