|Azure SQL Revealed (Apress)|
Page 2 of 3
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 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 stop or start your 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. The author helpfully acknowledges that a network engineer might be needed to help (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 Azure Active Directory, and Role-Based Access Control.
Next, there’s a look at ways of protecting your data, covering the familiar topics of Transparent Data Encryption, Always Encrypted, and Dynamic Data Masking. 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, Advanced Threat Protection (additional features you can purchase), and Azure Security Centre - 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), and partitioning.
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, DMVs, XEs, and Query Store. 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 index creation, dropping indexes, and forced plan.
Overall, this was another excellent chapter, full of detail, walkthroughs, and links for more information. There are very helpful tables showing the resource limits (e.g. # CPUs) for the various server tiers, for both Managed Instance and SQL Database.
|Last Updated ( Wednesday, 14 July 2021 )|