Chapter 9. High Availability And Disaster Recovery

While backup and restore provide the backstop of database recovery, High Availability (HA) and Disaster Recovery (DR) typically provide a more immediate solution to getting your systems online and usable again. HA allows almost immediate database usability after a recoverable fault (e.g. disk failure), whereas DR typically provides recoverability for wider more intense problems (storms in a given region).

Azure provides inbuilt HA and DR, an immediate advantage over on-premise systems since these require expert knowledge and can be time consuming to set up.

The chapter opens with a look at local and zone-redundant HA, with useful descriptions and diagrams. There’s a short diversion into Accelerated Database Recovery which speeds up the recovery process. DR can be implemented via geo-replication.

For both HA and DR, the series of step-by-step walkthroughs should provide you with the details you need so you’ll feel confident configuring and failing over systems. 

Overall, a useful chapter, highlighting the need for HA and DR, the advantage you have with it being provided automatically in Azure (some things may need configuring).

Chapter 10. Monitoring And Tuning

Problems with servers, databases and queries are common. A monitoring system can help you investigate underlying issues so performance can be improved.

Azure provides some monitoring out-of-the-box, and this is examined using the Azure portal. This includes database space usage, and CPU usage, over a given time period – with some default graphs provided. Alerts can be created to notify you when a condition occurs (e.g. CPU usages over 80%), a step-by-step example of this is explained. 

For deeper and more comprehensive monitoring, you need to set up logging. Various conditions can be monitored and logged, including: 

• DatabaseWaitStatistics – reason why SQL is not running

• Timeouts 

• Blocking

• Deadlocks 

As before, there’s a step-by-step walkthrough on how to set up diagnostics and monitoring. Additionally, the logs can be analyzed using Azure SQL Analytics, and an example is provided. 

Next, monitoring using Dynamic Management Views (DMVs) is discussed, these are internal views that record interesting information about queries. If you’re familiar with using DMVs on-premise, you’ll be in familiar territory. Sample code and examples are provided for: database metrics, connections, query performance, and blocking.

This is followed with using Extended Events for monitoring, this is the light-weight replacement for SQL Profiler/trace. A useful template example is provided, that records details of queries taking longer than 10 seconds to run.

The final section covers tuning, including: 

• automatic tuning – automatically create and drop indexes, and run query with old faster plan

• in-memory technology – have tables in-memory together with truly compiled SQL

• columnstore indexes – offers improved compression, segment elimination, and batch-mode processing 

This chapter provides many approaches to monitoring and tuning Azure SQL. As always, some very useful template code and walkthroughs are provided. It might have been useful to correlate the different methods of monitoring information with each other, since sometimes problems are not what they initially seem. 

Chapter 11. Database Features

This chapter seems to be a mix of largely disparate features. 

The chapter opens with a look at SQL Data Sync, this allows you to synchronize data between Azure SQL databases and on-premise databases. This is a very useful feature for those who are migrating their databases to the cloud, so they can gain confidence that their applications will continue to work. Data can be synchronized in one direction or both, and you can prioritize which database wins when they change the same data row. The data sync can be run manually or scheduled. Data Sync is not currently available on SQL Managed Instance (but check again, things change often in Azure). 

There’s a useful step-by-step walkthrough on creating a Data Sync using PowerShell, however I often think the examples should initially be done in the Azure portal, and when you have a general understanding of the task, you can then automate with PowerShell.

Next, we jump to online and resumable index operations – I told you it was a chapter with no discernable connection between topics! We then jump to SQL graph databases and queries, these are not relational databases but can be queried via SSMS. They are typically used to represent many-to-many relationships or hierarchies.

Much of this chapter is taken up with the use of Machine Learning Services, using python and R languages. It is currently available only on SQL Managed Instance. Some differences between on-premise and Managed Instance are given (e.g. latter doesn’t support Java packages). There’s a helpful walkthrough of running a basic python script (useful for ensuring you’ve setup the environment correctly). This is followed by a more appropriate example, on forecasting monthly sales (Machine Learning essentially looks of patterns and this can be used to make predictions). 

The chapter ends with a look at the use of distributed transactions in Azure SQL Managed Instance. Distributed transactions involve 2 or more database servers, and these need to be added to a Server Trust Group. An example is given on creating a Server Trust Group, and running a distributed transaction (where the instances are joined via linked servers). 

This chapter really is a mix of different feature with little to link them. That said, the content is very good, and the examples are exactly what’s needed for your first steps in using Azure SQL.

Chapter 12. App Modernization

This oddly named chapter discusses migrating databases to SQL Managed Instance, and then discusses 2 of the more unusual Azure SQL offerings, namely Serverless and Hyperscale service tiers.

The chapter opens with a look at migrating your databases from on-premise to SQL Managed Instance. This is often best for migrating existing applications, having almost 100% compatibility with on-premise. SQL Managed Instance manages backups, security, patching of SQL Server and Windows, HA/DR, and scaling – this allows you to concentrate on your business applications. Various migration options are discussed, but no examples given (usual for this book).

Next, the chapter looks at using the serverless service tier. Serverless has the advantage of being able to scale automatically, can be paused during times of inactivity, and is billed by the second. A useful step-by-step example of creating a serverless database is given. This is followed with an example illustrating the auto-scaling and associated billing functionality. The section ends with a very helpful table comparing serverless and provisioned (i.e. typical) offerings.

Lastly, the hyperscale service tier is examined, this is the ideal choice for systems that require high performance and scalability. Hyperscale currently supports databases up to 100 TB size. Examples are provided on moving an existing SQL Database to the hyperscale service tier using both the Azure portal and PowerShell code.

This chapter provided some very useful discussions and examples, of the more ‘unusual’ Azure database offerings. They may not always be unusual, Bob Ward of Microsoft suggest serverless could be particularly game-changing.

I’m not sure if this chapter’s title indicates its content. Additionally, using the word ‘app’ instead of ‘application’ here might cause more confusion. Perhaps the chapter’s migration content could have been included in the migration chapter.

Conclusion 

This book aims to introduce Azure SQL managed databases, and succeeds. Overall, it has a good flow of topics within and between chapters, with useful discussions, diagrams, and example code. The book’s overarching advantage lies in its many practical step-by-step walkthroughs, the book is worth reading just for these. That said, I felt there was some confusion in the introductory chapters, the concepts could have been explained more clearly - but the book certainly gets better after these initial chapters.

The more experience you have of on-premise SQL Server, the easier the book is to read and put into context (e.g. distributed transactions, HA/DR). Some Azure SQL functionality is only in Azure SQL Database or Azure SQL Managed Instance, but since Azure changes quickly, it’s worth checking regularly for new functionality. 

Overall, a very practical book, useful for DBAs and developers that want to know about Azure SQL. Recommended.

Related Reviews:

Azure SQL Revealed (Apress)

Exam Ref AZ-900 Microsoft Azure Fundamentals

Microsoft Azure Data Fundamentals Exam Ref DP-900 (Microsoft Press)

For more books on Azure see Cloud Computing Books Pick Of The Bunch on Programmer's Bookshelf.

To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.