Design Overview of Big SQL HA


The Big SQL High Availability ( Big SQL HA ) solution provides high availability for the meta data of the BIGSQL Database ( Big SQL Metastore ) and for the Big SQL Scheduler component. The BIGSQL Database is the database that is used by Big SQL applications to access Hadoop tables.  High Availability for the Hadoop Name Node and for the Hive catalog is not part of this solution.


The Big SQL HA solution became Generally Available in version of Big SQL and this document relates to that version specifically.


At its core, the Big SQL HA solution uses two key enabling technologies: DB2 High Availability for Disaster Recovery ( DB2 HADR ) and Tivoli System Automation for Multiplatforms ( TSAMP ).

DB2 HADR is used to ship the transaction logs of the main Big SQL database to a live standby system ( The Secondary Head ). The DB2 HADR components on the Secondary Head constantly replays the transaction logs shipped to it from the Primary Head in order to keep a real-time and up-to-date copy of all the catalog information of the Big SQL Database on the Primary Head node.


TSAMP is used to create a complete resource model for the Big SQL resources on the Primary Head and on the Secondary Head.  This resource model contains resource groups for the Big SQL processes related to the Big SQL database and for the Big SQL Scheduler.  The processes that are part of these resource groups are constantly monitored by TSAMP.  TSAMP has the ability to start and stop any of these processes.  TSAMP ensures that these resources are highly available.  This means that if a process is killed for any reason it will be restarted automatically.  If the Primary Head is no longer available for any reason ( a power outage for example ), then the Secondary Head will assume the role of the Primary Head.  All client applications currently connected to the Primary Head and are set up to take advantage of Automatic Client Reroute ( ACR ) will be automatically re-routed to the Secondary Head.  This event is called a failover.  Once a failover takes place, the original Secondary Head will continue to act as the Primary Head and the original Primary Head will act as the Secondary Head.  If the acting Primary ( original Secondary ) is rebooted for example, the acting Secondary ( original Primary ) will re-assume the role of the Primary Head and the acting Primary ( original Secondary ) will re-assume the role of the Secondary Head.  This event is called a failback.


The Big SQL Scheduler is running on both the Primary Head and the Secondary head at all times, but only the Scheduler running on the Primary head is actively participating in processing user queries. The other instance of the Big SQL Scheduler running on the Secondary Head is not actively participating in the processing of user queries but is only running in a standby mode.  When a failover or failback occurs, the Scheduler instance on the new Primary becomes the active instance and the other instance continues running in standby mode.


The worker nodes in the Big SQL cluster are simply processing units for the user workload. In the event of a failover or failback, worker nodes automatically detect the failure of the “old” primary head node, are able to determine its “new” location and, automatically re-connect to the “new” head node all while user applications are failing over.  Please note that Hadoop data sits on HDFS (or GPFS) and, as such, it is already highly available via native and implicit replication on HDFS.  As a result, if a worker node goes down, the data can be accessed via any another available worker node that has a replica of the data.


Only the Primary Head can communicate with the worker nodes for processing queries. The Secondary Head is not allowed to communicate with the workers.  The Secondary Head’s only role is to constantly replay the transaction logs sent to it from the Primary Head and be ready to take over the role of the Primary during a failover.  When a failover occurs, the worker nodes are informed by the new Primary Head that it is now the acting Primary and communication starts occurring between the new Primary Head and the workers.  All communication between the old Primary and the worker nodes is terminated (this is to avoid potential “split-brain” scenarios).


Before Enabling High Availability (HA)


The user should ensure that the package “net-tools” is installed on the primary and secondary nodes as utilities in that package are needed by the enableha process. To check whether this exists simply execute in any terminal on each machine the following command:

which ifconfig

If this is not found you can use yum to install this package as root user:

yum install net-tools


Big SQL High Availability (HA) Install Process in Ambari


HA for Big SQL is enabled via the Secondary Head component in Ambari.  When the component is first installed, HA is in an inactive state.  To activate it, the user must select the “EnableHA” option in the Big SQL service actions:




In order to enable HA, the Secondary Head component must be located on a dedicated host that doesn’t contain the head node or any worker nodes. If the user needs to enable HA and the Secondary Head component is located on a host that contains a worker node or the Head node, then it must be re-located to be on its own dedicated host.  To relocate the component to its own host, you can delete it through Ambari’s hosts view (the component must be stopped to do this) and then add it to a different host.


As a part of the process to Enable HA, the user will be logged out of Ambari. When he/she logs back in, a message saying “Big SQL HA has been set up successfully on Secondary host” will be displayed in the operation output, indicating a successful setup.


The Big SQL HA Functional Interface in Ambari


Just like the head and worker nodes, the Secondary Head node can be started and stopped through Ambari. When you stop the component, you will no longer have high availability for the Big SQL database metastore. When you re-start it, the Secondary Head node will start replicating logs once again and will eventually catch up with any logs missed while it was deactivated.  The Big SQL database metastore will once again be in HA mode.


Ambari will display a status message for the Secondary head node. If you see “Started”, that means that Big SQL database metastore is highly available. If you see “Stopped”, it means that your Secondary Head node is not running, or that it is unable to communicate with the active Primary Head node.


In the case of a Primary Head node failure, the Secondary Head node will take over, and become the new “acting” Big SQL metastore. Once the failed node is working again, it will be working as a standby database. This will be reflected when you try to start or stop either head node, with an informational message saying “Failover in effect!”




User-Triggered Failover or Failback


The user can trigger a failover or failback for maintenance activities. This can be done via running the following command as the instance user ( bigsql ) : -U <instance-user>


Disabling Big SQL HA


You can disable High Availability for the Big SQL by choosing “DisableHA” in the service actions menu. This will return the Secondary Head component to an inactive state.




If you are in a failover state, and attempt to disable HA, you will see an error message asking you to run the following command: -U <instance-user>

on the active Head Node. This command will trigger failback so that the original Primary Head is the currently active Primary.  Note that both the Primary Head and the Secondary Head must be running before running the above command.

Upon successful completion of this command then re-run the “DisableHA” option once again.


Example Big SQL HA Usage Scenario


  1. Install and configure a Big SQL cluster in Ambari
  2. Enable High Availability
    1. Ensure that the Secondary Head component is on its own dedicated host.  A dedicated host means there is no Big SQL Head node or Worker node on this host.
    2. If it isn’t, delete it from the current host and then add it to a new one
    3. On the Big SQL page, choose “EnableHA” in the Service Actions menu
  3. Outage on the primary head node – Secondary Head takes over Primary role.  Old Primary Head node comes back and begins working as the Secondary Head.
  4. Disable High Availability
      1. Choose the “DisableHA” option in Big SQL service actions.
      2. If still in a failover, you will get an error message with instructions to trigger a failback.
      3. Follow the instructions to trigger a failback by running the script.
      4. Once failback has completed, re-try disabling HA.


Configuring Clients to Work with High Availability in Big SQL


To configure clients to automatically reroute to the new primary Head node in the case of a failover there are some settings that are recommended on the client side. In this case we are using an IBM JDBC client to connect to our primary Head node. The primary Head node and secondary already configure a value for alternate server that is sent to the client in case of failover. This helps applications that are already connected to try to connect to the new primary in case of a failover while the application is running. However for applications that are establishing a new connection after failover has occurred, client reroute values must be set on the client side.  We recommend the following data source properties in JDBC on the client side when working with High Availability in Big SQL.

Let’s assume we have this primary head node:

and this secondary head node:

Let’s also assume that the Big SQL Server port is 32051 and that this is the same on both the Primary Head and Secondary head.

We will want to configure the client to try to connect to the secondary head node in case a failover has occurred. We use the following data source property to set this on the client

clientRerouteAlternateServerName =

We also need to tell the client which port to try to connect to in the event of an automatic client reroute with the following property:

clientRerouteAlternatePortNumber = 32051 

The client may still throw errors in the event of failover so if you don’t want to see -4498 errors you should set the client to enable a seamless failover.

enableSeamlessFailover = yes

IBM recommends that you use the IBM JDK for all your client connections to Big SQL , however if you have to use the Open JDK for business reasons you will need to also set the following property to force the client to reroute after a set amount of time

blockingReadConnectionTimeout = 1200

You will need to be sure when setting this parameter to align this value to the time your longest-running application SQL statement needs to execute otherwise it will timeout.

Using our example hosts above this is how we would set these values in a jdbc connection URL.



2 comments on"High Availability in Big SQL version"

  1. How would you configure JDBC with Kerberos against a HA cluster?

    We need to give the Kerberos Principal of the target machine but if we don’t know which will in the end serve the request…

Join The Discussion

Your email address will not be published. Required fields are marked *