Professional Azure SQL Database Administration

Equip yourself with the skills to manage and maintain data in the cloud, 2nd Edition

Ahmad Osama

• Publisher: Packt Publishing
• Year: 2019
• ISBN:
  9781789804188
• Edition: 2
• Topic: Informatik
• Subtopic: Datenbanken

Leverage the features of Azure SQL database and become an expert in data management

Key Features

• Explore ways to create shards and elastic pools to scale Azure SQL databases
• Automate common management tasks with PowerShell
• Implement over 40 practical activities and exercises to reinforce your learning

Book Description

Despite being the cloud version of SQL Server, Azure SQL Database differs in key ways when it comes to management, maintenance, and administration. This book shows you how to administer Azure SQL database to fully benefit from its wide range of features and functionality.

Professional Azure SQL Database Administration begins by covering the architecture and explaining the difference between Azure SQL Database and the on-premise SQL Server to help you get comfortable with Azure SQL database. You'll perform common tasks such as migrating, backing up, and restoring a SQL Server database to an Azure database. As you progress, you'll study how you can save costs and manage and scale multiple SQL Databases using elastic pools. You'll also implement a disaster recovery solution using standard and active geo-replication. Whether it is learning different techniques to monitor and tune an Azure SQL database or improving performance using in-memory technology, this book will enable you to make the most out of Azure SQL database features and functionality for data management solutions.

By the end of this book, you'll be well versed with key aspects of an Azure SQL database instance, such as migration, backup restorations, performance optimization, high availability, and disaster recovery.

What you will learn

• Understand Azure SQL Database configuration and pricing options
• Provision a new SQL database or migrate an existing on-premise SQL Server database to Azure SQL Database
• Back up and restore Azure SQL Database
• Secure an Azure SQL database
• Scale an Azure SQL database
• Monitor and tune an Azure SQL database
• Implement high availability and disaster recovery with Azure SQL Database
• Automate common management tasks with PowerShell
• Develop a scalable cloud solution with Azure SQL Database
• Manage, maintain, and secure managed instances

Who this book is for

If you're a database administrator, database developer, or an application developer interested in developing new applications or migrating existing ones with Azure SQL database, this book is for you. Prior experience of working with an on-premise SQL Server or Azure SQL database along with a basic understanding of PowerShell scripts and C# code is necessary to grasp the concepts covered in this book.

Chapter 1

Microsoft Azure SQL Database Primer

Learning Objectives

By the end of this lesson, you will be able to:

• Describe the architecture of Microsoft Azure SQL Database
• Identify the differences between the on-premises SQL Server and Azure SQL Database
• Provision a SQL managed instance
• Provision an Azure SQL database using the Azure portal and Windows PowerShell

This lesson introduces the Azure SQL Database architecture, the difference between Azure SQL Database and on-premises SQL Server, and Azure SQL Database managed instance (SQL managed instance).

Introduction

There are very few relational database systems as established and widely used as Microsoft's SQL Server. Azure SQL Database, released on February 1, 2010, is a cloud database service that is based on Microsoft's SQL Server.

It is compatible with most SQL Server 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 Database-as-a-Service (DBaaS) can offer. Azure SQL Database is a DBaaS option for any organization with applications built on SQL Server Database.

Azure SQL Database uses familiar Transact-SQL programming and a user interface that is well known and is also easy to adopt. It is therefore important for SQL Server Database administrators and developers to learn how to use Azure SQL Database.

Note

Azure SQL Database is also known as SQL Azure or SQL Database instance.

This lesson covers the Azure SQL Database architecture in detail. After familiarizing ourselves with the architecture, we'll learn how to provision Azure SQL Database through activities and explore pricing, settings, and its properties. We'll also identify the key differences between Azure SQL Database and SQL Server – mainly the SQL Server features that are not supported by Azure SQL Database.

The Azure SQL Database Architecture

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

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
• Elastic pool
• Managed instance

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 the following diagram:

Figure 1.1: The four layers of Azure SQL Database

Note

If you were to compare it to the on-premise SQL Server architecture, other than the Service Layer, the rest of the architecture is pretty similar.

Client Layer

The client layer acts as an interface for applications to access a SQL database. It can be either on-premises or on Microsoft Azure. The Tabular Data Stream (TDS) is used to transfer data between a SQL database and applications. SQL Server also uses TDS to communicate with applications. This allows applications such as .NET, ODBC, ADO.NET, and Java to easily connect to Azure SQL Database without any additional requirements.

Service Layer

The service layer acts as a gateway between the client and platform layers. It is responsible for:

• Provisioning a SQL database
• User authentication and SQL database validation
• Enforcing security (firewall rules and denial-of-service attacks)
• Billing and metering for a SQL database
• Routing connections from the client layer to the physical server hosting the SQL database in the platform layer

Platform Layer

The platform layer consists of physical servers hosting SQL databases in data centers. Each SQL database is stored on one physical server and is replicated across two different physical servers:

As shown in Figure 1.1, the Platform Layer has two other components: Azure Fabric and Management Services. Azure Fabric is responsible for load balancing, automatic failover, and the automatic replication of a SQL database between physical servers. Management Services takes care of an individual server's health monitoring and patch updates.

Infrastructure Layer

This layer is responsible for the administration of the physical hardware and the OS.

Note

Dynamic routing allows us to move a SQL database to different physical servers in the event of any hardware failures or for load distribution.

Azure SQL Database Request Flow

The following diagram shows the Platform layer:

Figure 1.2: Platform layer – nodes

The application sends a TDS request (login, DML, or DDL queries) to the SQL database. The TDS request is not directly sent to the Platform layer. The request is first validated by the SQL Gateway Service at the Service layer.

The Gateway Service validates the login and firewall rules, and checks for denial-of-service attacks. It then dynamically determines the physical server on which the SQL database is hosted and routes the request to that physical server in the Platform layer. Dynamic routing allows the SQL database to be moved across physical servers or SQL instances in the event of hardware failures.

Note

Here, a node is a physical server. A single database is replicated across three physical servers internally by Microsoft to help the system recover from physical server failures. The Azure SQL Server user connects to just a logical name.

Dynamic routing refers to routing the database r...