1. Introduction to Azure SQL managed databases

There are very few relational database systems as established and widely used as Microsoft's SQL Server. SQL Server on Microsoft Azure comes in three different flavors (commonly known as the Azure SQL family): SQL Server on Azure Virtual Machines (VM) (infrastructure as a service, or IaaS), Azure SQL Database (platform as a service, or PaaS), and Azure SQL Managed Instance (PaaS).

Each of these products has specific use cases, which makes it easy for us to move to Azure SQL whether we're starting up with a new application or migrating an existing workload to Azure.

The IaaS offering, SQL Server on Azure VM, is similar to an on-premises service where Microsoft manages the hardware, virtualization, and infrastructure, and database administrators (DBAs) manage every aspect of SQL Server.

The PaaS offerings, Azure SQL Database and Azure SQL Managed Instance, allow DBAs to focus more on monitoring, capacity planning...

Who manages what?

Figure 1.1 lists the tasks that you (the DBA) and Microsoft manage for Azure SQL PaaS:

Figure 1.1: Who manages what?

Note

Fixing outages here refers to application outages that arise due to blockages, deadlocks, and broken releases, rather than infrastructure outages.

In an Azure SQL PaaS environment, the DBA works closely with application developers to understand the application and database design, help with the migration (when moving from on-premises to Azure), choose the right performance tier to start with, and then continuously monitor performance for cost optimization.

The DBA also has to work closely with DevOps and often get into DevOps' shoes to automate the release and deployment process and provision the database infrastructure.

This requires learning a new set of skills, such as familiarity with different Azure services, DevOps, and monitoring and management tools.

This chapter introduces the two Azure...

The Azure SQL Database architecture

Azure SQL Database is a highly scalable, multi-tenant, and highly available Platform-as-a-Service (PaaS) or Database-as-a-Service (DBaaS) offering from Microsoft.

Azure SQL Database, first released on February 1, 2010, is a cloud database service that is based on Microsoft SQL Server.

It is compatible with most SQL Server database-level features and is optimized for Software-as-a-Service (SaaS) applications.

As organizations are adopting cloud computing and moving their applications into the cloud, Azure SQL Database offers everything that DBaaS can offer. Azure SQL Database is a DBaaS option for any organization with applications built on SQL Server databases.

SQL Database uses familiar Transact-SQL programming and a user interface that is well known and easy to adopt. As companies move their workloads to the cloud, it is important for SQL Server DBAs and developers to learn how to use Azure SQL Database for a smooth transition from...

Provisioning an Azure SQL Database

Provisioning an Azure SQL Database refers to creating a new and blank Azure SQL Database.

In this section, we'll create a new SQL Database in Azure using the Azure portal:

1. Open a browser and log in to the Azure portal using your Azure credentials: https://portal.azure.com.
2. In the left-hand navigation pane, select Create a resource:
   

   Figure 1.4: Azure pane

3. On the New page, under Databases, select SQL Database:
   

   Figure 1.5: Azure panel

4. On the SQL Database page, under the Project details heading, provide the Subscription and Resource group details. Click the Create new link under the Resource group textbox. In the pop-up box, set the Resource group name as toystore.

   Note

   A resource group is a logical container that is used to group the Azure resources required to run an application.

   For example, the toystore retail web application uses different Azure resources, such as Azure SQL Database, Azure VMs, and Azure Storage. All of these...

Introduction to Azure SQL Managed Instance

Azure SQL Managed Instance is a fully managed SQL Server instance offering announced in May 2017 and made generally available on October 1, 2018.

Azure SQL Managed Instance provides nearly 100% surface area compatibility with the latest SQL Server (Enterprise Edition) database engine, providing all the PaaS benefits available with Azure SQL Database, such as automatic patching and version updates, automatic backups, high availability, and so on.

Note that Azure SQL Managed Instance is its own product within the Azure SQL family, rather than being just a deployment option for Azure SQL Database, with near 100% compatibility with on-premises SQL Server instances.

Azure SQL Managed Instance supports most of the instance-scoped features of traditional SQL Server deployment, which were previously not available in Azure SQL Database, since Azure SQL Database is scoped at the database-level. Azure SQL Managed Instance, therefore, provides...

Connecting to Azure SQL Managed Instance

Azure SQL Managed Instance is a set of services hosted on one or more isolated virtual machines inside a virtual network subnet.

When we provision an Azure SQL Managed Instance, a virtual cluster is created. A virtual cluster can have one or more SQL Managed Instances.

Applications connect to databases via an endpoint, <mi_name>.<dns_zone>.database. windows.net, and should be inside a virtual network, a peered virtual network, or an on-premises network connected via VPN or Azure ExpressRoute.

Unlike Azure SQL Database, Azure SQL Managed Instance supports Azure Virtual Network (VNet). An Azure VNet is a logical boundary or isolation that groups resources within a specified Azure region and enables secure communication between resources, the internet, and on-premises networks:

Figure 1.30: High-level connectivity architecture for SQL Managed Instances

Figure 1.30 shows a high-level connectivity...

Differences between SQL Database, SQL Managed Instance, and SQL Server

SQL Database and SQL Managed Instance are PaaS offerings from the Azure SQL family and therefore some of their features differ from the on-premises SQL Server. Some of the important features that differ are as follows:

Backup and restore

SQL Database

Conventional database backup and restore statements aren't supported on SQL Database. Backups are automatically scheduled and start within a few minutes of the database being provisioned. Backups are consistent, transaction-wise, which means that you can do a point-in-time restore.

There is no additional cost for backup storage until the amount stored goes beyond 100% of the database's size.

You can reduce the backup retention period to manage backup storage costs. You can also use the long-term retention period feature to store backups in a separate Azure blob container for a much lower cost for up to 10 years.

Apart from automatic backups...

Activity: Provisioning Azure SQL Server and SQL Database using PowerShell

This section discusses provisioning Azure SQL Server and SQL Database using PowerShell. To understand the process, let's take the example of Mike, who is the newest member of the data administration team at ToyStore Ltd., a company that manufactures toys for children. ToyStore has an e-commerce web portal that allows customers to purchase toys online. ToyStore has migrated the online e-commerce portal to Microsoft Azure and is therefore moving to Azure SQL Database from an on-premises SQL Server. Mike is asked to provision the Azure SQL Database and other required resources as his initial assignment. This can be achieved by following these steps:

Note

If you are short of time, you can refer to the C:\Code\Chapter01\Provision-AzureSQLDatabase.ps1 file. You can run this file in the PowerShell console instead of typing the code as instructed in the following steps. Open a PowerShell console and enter...

Summary

This chapter was an introduction to the SQL PaaS offering from Microsoft. We learned about the Azure SQL architecture and the different layers that make up the Azure SQL infrastructure.

We also learned about the request flow through the different layers when a user connects to and queries an Azure SQL Database. We learned how to connect to and query a database from SQL Server Management Studio and the Azure portal.

We learned about the Azure SQL Managed Instance connectivity architecture and learned how to provision a managed instance using PowerShell.

Most importantly, the chapter covered the differences between Azure SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

In the next chapter, we will discuss how to migrate data from an on-premises system to an Azure SQL Database and Azure SQL Managed Instance.