How can you manage and monitor databases online?

In my experience, online database management and monitoring play a crucial role. These tools allow you to monitor databases, receive alerts, and take necessary actions. As a Database Administrator (DBA), it’s essential to create alerts selectively, focusing on critical issues that demand immediate attention. Monitoring tools often come with predefined alert packages and default thresholds, but as DBAs, we must customize them based on the specific environment and client requirements. Sometimes, what seems unimportant to us might be crucial for the client.

This is a problem all DBAs have. Creating proper alerting and monitoring is key, and no SMS doesn't do enough out of the box. That's why we developed our own answer to this problem to run our Managed Services clients. Automated alerting, custom alerting, automating maintenance and backups allows us to be both proactive and reactive, and cuts our workload by 80%!

On this, I'd simply say that no matter which cloud provider hosts your database, the best approach in monitoring and foreseeing bottlenecks and doing efficient defensive performance tuning is to use platform-managed monitoring dashboards, with alarms set on your key performance metrics. e.g. AWS-managed Grafana Dashboard, AWS, X-Rays. I hope this helps someone.

- Great amount of monitoring tools can query database internal state and telemetry data through the same port as data connection, this gives you flexibility and allows use same tools no matter where database is hosted. - You have better choices of different tradeoffs in cloud environment compared to on-prem database management (ie you can opt for more management and pay more, or alternatively, can use cloud virtual machines to pay less, and manage more, while still having benefit of being able to scale hardware when needed).

While online tools offer convenience, they also introduce a layer of complexity regarding performance diagnostics. Network factors external to the database can influence performance metrics, potentially leading to misdiagnosis of issues. DBAs must be adept at distinguishing between genuine database performance issues and those stemming from network or cloud service provider problems.

In reality, main principles and challenges are same no matter where database is stored: - I do hope that you have no "trusted perimeter" in 2023 and beyond, and always authenticate everything, preferring short-lived tokens to do so. - Datum that your customers entrust you in a way is similar to nuclear waste — you want to never touch it directly, you don't want to move it across different locations or vendors, and yet your customers need it preserved reliablly and securely - there is trade-off between availability (say country-wide power outage or disaster) and latency (if you really want to have all data securely saved in different continents - your customer will need to wait few data round-trips across ocean on each click)

Online tools for relational databases often come with rich visualization features that can simplify complex database schemas and relationships. This can be particularly beneficial for new DBAs or those managing large-scale databases, as it provides a more intuitive understanding of the database structure and data flow, which is essential for effective management and optimization.

This paragraph was mostly "miss" by AI that generated article on "monitoring". 1) SQLite is more like replacement for your local "configuration file", not as online database for multiple users. 2) AI mixed database hosting platforms and tools to monitor them. 3) While major cloud vendors indeed offer managed SQL services (which offer great monitoring out of box) (Google Cloud SQL for example even does emit SQL query plan steps as opentelemetry distributed trace spans) you almost never can use these to monitor your database that is hosted somewhere else, even in virtual machine hosted in same cloud vendor.

The heterogeneity of NoSQL databases means that online management tools must be versatile and adaptable to various data models. This diversity requires DBAs to have a broad skill set and deep understanding of different data structures and query languages to leverage these tools effectively for database optimization and problem resolution.

One of the critical best practices for online database management is the implementation of a robust disaster recovery plan. Online tools often provide streamlined backup and recovery processes, but DBAs must ensure these are configured correctly and tested regularly. The ability to quickly restore operations with minimal data loss is vital for maintaining trust and ensuring business continuity in the event of a failure.

- always consider your customer needs, and any policies that may constrain your vendor choice. (government may need to keep data in country, bigger customer will request that they control their encryption keys for data etc) - in most cases, adding new vendor will be more complicated from security and compliance perspective than using database functionality if offered by existing vendors. - at any point in time, any of your customer may request you to stop processing (and controlling) their datum, your whole vendors chain, including data backups and database monitoring, must be able to handle such request.

A Database Management System (DBMS) selection represents a milestone in an effective database that suits your needs, the popular options include MySQL, PostgreSQL, Microsoft SQL Server, and cloud-based solutions like Amazon RDS or Google Cloud SQL. Set up hosting is needed when you choose a DBMS, this could be in your own servers and Plan and design your database schema which includes defining tables, relationships, and data types. Proper design is critical for efficient data management.

Think about managing DB’s at scale in every design aspects . Whatever tool we use / build can manage 1 db or 1 million db . It should operate and work . Build your own CLI . Every db comes with a library and 99.99% activities we do on managing data base or data store is same . So spending time and developing an extendable CLI will help in longterm . I was part of team that managed 300k DB’s . We find a problem , then solve it . Add the learning to CLI ( detect , remediate etc ) . So next time the problem comes other team members can easily adapt and use .

Equally important topic is managing your database "offline" state. - Do you always know why it is offline? - Do you know which replica is having latest data if primary node is gone and not known if it comes back or not? - How do you avoid split-brain scenario where you did promote one of replicas to be new primary instance, and then old primary node actually comes back online? - What if your cloud vendor reports database as "UP" but you have no way to connect to it - what is your debug toolkit for port access in cloud environment?