Scenario:

Attempting to run queries against a SQL database with a 100% availability SLA.

Issue:

Possible table locks caused by competing calls from third party and native SQL queries. Which may negatively impact SLA

Solution:

Create a secondary SQL database and sync it to the primary. Then convert the secondary database to a read-only database. Finally configure the SQL databases to run in a disaster recovery mode, meaning that the secondary listens to the primary's heartbeat and switches over to primary role when it detects the primary server is down.

Requirements:

  1. The SQL servers will run on JDBC 4.0 or higher to leverage the listener feature to allow the disaster recovery architecture
  2. The third party software running queries against the SQL database should support ALWAYS-ON connection
  3. The third party software should also have JDBC 4.0 or higher running

References:

https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-driver-support-for-high-availability-disaster-recovery?view=sql-server-ver15