Beyond Azure SQL Database Management

In this chapter, we are continuing the journey of working with Azure SQL Databases, and since SQL Servers can be implemented using two service models (Infrastructure as a Service (IaaS) and Platform as a Service (PaaS)), we'll kick off the chapter by illustrating the difference between them, followed by covering elastic database pools, then demonstrating how to set Azure Active Directory (AD) authentication on Azure SQL Databases.

Since the availability of your databases is very important, and you want to avoid losing them even in the event of a disaster, you have to embrace active geo-replication, which will not only play a role in ensuring that doesn't happen but will also help you to build a globally distributed application. The concept of doing so and how to do so is covered in detail later in the chapter. Finally, you will learn...

SQL Database (IaaS/PaaS)

An SQL Database can be implemented in Azure in two ways:

• Using Azure SQL Database: It follows the PaaS model, and we have been using it so far
• Using Azure VMs and building SQL on them: This follows an IaaS model, and will be covered in more detail shortly

Azure SQL Database (PaaS)

As mentioned earlier, Azure SQL Database is a relational database as a service, built and hosted on Azure.

Azure SQL Database minimizes the costs of managing and provisioning databases. Using this model will reduce the responsibility of managing the virtual machines that host the SQL server, the operating system, and even the SQL Server software.

This model eliminates concerns regarding upgrades, backups, and even the high...

Azure SQL elastic database pools

In the previous chapter, some interesting and important topics were covered regarding elastic database pools. In this section, we will be working on creating and managing elastic database pools.

Creating an elastic database pool

To get your elastic database pool up and running, you have to follow these steps:

1. Navigate to the SQL databases blade, and click on Add or Create SQL databases, as shown in the following screenshot:

Figure 5.1: SQL Databases blade

2. Once you have clicked on Add or Create SQL databases, a new blade will pop up, as shown in the following screenshot:

Figure 5.2: Create a new SQL Database blade

3. Since most of the required fields were covered in the previous chapter...

Setting Azure AD authentication to Azure SQL Database

So far, we have been using SQL authentication to connect to Azure SQL Database, as we did in the previous chapter via SQL Server Management Studio.

Using Azure AD will provide centralized administration for database users' identities.

Doing so is very straightforward; you can follow the following steps to do so:

1. Navigate to the Azure SQL Server and scroll down to Active Directory admin, as shown in the following screenshot:

Figure 5.10: Azure AD admins for the Azure SQL Server

2. Click on Set admin and a new blade will pop up, as shown in the following screenshot:

Figure 5.11: Azure AD users and groups

3. Select the desired user or group to be added as an AD admin on the SQL Server, then save your changes to be applied to the SQL Server, as shown in the following screenshot:

Figure 5.12: Saving changes to the SQL Server...

Active geo-replication

Active geo-replication is one of the most important business continuity methodologies.

When using active geo-replication, you can configure up to four secondary databases within the same region or in different regions with reading access. This will help to reduce latency for users or applications that need to query the database from a different region.

If a catastrophic disaster occurs, you can failover to the other region using a failover group.

Failover groups are mainly designed to manage every aspect of geo-replication automatically, such as connectivity, relationships, and failover. Considering that it is enabled across Azure SQL Database Basic and Standard service tiers.

Implementing active geo-replication

...

Automating the tasks

As usual, at the end of each chapter, we get our hands dirty with automation.

Creating an elastic database pool using PowerShell

To create an elastic database pool, you must have an Azure SQL Server. You can use the one created in the previous chapter using PowerShell or create a new one yourself.

Then, you can run the following cmdlet to build an elastic database pool:

New-AzureRmSqlElasticPool -ResourceGroupName PacktPub -ServerName "packtpubsqlps" -ElasticPoolName "EDPPS" -Dtu 400 -DatabaseDtuMin 10 -DatabaseDtuMax 100

Where:

• -Dtu is the maximum number of eDTUs in the pool
• -DatabaseDtuMin is the minimum number of eDTUs assigned to a database
• -DatabaseDtuMax is the maximum number...

Further information

Most of the important and commonly used features of Azure SQL Database have been covered in the last two chapters. However, for further information about other topics related to Azure SQL Database that has not been covered, you can check out the following links:

• Monitoring and tuning performance: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-troubleshoot-performance
• Scaling out with Azure SQL Database: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-introduction
• SQL Server Database migration to SQL Database in the cloud: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate
• Securing Azure SQL Database: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview

Summary

This chapter has been an extension of the previous one, and throughout the chapter, very important things have been brought to the table to illustrate the use of Azure SQL Database also fulfilling the need to design globally distributed applications.

In the next chapter, we will work on one of the most important features of Azure, which is Azure Backup. This feature has become much more important recently, especially since the recent ransomware attacks.