The process of setting up an HADR environment is straightforward. After ensuring the systems you intend to use as the primary and standby servers are identical and that a TCP/IP connection exists between them, you simply perform the following tasks in order:
1. Determine the host name, host IP address, and the service name or port number for both the primary and the standby database servers.
2. Create the principal standby and auxiliary standby databases by restoring a backup image or initializing a split mirror copy of the database that is to serve as the primary database.
3. Set the HADR configuration parameters on both the primary, principal standby, and auxiliary standby databases.
4. After the standby databases have been created, you must set the HADR configuration parameters which are shown in the HADR-specific parameters section.
The following diagram shows the HADR setup, where the host, HADR1, is the primary
database server, host HADR2 is the principal standby, and hosts HADR3 and HADR4 are
auxiliary standbys. This setup also shows virtual IP configuration and automatic
management through Tivoli System Automation for Multi Platforms (TSAMP). This
setup is only possible between the primary and principal standby with SYNC or
NEARSYNC data synchronization mode:
Start configuring the standby databases and then the primary database by using the
following commands. On the principal standby, the HADR_TARGET_LIST command’s first entry is always the primary server and associated port number:
UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST HADR2.ibm.com HADR_LOCAL_SVC 55002 HADR_REMOTE_HOST HADR1.ibm.com HADR_REMOTE_SVC 55001 HADR_REMOTE_INST db2inst1 HADR_TIMEOUT 120 HADR_SYNCMODE NEARSYNC HADR_PEER_WINDOW 0 HADR_TARGET_LIST HADR1.ibm.com:55001|HADR3.ibm.com:55003|HADR4.ibm.com:55004;
On the first auxiliary standby, the HADR_TARGET_LIST command’s first entry is always the principal standby, then the primary, and then the other auxiliary standby (if there is one):
UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST HADR3.ibm.com HADR_LOCAL_SVC 55003 HADR_REMOTE_HOST HADR2.ibm.com HADR_REMOTE_SVC 55002 HADR_REMOTE_INST db2inst2 HADR_TIMEOUT 120 HADR_SYNCMODE SUPERASYNC HADR_PEER_WINDOW 0 HADR_TARGET_LIST HADR2.ibm.com:55002|HADR1.ibm.com:55001|HADR4.ibm.com:55004;
UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST HADR4.ibm.com HADR_LOCAL_SVC 55004 HADR_REMOTE_HOST HADR2.ibm.com HADR_REMOTE_SVC 55002 HADR_REMOTE_INST db2inst2 HADR_TIMEOUT 120 HADR_SYNCMODE SUPERASYNC HADR_PEER_WINDOW 0 HADR_TARGET_LIST HADR2.ibm.com:55002|HADR1.ibm.com:55001|HADR3.ibm.com:55003;
On the primary database, the HADR_TARGET_LIST command’s first entry is always the
principal standby and then the auxiliary standbys:
UPDATE DB CFG FOR sample USING HADR_LOCAL_HOST HADR1.ibm.com HADR_LOCAL_SVC 55001 HADR_REMOTE_HOST HADR2.ibm.com HADR_REMOTE_SVC 55002 HADR_REMOTE_INST db2inst2 HADR_TIMEOUT 120 HADR_SYNCMODE NEARSYNC HADR_PEER_WINDOW 0 HADR_TARGET_LIST HADR2.ibm.com:55002|HADR3.ibm.com:55003|HADR4.ibm.com:55004;
5. Connect to the standby instances and start HADR on the principal and auxiliary standby databases. To start HADR, you must execute the START HADR command as follows:
START HADR ON [DATABASE | DB] [DatabaseAlias] > AS [PRIMARY | SECONDARY]
Let’s explore the following commands from the preceding code:
- DatabaseAlias identifies the alias assigned to the database to start HADR
- UserName identifies the name assigned to the user who is starting HADR
- Password identifies the password of the user who is starting HADR
For example, if you want to start HADR on a database named SAMPLE and indicate that it is to act as a standby database, you could do so by executing a START HADR command that looks as follows:
START HADR ON DATABASE sample AS STANDBY;
6. Connect to the primary instance and start HADR on the primary database. Execute a START HADR command as follows:
START HADR ON DATABASE sample AS PRIMARY;
SUPERASYNC is the only supported synchronization mode for auxiliary HADR standby
databases. You can use the TAKEOVER HADR ON DATABASE command to perform a role switch between primary and standby HADR databases. After a successful role switch, the HADR_STATE command should be PEER. Multiple standby setup is not supported in the pureScale environment. You can update the value of the hadr_target_list parameter online. However, there are restrictions on modification when HADR is active. You cannot change the principal standby of the primary without first stopping HADR on the primary. You cannot remove a standby from the list if it is connected to the primary. To disconnect a standby, deactivate it. Then, you can remove it from the primary’s target list. You cannot dynamically update the hadr_target_list configuration parameter for a standby unless you enabled the HADR reads on the standby feature. You cannot remove the primary database from the target list of a standby if the standby is connected to the primary. The target list must contain IP addresses that are either IPv4 or IPv6, but not a combination of the two. You cannot dynamically update the hadr_target_list configuration parameter in a Db2 pureScale environment.