Chapter 4. Backups

Backups are probably the most important part of a DBA’s job. Azure offers automatic backups by default, one more thing for businesses not to worry about. 

There’s some useful advice on factors that affect backup storage costs, including: 

• Choose the right backup storage type – there’s a useful table to help you decide

• Maximize your free backup storage space

• Configure Long Term Retention (LTR) backups 

Examples on how to do each of the above are included for most of the listed items.

Next, there’s a section on performing manual backups, producing DACPACs (i.e. schema only) and BACPACs (i.e. schema and data), that can be used to restore databases. There’s a useful table that compared manual and automated backups (e.g. automated backups are transactionally consistent). 

This chapter provides a reassuring note that by default automatic backups are created in Azure. There are some very useful step-by-step walkthroughs (e.g. performing a manual backup via PowerShell). Overall, a must read for DBAs.

Chapter 5. Restoration

The flip side of backup is restore, and it’s an equally important topic. The chapter provides many step-by-step examples of performing various types of restore (point-in-time, long-term database restore), using various methods (e.g. Azure portal, PowerShell). Again, it’s the detailed practical examples in this book that make it stand out.

Just like the previous chapter, this chapter is a must read for DBAs.

Chapter 6. Security

Security is a big and important topic, and suitably this chapter is the biggest in the book. The chapter opens with a look at network security, concentrating on database and server-level firewall rules, giving examples using the Azure portal and T-SQL. The section continues with an alternative to firewall rules, namely endpoints, and configuration details are provided. Much of this networking section may be unfamiliar territory for traditional DBAs.

Next, authentication is examined, covering SQL Authentication and the various means of Azure Active Directory authentication. Helpfully, useful examples are provided. This leads nicely into a familiar DBA topic, authorization, covering server-level roles, contained databases, groups and roles, row-level security, and dynamic data masking. 

There’s a useful discussion on SQL Server’s inbuilt Data Discovery (what parts of your data are sensitive), and Classification (what kind of sensitivity) to aid you with security/privacy.

Another facet of security is Auditing, which records database activity, allowing you to follow regulatory requirements, and catch anomalies and security breaches. Helpful examples are provided on how to implement and configure auditing.

Azure provides additional security functionality via the paid-for Azure Defender for SQL, which includes:  

• Vulnerability assessment – check for security concerns (e.g. unsecured data, extra permissions, firewall rules)

• Advanced Threat Protection – alert users to:

• SQL injection – identifies SQL injection attack or if code is vulnerable

• Unusual Access – e.g. odd location

• Brute-force SQL – high number of login failures tired with different credentials  

The chapter ends with some useful step-by-step walkthroughs for row-level security, dynamic data masking, and using Azure Defender for SQL to show SQL injection and brute-force attacks – all very useful.

This chapter is essential reading for the DBA, since lapses in security can cause extensive damage. The chapter covers a great many areas that involve security and privacy. Being primarily a DBA, I must admit, with network security, I always try to get a network security specialist to double-check my work. The layout of the bullet points for Azure Defender for SQL is incorrect.

Chapter 7. Scalability

If data volumes increase or your servers have peaks in activity, you’ll want to scale-up your systems to ensure performance is maintained or improved. And of course, if your data volumes decrease or there are times when your systems are less active, it is possible to scale-down your systems. Azure provides functionality for this. 

Vertical scaling involves changing the resources of your server/database (e.g. CPUs, RAM). An example is provided in T-SQL to change the service layer (i.e. get more resources). While this is useful, a much better solution is to be able to change the service tier dynamically, and a very useful example of this is given (you create an automation account and runbook, to perform some action when a condition is met e.g. CPU usage is greater than 70%).

Horizontal scaling (also called sharding) partitions data from one database into several other databases (e.g. a large customer table might have customer names starting with A to M in one database, and those names starting with N to Z in another database). Details of the architecture and components are outlined. As always, a very useful step-by-step walkthrough is provided.

The chapter ends with a look at scaling of a SQL Managed Instance, using both the Azure portal and PowerShell. There’s an interesting section on other methods of scaling (e.g. read replica).

I found this chapter useful, and scaling your systems up or down during busy/quieter times should produce a cost saving (remember serverless provides automatic tuning out-of-the-box, so might be a better choice). There’s a very helpful example of automatically scaling of tiers using a runbook.

Chapter 8. Elastic And Instance Pools

Elastic pools allow you to host several SQL databases together, so they share the resources (e.g. CPU) more economically. This is especially useful if the databases use the resources at different times (e.g. systems in UK and Australia), or they have peak usage at different times (e.g. end of day processing). If you have 4 database systems, each using 50 DTUs, that have similar usage but spread over the day, it can be cheaper to host them all in an elastic pool of 100 DTUs (and even cheaper if you have more databases).

The chapter opens with a helpful discussion of when you should consider using elastic pools. Even better, it provides a useful calculation to help you size your elastic pool based on the average and peak usage of its underlying databases.

Next, you’re treated to another very helpful step-by-step walkthrough on creating an elastic pool and adding databases to it, using the Azure portal. This is followed with a similar example using PowerShell. 

It is possible to run T-SQL against all the databases in the elastic pool (e.g. rebuild indexes), by creating elastic database jobs, details on how to do this using T-SQL is demonstrated.

This is followed by similar discussions and examples using SQL Managed Instance, and the key differences are explained.

Cost is obviously a key concern when considering cloud-based systems. The use of elastic pools can reduce the costs considerably. This chapter explains the use of elastic pools convincingly, with ever-helpful examples.