- Home
- IT Courses
- MS-DP3021: Configure and Migrate To Azure Database For PostgreSQL
MS-DP3021: Configure and Migrate To Azure Database For PostgreSQL
Course Code: MS-DP3021
Azure Database for PostgreSQL is a Platform as a Service database service in the Microsoft cloud. It bases itself on the PostgreSQL open-source relational database and includes built-in high availability, automatic backup and restore, as well as comprehensive security features. The pay-as-you-go pricing model provides predictable performance and near-instant scaling. In this learning path, you learn the main features of PostgreSQL and how they work in Azure Database for PostgreSQL. You learn about the different Azure Database for PostgreSQL implementation options, and how to configure a server for your needs.
This course is tailored for:
- PostgreSQL Developers: Individuals who develop applications using PostgreSQL and are looking to migrate their databases to Azure.
- Database Administrators: Professionals responsible for managing and maintaining PostgreSQL databases who want to learn about Azure’s database services and migration strategies.
- IT Professionals: Those involved in database management and cloud migration projects, seeking to enhance their skills in configuring and migrating PostgreSQL databases to Azure.
Before starting this module, you should have:
- Understanding of Relational Databases: You should be familiar with the concepts and structure of relational databases.
- Basic SQL Knowledge: You need to have a basic understanding of SQL, including writing and executing SQL queries.
This course aims to equip participants with the following skills and knowledge:
- Explore PostgreSQL Architecture: Understand the architecture of PostgreSQL and how it integrates with Azure.
- Client-Server Communication: Learn about connection handling and the PostgreSQL client tools.
- Query Processing: Identify and understand the components involved in PostgreSQL query processing.
- Security: Implement security features, create database users, and manage permissions in Azure Database for PostgreSQL.
- Procedures and Functions: Create and use stored procedures and functions within PostgreSQL.
- Write-Ahead Logging: Understand the concepts of write-ahead logging, replication, and logical decoding.
- Configuration and Management: Configure and manage Azure Database for PostgreSQL, including system catalogues and vacuum processes.
- Concurrency: Learn about concurrency control, isolation levels, and locking mechanisms in PostgreSQL.
- Query Tuning: Assess and improve query performance using tools like the Query Store.
- Migration: Explore various online and offline migration options and tools for moving databases to Azure Database for PostgreSQL.
There is no Associated Certification or Exam for this course, however, there is a link to your learning path (Assessment Link)
Modules
PostgreSQL is an open-source relational database system that is available as an Azure service. In this module, you learn about the PostgreSQL architecture, and how to manage different aspects of Azure Database for PostgreSQL.
Lessons
- Introduction.
- Describe Azure Database for PostgreSQL.
- Understand PostgreSQL architecture.
- Understand PostgreSQL shared memory.
- Exercise – Explore Azure Database for PostgreSQL.
- Knowledge check.
- Summary.
Learning Objectives
- Describe the architecture of PostgreSQL.
- Describe the options for creating an Azure Database for PostgreSQL server.
- Understand PostgreSQL shared memory.
PostgreSQL is a client-server system, which allows many clients to connect to a central server. In this module, you learn how PostgreSQL manages connections from clients, and look at some common PostgreSQL client tools.
Lessons
- Introduction.
- Understand connection handling.
- Understand the PostgreSQL client tools.
- Exercise - Explore PostgreSQL with client tools.
- Knowledge check.
- Summary.
Learning Objectives
- Understand connection handling.
- Understand the PostgreSQL client tools.
The Azure Database for PostgreSQL server receives SQL queries and returns the relevant results. Each SQL query goes through several stages in order to return the correct results as quickly as possible.
Lessons
- Introduction.
- Identify the query processing components.
- Understand the EXPLAIN statement.
- Exercise - Execute the EXPLAIN statement.
- Knowledge check.
- Summary
Learning objectives
- Identify the query processing components.
- Understand EXPLAIN.
Azure Database for PostgreSQL includes comprehensive security features including encryption, authentication, and granting permissions to database users. In this module, you learn about the security features of Azure Database for PostgreSQL.
Lessons
- Introduction.
- Describe Azure Database for PostgreSQL security.
- Describe built-in Azure Database for PostgreSQL server roles.
- Grant permissions in Azure SQL Database for PostgreSQL.
- Understand encryption in Azure Database for PostgreSQL.
- Exercise: Configure permissions in Azure Database for PostgreSQL.
- Knowledge check.
- Summary.
Learning Objectives
- Describe the security features of Azure Database for PostgreSQL.
- Create database users and grant permissions.
- Understand encryption in Azure Database for PostgreSQL.
PostgreSQL supports stored procedures and functions to make SQL queries reusable. In this module, you learn how to create and run stored procedures and functions.
Lessons
- Introduction.
- Create a stored procedure in Azure Database for PostgreSQL.
- Call a stored procedure in Azure Database for PostgreSQL.
- Create and use a function in Azure Database for PostgreSQL.
- Exercise - Create a stored procedure in Azure Database for PostgreSQL.
- Knowledge check.
- Summary.
Learning objectives
- Create a stored procedure in Azure Database for PostgreSQL.
- Call a stored procedure in Azure Database for PostgreSQL.
- Create and use a function in Azure Database for PostrgreSQL.
Azure Database for PostgreSQL is an ACID-compliant database service. Write-ahead logging ensures changes are both atomic and durable. In this module, you learn how Azure Database for PostgreSQL implements write-ahead logging.
Lessons
- Introduction.
- Describe write ahead logging.
- Describe replication and logical decoding.
- Exercise - List table changes with logical decoding.
- Knowledge check.
- Summary.
Learning objectives
- Describe write-ahead logging.
- Describe replication and logical decoding.
Azure Database for PostgreSQL employs system catalogues and views to manage metadata efficiently. Understanding these can significantly enhance database performance and streamline the administration of PostgreSQL systems.
Lessons
- Introduction.
- Understand the system catalogues and system views.
- Investigate metadata with system catalogues and system views.
- Explain the purpose of vacuum and autovacuum.
- Configure autovacuum server parameters.
- Exercise - Configure system parameters and explore metadata with system catalogues and views.
- Knowledge check.
- Summary.
Learning Objectives
- Describe system catalogues and system views in Azure Database for PostgreSQL.
- Investigate metadata in Azure Database for PostgreSQL.
- Explain the purpose of the vacuum process.
- Configure vacuum server parameters.
Azure Database for PostgreSQL is a multi-user relational database solution. The increase in users brings a risk of conflicts and so it is important to understand the concurrency systems that are in place.
Lessons
- Introduction.
- Understand multi-version concurrency control (MVCC) and snapshots.
- Describe isolation levels.
- Understand locking.
- Exercise - Understand locking.
- Knowledge check.
- Summary.
Learning Objectives
- Describe Multi-Version Content Control and snapshots in Azure Database for PostgreSQL.
- Describe isolation levels in Azure Database for PostgreSQL.
- Describe locking in Azure Database for PostgreSQL.
Database management systems (DBMSs) need systems to understand the data and the queries that are running against this data. It's important to understand how performance tuning works and how you can assess performance metrics.
Lessons
- Introduction.
- Understand statistics.
- Assess query performance with the Query Store.
- Exercise - Assess query performance using Query Store.
- Knowledge check.
- Summary.
Learning Objectives
- Describe statistics in Azure Database for PostgreSQL.
- Assess query performance with the Query Store.
Azure Database for PostgreSQL Flexible Server supports data migration from PostgreSQL servers. This module covers online and offline migration tools and methods, helping you choose the right approach for your scenario.
Lessons
- Introduction.
- Compare online and offline migrations.
- Identify migration tool options.
- Analyse the decision criteria for tool selection and migration model.
- Exercise – Offline PostgreSQL database migration.
- Knowledge check.
- Summary.
Learning Objectives
- Understand the different online and offline migration options.
- Identify the different migration tools available.
- Select the appropriate migration tool for your specific scenario.