Zero to HADR in 10 minutes on Amazon's EC2

One of the key benefits of Cloud Computing is how fast someone can provision a new system and/or software. But how fast is it really? What if I was an eager new business owner and wanted to get up and running on the cloud today with an enterprise DB.

As a new business owner on the Cloud, DB2 would be a wonderful choice. I could start with the free version, called "DB2 Express-C", which is available on Amazon today. DB2 also has numerous features to leverage for my business over time and it can scale from one system to 1000 systems with peta-bytes of data. Not only that, there are industry leading software products such as Cognos, WebSphere, SAP and hundreds of applications that work seamlessly with DB2. All waiting for me and my new business at any time....

To be fair, I wanted this example to be somewhat realistic. My new business would need an enterprise DB with High Availability and Disaster Recovery as part of the solution. I also didn’t want to show just a point and click demo since it hides so many of the details. It would cut down the time dramatically but it is always nice to look under the hood of a great car!

I decided to try an example with HADR. How long would it actually take to get it up and running using the command line on EC2? For the setup, I used the HADR best practices whitepaper and the HADR Redbook (page 71) as my guide.

Step #1: Provisioning two EC2 instances (1:28pm)

To keep the costs to a minimum, I picked the “m1.small” EC2 instance type and provisioned 2 systems. This usually takes 5-1o minutes but sometimes 15 minutes or more (this time it took 15 minutes). Queue the Jeopardy music...

     ...  da da da da da da da da...

One coffee run and a few processed emails later, we have our two EC2 instances… now on to the setup.

Note: The AMI that I’m using has DB2 pre-installed which makes this even faster. The private host names of the two EC2 instances are domU-12-31-39-01-7A-55.compute-1.internal (used as primary) and  domU-12-31-39-00-65-94.compute-1.internal (used as standby). Also, any command prefixed with # was run as root and $ as the instance owner.

Step #2: Create the instance on the primary and standby (1:43pm)

On both the primary and standby, I created the DB2 instance using the following:

# ./db2icrt -s wse -a SERVER -p 55555 -u db2fenc1 db2inst1

DBI1070I  Program db2icrt completed successfully.

Step #3: Create the sample database (1:44pm)

# su – db2inst1

$ db2sampl

Step #4: Enable LOGRETAIN and LOGINDEXREBUILD (1:45pm)

$ db2 update database configuration for sample using LOGRETAIN recovery

$ db2 update database configuration for sample using LOGINDEXBUILD ON

Step #5: Take a backup on the primary (1:45pm)

There are a few choices for backups location. We could back up to ephemeral storage, we could provision an EBS volume to back up directly there or in theory, we could back up to S3. Since this is about setting up HADR, I’ll just use the ephemeral storage and save backup for a future topic.

# mkdir /backup

# chown db2inst1 /backup

# su - db2inst1

$ db2start

$ db2 backup database sample to /backup      

Backup successful. The timestamp for this backup image is : 20090703114747

 

Step #6: Restore the DB2 backup on the standby (1:46pm)

First, we need to copy the backup over to the second system (I also created the /backup directory):

# scp -i db2cloudkey.pem /backup/SAMPLE.0.db2inst1.NODE0000.CATN0000.20090703114747.001 domU-12-31-39-00-65-94.compute-1.internal:/backup

SAMPLE.0.db2inst1.NODE0000.CATN0000.20090703114747.001       100%   98MB  16.3MB/s   00:06  

Now to restore on the standby:

$ db2 restore database sample from /backup taken at 20090703114747 replace history file

Step #7: Configure the primary for HADR (1:48pm)

$ db2 update alternate server for database sample using hostname domU-12-31-39-00-65-94.compute-1.internal  port 55555

$ db2 update db cfg for sample using HADR_LOCAL_HOST domU-12-31-39-01-7A-55.compute-1.internal

$ db2 update db cfg for sample using HADR_REMOTE_HOST domU-12-31-39-00-65-94.compute-1.internal

$ db2 update db cfg for sample using HADR_LOCAL_SVC DB2_HADR_1

$ db2 update db cfg for sample using HADR_REMOTE_SVC DB2_HADR_2

Step #8: Configure the standby for HADR (1.50pm)

$ db2 update alternate server for database sample using hostname domU-12-31-39-01-7A-55.compute-1.internal port 55555

$ db2 update db cfg for sample using HADR_LOCAL_HOST domU-12-31-39-00-65-94.compute-1.internal

$ db2 update db cfg for sample using HADR_REMOTE_HOST domU-12-31-39-01-7A-55.compute-1.internal

$ db2 update db cfg for sample using HADR_LOCAL_SVC DB2_HADR_2

$ db2 update db cfg for sample using HADR_REMOTE_SVC DB2_HADR_1

 

Step #9: Make common changes for both primary and standby (1:51)

Add the following to the /etc/services file on both the primary and standby:

DB2_HADR_1      50001/tcp

DB2_HADR_2      50002/tcp

Now for  some common changes to the database configuration on both the primary and standby(I just cut and pasted this on both systems):

$ db2 update db cfg for sample using HADR_REMOTE_INST db2inst1

$ db2 update db cfg for sample using HADR_SYNCMODE SYNC

$ db2 update db cfg for sample using HADR_TIMEOUT 3

$ db2 update db cfg for sample using HADR_PEER_WINDOW 120

$ db2 connect to sample

$ db2 quiesce database immediate force connections

$ db2 unquiesce database

$ db2 connect reset

 

Step #10: Activate HADR on primary and standby (1.52pm)

On the standby:

$ db2 deactivate database sample

$ db2 start hadr on database sample as standby

 

On the primary:

$ db2 deactivate database sample

$ db2 start hadr on database sample as primary

 

Validation (1:53pm)

At this point, HADR is set up and ready to go. To make sure it is working, let's check the LSNs for the standby as we do an insert on the primary. The LSN is the Log Sequence Number and this should increment on the standby (and primary) as we insert data into the primary.

On the standby, we can get the LSNs (for primary and standby) using db2pd:

$ db2pd -db sample -hadr |egrep LOG

S0000000.LOG 0          0x0000000001388000

S0000000.LOG 0          0x0000000001388000

 

On the primary, let’s do a quick insert.

$ db2 "insert into STAFF values (550,'Wilding', 42, 'Sales', 6, 44444.44, 423.33)"

And check that the LSNs were updated on the standby:

$ db2pd -db sample -hadr |egrep LOG

S0000000.LOG 0          0x00000000013884C7

S0000000.LOG 0          0x00000000013884C7

It worked!

Summary and Costs

25 minutes later (15 minutes for provisioning and 10 minutes for the manual setup of HADR), we have HADR set up and working. Since I was able to grab a coffee and continue working during the provisioning, setting up HADR on EC2 only took 10 minutes away from my "new business".

We could spend more time configuring HADR and DB2 but we could also have done this example using a point and click demo which would have reduced the manual steps. What's nice about Cloud Computing is that once you know what you need, you can even save an AMI with the right setup and/or automate many of the manual steps. Given that it took more time to provision the EC2 instances than to manually configure HADR, I was pretty impressed. Kudos to the HADR folks for an easy set up!

How much did this little venture cost? Since EC2 is rounded up to the hour, the price for the “m1.small” EC2 instance is $0.10 per hour and we were using two systems, we spent $0.20 on AWS. I even checked the usage cost on my AWS account and it confirmed the $0.20 cost by moving $6.41 to $6.61 for July usage. My new business can certainly afford that!

 To learn more about HADR, DB2 on Amazon or Jeopardy Music:

• HADR Best Practices  
• HADR Redbook
• DB2 on Amazon's EC2
• Jeopardy Music

- Mark Wilding (mwilding <.at.> ca.ibm.com)

 

Comments

[Mark wilding]

There is a WorkGroup Edition available here:

http://developer.amazonwebservices.com/connect/entry.jspa?externalID=2839&utm_source=ibm&utm_medium=catalog&utm_campaign=link

For the second question, it is more difficult to set up but it works well. The synchronous replication prevents a lot of headaches. I'll provide the detailed instructions in a future topic.

[Casino Royale]

Interesting. A couple of questions linger on my mind :

a) Is there an enterprise edition of db2 available with AMI ? I dint see this being offered.

b) The cases when the machine instances go down ( of both primary / standby ) would be more interesting to solve since the host names would vary when the machine instances are brought up ..

Any thoughts ?