Cloud computing has revolutionized the way data is stored, accessed, and processed. But how do you design a cloud-based database that meets your needs and goals? In this article, you will learn some basic principles and steps to create a reliable, scalable, and secure cloud database.
The first step is to select a cloud service provider (CSP) that offers the features, performance, and pricing that suit your project. There are many CSPs to choose from, such as Amazon Web Services (AWS), Microsoft Azure, Google Cloud Platform (GCP), and others. Each CSP has its own advantages and disadvantages, so you need to compare them based on your requirements and preferences. Some factors to consider are the availability, reliability, scalability, security, and compatibility of the cloud services.
The best way to choose a cloud provider for your cloud based database, is to check which services are required for your business needs. Check your budget and choose suitable services that will save money while delivering without any issues.
I was part of the Award winning team at UTSA (Principle Oracle DBA). Our strat that wiped out 10 years of technical debt overnight, and stringently tested by FEMA(very secure) involved a simple method. "Follow the instructions". By using all the defaults, we discovered that database software evolution (Oracle EE extreme edition) is so advanced already, just making sure you follow their recommended guidelines (but do it comprehensively. Turn on encryption between monitoring and DBs, etc.) you can achieve good results. Sometimes overthinking by less trained and definitely overworked on premise engineers(on-premise cloud) could lead to mistakes, etc. So having this concept as a starting point should at least be helpful.
Firstly, categorize the application for OLTP and OLAP. Secondly, collect essential metadata such a IOPS, data type, number of concurrent session, etc Atlast choose some Multi cloud strategy with respect to SLA and budget.
First you need to analyze your system requirements and best database tool then decide to choose cloud services provider along with your budget
Here is my thought folks ?? 1. Requirements Gathering: Understand the data size, workload type (OLAP, OLTP), and performance needs. 2. Choose the Right Service: Decide between managed databases like Azure SQL Database or more customizable solutions like SQL Server on Azure VMs. 3. Design for High Availability: Use redundancy features like geo-replication and failover groups. 4. Security: Implement encryption, firewall rules, and ensure compliance with standards. 6. Backup and Disaster Recovery: Set up automated backups and test recovery processes. 7. Cost Management: Monitor and adjust resources to control costs without compromising performance.
The next step is to choose a database model that matches your data structure, query patterns, and application logic. There are two main types of database models: relational and non-relational. Relational databases store data in tables with predefined schemas and support SQL queries. Non-relational databases store data in various formats, such as key-value pairs, documents, graphs, or columns, and support flexible schemas and diverse queries. Depending on your data complexity, consistency, and volume, you may opt for a relational or a non-relational database, or a hybrid of both.
Choose the Right Database Type: Select the appropriate type of database based on your data model and use cases. Common options include: Relational Databases: Suitable for structured data and complex queries. NoSQL Databases: Ideal for unstructured or semi-structured data and horizontal scaling. In-Memory Databases: Offer high-speed data retrieval for applications with low-latency requirements. Data Warehouses: Designed for analytical and business intelligence workloads. Graph Databases: Specialized for managing and querying connected data.
The latest DB architecture gaining traction in lakes is Medallion Architecture: A design pattern used to logically organize data in a lakehouse, with a goal to incrementally and progressively improving the structure and quality of data as it flows threach layer of architecture (from Bronze—> Silver—> Gold layer tables) Also referred to as multi-hop architectures
The best way we can't change our business logics although we make changes some little level where its requirement of Cloud services . As DBA we make a strong analysis parallel all CSP according to our business needs , data security ,recovery ,cost and support level ,.
Actually, you need to do that before you select cloud vendor :) otherwise you will be heavilly limited by it's offerings. - This step is there you consider what will be your data needs and workloads. Will you have more reads or more writes? Will transactions mostly be very short, or we need to do some complex analytics? What will be later done with datum you gather? - Be sure to consider some non-standard options, like Google Cloud Spanner which is relational database with strong consistency, but with awesome horizontal scaling capabilities. - Some modern databases like Google BigQuery have ability to do ML natively inside datastore, or use AI to allow business people to ask questions about data in plain English.
Start with an analysis of your data type, volume, and use cases. Assess the specific requirements and constraints of your application, including performance, scalability, and security. Consider relational databases for structured data and strong consistency needs. Opt for non-relational databases for flexible schemas, diverse data types, and scalability. Assess how fast the data needs to be accessed and processed, and the volume of data to be handled. Some databases are optimized for read-heavy operations, while others balance read and write operations effectively.
The third step is to design a database schema that defines the structure, relationships, and constraints of your data. A database schema helps you organize your data logically and efficiently, and ensures data integrity and quality. Depending on your database model, you may need to follow different design principles and techniques. For example, if you use a relational database, you may need to apply normalization rules to reduce data redundancy and anomalies. If you use a non-relational database, you may need to optimize your schema for performance and scalability.
Choose a primary key that is unique, short, and well-distributed to prevent hotspots and improve performance. Swap the order of keys or use interleaved tables to optimize for common queries that use prefixes or ranges of keys.
Construct a database schema aligning with the selected model, focusing on efficient data organization, integrity, and quality. For relational databases, utilize normalization to minimize redundancy. In contrast, non-relational databases prioritize performance and scalability, often requiring a tailored approach to optimize data retrieval and storage. Always ensure the schema supports future adjustments to accommodate evolving data and application needs.
These steps are exactly same for database that is hosted on-prem, in the cloud or in neighbours basement. - There is no rules, all decisions will be trade-offs between query latency and total database size, with some security related addiitons. - Be sure you can adapt to changing requirements. - Be sure the data you store is in format you (later) expect it to be and database can ensure that.
The fourth step is to implement a database security strategy that protects your data from unauthorized access, modification, or deletion. Database security involves multiple layers and aspects, such as encryption, authentication, authorization, auditing, backup, and recovery. You need to follow the best practices and standards of your CSP and your database model to secure your data at rest and in transit. You also need to monitor and update your database security regularly to prevent or detect any threats or breaches.
The security strategy must never be overlooked. Its should also be designed to fit thr organisation's security strategy. RBAC Configuration , Firewall, IP whitelisting, network segregation are some approaches to ensure hardening.
Implement robust data security measures, including encryption at rest and in transit, authentication, authorization, and role-based access control (RBAC). Leverage cloud provider security services and features.
Things to consider include: - row level security, different RBAC roles can see different results for same query - your security and audit logs should be immutable, but you need to also respect users right to be forgotten - prefer using short-living tokens for authentication, not long-lived passwords. - (my favourite): even if for some reason your internet facing application decides to execute "TRUNCATE TABLE users CASCADE;" it should have no right to do so, and no way to obtain credentials that allow it to do so. (yes you will need separate service and credentials to do schema changes)
The fifth step is to test and optimize your database performance to ensure that it meets your expectations and requirements. Database performance depends on various factors, such as the size, complexity, and frequency of your data and queries, the configuration and resources of your cloud service, and the network latency and bandwidth. You need to measure and analyze your database performance using various tools and metrics, such as throughput, latency, availability, and error rate. You also need to identify and resolve any performance issues or bottlenecks using various methods, such as indexing, caching, partitioning, or scaling.
Performance Optimization: Optimize database performance through query optimization, indexing, caching, and the use of read replicas or caches. Monitor database performance using cloud provider tools.
This could be tricky as in real life scenarios, it could be almost impossible to replicate prod and test data and as such the test might not sufficiently cover major use cases.
The sixth step is to monitor and maintain your database to keep it running smoothly and reliably. Database monitoring and maintenance involve checking and reporting the status, health, and performance of your database, as well as performing any necessary actions or changes to improve or fix your database. You need to use the tools and features provided by your CSP and your database model to monitor and maintain your database. You also need to follow a regular schedule and plan for database maintenance tasks, such as backup, recovery, update, or migration.
In my previous job, I managed the monitoring and maintenance of a critical company database hosted on a cloud infrastructure. We used the monitoring tools provided by the cloud service provider to promptly detect issues and implemented a regular schedule for maintenance tasks such as backups and updates. Continuous monitoring allowed us to swiftly address an unusual increase in workload and maintain operational continuity. Overall, monitoring and maintenance ensured the stability and reliability of the database, contributing to the success of the business.
Few more aspects to consider would be selection of best suited service plan amongst the various plans provided by cloud service provider. Support SLAs play a major role when application and database have performance or availability issues. Cost balance to feature comparison is needed to take the final call.
Important aspect of cloud databases is "vendor lock-in". You should not give up following fundamental rights: - mirroring all your data in realtime from your cloud vendor should be possible at least in some way, without distrupting clients - you should be able to migrate all data via "offline" data transfer process, in "database native format" (say PostgreSQL pg_dump) and then have similar/exactly same database system running on-prem or at different vendor.