In Chapter 14, High Availability and Redundancy Concepts, we will look at how we achieve high availability—that is, the ability to automatically recover from a local, zone, or even regional outage, by creating replicas of your database.

When you use the Premium or Business Critical tiers with Azure SQL Server, a read-only replica is automatically created for you within the region, and you can optionally generate more replicas in other regions. Similarly, with the Hyperscale tier, you can also optionally create replicas of your databases.

As well as providing a failover facility, these replicas can also be used by your applications, which can then help spread the load across multiple servers when you only need to read data. This is known as read scale-out and, as already indicated, this is automatically enabled on the Premium and Business Critical tiers, while it is optional on the Hyperscale tier.

Within your application, you can configure a database connection string with ApplicationIntent=ReadOnly, which redirects the connection to the read-only replica rather than the read-write replica.

An example scenario that makes use of this pattern is where you have separate components of your application being responsible for day-to-day read/write interactions with your data and reporting. By configuring the reporting components to use the read-only replica, you remove the load from the read-write replica, as we can see in the following example:

Figure 12.6 – Using read-only replicas to spread the load

Using read scale-out replicas is a great way to increase the performance of your applications but only works when your workloads can be easily separated into read and read/write operations; as such, the amount of scale-out you can achieve may be limited.

Another way to achieve scale that can, in theory, be near limitless is to use a pattern called database sharding.

Using database sharding

Database sharding is a process of splitting your data across one or more databases. For sharding to work, the schema of the databases is the same. However, you must find a way to partition the data that enables you to know which database houses which chunk of data; this is sometimes known as a sharding key.

There are two mechanisms that can be used to enable database sharding—application-controlled and database-controlled mechanisms.

With application-controlled sharding, your application is responsible for knowing which database contains which data. A typical example may be an application that provides services for multiple clients. Each client could have its own database on its own server, and the application would be responsible for mapping a particular client to each database, as we can see in the following example:

Figure 12.7 – Application-level sharding

With database-controlled sharding, the database platform itself manages the sharding of data between databases. In such situations, there needs to be a key that can be used for sharding the data. One such example could be to use a date key or a name key whereby different databases store data for different time periods; this would be especially useful in an archiving system or genealogy database where records have practical date fields, as in the following example:

Figure 12.8 – Database-level sharding

Through a combination of database architecture patterns and choosing the correct service tiers, this will help you build a robust and performant database solution. The next area to consider is security—specifically, encryption.