DB2 HADR Configuration for STSC Database

At this point you should already have a Sametime System Console and two DB2 Servers installed. One of the DB2 Servers will be used as a DB2 Primary Instance and the other one as the Secondary. I strongly encourage you to read the official IBM documentation before implementing the following configuration.

In the environment where I have done this I had the following components installed:

  • IBM DB2 10.1 (without any Fix Packs)
  • WebSphere Network Deployment 8.5.5.0
  • Sametime System Console 9 Hot Fix 1
  • Sametime Community Server 9 Hot Fix 1

I also used the IBM Data Studio. You can install the newest version on your client, just make sure that you connect to the “STSC” database as a user which has the instance owner role.

The steps to configure HADR for STSC database are as following:

  1. Stop Sametime System Console.
  2. Back up all Databases which are running on your DB2 Server including STSC Database.
  3. You should also have a backup of the whole server where DB2 is running or a configuration backup.
  4. Log in as “db2admin” or another User which has the instance owner role.
  5. The first thing which we need to do is to configure logging due to the fact that DB2 HADR relies on it to keep the secondary database in sync with primary.

  1. Manually create a folder in the file system where you would like to store the logging data.

  1. Select “Archive” and then browse to the location of the folder you created.

  1. You can keep the default values.

  1. I have kept the default value for the destination of active logs.

  1. The wizard creates a DB backup before applying the configuration changes, here you can select where you want to store the Backup.

Note: You can copy all commands and create a script of them to run it in another environment.

Commands:

CONNECT TO STSC;

QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;

UNQUIESCE DATABASE;

CONNECT RESET;

CONNECT TO STSC;

UPDATE DATABASE CONFIGURATION USING logarchmeth1 “DISK:C:\db2archlog_stsc” newlogpath “C:\STSC\DB2\NODE0000\SQL00002\LOGSTREAM0000” logprimary 13 logsecond 12 logfilsiz 1024 LOGINDEXBUILD OFF ;

CONNECT RESET;

DEACTIVATE DATABASE STSC;

BACKUP DATABASE STSC TO “C:\STSC\Backup” EXCLUDE LOGS WITHOUT PROMPTING;

  1. At the end select “Run”.

After the process is finished you may get a warning which states that you need to deactivate and activate the database in order for changes to take effect.

You can do this by issuing the following commands in DB2 CMD.

  1. Configure HADR

Now we can actually configure HADR.

  1. Connect to the STSC database.

We can now use “Online Backup” because we have configured archive logging, that is why it is quite handy to have archive logging configured even if you don´t want to configure HADR.

After going any further you should check the port numbers which are used by other databases. You need to make sure that you do not define ports for the new STSC database HADR configuration which are already used by other HADR configurations on the DB2 instance.

  1. Open db2 admin cmd.
  2. Connect to the specific database by issuing “db2 connect to <database_name>”.
  3. Then enter the “db2 get db cfg” command.

Note: Make sure you do not use the same ports as defined in the output.

Note: Mostly I use “Synchronous” HADR synchronization mode due to the fact that all my servers are in the same LAN. Depending on your network configuration you can choose which one works best for you.

  1. In the “Client Reroute Options” enter the Hostname of the secondary server as well as the port number on which the secondary database should be accessible by the Sametime System Console in a case of a failover.

2. Create a connection to the standby instance.

3. Create a new Connections Profile to the standby server and select the most recent backup of the STSC database.

4. Select a directory on the standby system where the backup file will be copied. If the specific folder does not exist, create it manually, otherwise you will get an error. J

5. Select a port for the HADR service on the standby system.

6. You will need to specify the “Client Reroute Options” again, this time you will need the to specify the primary server.

7. After you are happy with the configuration, click run.

Note: you can ignore the following warnings.

These Commands were shown in the Data Studio as a result from our configuration:

RESTORE DATABASE STSC FROM “C:\STSC\Backup” TAKEN AT 20150827095005 INTO STSC REPLACE HISTORY FILE WITHOUT PROMPTING;

–Primary Database Configuration Commands;

— Configure database for client reroute – <Primary_Host>(DB2) – STSC;

UPDATE ALTERNATE SERVER FOR DATABASE STSC USING HOSTNAME <Secondary_Host> PORT 50000;

— Update HADR configuration parameters on database – <Primary_Host>(DB2) – STSC;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_HOST <Primary_Host>;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_SVC 56001;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_HOST <Secondary_Host>;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_SVC 56002;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_INST DB2;

UPDATE DB CFG FOR STSC USING HADR_TIMEOUT 120;

UPDATE DB CFG FOR STSC USING HADR_TARGET_LIST <Secondary_Host>:56002;

UPDATE DB CFG FOR STSC USING HADR_SYNCMODE SYNC;

UPDATE DB CFG FOR STSC USING HADR_PEER_WINDOW 0;

UPDATE DB CFG FOR STSC USING HADR_SPOOL_LIMIT 0;

UPDATE DB CFG FOR STSC USING HADR_REPLAY_DELAY 0;

UPDATE DB CFG FOR STSC USING BLOCKNONLOGGED NO;

STOP HADR ON DATABASE STSC;

–Standby Database Configuration Commands for <Secondary_Host>-DB2;

— Configure database for client reroute – <Secondary_Host>(DB2) – STSC;

UPDATE ALTERNATE SERVER FOR DATABASE STSC USING HOSTNAME <Primary_Host> PORT 50000;

— Update HADR configuration parameters on database – <Secondary_Host>(DB2) – STSC;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_HOST <Secondary_Host>;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_SVC 56002;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_HOST <Primary_Host>;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_SVC 56001;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_INST DB2;

UPDATE DB CFG FOR STSC USING HADR_TIMEOUT 120;

UPDATE DB CFG FOR STSC USING HADR_TARGET_LIST <Primary_Host>:56001;

UPDATE DB CFG FOR STSC USING HADR_SYNCMODE SYNC;

UPDATE DB CFG FOR STSC USING HADR_PEER_WINDOW 0;

UPDATE DB CFG FOR STSC USING HADR_SPOOL_LIMIT 0;

UPDATE DB CFG FOR STSC USING HADR_REPLAY_DELAY 0;

UPDATE DB CFG FOR STSC USING BLOCKNONLOGGED NO;

— Start HADR on standby database – <Secondary_Host>(DB2) – STSC;

DEACTIVATE DATABASE STSC;

START HADR ON DATABASE STSC AS STANDBY;

— Start HADR on primary database – <Primary_Host>(DB2) – STSC;

START HADR ON DATABASE STSC AS PRIMARY;

7. Post Configuration and Verification

Verify that the HADR is working.

Primary server:

Standby server:

Check if you can connect to the database.

You can restart the system just to make sure that everything is ok after the restart.

Primary:

Stand by:

Primary:

Standby:

8. Test a Failover

Standby:

Primary:

9. WebSphere & SSC Configuration

Alternate Server Name: <Secondary_Host>

Alternate Port Name: 50000

Do this for every DB2 Datasource/Database configured as HADR.

Restart the Application Server. à In our Case, the SSC.

  1. Check and verify

Start the application, SSC.

Primary:

Our Application Server has the IP of 192.168.13.29

Failover to Standby and check if the Application can connect to the Standby Server.

If you get an error message, which refers to db2 database, in SSC just refresh or click on the same menu once again.

Some useful information regarding DB2 HADR: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/DB2HADR/page/HADR+commands

We have our DB2 Servers running in the Configuration described above for quite some time and we didn’t have any issues with it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s