Chapter 4:​ Deploying Azure SQL

This section looks at deploying (creating) an Azure SQL Managed Instance and an Azure SQL Database (collectively these are ‘Azure SQL’) – they’re given together here because their deployment steps overlap.

First there’s a look at pre-deployment considerations, including the option of a new deployment or a migration. The resource limits for the various service tiers and sizes, will affect the Azure SQL option you choose (e.g. I/O latency).

The chapter provides step-by-step walkthroughs for both Azure SQL Managed Instance and Azure SQL Database (all the various types e.g. Serverless), for both new deployment and migration.

As always, the basic steps are given in the example walkthroughs provided, together with a great many links for further information. Post-deployment verification steps are also provided, together with some troubleshooting tips (e.g. SQL Server Error log).

This chapter provides clear practical instructions for deploying Azure SQL Managed Instance and Azure SQL Database, together with post-deployment checks – all with useful walkthroughs.

Chapter 5:​ Configuring Azure SQL

This section looks at configuring an Azure SQL Managed Instance and an Azure SQL Database.

The Managed Instance, being more similar to on-premise SQL Server, has more configuration options. However, some configuration options are not supported (you need to remember you are abstracted/protected from several physical settings) – such limitations are described relating to sp_configure, trace flags, and tempdb. There’s a useful section on configuring databases (create and alter).

Similarly, SQL Database configuration is examined, here there is less scope for changes, or rather, much is already pre-configured and can’t be changed – allowing you to focus on your applications.

There’s a very useful section on configuration restrictions (e.g. you can’t change the collation after deployment in Managed Instance, and sp_configure is not supported in SQL Databases). These make sense when you understand the context of the environment you’re in.

The chapter ends with a look at the many methods of loading data (e.g. BCP, SSIS, BACPAC, ADF etc).

Overall, this chapter, like the last one, has great explanations. The restrictions make sense once you understand what the environment supplies, where features are unnecessary or may be detrimental.

Chapter 6:​ Securing Azure SQL

Moving applications to the cloud often raises concerns about security. Azure provides various features to keep your databases and data secure.

The chapter opens with a look at network security, providing useful advice on typical secure configurations. Some very helpful walkthroughs are provided. If you’re like me, you might want to get a network/security specialist to double-check your work (DBAs typically concentrate on DBA work).

Next, various methods of authentication and access are discussed. These largely follow themes you’ll understand from on-premise SQL Server, including Role-Based Access Control and Microsoft Entra ID (previously known as Azure Active Directory).

Next, there’s a look at ways of protecting your data, covering familiar topics, including: Transparent Data Encryption, Always Encrypted, and SQL Ledger. There’s a helpful section on Azure’s various methods of monitoring security – something everyone needs to become familiar with.

The chapter ends with a look at some advanced security features, including Data Classification, SQL Threat Protection (additional features you can purchase), and Microsoft Defender for Cloud - which is the go-to place for Azure’s security functionality.

This chapter is a must read for everyone, security touches all our roles. Hopefully after reading this chapter, you’ll understand the range and depth of security feature Azure provides – but it is up to you to implement them effectively!

Chapter 7:​ Monitoring and Tuning Performance for Azure SQL

I tend to specialise in performance tuning, so for me, this was the most anticipated chapter. And it doesn’t disappoint.

First there’s a look at factors that might limit your performance, namely the service tier you choose. In essence, choosing a tier with more CPUs, RAM, and faster IO will perform better (at a cost). There’s a brief look at the features that may affect performance, including: indexes (all types), in-memory OLTP (depends on tier), partitioning, and Intelligent Query Processing (IQP).

Next, configuration of features that affect performance are examined, including: tempdb, MAXDOP, and Resource Governor – as always, Azure makes a good guess at these settings for you. There’s a useful section on the importance of maintaining indexes and statistics.

Various performance monitoring tools are discussed, including: Azure Monitor, Dynamic Management Views (DMVs), Extended Events (XEs), Query Store, Database Watcher (a centralised monitoring dashboard), and context-sensitive Copilot. Much of what you’ve learned in the on-premise environment is applicable here. There’s a very good walkthrough on the use of Query Store to investigate degraded queries. Additionally, some very helpful DMVs are highlighted.

The chapter ends with a look at Intelligent Query Processing. Azure can be configured to detect query performance degradation, and automatically correct it. Features examined include automatic Plan Correction and index creation.

Overall, this was another excellent chapter, full of detail, walkthroughs, and links for more information.