Preparation for Google Cloud Professional Cloud Database Engineer Certification
About Me: I am working in Google as Cloud Migration Consultant for data. I am cloud enthusiast and a travel buff
Google cloud had recently launched Professional Database Engineer certification in Aug 2022.
I had taken this certification recently in Sept 2022.
I am writing this article to share some key focus points from the certification perspective.
The exam guide is available at the link -https://cloud.google.com/certification/guides/cloud-database-engineer
The certification exam is 2 hrs long and typically has 50 questions.
This certification is intended for the audience who have been working with database solutions (relational or no-sql) , planning to adopt/design/manage GCP cloud platform database offerings either from (lift-shift or modernisation) perspective.
This certification focusses on the below key aspects of each database solution offering
- Which database better suits your purpose (NoSQL vs SQL, Latency, Global or Regional presence, TCO, RPO, RTO, Size & Scale)
- High Availability (Fault Tolerance)
- Scalability (Auto-Scaling or Manual Scaling support, Scale out or Scale Up)
- Data Migration(homogenous and heterogenous migrations, one-time migration and replication, tools and templates available, third-party tools Eg: Striim)
- Connectivity to the databases (Serverless, Managed platform — private/public ip connectivity)
- Monitoring performance and taking corrective measures to avoid sub-optimal performance
- Security, Encryption and Access Control (IAM Roles)
- Backups (Manual, Automated, Point in Time Restoration Support, How does Google’s acquisition Actifio help in this regard)
To start with, the certification expects the understanding on which database options are available and what are the use-cases for them
Note — AlloyDB is one of the new offerings in GCP platform but not yet included in the certification at the time of writing.
I have focused this article on major contenders — Cloud SQL, Cloud Spanner, Big Table and Bare Metal in this blog and mentioned few other topics towards the end which should be prepared
Cloud SQL
Cloud SQL is in-arguably the most focussed topic in this certification owing to the complexity, features and the breadth of the product itself.
Key Focus Points:
Provisioning of Cloud SQL
- What are the supported versions of the database offerings (MySQL, POSTGRE and SQL server)
What is the maximum capacity that can be provisioned (64 TB, 96 VCPUS)
Regional (HA) or Zonal configuration
1. Cloud SQL does not support cross region HA instances
2. Cloud SQL performs synchronous replication on failover instance
3. Switch over takes couple of minutes to the failover instance (impacts the RPO requirement if less than 3–5 minutes)
Configuration of CPUS/Storage
How does configuration of VCPUs and Storage size affect network throughput, Disk throughput and IO throughput
1. Network throughput — Maximum limited to 2000 MB/s which reaches at 8 vCPU configuration.
2. Disk Throughput — 0.48 Mb/s for every 1 GB storage, Read capped at 1200 Mb/s and Write capped at 400 Mb/s
3. IO Throughput — 30 operations/second for every 1 GB storage, Read throughput capped at 60K and Write throughput capped at 25K
Replica Settings
1. What use-case is solved by having replicas for the instance
2. Replica creation does not require any downtime, the applications can still connect to the instance and read data, only admin operations cannot be performed
3. Replica placement (Cloud SQL does not provide explicit load balancing between the primary and replica instances, think about Proxy SQL) and what needs to be enabled for creation of replicas (Binary logging and PITR)
4. Type of Replicas — Understand the use-case for each of them
- External Replicas
- Replication from external server (Understand the cloud APIs on how the replication from external server is achieved)
- Cascading replicas — Replicas of replicas
5. Understanding Replication lag between instances (cross-regional replicas will have greater replication lag than same region replicas)
6. Disaster Recovery strategy using cross-region replicas — https://cloud.google.com/sql/docs/mysql/intro-to-cloud-sql-disaster-recovery
Scalability
1. From the scalability perspective, Cloud SQL supports scale up approach but can hit the hard limits
Refer the below for alternate approaches (database sharding, split large database on domain specific database)
https://cloud.google.com/community/tutorials/horizontally-scale-mysql-database-backend-with-google-cloud-sql-and-proxysql
2. Scalability is manual in nature for cloud SQL
Connectivity
1. Cloud SQL provides combination of Private IP and Public IP connectivity
2. Understand how the private connectivity is provided between customer and Tenant project via VPC Peering (Private Service Connection and Allocated IP Range)
3. Understand for which type of products, what type of connectivity options are available for the applications
Database Migration
This is extremely important part of the data migration life-cycle.
This topic in itself is pretty vast and diverse with deep knowledge required on the databases itself.
The complexity further extends on Heterogenous migrations
I would like to highlight some of the key focus points here from the perspective of the certification.
- Understand Database Migration Service -DMS (Near real time or real time replication, down time, supported combination of source and targets, One Time vs. Continuous migration)
Check the pre-requisites for each type of database
https://cloud.google.com/database-migration/docs/mysql/configure-source-database
https://cloud.google.com/database-migration/docs/mysql/rds-no-superuser
https://cloud.google.com/database-migration/docs/mysql/mysql-definer - Datastream — Providing CDC flavour for supported databases, integration with dataflow
- What tools are available for facilitating cross database data migrations Eg: Striim, Fivetran, Dataflow etc.
Qwiklab — https://www.coursera.org/lecture/enterprise-database-migration/lab-review-online-data-migration-to-cloud-spanner-using-striim-4qWdS - Offline Migrations vs. Online Migrations
- Watch the below which will give idea on different phases of migration and vendor tools which supports these phases (MigVisor, Stratozone, Ispirer..)
https://www.youtube.com/watch?v=-W4sbomrhyY
https://www.youtube.com/watch?v=PDFC7XpGAVU
Data Ingestion
1. Cloud SQL has support for SQL, CSV files import and export using gcloud commands (understand the commands and requirements)
2. Dataflow templates, Data Fusion and others can be used for pulling and pushing data in.
Monitoring the performance
1. Leverage Cloud Monitoring for key metrics https://cloud.google.com/sql/docs/mysql/admin-api/metrics
2. Query Insights for POSTGRE
3. Use of tools like SQL Commenter
4. What measures can be taken to identify and fix performance bottlenecks
https://www.youtube.com/watch?v=gIeuiGg-_iw
https://www.youtube.com/watch?v=rN99XFcAbyo
5. What tools are available for performance benchmarking
Backups and Maintenance
1. Cloud SQL supports both manual and automated scheduled backups (know the gcloud parameters for enabling them)
2. Point In Time Restoration is strong feature (understand the pre-requisites for having PITR and how to do restoration(clone) — support for time based restoration and based on binary logs as well)
3. Maintenance is also focussed area, understand what attributes of maintenance are there (maintenance order, re-schedule maintenance and deny maintenance)
4. Where are the backups stored, can it be accessed or shipped to cloud storage or are these proprietary
5. Between backups and import/export, what are the differences, at what level can backup be performed (database, instance or table level)
Security
1.Cloud SQL Auth Proxy — Unix Sockets and TCP connections, how serverless products leverage Auth proxy for connectivity.
2. What is the use of SSL certificates and when to leverage (public IP connectivity)
3. How does the certificate rotation happen, any downtime, can it be rolled back
5. Data at Rest Encryption using CMEK keys, Rotation of keys and impact on the backups, cloning and replicas
6. Authentication mechanisms — IAM based or DB User Password based
7. Instance level authorization and Database level authorization (grants..)
8. Authorized Networks for public IP connection and what restrictions it brings to serverless products which does not have dedicated IP and how to work around it.
Summary on authorization and authentication mechanisms supported
Cloud Spanner
Cloud Spanner has fair share of questions in the certification. This is one of the most genius products bringing the segregation of compute and storage and massive horizontal scalability for relational databases.
Key Focus Points:
Cloud Spanner Background
Understanding how cloud spanner achieves the performance with high availability is good to know
https://thedataguy.in/internals-of-google-cloud-spanner/
https://ai.google/research/pubs/pub39966
https://ai.google/research/pubs/pub45855
Cloud Spanner Provisioning
1. Understanding the units of provisioning (Node and Processing units, Cloud Spanner supports granular instance provisioning — 1/10th of the node)
2. Regional or Multi-Regional Instances (How does the replication occur) — https://cloud.google.com/spanner/docs/replication
https://cloud.google.com/spanner/docs/whitepapers/life-of-reads-and-writes#aside-distributed-filesystems
3. Number of databases co-relation with Cloud Spanner instance size(Side note — there is impact of having too many databases on key visualiser, try to to find why)
4. Pros — Don’t have to worry on maintenance patches or schedule, Global reach, 99.999% availability, can easily scale up or down, Load balancing is done internally, no additional configurations needed on network side
5. Cons — Cost (which can be offset by the above benefits and TCO)
Cloud Spanner Dialect comparison
Cloud spanner provides Google Standard SQL dialect and POSTGRE dialect, good to know some differences (not an exhaustive list)
High Availability
1. Cloud Spanner at minimum has regional configuration which provides auto replication on all the available zones of region, so no explicit need to configure HA instance
2. Depending on the geographies of the applications/users accessing spanner instance, either regional or multi-regional configuration can be provisioned, the leader region is configurable for multi-region instances
Scalability
1. Cloud Spanner allows to add/remove processing units/nodes at will but is manual in nature
2. Cloud Spanner Auto-Scaler toolkit is available for handling the auto-scaling (read about the different types of algorithms supported) — https://cloud.google.com/architecture/autoscaling-cloud-spanner
Data Ingestion
1. Pre-built Dataflow templates available for CSV and AVRO files (CSV at table level)
2. HarbourBridge is something good to read about and know it as an option.
3. Articles and videos to read through — https://cloud.google.com/spanner/docs/migrating-mysql-to-spanner
https://cloud.google.com/spanner/docs/migrating-oracle-to-cloud-spanner
https://cloud.google.com/spanner/docs/migrating-postgres-spanner
https://www.youtube.com/watch?v=xdVVYiV9VXI
Backups and Restoration
1. Cloud Spanner provides on-demand backups, not scheduled as compared to Cloud SQL
2. Point in time recovery is supported (understand the attributes — version_retention_period, earliest_version_time)
Schema Design
Understand the best practices of schema design as this can easily lead to sub-optimal performance if not designed correctly https://cloud.google.com/spanner/docs/schema-and-data-model
Security
1. Understand the different roles of spanner and subtle differences between the roles.
2. Granularity of the roles (Instance, DB level)
Performance Monitoring and optimisation
1. What tools are available to analyse the performance
Introspection tools — Read, Lock, Transaction, Query Stats (understand how they can help to identify issue)
Key Visualiser — Understand the different patterns and when to use
Cloud Monitoring metrics
Query Execution Plans, Indexes
Cloud Spanner supports transactions and batches (good to know the type of mutations available — DML, mutations and partitioned DML)
Note —
Change Streams is one of the good features about cloud spanner (good to know)
Cloud Spanner supports fine grained authorization (but not covered in certification at time of writing)
Cloud Spanner Postgre Dialect is not covered as part of certification
Cloud BigTable
Cloud BigTable invariably comes into picture when the requirement is of low latency reads and writes with wide or narrow NoSQL tables in GCP ecosystem.
Key Focus Points
Cloud BigTable Provisioning
1. Choice between SSD and HDD disks, in what use cases would you prefer HDD disks (higher storage, less retrieval, with intention of backup cluster)
2. Cloud BigTable Instances have zonal clusters and can have maximum up to 8 clusters (can be in any region) — How does it affect the replication lag
3. Best feature about Big Table — Each cluster can have its own spec with its own scaling settings.
4. Understand how big a node and how many nodes can be provisioned per instance (Refer the quotas for the region)
5. What attributes can be and cannot be modified once the instance is in place
Scaling
1. The biggest plus of BigTable, comes with auto scaling setting for the cluster (understand which attributes can be configured for auto-scaling)
Schema Design
1. Touch base on best practices for schema design on Big Table https://cloud.google.com/bigtable/docs/schema-design
2. How many tables should be created per database, How it impacts the performance and Key Visualiser metrics
Application Profiles
Another strong feature of BigTable — that provides control on how to handle the incoming requests.
Understanding how it affects the consistency model in case of single cluster routing and multi-cluster routing.
What are default settings of app profiles
How does it impact on failover of an instance.
Performance Monitoring
1. Key Visualiser
2. Finding the hot tablets and rectifying the schema design or increasing the capacity
3. Cloud Monitoring Metrics
Security
1. IAM Roles
2. Table Level permission settings
Import/Export
1. Hbase Utilities for migrating sequence files (snapshots, replication library)
2. Dataflow templates for ingesting different formats of data
Good to know — Bigtable can be integrated with BigQuery using federated tables for analytics
Bare Metal Solution
Provides way to manage Bare Metal Solution hardware installed in a regional extension located near a Google Cloud datacenter.
- Specialised workloads like Oracle which does not have direct support in GCP due to licensing issues
- Purpose build HPE machines in the regional extension to Google Cloud
- Connected via Partner Interconnect (10 GBPS) line
- Egress between Regional Extension and Google Cloud is free
- VPC Peering is done b/w regional extension and Google Cloud.
- Provisioned in a co-located datacenter with latency less than 2 ms
It would be very useful to understand the different concepts around Oracle Eco-System
1. Oracle Data Guard
2. Oracle RAC
3. Oracle Exadata — Specialised Hardware
4. Oracle RMAN — Backups/Restoration
5. Ora2Pg Tools — Oracle to Postgre schema conversion
6. LogMiner
Architecture of Bare Metal Solution
https://cloud.google.com/bare-metal/docs/bms-planning
Course Era has good videos on BMS solution and use-cases which can be leveraged.
High Level Comparison of Database Offering
Finally I tried to put up comparison between the database offerings to best of my knowledge.
Other notable mentions
1. Cloud Firestore — (Understand the use-case for firestore and how the DB is provisioned, difference between Native mode and Datastore mode, Offline persistence)
2. Database specific tools (Eg: PgBouncer, PgBench for Postgre, good to know such tools and functionalities)
Hope the content provided helps you in the certification journey.
Linked-In Handle — https://www.linkedin.com/in/murli-krishnan-a1319842/
Happy Learning !!!
