Professional Azure SQL Database Administration
eBook - ePub

Professional Azure SQL Database Administration

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

Ahmad Osama

Share book
  1. 562 pages
  2. English
  3. ePUB (mobile friendly)
  4. Available on iOS & Android
eBook - ePub

Professional Azure SQL Database Administration

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

Ahmad Osama

Book details
Book preview
Table of contents

About This Book

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.

Frequently asked questions

How do I cancel my subscription?
Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is Professional Azure SQL Database Administration an online PDF/ePUB?
Yes, you can access Professional Azure SQL Database Administration by Ahmad Osama in PDF and/or ePUB format, as well as other popular books in Ciencia de la computación & Bases de datos. We have over one million books available in our catalogue for you to explore.



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).


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.


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
Figure 1.1: The four layers of Azure SQL Database


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.


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.


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...

Table of contents