Most database systems—including MySQL, Oracle, and SQL Server—require external data replication and switchover mechanisms when deployed in a high availability (HA) environment. AIX’s DB2 has its own built-in HA environment, called High Availability Disaster Recovery. HADR is quick to set up and fairly easy to configure. DB2 does offer other replication services, such as log shipping; however, HADR offers a pretty bulletproof approach when switching from the primary to the standby DB2 server, often referred to as failover.
This article isn’t about all the features of HADR, but rather how to set it up, and how to switch from a primary to a backup server and then switch back. Although HADR won’t work for all applications, once you understand the capabilities of HADR, and how simple it is to configure, you’ll be ready to deploy it on an application-by-application basis.
A Sample Environment
The HADR scheme uses a one-to-one replication relationship: one primary database to one standby database. In that configuration, if the primary server suddenly fails, you can specify automatic takeover by the standby DB2 server, which will then act as the primary server. The type of synchronization you’re using and the volume of transactions the DB2 server is processing determine how “synced up” the standby server is at failover time.
HADR isn’t just a disaster recovery (DR) tool: You can use it to fail over database processing to a backup server for routine maintenance activities that otherwise would require application downtime, such as installing AIX PTFs. Before the maintenance begins, you make the standby server the primary server and then redirect the users via DB2’s client reroute utility or through DNS host redirection. When maintenance is completed, you switch the servers back. How easy is that?
Let’s get started with our sample environment. Consider a primary server already running a DB2 instance using TCP/IP as the transport communication method (DB2COMM=tcpip) with the following details:
- Primary host: prime01.test.com
- Instance: zxpdinst
- Database: zxproddb
- DB2 TCP instance service entry: db2c_zxpdinst
- TCP entry in /etc/services: db2c_zxpdinst 60500/tcp
The primary database is configured with LOGRETAIN ON; that is, the logs are retained for recovery, which is a prerequisite for HADR.
The next task is to create an identical (and empty) DB2 instance on the secondary (standby) server. Although the instance name and TCP port number don’t have to be the same, it’s good practice to keep them this way, both for internal consistency and to ensure compatibility with external networking devices, such as firewalls, that might have rules about TCP ports permitted between systems.
Let’s assume the secondary, or standby, server is at a different geographical location than the primary server and has the following details:
- Standby host: stand01.test.com
- Instance: zxpdinst
- DB2 TCP instance service entry: db2c_zxpdinst
- TCP entry in /etc/services: db2c_zxpdinst 60500/tcp
With these preconditions, let’s look at how you get HADR up and running.
Setting Up HADR
The first order of business is to start with identical copies of the DB2 instance in question on both servers. Once these copies are established, HADR replication will keep them identical (within the limits of the connectivity between the two servers).
Log on to the primary host as the instance owner, then take a database dump with all updating applications stopped in order to preserve database consistency:
- $ db2 backup db zxproddb user zxpdinst using **** online to '/opt/dump'
Transfer that dump to the remote standby host, stand01, then restore it as the instance owner, taking care that you do not specify rolling forward. The database needs to be inrollforward pending mode:
- $ restore db ZXPRODDB user zxpdinst using **** from /opt/dump/ taken at 20120616113000 without rolling forward
We’re now ready to configure HADR. HADR requires two TCP ports to communicate with the standby server: one port for the primary and one port for the secondary. To avoid possible interference with DB2’s automatic socket listeners, the port numbers you choose shouldn’t be the next numbers in the sequence that you have for the instance (e.g., the example uses port 60500 for instance queries, so we wouldn’t use 60501 and 60502). In this demonstration, we’ll use the TCP ports 18900 and 18901, which we’ve already confirmed aren’t in use by other services, with the following descriptions in /etc/services: Place both entries in /etc/services on both the primary and standby servers.
- hadr_zxpdinst_p 18900 # primary zxpdinst hadr
- hadr_zxpdinst_s 18901 # secondary zxpdinst hadr
Next, on the primary server, prime01, configure the database for HADR. As the instance owner zxpdinst, set the database configurations shown in Figure 1.
- db2 update db cfg for ZXPRODDB using HADR_LOCAL_HOST prime01.test.com
- db2 update db cfg for ZXPRODDB using HADR_REMOTE_HOST stand01.test.com
- db2 update db cfg for ZXPRODDB using HADR_LOCAL_SVC hadr_zxpdinst_p
- db2 update db cfg for ZXPRODDB using HADR_REMOTE_SVC hadr_zxpdinst_s
- db2 update db cfg for ZXPRODDB using HADR_REMOTE_INST zxpdinst
- db2 update db cfg for ZXPRODDB using HADR_SYNCMODE ASYNC
- db2 update db cfg for ZXPRODDB using HADR_TIMEOUT 120
- db2 update db cfg for ZXPRODDB using INDEXREC RESTART
- db2 update db cfg for ZXPRODDB using HADR_PEER_WINDOW 0
Figure 1: Database Configurations
Then, on the secondary server, stand01, configure HADR as shown in Figure 2.
- db2 update db cfg for ZXPRODDB using HADR_LOCAL_HOST stand01.test.com
- db2 update db cfg for ZXPRODDB using HADR_REMOTE_HOST prime01.test.com
- db2 update db cfg for ZXPRODDB using HADR_LOCAL_SVC hadr_zxpdinst_s
- db2 update db cfg for ZXPRODDB using HADR_REMOTE_SVC hadr_zxpdist_p
- db2 update db cfg for ZXPRODDB using HADR_REMOTE_INST zxpdinst
- db2 update db cfg for ZXPRODDB using HADR_SYNCMODE ASYNC
- db2 update db cfg for ZXPRODDB using HADR_TIMEOUT 120
- db2 update db cfg for ZXPRODDB using INDEXREC RESTART
- db2 update db cfg for ZXPRODDB using HADR_PEER_WINDOW 0
Figure 2: HADR Configuration
Both the primary and secondary entries complement each other. But the HADR_TIMEOUT, INDEXREC, and HADR_SYNCMODE settings need some explaining, because they support different values depending on your operating requirements.
HADR_TIMEOUT determines the amount of time you can go without communication before HADR considers it a permanent failure and causes replication to stop with an error (the example uses 120 seconds). Note that this doesn’t do anything to fail over applications to the standby server; you must do that on the application side using either DB2 rerouting or DNS redirection. However, you don’t want replication to stop as a result of routine network congestion, so take into account busy times on your network before setting this value. Setting HADR_TIMEOUT too low will result in unnecessary replication termination, but setting it too high will prevent you from discovering that replication stopped due to a network outage.
The INDEXREC setting determines how indexes are re-created on a server when it’s officially designated as the new primary server. This happens both in a failover from primary to secondary, and in the reverse situation—failback from secondary to primary. An INDEXREC value of RESTART will rebuild any invalid indexes once the takeover command has completed. Other values include ACCESS, which rebuilds the index upon first access; ACCESS_NO_REDO, which prevents rebuilds on indexes during rollforward; RESTART_NO_REDO, which prevents rebuild on indexes for instant RESTART rebuilds; and SYSTEM, which adopts the default value configured on the system receiving control.
HADR_SYNCMODE specifies the type of synchronization. In this demonstration I’ve chosen ASYNC mode, which means the log writes are considered good to go if any transactions have been written to the primary database, and doesn’t wait for the standby server to acknowledge this. ASYNC trades a slightly reduced protection against transaction loss for a shorter transaction response time than the alternative setting, SYNC, which provides the greatest protection against transaction loss, but at the cost of slower response times. I prefer ASYNC to SYNC because it doesn’t flood the network with replication traffic during routing operations, especially when you’re replicating a number of databases.
In SYNC mode, all log writes are considered successful when written to the primary server and when the standby server has acknowledged that the logs have been written to it. Although this method pretty much guarantees an up-to-date replication of the primary and standby servers, those acknowledgements create a lot of network traffic.
In NEARSYNC mode, writes are considered successful when they’ve been written to the primary server and acknowledgement is received from the standby server that the logs have been received into the buffer (but not necessarily written to disk) of the standby server.
Starting Up HADR
To start HADR, start the standby server first. On the standby server, stand01, issue the following command:
- db2 start hadr on db zxproddb as standby
Then, on the primary server, prime01, issue the following command:
- db2 start hadr on db zxproddb as primary
HADR has now been invoked and transactions will be replicated onto the standby server, stand01.
Executing Takeovers
As I noted earlier, HADR failover isn’t automatic; it must be invoked explicitly. To execute a takeover on the standby server, so that the standby server becomes the primary server, issue the following command:
- db2 takeover hadr on db zxproddb
The standby server, stand01, has now been promoted to the primary server. Note that you can only perform a takeover when the databases are in a peer state. To re-direct user applications to the new primary database, you can use either DB2 alternative routing (not discussed here) or DNS redirection, in which you replace the primary IP address for the DB2 server with the standby IP address. This assumes that the entire server is down—or at least that all services defined by the particular host name are down.
Note that you’re responsible for making sure that failover DNS occurs in a timely manner. You might do this using a script that makes the necessary zone file changes and propagates them. Ensure in advance that the DNS TIME-TO-LIVE value is set to a low number, such as 60 seconds, to prevent local DNS caching from delaying failover.
If the primary database is down, the takeover command won’t work, because a normal takeover needs to communicate with the primary database. In the event all communication with the primary is lost, as is likely in a system failure, you can issue a takeover “by force”:
- db2 takeover hadr on db zxproddb by force
When the primary server (prime01 in the example) is functioning again, you’ll want to initiate failback so that the original server becomes the primary again (as in the initial setup). Issue the following command on server prime01 if the original primary database has gone down and is beyond repair:
- db2 takeover hadr on db zxproddb
Then, on the standby server, stand01, perform a database dump and restore it to the primary server in rollforward pending mode. Change the config HADR setting as discussed earlier, then simply switch roles so that the primary is back where it was.
There will be times when you need to stop HADR; for example, during a scheduled communication outage. Stopping HADR is the reverse of starting it. On the primary server, prime01, issue the following command:
- db2 stop hadr on zxproddb
Then, on the standby server, stand01, issue the following command:
- db2 deactivate db zxproddb
- db2 stop hadr zxproddb
If your primary server continues to update the replicated DB2 instances, you’ll likely need to re-copy a database dump from the primary to the secondary.
Monitoring HADR
You need to monitor HADR both to ensure its ongoing healthy operation and to detect failovers. One AIX-supplied monitoring tool is db2top, which uses DB2 snapshot monitoring APIs to retrieve information about the state of the database system. Like the UNIX “top” command, db2top displays a continuously updating panel of DB2 performance values and has the ability to drill down to more detailed displays for specific performance variables. The tool assumes you have a lot of internal knowledge about DB2, and thus isn’t easy for novice DB2 admins to use.
A somewhat more usable tool is the db2pd utility, which retrieves information directly from the DB2 memory sets rather than via the snapshot APIs. Its output is more readable than db2top, and it explicitly spells out HADR states for all servers. Figure 3 shows sample db2pd output of HADR output on zxproddb using the db2pd utility.
- $ db2pd -db ZXPRODDB -hadr
- Database Partition 0 -- Database ZXPRODDB -- Active -- Up 0 days 13:02:07
- HADR Information:
- Role State SyncMode HeartBeatsMissed LogGapRunAvg (bytes)
- Primary Peer Async 0 486
- ConnectStatus ConnectTime Timeout
- Connected Wed Jun 13 21:35:46 2012 (1339619746) 120
- LocalHost LocalService
- prime01.test.com hadr_zxpdinst_p
- RemoteHost RemoteService RemoteInstance
- stand01.test.com hadr_zxpdinst_s zxpdinst
- PrimaryFile PrimaryPg PrimaryLSN
- S0148949.LOG 5989 0x000001C6324A16
- StandByFile StandByPg StandByLSN
- S0148949.LOG 59890x000001C6324A13F5
Figure 3: Current Primary and Secondary Databases
The information in Figure 3 tells us the current primary and secondary databases, and whether the logs are in sync on the primary and secondary. It also informs us that the primary is in a peer state, meaning it’s in synchronization with the standby server.
Making a Start With HADR
There are a quite a few more configuration settings available for optimizing HADR, but these settings and commands will get you going with HADR experimentation and even initial production deployment.
Courtesy POWER IT Pro.
No comments:
Post a Comment