How do you create a high availability database?

High Availability (HA) and consistency are two important but sometimes conflicting goals in the world of databases. The database system can be available but the data can be inconsistent. Most of the database replication is asynchronous. Choose a HA method based on the data you can afford. Synchronous database systems (clusters) can introduce latency with 2P commit, but they ensure consistency and minimum downtime. In the case of MySQL, it has native Asynchronous / Semi Synchronous Replication / Virtually Synchronous (Group Replication) solutions. (Galera is a good Virtually Synchronous solution too). Most Cloud providers support Native asynchronous replication for MySQL. RDS Aurora is synchronous in nature. Consider Consistency in HA.

Your budget may well be the defining limitation on what platform option that you choose. However there are often additional options here too. Its not just platform that affects HA features, sometimes Edition does too. Platforms that offer standard and premium (often known as Enterprise) editions, will also offer different toolsets to provide HA solutions. Then there is the question of whether you want to non-primary HA nodes to support functionality such as reporting, or remain truly passive? Options such as Microsoft's Software Assurance will allow you to for-go licence costs for a secondary node as long as that node support no business processing.

I would prefer and recommend to use SQL server Always On feature to achieve HA and DR solutions. AOAG can be set up over multi subnet network which is used to replicate data on different Data-centers which can be used for reporting purposes and to offload backups.

The first step towards a well-designed highly available database should start at the requirement phase; by looking at what is required by the associated business processes. It can be the geographical span of users, concurrency, expected response times, etc. in the form of NFRs (Non-Functional Requirements). Also, alignment with the development or product teams to segregate the data that would be frequently changed / added, frequently accessed, archival plan, etc. This can be a great input to identify and plan for OLTP, OLAP, BI, ETL and Reporting requirements. We can then realize a technology stack and a database suite from the same vendor or mix and match from the best available. Example, MSSQL Database, Analysis Services, SSIS, SSRS, etc.

There are a number of different ways to achieve high availability in a database, depending on your specific needs and requirements. Some common approaches include: Case one ( two server )If we only have two servers and use MS SQL server, we can use Mirroring, in which the primary server streams data to the secondary server, which will be the Mirror server in this case. So, if the primary server fails, the secondary server (Mirror server) will come online but manual . Case two (3 servers): If we have more than three servers, we can use Clustering in which two servers communicate with each other via a witnesses server, which monitors the heartbeat of each database server and automatically replaces the secondary server if one of them fails.

The objective is to have maximum availability with minimum downtime for our db system. When utilizing automatic failover, we need to exercise this option with cautious since we do not want any false alarm triggers this event. Always test your backup and restore it once in a while, to make sure that the backup set is valid and to have the approximate restore time when the actual restore takes place. Cloud providers like AWS, offer managed database services with built-in high availability features such as Multi-AZ and replication db. Implementing high availability for a database requires careful planning, testing, and ongoing maintenance to ensure that the system can continue to operate reliably even when faced with unexpected challenges.

Database replication is akin to having multiple bridges to a city: if one fails, traffic reroutes, ensuring continuity. Synchronous replication commits transactions to primary and secondary databases simultaneously, like two bridges built in tandem. Asynchronous replication, however, has a slight delay, like constructing the second bridge after the first. This can introduce a lag but offers geographical flexibility. Modern databases blend both methods in hybrid replication. Using these strategies in distributed environments boosts read performance, balances loads, and ensures data remains accessible and consistent, even amidst challenges.

Choosing a suitable database platform with a focus on implementing replication and backup strategies for high availability involves careful consideration of various factors. Also, database platforms come with built-in replication and backup features. Evaluate the capabilities of your chosen database platform to determine if these meet your high availability requirements. The predominant options available are: - Synchronous vs. Asynchronous Replication - Multi-master & Multi-active replication On the backup front, always adhere to The phrase "a backup that is not tested is not valid" which emphasizes the importance of regularly testing backups to ensure their reliability and effectiveness in the event of data loss or system failure.

Replication and backup strategies can be designed as per desired RPO and RTO.Testing backup strategies should be added so that no last minute execution lapses are encountered.Everything comes with additional overhead and that is why you have different type of database and data model to be considered while implementing such strategies.

The three largest factors to consider here are server power, network latency and storage speed. If you want to minimise potential data loss, then you need synchronous commit. SC means that your transactions will not commit on the primary node until they have hardened on your secondary nodes. This introduces transactional latency, so if any one of those 3 factors slows down for any reason, then your application will start to see performance degradation as a result. Asynchronous commit will avoid that, but at the potential loss of data in a failover as unhardened transactions are lost. A common pattern in clustering is synchronous commit on clustered pairs of servers in the same data centre and asynchronous commit on remote nodes.

MONGODB *Mongodb routes(load balancer) routes the client queries with the right shard reducing IO *Reduces overhead by providing connection pooling and improves query performance *As the data grows helps data distribution uniformly based on shared keys

Load Balancing and clustering are most imp features most of the databases are providing natively. Clustering (HA) is good and helps to have high availability (MAA) , based on the applications needs we can use native database capabilities or home grown tools for this. We can use external load balancers for routing traffic or use SCAN or Routers in the database.

Even though database is configured in a HA pattern it is always better to crosscheck whether Application is designed in a way that it leverages those Features.Also we have to test HA functionality so that user doesn't notices drop connection during node failures.If it is a Cloud based application then it may have auto scaling enabled for catering user load during peak hours.But if it is a non Cloud based application then we have consider how we are going to handle peak hours load balancing.

When discussing load balancing one should always consider a transactional aspect of it. Data integrity is an important matter in some systems, while in others could be omitted. Linear scalability is possible, but there are only a few solutions that allow scalability without affecting the transactional nature of rational databases. I’d mention here pureScale and RAC solutions. And it’s a different world when it comes to analytics databases there are more options here. However, this results from the nature of the workload. Data must be consistent, but there are no “transactions”. So computing power is all that matters, so suitable solutions are cloud and dedicated appliances.

The Confidentiality, Integrity and Availability of data are interrelated. Designing a good redundant quorum is very important to avoid split-brain scenarios which can impact integrity of the data. Also, implementation of proper security best practices and encryption mechanism should align with the confidentiality of the data. Also, in some cases we might require backups to be stored in geographically separated datacenters. Additionally, we need to ensure we have the required fire IDs and stand-by users ready in case of a disaster which can be activated on the go.

The highly available database platform should not only offer robust monitoring and testing capabilities but also integrate seamlessly with your chosen tools and processes. Regular monitoring and testing, supported by a well-established framework, ensure that your database remains resilient and performs optimally under varying conditions. Apart from generic features as stated above, I'd like to see: - Simulated Failover Testing - Historical Analysis and Security Monitoring - Compatibility with Monitoring Tools like AWS CloudWatch and it's equivalent services - Scalability and Performance Metrics

An amazing colleague, Erik Darling, once said that a disaster recovery strategy is just a plan until it's tested and proven. You have to test in order to actually be able to say you can support HA/DR failures and failovers.

If one considers monitoring the top 5, 10, and 50 slowest queries is enough then one is truly mistaken. That’s a good eyecatcher, but it's just the beginning. I witnessed a situation when one of the database engine processes (due to a defect) consumed almost all available resources and still, the primary focus was on the top slowest queries. Sanity check. I’d look at monitoring as part of a wider process that needs to be established. Learning as we go and from the incidents we experienced. Any situation should be reviewed and if possible, added to monitoring.

The most important part at this stage is to document the entire setup and the potential disaster scenarios along with the corresponding procedures of recovery. Each scenario should be tested and the RTO and RPO noted. If this does not match with the NFR, tune the setup and process and re-test until we are within the required RTO and RPO. Again, perform benchmarking and agree on the BAU performance figures and the minimal performance figures of a recovered scenario (if different to optimize cost). Finally, the relevant documentation should be placed in a RED file with the other DR documents as part of the Business Continuity Plan (BCP) and also a soft copy in the BCP common location on a SharePoint or Documentum readily available online.

Always consider testing your DR or HA solutions because from each 10 companies you'll find 1 company that faced a service intruption for days due to invalid backup or invalid replica, DBA should have a monthly testing for both solutions, for the backup you can perform a random restore on test server so you can confirm the reliability of your backup files, for the replication you can test doing a failover in the monthly maintainance time window, you can see these actions are boring but I believe it mattars.

Oracle's RAC and ADG will help you achieve HA. Its very effective but you will have to pay for it. Your database should be enterprise edition. You can also consider subscribing to oracle cloud offering such as Exacc and autonomous database.

Jack of all trades is a master of none. DevOps – no problem, but keep in mind there must be some people focused on database skills. The best candidates to play that role are legacy DBAs.

The multi-master (active-active) design of pgEdge inherently ensures exceptionally high availability in a straightforward manner. In the event of one or more nodes going offline, traffic seamlessly shifts to the remaining active nodes without any delay or loss of transactions. Unlike scenarios where previously passive nodes deliberate to select a new active node, pgEdge handles failover instantly. Disaster recovery and failover are seamlessly integrated into pgEdge, simplifying the architectural planning and deployment process. For critical applications, the inability to provide service due to a cloud provider's region being inaccessible is unacceptable. Moreover, the standout feature is zero downtime during maintenance activities!

Always test "fsync" - in many cloud setups remote disks only report that they have successfully saved everything onto storage media, and in reality data is saved by timer no matter what filesystem commands where issued to operating system. In reality this means that you can loose say 49ms of data, that was reported as "saved" by database and operating system.

I would like to re-wind a little bit after all the important information already given: Before even starting you really need to know what HA actually means, what the actual requirements are in your case. What's the needed availability? 98%, 99%, 99.9%, 99.99% and so on. What kind of redundancy do you need or can you afford? 2 data centers, multiple geographical locations, private cloud or public cloud? As already mentioned, how quickly do you need to be up again after a disaster? After requirements you need to check what you already have: what kind of technology do you already have in your eco-system? And finally, does the information in your HA database need to match other content, which is stored somewhere else?