Professional Azure SQL Managed Database Administration, 3e
Article Index
Professional Azure SQL Managed Database Administration, 3e
Chapters 4 - 8
Chapters 9 - 12, Conclusion

Author: Ahmad Osama & Shashikant Shakya
Publisher: Packt Publishing
Pages: 724
Print: 1801076529
ISBN: 978-1801076524
Audience: DBAs & devs
Rating: 4.5
Reviewer: Ian Stirk

This book aims to introduce Azure SQL managed databases, how does it fare?

Businesses are increasingly aware of the advantages of cloud-based systems (e.g. cost savings, scalability), this has resulted in a growing interest and migration of systems to the cloud. Microsoft provides popular cloud-based database options, called Azure SQL. If you want to increase your job prospects, it makes sense to learn about Azure SQL. 

This book is aimed at DBAs and developers that are creating or migrating applications to Azure SQL. Some prior knowledge of on-premise SQL Server is assumed, together with a (little) understanding of PowerShell code.

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1. Introduction To Azure SQL Managed Databases

The book opens with a brief look at the three flavors of Azure, namely: 

  • Azure VM (similar to on-premises, but Microsoft hosts your server)

  • Azure SQL Database (has most cloud features)

  • Azure SQL Managed Instance (cloud database with some server functionality) 

Note that the term ‘Azure SQL’ is the collective name for Azure SQL Database and Azure SQL Managed Instance. This book is largely about the two Azure SQL offerings, where Microsoft provides the infrastructure (e.g. backups, patching), allowing you to focus on your business processes. A diagram describing the three different types of Azure offerings would be helpful here, and one is provided, but in the final chapter!

There’s a brief look at the database architecture, followed by a step-by-step walkthrough on the provisioning of an Azure SQL database (and connecting to it via both the Azure portal and SSMS). Then there’s a brief look at connecting to an Azure SQL Managed Instance.

To put SQL Database, SQL Managed Instance, and SQL Server (i.e. typically on-premise) into context, there’s a useful section outlining the differences between them. In essence, SQL Managed Instance is similar to SQL Database but has some additional server functionality (e.g. SQL Server Agent, CLR, Resource Governor).

The chapter ends with step-by-step exercises on provisioning: 

  • Azure SQL Managed Instance using the Azure portal

  • Azure SQL Managed Instance and SQL Database using PowerShell 

Overall, this chapter is easy to read, with good explanations and flow between sections, helpful diagrams, and many useful practical walkthroughs – the latter is perhaps the outstanding feature of this book, very practical. That said, sometimes the material does not flow (e.g. the terms Single database and Elastic pool are first mentioned without any context or explanation). Everything is generally correct, and is easy to understand, if you already know the topic… and for beginners this may be a problem initially. Like other Azure SQL books, this one assumes too much knowledge of networking (e.g. virtual network subnet, endpoint etc). I felt the first few chapters should have been clearer, the book certainly gets better as you progress through it.

Chapter 2. Service Tiers

Having set the background to what Azure SQL is, we now look at the 2 options for purchasing computing processing, namely: 

  • Database Transaction Units (DTUs) 

  • vCores 

A DTU represents a given amount of CPUs, I/O, RAM, and storage. It’s available as basic, standard, and premium service tiers – providing progressively more resources. Within each of these service tiers, there are further options allowing for more resources. The DTU model is rather fixed, with the storage being tied to the compute power.

The vCore model separates the storage from the compute component, allowing greater control over your needs, and providing more options. Azure SQL Managed Instance can only use the vCore pricing model. The vCore model is split as follows: 

  • General Purpose – good balance for most business workloads

  • provisioned – normal vCore model

  • serverless – on demand usage, can reduce costs significantly 

  • Business Critical – preferred option for low latency, fast processing, and high availability

  • Hyperscale – auto scaling, highly resilient, use if database is over 8 TB, scales up to 100 TB 

For me, the above vCore model description needs a diagram to explain it more clearly, one should have been provided. 

One of the difficult questions to answer, when you move your database from on-premise to the cloud is, how much resource (CPU, RAM etc) do you need? Often people just get a similar sized machine. However, a better answer is available. The chapter discusses how you can use a PowerShell script to monitor your on-premise processing, and use this to determine the machine size in the cloud. This is very useful. However… since the book was written, there’s a new and easier feature available called sqlassessment (part of the Data Migration Assistant), and this should now be used instead.

It's very easy to scale up (or down) an Azure SQL Database, and an example of doing this with PowerShell is provided. This is followed with a step-by-step walkthrough on how to provision a Hyperscale SQL Database with PowerShell.

The chapter ends with an interesting section that discusses the factors which influence your choice of DTUs or vCores. The vCores pricing model provides cost savings by letting you use software assurance licenses, additionally it provides more options for resource combinations. 

This chapter provided a useful overview of the two pricing models. Parts could have been clearer, as stated already, if you understand the topic there is nothing wrong, but if you’re new to the topic things could have been made easier.

Chapter 3. Migration

Migration is often an area where you’ll have your first contact with Azure SQL, so this should be a popular read. The chapter opens with an outline of a migration strategy, covering: 

  • Determining the migration benefits – is migration to the cloud worth it?

  • Selecting a service model – SQL Database (single or elastic pool) or SQL Managed Instance

  • Selecting a service tier – how much resources are needed?

  • Selecting the primary region and disaster recovery region

  • Determining compatibility issues – some functionality may not be supported

  • Selecting a migration tool – Microsoft offers several migration tools 

Next, there’s a look at the areas to consider when choosing between SQL Database or SQL Managed Instance. Typically, SQL Managed Instance provides similar functionality as on-premise SQL Server (e.g. SQL Agent), but doesn’t have all the features of SQL Database. Migration to SQL Managed Instance is often easier/quicker, often referred to as a lift-and-shift operation. 

The authors suggest if you want to perform cross-database queries, you should opt for SQL Managed Instance instead of SQL Database, however, it is possible to use elastic queries with SQL Database to use tables on other databases.

There’s a useful section on the various tools that can be used to find any compatibility issues, these include: Data Migration Assistant (DMA), SSMS, SSDT, and Azure Database Migration Services. I’ve used DMA extensively, and found it invaluable for reporting compatibility problems with specific objects (e.g. stored procedures), together with helpful solutions.

The chapter then looks at the various tools to perform the migration itself. There’s a very useful table that provides a comparison between the various migration tools. The chapter ends with a set of step-by-step activities, covering how to migrate using 6 different methods!

Since Azure changes quickly, this chapter misses a recent, and perhaps easiest migration method. The latest versions of SSMS allow you to right-mouse click on a database, select Tasks, and then ‘Deploy Database to Microsoft Azure SQL Database’, very easy.

This chapter was very readable, and is sure to be of use to anyone needing to migrate a database to the cloud. The table of the various migration methods is especially helpful.


Last Updated ( Tuesday, 04 January 2022 )