This document in the Google Cloud Architecture Framework provides best practices to deploy your system based on database design. You learn how to design, migrate, and scale databases, encrypt database information, manage licensing, and monitor your database for events.
Key services
This document in the Architecture Framework system design category provides best practices that include various Google Cloud database services. The following table provides a high-level overview of these services:
| Google Cloud service | Description |
|---|---|
| Cloud SQL | A fully managed database service that lets you set up, maintain, manage, and administer your relational databases that use Cloud SQL for PostgreSQL, Cloud SQL for MySQL, and Cloud SQL for SQL Server. Cloud SQL offers high performance and scalability. Hosted on Google Cloud, Cloud SQL provides a database infrastructure for applications running anywhere. |
| Bigtable | A table that can scale to billions of rows and thousands of columns, letting you store up to petabytes of data. A single value in each row is indexed; this value is known as the row key. Use Bigtable to store very large amounts of single-keyed data with very low latency. It supports high read and write throughput at low latency, and it is a data source for MapReduce operations. |
| Spanner | A scalable, globally distributed, enterprise database service built for the cloud that includes relational database structure and non-relational horizontal scale. This combination delivers high-performance transactions and consistency across rows, regions, and continents. Spanner provides a 99.999% availability SLA, no planned downtime, and enterprise-grade security. |
| Memorystore | A fully managed Redis service for Google Cloud. Applications that run on Google Cloud can increase performance by using the highly available, scalable, secure Redis service without managing complex Redis deployments. |
| Firestore | A NoSQL document database built for automatic scaling, high performance, and application development. Although the Firestore interface has many of the same features as traditional databases, it is a NoSQL database and it describes relationships between data objects differently. |
| Firebase Realtime Database | A cloud-hosted database. Firebase stores data as JSON and it synchronizes in real time to every connected client. When you build cross-platform apps with Google, iOS, Android, and JavaScript SDKs, all of your clients share one real-time database instance and automatically receive updates with the newest data. |
| Open source databases | Google partners offer different open source databases, including MongoDB, MariaDB, and Redis. |
| AlloyDB for PostgreSQL | A fully managed PostgreSQL-compatible database service for demanding enterprise workloads. Provides up to 4x faster performance for transactional workloads and up to 100x faster analytical queries when compared to standard PostgreSQL. AlloyDB for PostgreSQL simplifies management with machine learning-enabled autopilot systems. |
Database selection
This section provides best practices for choosing a database to support your system.
Consider using a managed database service
Evaluate Google Cloud managed database services before you install your own database or database cluster. Installing your own database involves maintenance overhead including installing patches and updates, and managing daily operational activities like monitoring and performing backups.
Use functional and non-functional application requirements to drive database selection. Consider low latency access, time series data processing, disaster recovery, and mobile client synchronization.
To migrate databases, use one of the products described in the following table:
| Database migration product | Description |
|---|---|
| Cloud SQL | A regional service that supports read replicas in remote regions, low-latency reads, and disaster recovery. |
| Spanner | A multi-regional offering providing external consistency, global replication, and a five nines service level agreement (SLA). |
| Bigtable | A fully managed, scalable NoSQL database service for large analytical and operational workloads with up to 99.999% availability. |
| Memorystore | A fully managed database service that provides a managed version of two popular open source caching solutions: Redis and Memcached. |
| Firebase Realtime Database | The Firebase Realtime Database is a cloud-hosted NoSQL database that lets you store and sync data between your users in real time. |
| Firestore | A NoSQL document database built for automatic scaling, high performance, and ease of application development. |
| Open source | Alternative database options including MongoDB and MariaDB. |
Database migration
To ensure that users experience zero application downtime when you migrate existing workloads to Google Cloud, it's important to choose database technologies that support your requirements. For information about database migration options and best practices, see Database migration solutions and Best practices for homogeneous database migrations.
Planning for a database migration includes the following:
- Assessment and discovery of the current database.
- Definitions of migration success criteria.
- Environment setup for migration and the target database.
- Creation of the schema in the target database.
- Migration of the data into the target database.
- Validation of the migration to verify that all the data is migrated correctly and is present in the database.
- Creation of rollback strategy.
Choose a migration strategy
Selecting the appropriate target database is one of the keys to a successful migration. The following table provides migration options for some use cases:
| Use case | Recommendation |
|---|---|
| New development in Google Cloud. | Select one of the managed databases that's built for the cloud—Cloud SQL, Spanner, Bigtable, or Firestore—to meet your use-case requirements. |
| Lift-and-shift migration. | Choose a compatible, managed-database service like Cloud SQL, MYSQL, PostgreSQL, or SQLServer. |
| Your application requires granular access to a database that CloudSQL doesn't support. | Run your database on Compute Engine VMs. |
Use Memorystore to support your caching database layer
Memorystore is a fully managed Redis and Memcached database that supports submilliseconds latency. Memorystore is fully compatible with open source Redis and Memcached. If you use these caching databases in your applications, you can use Memorystore without making application-level changes in your code.
Use Bare Metal servers to run an Oracle database
If your workloads require an Oracle database, use Bare Metal servers provided by Google Cloud. This approach fits into a lift-and-shift migration strategy.
If you want to move your workload to Google Cloud and modernize after your baseline workload is working, consider using managed database options like Spanner, Bigtable, and Firestore.
Databases built for the cloud are modern managed databases which are built from the bottom up on the cloud infrastructure. These databases provide unique default capabilities like scalability and high availability, which are difficult to achieve if you run your own database.
Modernize your database
Plan your database strategy early in the system design process, whether you're designing a new application in the cloud or you're migrating an existing database to the cloud. Google Cloud provides managed database options for open source databases such as Cloud SQL for MySQL and Cloud SQL for PostgreSQL. We recommend that you use the migration as an opportunity to modernize your database and prepare it to support future business needs.
Use fixed databases with off-the-shelf applications
Commercial off-the-shelf (COTS) applications require a fixed type of database and fixed configuration. Lift and shift is usually the most appropriate migration approach for COTS applications.
Verify your team's database migration skill set
Choose a cloud database-migration approach based on your team's database migration capabilities and skill sets. Use Google Cloud Partner Advantage to find a partner to support you throughout your migration journey.
Design your database to meet HA and DR requirements
When you design your databases to meet high availability (HA) and disaster recovery (DR) requirements, evaluate the tradeoffs between reliability and cost. Database services that are built for the cloud create multiple copies of your data within a region or in multiple regions, depending upon the database and configuration.
Some Google Cloud services have multi-regional variants, such as BigQuery and Spanner. To be resilient against regional failures, use these multi-regional services in your design where possible.
If you design your database on Compute Engine VMs instead of using managed databases on Google Cloud, ensure that you run multiple copies of your databases. For more information, see Design for scale and high availability in the Reliability category.
Specify cloud regions to support data residency
Data residency describes where your data physically resides at rest. Consider choosing specific cloud regions to deploy your databases based on your data residency requirements.
If you deploy your databases in multiple regions, there might be data replication between them depending on how you configure them. Select the configuration that keeps your data within the desired regions at rest. Some databases, like Spanner, offer default multi-regional replication. You can also enforce data residency by setting an organization policy that includes the resource locations constraints. For more information, see Restricting Resource Locations.
Include disaster recovery in data residency design
Include Recovery Time Objective (RTO) and Recovery Point Objective (RPO) in your data residency plans, and consider the trade-off between RTO/RPO and costs of the disaster recovery solution. Smaller RTO/RPO numbers result in higher costs. If you want your system to recover faster from disruptions, your system will cost more to run. Also, factor customer happiness into your disaster recovery approach to make sure that your reliability investments are appropriate. For more information, see 100% reliability is the wrong target and Disaster recovery planning guide.
Make your database Google Cloud-compliant
When you choose a database for your workload, ensure that the selected service meets compliance for the geographic region that you are operating in and where your data is physically stored. For more information about Google's certifications and compliance standards, see Compliance offerings.
Encryption
This section provides best practices for identifying encryption requirements and choosing an encryption key strategy to support your system.
Determine encryption requirements
Your encryption requirements depend on several factors, including company security policies and compliance requirements. All data that is stored in Google Cloud is encrypted at rest by default, without any action required by you, using AES256. For more information, see Encryption at rest in Google Cloud.
Choose an encryption key strategy
Decide if you want to manage encryption keys yourself or if you want to use a managed service. Google Cloud supports both scenarios. If you want a fully managed service to manage your encryption keys on Google Cloud, choose Cloud Key Management Service (Cloud KMS). If you want to manage your encryption keys to maintain more control over a key's lifecycle, use Customer-managed encryption keys (CMEK).
To create and manage your encryption keys outside of Google Cloud, choose one of the following options:
- If you use a partner solution to manage your keys, use Cloud External Key Manager.
- If you manage your keys on-premises and if you want to use those keys to encrypt the data on Google Cloud, import those keys into Cloud KMS either as KMS keys or Hardware Key Module (HSM) keys. Use those keys to encrypt your data on Google Cloud.
Database design and scaling
This section provides best practices for designing and scaling a database to support your system.
Use monitoring metrics to assess scaling needs
Use metrics from existing monitoring tools and environments to establish a baseline understanding of database size and scaling requirements—for example, right-sizing and designing scaling strategies for your database instance.
For new database designs, determine scaling numbers based on expected load and traffic patterns from the serving application. For more information, see Monitoring Cloud SQL instances, Monitoring with Cloud Monitoring, and Monitoring an instance.
Networking and access
This section provides best practices for managing networking and access to support your system.
Run databases inside a private network
Run your databases inside your private network and grant restricted access only from the clients who need to interact with the database. You can create Cloud SQL instances inside a VPC. Google Cloud also provides VPC Service Controls for Cloud SQL, Spanner, and Bigtable databases to ensure that access to these resources is restricted only to clients within authorized VPC networks.
Grant minimum privileges to users
Identity and Access Management (IAM) controls access to Google Cloud services, including database services. To minimize the risk of unauthorized access, grant the least number of privileges to your users. For application-level access to your databases, use service accounts with the least number of privileges.
Automation and right-sizing
This section provides best practices for defining automation and right-sizing to support your system.
Define database instances as code
One of the benefits of migrating to Google Cloud is the ability to automate your infrastructure and other aspects of your workload like compute and database layers. Google Deployment Manager and third-party tools like Terraform Cloud let you define your database instances as code, which lets you apply a consistent and repeatable approach to creating and updating your databases.
Use Liquibase to version control your database
Google database services like Cloud SQL and Spanner support Liquibase, an open source version control tool for databases. Liquibase helps you to track your database schema changes, roll back schema changes, and perform repeatable migrations.
Test and tune your database to support scaling
Perform load tests on your database instance and tune it based on the test results to meet your application's requirements. Determine the initial scale of your database by load testing key performance indicators (KPI) or by using monitoring KPIs derived from your current database.
When you create database instances, start with a size that is based on the testing results or historical monitoring metrics. Test your database instances with the expected load in the cloud. Then fine-tune the instances until you get the desired results for the expected load on your database instances.
Choose the right database for your scaling requirements
Scaling databases is different from scaling compute layer components. Databases have state; when one instance of your database isn't able to handle the load, consider the appropriate strategy to scale your database instances. Scaling strategies vary depending on the database type.
Use the following table to learn about Google products that address scaling use cases.
| Use case | Recommended product | Description |
|---|---|---|
| Horizontally scale your database instance by adding nodes to your database when you need to scale up the serving capacity and storage. | Spanner | A relational database that's built for the cloud. |
| Add nodes to scale your database. | Bigtable | Fully managed NoSQL big data database service. |
| Automatically handle database scaling. | Firestore | Flexible, scalable database for mobile, web, and server development. |
| To serve more queries, vertically scale up Cloud SQL database instances to give them more compute and memory capacity. In Cloud SQL, the storage layer is decoupled from the database instance. You can choose to scale your storage layer automatically whenever it approaches capacity. | Cloud SQL | Fully managed database service that helps you set up, maintain, manage, and administer your relational databases on Google Cloud. |
Operations
This section provides best practices for operations to support your system.
Use Cloud Monitoring to monitor and set up alerts for your database
Use Cloud Monitoring to monitor your database instances and set up alerts to notify appropriate teams of events. For information about efficient alerting best practices, see Build efficient alerts.
All databases that are built for the cloud provide logging and monitoring metrics. Each service provides a dashboard to visualize logging and monitoring metrics. The monitoring metrics for all services integrate with Google Cloud Observability. Spanner provides query introspection tools like the Key Visualizer for debugging and root cause analysis. The Key Visualizer provides the following capabilities:
- Helps you analyze Spanner usage patterns by generating visual reports for your databases. The reports display usage patterns by ranges of rows over time.
- Provides insights into usage patterns at scale.
Bigtable also provides a Key Visualizer diagnostic tool that helps you to analyze Bigtable instance usage patterns.
Licensing
This section provides best practices for licensing to support your system.
Choose between on-demand licenses and existing licenses
If you use Cloud SQL for SQL Server, bringing your own licenses isn't supported; your licensing costs are based on per-core hour usage.
If you want to use existing Cloud SQL for SQL Server licenses, consider running Cloud SQL for SQL Server on Compute VMs. For more information, see Microsoft licenses and Choosing between on-demand licenses and bringing existing licenses.
If you use Oracle and if you're migrating to the Bare Metal Solution for Oracle, you can bring your own licenses. For more information, see Plan for Bare Metal Solution.
Migration timelines, methodology, and toolsets
This section provides best practices for planning and supporting your database migration to support your system.
Determine database modernization readiness
Assess whether your organization is ready to modernize your databases and use databases that are built for the cloud.
Consider database modernization when you plan workload migration timelines, because modernization is likely to impact your application side.
Involve relevant stakeholders in migration planning
To migrate a database, you complete the following tasks:
- Set up the target databases.
- Convert the schema.
- Set up data replication between the source and target database.
- Debug issues as they arise during the migration.
- Establish network connectivity between the application layer and the database.
- Implement target database security.
- Ensure that the applications connect to the target databases.
These tasks often require different skill sets and multiple teams collaborate across your organization to complete the migration. When you plan the migration, include stakeholders from all teams, such as app developers, database administrators, and infrastructure and security teams.
If your team lacks skills to support this type of migration, Google's partners can help you perform your migrations. For more information, see Google Cloud Partner Advantage.
Identify tool sets for homogeneous and heterogeneous migrations
A homogeneous migration is a database migration between the source and target databases of the same database technology. A heterogeneous migration is a migration whose target database is different from the source database.
Heterogeneous migrations usually involve additional steps of schema conversion from the source database to the target database engine type. Your database teams need to assess the challenges involved in the schema conversion, because they depend on the complexity of the source database schema.
Test and validate each step in data migration
Data migrations involve multiple steps. To minimize migration errors, test and validate each step in the migration before moving to the next step. The following factors drive the migration process:
- Whether the migration is homogeneous or heterogeneous.
- What type of tools and skill sets you have to perform the migration.
- For heterogeneous migrations, your experience with the target database engine.
Determine continuous data replication requirements
Create a plan to migrate the data initially and then continuously replicate the data from the source to the target database. Continue replication until the target is stabilized and the application is completely migrated to the new database. This plan helps you to identify potential downtime during the database switch and plan accordingly.
If you plan to migrate database engines from Cloud SQL, Cloud SQL for MySQL, or Cloud SQL for PostgreSQL, use Database Migration Service to automate this process in a fully managed way. For information about third-party tools that support other types of migrations, see Cloud Marketplace.
Recommendations
To apply the guidance in the Architecture Framework to your own environment, we recommend that you do the following:
Multi-tenancy for databases involves storing data from multiple customers on a shared piece of infrastructure, in this case a database. If you offer a software-as-a-service (SaaS) based offering to your customers, make sure that you understand how you can logically isolate datasets that belong to different customers, and support their access requirements. Also, evaluate your requirements based on levels of separation.
For relational databases such as Spanner and Cloud SQL, there are multiple approaches, such as isolating tenants' data at the database-instance level, database level, schema level, or the database-table level. Like other design decisions, there is a tradeoff between the degree of isolation and other factors such as cost and performance. IAM policies control access to your database instances.
Choose the right database for your data model requirements.
Choose key values to avoid key hotspotting. A hotspot is a location within a table that receives many more access requests than other locations. For more information about hotspots, see Schema design best practices.
Shard your database instance whenever possible.
Use connection-management best practices, such as connection pooling and exponential backoff.
Avoid very large transactions.
Design and test your application's response to maintenance updates on databases.
Secure and isolate connections to your database.
Size your database and growth expectations to ensure that the database supports your requirements.
Test your HA and DR failover strategies.
Perform backups and restore as well as exports and imports so that you're familiar with the process.
Cloud SQL recommendations
- Use private IP address networking (VPC). For additional security, consider
the following:
- Use Cloud SQL Auth proxy to support private networking.
- Restrict public IP address access
constraints/sql.restrictPublicIp.
- If you need public IP address networking, consider the following:
- Use the built-in firewall with a limited or narrow IP address list and ensure that Cloud SQL instances require that incoming connections use SSL. For more information, see Configuring SSL/TLS certificates.
- For additional security, consider the following:
- Don't grant general access; instead, use Cloud SQL Auth proxy.
- Restrict authorized networks
constraints/sql.restrictAuthorizedNetworks.
- Use limited privileges for database users.
What's next
Learn data analytics best practices, including the following:
- Learn core data analytics principles and key Google Cloud services.
- Learn about the data lifecycle.
- Learn how to ingest data.
- Choose and manage data storage.
- Process and transform data.
Explore other categories in the Architecture Framework such as reliability, operational excellence, and security, privacy, and compliance.