Chapter 12: Creating Scalable and Secure Databases

In the previous chapter, we began Section 4, Applications and Databases, by looking at web-based application components in Azure and the different architectural patterns that can be leveraged to create resilient and performant applications.

Nearly all applications use a database to hold information, and therefore this chapter looks at the data tier. We start by examining the two main database options in Azure— Azure Structured Query Language (Azure SQL) and Azure Cosmos DB. We then progress into the different pricing and service tiers available for each service and look at how these impact scalability and resilience.

Finally, we look at how to secure database services through encryption and the different areas where this can be applied.

With this in mind, this chapter covers the following four topics:

• Selecting a database platform
• Understanding database service tiers
• Designing scalable databases
• Securing...

Technical requirements

This chapter will use the Azure portal (https://portal.azure.com) for examples throughout.

Selecting a database platform

Within Azure, there are two main types of database—Azure Cosmos DB and Azure SQL. There are also several different kinds of SQL databases—Azure SQL Database, Azure SQL Managed Instance, or even traditional Microsoft SQL running on a virtual machine (VM). In this section, however, we will consider why you might choose Cosmos DB over SQL Server.

With the choice between Cosmos DB and SQL and a choice between relational SQL databases and hierarchical NoSQL databases, we need to understand the differences between them. We will start with SQL.

Understanding SQL databases

SQL databases are built around the concept of tables, and within each table, you would have rows of data split into cells. Each cell would contain an individual piece of data within a record, and each row would be an entire record.

SQL databases have a schema that lays out your data structure in tables, columns, and rows. The schema must be defined before you can enter data...

Understanding database service tiers

Usage patterns and requirements for any type of database vary greatly depending on the application that uses them. In an on-premises scenario, you would need to calculate the optimum amount of RAM, CPU cores, and disk configurations to best optimize a database or set of databases for any given solution.

In Azure, unless you opt for running SQL on a VM, many of these configuration details are abstracted away, and instead you must choose a pricing model and a service tier.

Because Cosmos DB and Azure SQL Database are different platforms, the configuration options are also different. Therefore, the options for each must be considered separately. We will start by understanding Azure SQL pricing tiers.

SQL Database tiers

Azure SQL has the most options available; you must decide between a pricing model, a service tier, and even different SQL products.

You essentially have two options when it comes to choosing a Platform-as-a-Service (PaaS) SQL...

Designing scalable databases

As applications grow, you need to be able to accommodate the need for more storage and more processing power to cope with demand. Sometimes, this growth is organic and happens naturally over time as your service becomes more popular.

For some applications, the need to rapidly scale out is known ahead of time—for example, with reporting solutions where you know you will be ingesting large amounts of data.

Within Azure, there are many options for achieving growth. However, the correct choice will, as always, depend on your precise requirements and solution architecture.

Both Cosmos DB and Azure SQL Database design for scale from the outset. As we saw in the previous section, the available tier is built around Azure storage with separate compute nodes. Upgrade to the Hyperscale tier, and your databases can grow up to 100 TB. However, in order to manage additional storage, you must also increase the compute capabilities.

The default options for...

Securing databases with encryption

When storing data in a database, a key question many organizations will need to know, especially when that data is being held with a public cloud provider such as Azure, is: How safe is my data?

Using encryption is a great way to protect data. Encryption protects your data in the event of other protection measures—such as networks, firewalls, or authentication controls— being circumvented. If a hacker were able to bypass other controls, they would not be able to read any encrypted data without the key used to encrypt it.

There are multiple areas where encryption can be used, including the following:

• Encryption in transit
• Encryption at rest
• Encryption in use

First, we will consider in-transit protection.

Encryption in transit

Encrypting data in transit means ensuring that when moving data from point A to point B, any interception of that traffic would not yield anything useful.

Azure SQL Database and Azure SQL...

Summary

In this chapter, we have looked at the two main types of databases available—SQL and NoSQL databases, specifically looking at Azure SQL Database, Azure SQL Managed Instance, and Azure Cosmos DB. We have looked at how the different pricing and service tiers have an impact on multiple areas such as costs, scalability, resilience, and performance.

We also looked at different architectural patterns for ensuring our databases remain performant, based on expected usage scenarios and growth. Finally, we examined the different types of encryption we can employ to secure our data, including encryption in transit, at rest, and in use.

In the next chapter, we continue the data management theme by looking at the different tools available for the integration, movement, and transformation of data.

Exam scenario

MegaCorp Inc. is building a new e-commerce application. Due to the nature of the data, there will be lots of interrelated records, and it is imperative that data across records is kept consistent.

The site will start with a small number of records initially, but is expected to expand quite rapidly. The company wants to keep costs aligned with growth—that is, sales need to fund the scaling-out of the system. Any scaling operation will be managed by the team as they will first analyze usage patterns and sales.

As part of the application, the sales and marketing teams will run regular reports against the database. The management team is concerned the reporting mechanism could affect the performance of the system and have asked if there is some way to separate the customer experience and the sales and marketing teams' access.

You have been tasked with designing a database solution to accommodate their needs.

Further reading

For more information on topics covered in this chapter, you can refer to the following links:

• Azure Cosmos DB: https://docs.microsoft.com/en-us/azure/cosmos-db/introduction
• Azure SQL Database: https://azure.microsoft.com/en-gb/services/sql-database/
• Azure SQL security: https://docs.microsoft.com/en-us/azure/azure-sql/database/security-overview
• Azure SQL service tiers: https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tiers-general-purpose-business-critical