Professional Azure SQL Managed Database Administration

Efficiently manage and modernize data in the cloud using Azure SQL, 3rd Edition

Ahmad Osama, Shashikant Shakya

• Verlag: Packt Publishing
• Jahr: 2021
• ISBN:
  9781801073325
• Auflage: 3
• Thema: Computer Science
• Thema: Desktop Applications

Master data management by effectively utilizing the features of Azure SQL database.

Key Features

• Learn to automate common management tasks with PowerShell.
• Understand different methods to generate elastic pools and shards to scale Azure SQL databases.
• Learn to develop a scalable cloud solution through over 40 practical activities and exercises.

Book Description

Despite being the cloud version of SQL Server, Azure SQL Database and Azure SQL Managed Instance stands out in various aspects when it comes to management, maintenance, and administration. Updated with the latest Azure features, Professional Azure SQL Managed Database Administration continues to be a comprehensive guide for becoming proficient in data management.

The book begins by introducing you to the Azure SQL managed databases (Azure SQL Database and Azure SQL Managed Instance), explaining their architecture, and how they differ from an on-premises SQL server. You will then learn how to perform common tasks, such as migrating, backing up, and restoring a SQL Server database to an Azure database.

As you progress, you will study how you can save costs and manage and scale multiple SQL databases using elastic pools. You will also implement a disaster recovery solution using standard and active geo-replication. Finally, you will explore the monitoring and tuning of databases, the key features of databases, and the phenomenon of app modernization.

By the end of this book, you will have mastered the key aspects of an Azure SQL database and Azure SQL managed instance, including migration, backup restorations, performance optimization, high availability, and disaster recovery.

What you will learn

• Understanding Azure SQL database configuration and pricing options
• Provisioning a new SQL database or migrating an existing on-premises SQL Server database to an Azure SQL database
• Backing up and restoring an Azure SQL database
• Securing and scaling an Azure SQL database
• Monitoring and tuning an Azure SQL database
• Implementing high availability and disaster recovery with an Azure SQL database
• Managing, maintaining, and securing managed instances

Who this book is for

This book is designed to benefit database administrators, database developers, or application developers who are interested in developing new applications or migrating existing ones with Azure SQL database.

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.

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

Inhaltsverzeichnis

• Professional Azure SQL Managed Database Administration – Third Edition
• Preface
• 1. Introduction to Azure SQL managed databases
• 2. Service tiers
• 3. Migration
• 4. Backups
• 5. Restoration
• 6. Security
• 7. Scalability
• 8. Elastic and instance pools
• 9. High availability and disaster recovery
• 10. Monitoring and tuning
• 11. Database features
• 12. App modernization
• Index