Wednesday, October 13, 2010

DB2 HADR Setup

In DB2, a graphical setup wizard provides the HADR administrator an efficient and straightforward means of creating and managing an entire HADR infrastructure from scratch

A complete step-by-step guide to installing the entire HADR infrastructure


Setting up an HADR includes the following requirements:

  • HADR is a DB2 optional licensing feature. The full use of HADR is allowed with Enterprise Server Edition (ESE), but you might need more DB2 licenses for the standby server(s). Consult with IBM IM Sales representatives for the details.
  • The operating system on the primary and standby databases should be the same version, including the patches. For a short time, during upgrades, they can be different.
  • A TCP/IP interface must be available between the HADR primary and the standby.
  • The DB2 version and level must be the same on both the primary and the standby systems.
  • The DB2 software for both the primary and the standby database must have the same bit size (32-bit or 64-bit).
  • Buffer pool sizes on the primary and the standby should be the same. Note that if you build the standby database by restoring the database using the backup copy from the primary, the buffer pool sizes are the same, because this information is included in the database backup.
  • The primary and standby databases must have the same database name. This means that they must be in different instances.
  • Table spaces must be identical on the primary and standby databases including:

Table space type

Table space size

Container path

Container size

Container file type

  • The amount of space allocated for log files should also be the same on both the primary and standby databases

Highly recommended parameters

These are some of the highly recommended parameters to set up an HADR:

  • Use identical host computers for the HADR primary and standby database servers. That is, they should be from the same vendor and have the same architecture.
  • Use a high-speed, high-capacity network for the TCP/IP interface.
  • Ensure that the primary and standby database servers have equal amounts of memory.
  • Have identical database (DB) and database management (DBM) configuration parameters

Before starting HADR, the database should be in Archive log mode.

If you work in a high-security environment where people performing DB2 installs and maintenance are not given sesu/sudo root or Windows Administrator authority other than in a special change window, take the necessary steps to request that authority.

If you are running HADR setup from the GUI wizard as the DB2 instance ID for example, this specifically requires that the DB2 instance ID has the authority to update the /etc/services file (%Systemroot%\system32\drivers\etc\services on Windows), unless these ports have already been registered. You might also have to manually update the /etc/hosts (%Systemroot%\system32\drivers\etc\hosts on Windows) file if you do not have a reliable Domain Name System (DNS) in place, but still want to use host names rather than IP addresses.

The two servers you would use in a real world implementation must be connected by a very reliable TCP/IP connection. They must preferably have no firewall between them, or at least with port 523 open for the DB2 Administration Server, the main port for each DB2 instance, and the two ports you set aside for HADR communication must be left open

Depending on your environment, and your choice of HADR SYNCMODE, you might be working with physically separate servers:

  • Over an intranet WAN (recommend ASYNC or NEARSYNC; more for disaster recovery functionality as opposed to a strictly high availability implementation)
  • On a LAN (recommend NEARSYNC)
  • On servers sitting physically next to each other in the same rack (recommend SYNC)

If you want to reduce the number of steps performed inside the HADR GUI wizard as much as possible, in addition to changing from circular to linear/archival logging, you can catalog entries for the remote system on both the servers beforehand:

  • ADMIN TCPIP NODE for the DB2 Admin Server
  • TCPIP NODE for the DB2 instance
  • DATABASE alias for the DB2 database

Commands to catalog remote entries on primary server (Server1)

Ø db2 catalog admin tcpip node Server2 remote Server2 system Server2 [ostype WIN]

Ø db2 catalog tcpip node DB2_SEC remote Server2 server 50000 remote_instance DB2 system Server2 [ostype NT64]

Ø db2 catalog database OWM as OWM_SEC at node Server2

Commands to catalog remote entries on standby server (Server2)

Ø db2 catalog admin tcpip node Server1 remote Server1 system Server1 [ostype WIN]

Ø db2 catalog tcpip node DB2_PRI remote Server1 server 50000 remote_instance DB2 system Server1 [ostype NT64]

Ø db2 catalog database OWM as OWM_PRI at node Server1

The two example instances are both called DB2 on server host names Server1 and Server2, in a non-clustered configuration, with basic TCP/IP ethernet network adapter connectivity. Server1 is initially set up as the HADR primary, and Server2 is initially the HADR standby

Clients can communicate with the DB2 instance through port 50000, and DB2 HADR communicates between the servers through ports 55001 and 55002. The DB2 Admin Server (DAS) GUI interface communicates through port 523

HADR configuration

Start the DB2 Control Center (db2cc), and expand the All Databases tree on the left pane to show your databases.

2. Right-click the database you wish to set up, and click High Availability Disaster Recovery Set Up ...

3. The next screen is an introduction, giving you notes to keep in mind. Click

Next after reading them briefly

4. Confirm the primary database selection . This points out that the database must use linear (non-circular) logging, and must not be in a partitioned environment.

If the database is still using circular logging, click Configure..., and follow the subsequent wizard steps to configure linear/archival logging

5. Identify a standby database: After your database logging is in a state ready to start HADR configuration, and has been deactivated, you can proceed with the next step, this dialog box is displayed irrespective of whether you already have a database sitting on another server. All you need at this stage is to have a DB2 instance running there

The wizard knows whether the database has been successfully configured behind the scenes, and it refreshes the status to allow you to proceed. You might be asked for the DB2 Administration Server user ID and password in order for DB2 to connect to the standby server, copy backup files, and so on

If you have not already done so, the wizard prompts you to catalog a standby server IP/host name, Instance node name, and to either specify a backup image off the primary server, or an existing remote database. In order to have discovery working, the DB2 Administration Server must be running on the standby server

If no local entry in /etc/hosts (%Systemdir%\system32\drivers\etc\hosts for windows) for the remote hostname

SQL22212N A DB2 Administration Server communication error has been detected. Client system: "x.x.x.x". Server system "x.x.x.x".

Specify a backup image of the primary database: If you have just set up your database for linear logging in this wizard, you must have created an offline backup, which is perfect for use here, in HADR configuration.

If you were already using linear/archival logging, this is the point at which you choose Select a backup image from the list provided and choose the latest backup, or if the backups listed are not recent, run an online backup now with Backup the primary database

7. Restore database on standby system: specify an alias for the standby database on the remote system (Server2) to be used by the primary instance on the local system (Server1). This should exist from the time the standby database was identified

8. Copy objects to the standby system: If you have DB2 objects that do not get copied inside a database backup file, such as external code for UDFs and stored procedures, you can identify those objects here and have DB2 move them for you. Because we had none of those, we left the dialog box blank.

Click Next.

9. Specify TCP/IP communication parameters: the settings required for the HADR-specific host name and service/port number, for the primary and standby databases. Enter unique values as required and

Click Next.

Tip: The wizard automatically inserts values for the HADR services in the /etc/services (%Systemdir%\system32\drivers\etc\services for windows) file. If you run the wizard more than once or have these services already registered, the service port numbers in this dialog box are incremented (for example, 55003 and 55004, if we ran this same wizard again after using 55001 and 55002 the first time). Ensure that you manually change the values back as necessary on subsequent runs.

10. Configure databases for automatic client reroute: where we set up the IP address redirect facility: where each client connection to a DB2 server stores the primary IP address and port number of the primary server, and also of the standby server. When you switch roles, or force a takeover, the other IP address is then used by the client; all the client sees is an SQL message to inform that the connection has changed and the transaction or database connection should be retried.

The port number refers to the main port number for the DB2 instance, not to the HADR port. Just match the port number with the correct server name/IP address, and remember that the “alternate” for the primary is the standby server, and vice versa. Click Next.

11. Specify synchronization mode for Peer state log writing: lets us choose whether we want Synchronous, Near-Synchronous, or Asynchronous mode for HADR. We choose Synchronous for our example.

Click Next.

12. Review the actions that occur when you click Finish: This dialog box shows an overview of the settings chosen for this HADR configuration.

Click Show Command... to get command line statements in order to learn what goes on behind the scenes in the wizard. These commands are worth saving in a text file, certainly for subsequent configurations

You can choose to either have the wizard start HADR, or issue that command yourself afterward through the Manage HADR wizard or on the command line.

Click Finish

Note: The Show Command only takes into consideration the work to be done after configuring linear/archival logging, so prerequisite updates to the database configuration parameters LOGARCHMETH1/2 and LOGINDEXBUILD are not listed.

13. Execution of Steps: Figure 3-13 shows a dynamic summary of those commands being generated and run by the automated script. If any issues are encountered, the wizard should let you know what must be corrected, and you should be able to click Finish on the previous dialog box in order to retry the process.

14. Verify HADR setup: To confirm that HADR is up and running, you can check with the DB2 Control Center.

First, select View Refresh, otherwise it displays the remote database as “unknown”. Next, right-click either of the HADR databases and select High Availability Disaster Recovery Manage HADR

If you left the choice to start HADR as blank in step 11 you can click Start HADR... here, which should then show your two databases in a Peer state, with the appropriate synchronization mode, the connected state, and the log information on each. You can get similar information using the GET SNAPSHOT command or db2pd

No comments:

Post a Comment