TDE protects data against physical disk theft. However, there are times when you need the data to be protected within the database itself. You may want to protect sensitive data from some users but make it available to others.

One such example would be a finance application whereby approved users of the finance system can read the data; however, database administrators should not be able to read the data.

In such scenarios, you can employ a feature of SQL called Always on encrypted. The SQL Always on encrypted feature uses a key or certificate to encrypt data before it is sent to the database. Usually, this would be performed by the application.

The application would therefore use a key or certificate stored in a key vault or certificate store to encrypt the data before writing it to the database. Similarly, the data would be read from the database in its encrypted format and then decrypted by the application.

In this way, any access to the database by a database administrator by issuing direct Transact-SQL (T-SQL) commands such as a SELECT statement would return encrypted data, as we can see in the following example:

Figure 12.9 – Always Encrypted in use

Only users who had authenticated against the application would then be able to access the data; this split of administrator and authorized user access is known as segregation of duties (SoD).

Another feature of SQL that can help protect data at the field level is Dynamic data masking. Dynamic data masking is similar to Always Encrypted, in that individual fields are masked (but not encrypted). This is managed by the SQL server by configuring columns to use the masking feature and then using SQL roles to define who can read the data unmasked or who must read it masked.

Dynamic data masking can therefore be used without the need to modify existing code within an application.

Different organizations have different requirements for the security of their data. However, personal and sensitive data should always employ the highest levels of protection—often, this will involve using multiple layers.

As we have seen, Azure and SQL technologies can be used to provide both resilience and security at all levels.

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.