upgrades using q-rep at constant contact, inc. 2027a · who is constant contact? – we help small...

Zero Downtime Upgrades Using Q-REP

at Constant Contact, Inc.2027A

0

2027A

Dan Berry, Constant Contact Sankar Padhi, Constant Contact

AGENDA

• Who is Constant Contact, Inc

• Legacy Architecture

• Database Transactions per day

• What were our options

• Why Q-Replication

• What is Q-replication

• Planning for Q-replication

1

•• Automatic Application Reroute

• Q-replication Monitoring

• Final Configuration

WHO IS CONSTANT CONTACT?

A snapshot view of an incredible company

Who is Constant Contact?

– We help small businesses and organizations create and grow customer relationships and succeed by delivering professional, easy-to-use services and coaching at a reasonable cost.

• Permission-based email marketing

• Event marketing

• Online surveys

• Web-based, subscription solution starting at $15 per month• Web-based, subscription solution starting at $15 per month

– Our customers around the world love us, stay with us and recommend us to others.

• ~ 400,000 email marketing customers (70% with fewer than 10 employees)

• Large, under-penetrated market: >27 million small businesses in the US alone

– Personal touch is the key to our success formula: we are always there for our customers.

3

Who is Constant Contact?

– Social Media Marketing Leader

• Acquired Nutshell Mail (May 2010)

• Easy-to-use email summary of activities across your social networks

• Free tool – sign up at ConstantContact.com or NutshellMail.com

– Horizontal Scaling & Automation are key to our success

• Social Media Marketing is all about low cost online service

• Need to leverage emerging tools (NoSQL…etc)

• Infrastructure services must be able to scale cost effectively

4

Rapid, Consistent & Highly Visible Growth

Monthly Revenue (2004 – Q2 2010)

5

Note: monthly numbers unaudited

– The value of Email Marketing

• Drives repeat sales and customer referrals

• Keeps you in front of your best audience –

people who know you

– The value of Social Media Marketing

Suite of Marketing Solutions for Small Business

• Leverage the power of your raving fans to

acquire new customers through their networks

• Increased reach with endorsement

6

– The value of Event Marketing

• Drives increased event attendance

• Saves time and money; simplify event hosting

– The value of Survey

• Understand your customers and members better

Suite of Marketing Solutions for Small Business

• Understand your customers and members better

• Makes communications more relevant and targeted

7


• The Problem and Our Goal






AGENDA

8

•• Planning for Q-replication

• Automatic Application Reroute



THE PROBLEM:

High Availability Databases were implemented using roll-forward

Recovery was not immediate and required downtime

Maintenance was difficult due to downtime requirement

OUR GOAL:

It is vital that our customer experience nearly 100% uptime

9

Hardware Independent: If a physical server fails, web clients will failover instantly and website will be available to customer as if nothing happened.

Rolling OS\Database maintenance

Individual DB server can be taken offline for maintenance without maintenance window

Both DB servers accepts traffic for scaling and/or multi-site traffic

Nicknames

NicknamesSub01 and Sub02 Nicknames Nicknames NicknamesGlobal DBNicknames

DB2 V8.2

2.5 TB

(SaaS) Product DB

NicknamesWeb DB1 Nicknames

DB2 V8.2

Uni-di SQL Repl,

Export/Import

InternetDB2 V8.2

DB2 V8.2

1.1 TBDB2 V8.2

1.3 TBDB2 V8.2

1 TBDB2 V8.2

750 GBDB2 V8.2

1TBDB2 V8.2

1 TBDB2 V8.2

250 GB

Web DB2

Sub03 and Sub04 Sub05 and Sub06

LEGACY ARCHITECTURE

10

Web site depends on multiple databases

SQL replication and db2 export\import were used for data propagation

NicknamesInternal App

Uni-di SQL Repl,

Export/Import

Nicknames

DB2 V8.2

DW Stage

Non db2 databases

DB2 V8.2

1.3TB

Subscriber databases

Warehouse databases

Nicknames

DB2 V8.2

High Availability (HA)

Product Global Internal

App

Web DB

SUB06SUB05SUB01 SUB02 SUB03 SUB04

Product DB

SUB04

SUB01

SUB05 SUB06

SUB02 SUB03

Global

DB2 V8.2

Disaster Recovery (DR)

LEGACY ARCHITECTURE, cont’d

11

HA and DR were implemented using roll forward and they were not

accessible.

If primary database fails, copy over logs that are left, roll forward to

the end. Recovery was not immediate and required down time.

Maintenance was difficult due to downtime requirement

ROLLFORWARD DATABASES ROLLFORWARD DATABASES

DB Name Insert, Update and Delete

Product 690

Global DB 300

Subscriber01 946

Subscriber02 999

Subscriber03 958

Subscriber04 716

TRANSACTION/SECOND

12

Subscriber04 716

Subscriber05 1175

Subscriber06 971

Internal Appl. 218

Social 333

Data warehouse 82

Total Transaction / Second 7388

High Availability Disaster Recovery (HADR)• DB2 HADR is a replication solution that provides a high availability solution

REPLICATION: Replicate transactions between two

WHAT WERE OUR OPTIONS?

13

REPLICATION: Replicate transactions between two or more databases

• SQL Replication• Q-Replication

3rd PARTY• For example, Gridscale• $$$’s

– High throughput

– Low latency

– Instant failover

–

WHY Q-REPLICATION

14

– Active stand by

– Active - Active database

– Backup from alternate node







• Planning for Q-replication

AGENDA

15

•• Automatic Application Reroute



• Q-replication allows you to replicate committed transactional data from

DB2/Oracle sources to targets by using following:

– Q Capture reads DB2 recovery logs for changed source data and writes the changes to

WebSphere MQ queues.

– Q Apply retrieves captured changes from queues and writes the changes to targets.

– Subscription is an object to define how data from a source table is replicated to a target table.

WHAT IS Q-REPLICATION?

16

Source: IBM DB2 Information Integrator Introduction to Replication and Event Publishing.

• “The Fillmore Group” consulting firm helped us to develop ASNCLP

command line script to create multiple Q subscription.

• Storage requirement

– Plan for desired level of resilience for network outage or target outage.

• Primary or Unique key

– All published tables should contain a primary or a unique key.

– Eliminate duplicate records from tables

PLANNING FOR Q-REPLICATION

17

–

• Identity column

– DB2 does not allow inserts into columns that are defined with the AS

IDENTITY GENERATED ALWAYS clause.

– Create target table with a column with AS IDENTITY GENERATED BY

DEFAULY

– Identity range

• Naming standard for MQ, directory structure, accounts

DB2 V8.2

PRIMARY SERVER

DB2 V9.5

Q-rep Bi-Di

SECONDARY SERVER

Internet

IBMQREP_IGNTRANauthid=‘QREPADM’

IBMQREP_IGNTRANauthid=‘QREPADM’?

authid=‘QREPADM’

PLANNING FOR Q-REPLICATION. cont’d

18

Transactions can be filtered by the Capture program by specifying the AUTHID(s).

The IBMQREP_IGNTRAN option can be used to inform the Q Capture program about transactions that we do not want to be captured from db2 recovery log.

To use the IBMQREP_IGNTRAN option, the Q apply program’s authorization ID must

be unique and not shared by other applications.

PRIMARY SERVER SECONDARY SERVER

Real time Uni-di failover:

# of Tables# of Tables DataData# of bi# of bi--didi//uniuni--didi

ConfigurationConfiguration

Product database 619 2.5 TB 2

Six Subscriber databases 2976 6.15 TB 12

PLANNING FOR Q-REPLICATION. cont’d

19

Data Warehouse (DW) 60 250 GB 1

Internal App. database 156 Uni-di

186 Bi-di

1.2 TB 1

Cursor load

Why was this a challenge?

• Production v8.2 server already

max out (high CPU and IO)

• Need to perform an initial load of

2.5 TB data.

Q-Rep Bi-Di

Product DB

2.5 TB

DB2 V8.2

Product DB

2.5 TB

DB2 V9.5

Production Server Non-Production Server

INITIAL DATABASE LOAD

20

Nicknames

SUB01 SUB06

GlobalInternal

DB2 V8.2HA Roll Forward Server

Product

DB

2.5 TBWorkaround:

• Initiate load from roll-forward host.

• Started capture from a known

LSN.

Stop roll forward

… …

DB2 V8.2 to V9.5 Q-rep setup

Nicknames

DB2 V8.2

Web DB1 Nicknames

DB2 V8.2

Uni-di CaptureIBMQREP_IGNTRANauthid=‘qrepadm’



Web DB2

Product DB

DB2 V8.2

ACTIVE PRIMARY

Product DB

DB2 V9.5

ACTIVE STANDBY

Product DB

DB2 V9.5

ACTIVE STANDBY

Q-Rep BI-DI Q-Rep BI-DI

21

Nicknames

DB2 V8.2

Nicknames

DB2 V8.2

DW Stage

Ni




Internal DB

ACTIVE PRIMARY ACTIVE STANDBY ACTIVE STANDBY

Automatic, transparent connection to secondary server when primary

connection fails:

Options:

Automatic Client Reroute (ACR): Information configured on database

server

db2 update alternate server for database <dbname> using hostname <hhh>

port <nnn>

CLIENT RE-ROUTE

22

port <nnn>

Alternate information Stored on client

AlternateDataSource property (Java 4 drivers)

Failover using VIP (floating IP)

If there is a currently executing SQL statement, it will fail with sqlcode.

Transaction can then be re-driven without re-establishing a connection.

Nicknames

DB2 V8.2

Nicknames

DB2 V9.5

Bi-Di

Primary_VIP Secondary_VIP

… …Internet

CONFIGURING CLIENT RE-ROUTE

23

Nicknames

PRIMARY SERVER

Nicknames

Application Configuration:

<connection-url> jdbc:db2://Primary_VIP:60000/db_Name | jdbc:db2://Secondary_VIP:60000/db_Name

Controlled Failover:

•Stop DB2 on Primary Server

•Make sure all connections failed over to Standby Server

•Move Primary_VIP to Standby Server

•Start DB2 on Primary Server and establish Q-replication

STANDBY SERVER

SUB01 SUB02 SUB1A SUB2B SUB2A SUB1B

BI-DI Q-REP BI-DI Q-REP


DB2 V 8.2 DB2 V 9.5 DB2 V 9.5

SUBSCRIBER (SIX) DATABASE MIGRATION SCENARIO

24

SUB03 SUB04SUB3A

SUB4B SUB4A SUB3B

SUB05 SUB06 SUB5A SUB6B SUB6A SUB5B


DB2 V 8.2

DB2 V 8.2

DB2 V 9.5

DB2 V 9.5

DB2 V 9.5

DB2 V 9.5

Q-Rep Dashboard •Monitor Latency•Throughput

Nagios monitoring:•Q-Depth (SENDQ,RECVQ):

- Alert when depth exceeds 5000 messages

•Channel Status (Up or Down)

MONITORING/CRITICAL ALERTS

25

•Channel Status (Up or Down) - Monitor and alert if sender or receiver MQ channel

status is down

Overall capture\apply info[/home/qrepadm]$ db2 “select * from qrepadm.qrep_check with ur”

DBNAME INSERT_TIME ---------- -------------------------ROVS115 2010-08-29-12.25.02.145184ROVS116 2010-08-29-12.25.02.265208

2 record(s) selected.

Nicknames

DB2 V8.2

Internal DBNicknames

DB2 V8.2

Uni-di CaptureIBMQREP_IGNTRANauthid=‘QREPADM’

Nicknames

DB2 V8.2

Web DB1 Nicknames

DB2 V8.2

Web DB2

Product DB

DB2 V9.5

Uni-di CaptureIBMQREP_IGNTRANauthid=‘QREPADM’

DW

Product DB

DB2 V9.5

PRODUCT DB AFTER MIGRATION

26

DB2 V9.5Bi-Di

Online DB Online DB BackupBackup

• Active standby may be used for other applications

• Solution for geographically dispersed database

• Backup from active standby

ACTIVE PRIMARY ACTIVE STANDBY

DB2 V9.5

InternetBI-DI Q-REP

BI-DI Q-REP


SUB01A

DB2 V9.5

SUB03A

SUB02A

DB2 V9.5

SUB04A

SUB02B

DB2 V9.5

SUB01B

DB2 V9.5

SUB03BSUB04B

ACTIVE STANDBYACTIVE PRIMARY

SUBSCRIBER DB AFTER MIGRATION

27

Nicknames Nicknames

Nicknames

BI-DI Q-REP



SUB03A

DB2 V9.5

SUB05A

DB2 V9.5

SUB04A

DB2 V9.5

SUB06A

DB2 V9.5

SUB03B

DB2 V9.5

SUB04B

DB2 V9.5

SUB06B

DB2 V9.5

SUB05B

DB2 V9.5

ACTIVE PRIMARY

ACTIVE PRIMARY

ACTIVE STANDBY

ACTIVE STANDBY

• Allowed the fastest switchover back and forth multiple times during

production bufferpool issue

• Active standby is used for backup and data analysis

BENEFITS …

28

• Excellent solution for scheduled and unscheduled maintenance

• Flexibility on OS level, DB version, Data format

• Low impact on source database

Thank you for attending !!!

29

ConstantContact

[email protected]@constantcontact.com

R

upgrades using q-rep at constant contact, inc. 2027a · who is constant contact? – we help small...

Documents