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, and tuning, while Microsoft takes care of areas such as backup, high availability, and more.
This chapter introduces the Azure SQL Database architecture, the Azure SQL Managed Instance connectivity architecture, and the differences between the SQL Database, SQL Managed Instance, and SQL Server (on-premises or using Azure VM) offerings.
In this chapter, we will be covering the following topics:
- Describing the architecture of SQL Database
- Identifying the differences between an on-premises SQL Server, SQL Database, and SQL Managed Instance
- The connectivity architecture of SQL Managed Instance
- Provisioning an Azure SQL Database and Azure SQL Managed Instance using the Azure portal and Windows PowerShell
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 SQL PaaS offerings, SQL Database and SQL Managed Instance. We'll learn about the SQL Database and SQL Managed Instance architectures, provision SQL Database and SQL Managed Instance, and identify the key differences between SQL Database, SQL Managed Instance, and on-premises SQL Server.
Throughout this book, you will also learn more about the different aspects of managing and administrating SQL Database and SQL Managed Instance, such as provisioning, migration, backup, restore, security, monitoring, and performance.
Note
Azure SQL Database is also commonly referred to as SQL Azure or SQL Database instances.
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 SQL Server (on-premises or on Azure VM) to SQL Database.
Note
Microsoft takes care of the operating system (OS), storage, networking, virtualization, servers, installation, upgrades, infrastructure management, and maintenance.
Azure SQL Database has the following deployment options:
- Single database
- Elastic pool
Azure SQL Database allows users to focus only on managing data and is divided into four layers that work together to provide users with relational database functionality, as shown in Figure 1.2:
Figure 1.2: The four layers of Azure SQL Database
Note
If you were to compare SQL Database's architecture to the on-premises SQL Server architecture, other than the service layer, the architecture is pretty similar.
The Client Layer
The client layer acts as an interface for applications to access an SQL Database. It can be either on-premises or on Microsoft Azure. The Tabular Data Stream (TDS) is used to transfer data between an SQL Database and applications. SQL Server also uses TDS to communicate with applications. This allows applications such as .NET, ODBC, ADO.NET, Python, and Java applications to easily connect to Azure SQL Database without any additional requirements.
The Service Layer
The service layer acts as a gateway between the client and platform layers. It is responsible for provisioning an SQL Database, user authentication, SQL Database validation, enforcing security (including firewall rules and denial-of-service attacks), billing and metering for SQL Databases, and routing connections from the client layer to the physical server hosting the SQL Database in...
