Administering Azure SQL Databases

Introduction to Azure SQL

Azure SQL is a relational database service in the Microsoft cloud based on Microsoft SQL Server. In this introduction module, participants are introduced to the Azure SQL offering and concepts like Service Tiers, DTU's and the pricing of Azure SQL.

• Understanding Azure SQL
• Exploring the Azure SQL architecture
• Understanding Azure SQL pricing
• Understanding service tiers and DTU's
• Identifying limitations of Azure SQL
• Comparing Azure SQL Databases and Azure SQL Managed Instances
• Working with the Azure Management Portal
• LAB: Introduction to Azure SQL

Provisioning Azure SQL Databases

In this module, participants will learn how to create Azure SQL Servers and Databases. Once the Azure SQL Server and Azure SQL Databases are up and running, Firewall rules must be configured to allow client applications to connect to the Azure SQL Databases.

• Creating and configuring a Server in Azure SQL
• Creating and configuring an Azure SQL Database
• Implementing firewall rules
• Exploring the Azure SQL networking overview
• Exploring the Azure SQL management tools
• LAB: Creating Azure SQL Databases

Provisioning Azure SQL Managed Instances

Azure SQL Managed Instance is a cloud database service that combines the broadest SQL Server database engine compatibility. Students will learn about the key differences between an On-Premises SQL Server and an Azure SQL Managed Instance, how to provision and configure an Azure SQL Managed Instance,....

• Understanding Azure SQL Managed Instance
• Comparing on-premises SQL Server and Azure SQL Managed Instance
• Configuring virtual networks for Azure SQL Managed Instances
• Provisioning and configuring an Azure SQL Managed Instance
• Accessing Azure SQL Managed Instances
• Optional LAB: creating an Azure SQL Managed Instance

Azure SQL Data Storage

Before you can create and manage databases, you should understand how data is stored in them, know about the different types of files that SQL Server can utilize.

• Understanding data storage with SQL Server
• Understanding the structure of data files
• Working with transaction log files
• Working with filegroups
• LAB: Azure SQL Data Storage

Creating and Migrating Azure SQL Databases

In this module students will learn how on-premises SQL Server databases can be migrated to Azure SQL.

• Creating tables, views, stored procedures, ...
• Migrating an existing SQL Server database to Azure SQL
• Exporting and importing BACPAC files of a SQL Database
• Using the Azure Database Migration Service
• Copying an Azure SQL Database
• LAB: Migrating Azure SQL Databases

Azure SQL Database Client Connectivity

Connecting client applications to Azure SQL requires an understanding of the available connectivity options, drivers, and configuration settings. In this module, participants will explore the different connectivity libraries supported by Azure SQL, learn how to construct and manage connection strings, and understand how connectivity settings interact with firewall rules and authentication methods.

• Understanding connectivity options
• Exploring available connectivity libraries
• Working with Azure SQL Database connection strings

Securing user access to Azure SQL

Authentication requires that users must prove their identity before they can login to an Azure SQL Server or Database. Authorization is used to control what users can do and see inside an Azure SQL Database. Participants will learn how they can work Logins and Users to configure the security of Azure SQL

• Understanding authentication options
• Configuring authentication
• Configuring authorization
• Creating logins
• Working with server roles
• Working with database roles
• Creating database users
• Working with contained databases
• LAB: Securing user access to Azure SQL databases

A common application pattern in building SaaS applications is to provision a single database for each customer. But different customers often have varying usage patterns making it difficult to predict the resource requirements for each individual database. Elastic Database Pools solve this problem by allowing you to purchase resources for a pool of databases. Within a Database Pool, each database can then request resources between specified minimum and maximum parameters.

• Introducing Elastic Database Pools
• Implementing Elastic Database Pools and working with eDTU's
• Creating Elastic Database Pools
• Configuring Elastic Databases
• Monitoring Elastic Database Pools
• Creating Elastic Jobs
• Introducing instance pools in SQL Managed Instance
• LAB: Working with Elastic Database Pools and Elastic Jobs

Securing Azure SQL

Azure SQL Database and Azure SQL Managed Instance secure customer data by providing auditing and threat detection capabilities. This module covers how you can secure your data using features like Always Encrypted, Azure Defender for SQL,... You will also learn how you can audit user access to Azure SQL

• Implementing network security
• Configuring auditing in Azure SQL
• Using Azure Defender for SQL
• Enabling Advanced Threat Protection
• Implementing Always Encrypted
• Applying data discovery and classification
• Configuring dynamic data masking
• LAB: Auditing and securing Azure SQL

Monitoring and Tuning in Azure SQL

Azure SQL provides several ways for you to monitor your databases for issues, as well as look for ways to improve performance. You can create alerts using Microsoft Azure Monitor with metrics such as DTU, Database size percentage, and Deadlocks. You can also help improve your performance by using tools such as Query Performance Insight.

• Working with extended events
• Working with the SQL Server Query Store
• Using Query Performance Insight in Azure SQL Databases
• Using Azure SQL Analytics
• Enabling automatic tuning in Azure SQL
• Monitoring using DMVs
• Monitoring Azure SQL workloads with Database Watcher
• LAB: Monitoring Azure SQL databases

Backup and Restore in Azure SQL

This chapter will introduce you to backup and restore with Azure SQL Databases and Azure SQL Managed Instances. You'll learn about automated and manual backups, explore automated backup features, and perform the manual backup of an Azure SQL Managed Instance

• Understanding automatic backups in Azure SQL
• Configuring backup retention and storage
• Configuring long-term backup retention
• Performing manual backups with Azure SQL Managed Instances
• Performing a point-in-time restore
• Restoring from long-term backup
• Geo-restoring databases
• LAB: Backup and restore databases

High availability and disaster recovery

This module describes the capabilities that Azure SQL Database provides for business continuity and disaster recovery. Students will learn about the backup and restore features of an Azure SQL Database, how to setup geo-replication of Azure SQL Databases.

• Understanding business continuity
• Configuring high availability in the Basic, Standard and General-Purpose service tiers
• Configuring high availability in the Premium and Business Critical service tiers
• Implementing active geo-replication
• Configuring auto-failover groups
• LAB: High Availability