Use Case 1: Configure DataStream for a primary/secondary database architecture

A commonly used deployment scenario is the primary/secondary database architecture where the primary database replicates all information to the secondary databases.

For primary/secondary database architecture, you might want all WRITE requests to be sent to the primary database and all READ requests to the secondary databases.

The following figure shows the entities and the values of the parameters that you need to configure on the appliance.

Figure 1. DataStream Entity Model for Primary/Secondary Database Setup

Primary/secondary database setup

In this example scenario, a service (Svc_mysql_1) is created to represent the primary database and is bound to a load balancing virtual server (Lb_vsr_mysql_primary). Three more services (Svc_mysql_2, Svc_mysql_3, and Svc_mysql_4) are created to represent the three secondary databases, and they are bound to another load balancing virtual server (Lb_vsr_mysql_secondary).

A content switching virtual server (Cs_vsr_mysql_1) is configured with associated policies to send all WRITE requests to the load balancing virtual server, Lb_vsr_mysql_primary. All READ requests are sent to the load balancing virtual server, Lb_vsr_mysql_secondary.

When a request reaches the content switching virtual server, the virtual server applies the associated content switching policies to that request. After evaluating the policies, the content switching virtual server routes the request to the appropriate load balancing virtual server, which sends it to the appropriate service.

The following table lists the names and values of the entities and the policy configured on the Citrix ADC appliance.

Entity Type Name IP Address Protocol Port Expression
Services Svc_mysql_1 MYSQL 3306 NA
  Svc_mysql_2 MYSQL 3306 NA
  Svc_mysql_3 MYSQL 3306 NA
  Svc_mysql_4 MYSQL 3306 NA
Monitor lb_mon1 NA MYSQL-ECV NA mysql.res.atleast_rows_count(1)
Load balancing virtual servers Lb_vsr_mysql_primary MYSQL 3306 NA
  Lb_vsr_mysql_secondary MYSQL 3306 NA
Content switching virtual server Cs_vsr_mysql_1 MYSQL 3306 NA
Content switching policy Cs_select NA NA NA MYSQL.REQ.QUERY.COMMAND.contains("select")

Table 1. Entity and Policy Names and Values

To configure DataStream for a primary/secondary database setup by using the command line interface

At the command prompt, type

add db user user1 -password user1

add service Svc_mysql_1 mysql 3306

add service Svc_mysql_2 mysql 3306

add service Svc_mysql_3 mysql 3306

add service Svc_mysql_4 mysql 3306

add lb monitor lb_mon1 MYSQL-ECV -sqlQuery "select * from table1;" -evalrule "mysql.res.atleast_rows_count(1)" -database "NS" -userName  "user1"

add lb vserver Lb_vsr_mysql_primary mysql 3306

add lb vserver Lb_vsr_mysql_secondary mysql 3306

bind lb vserver Lb_vsr_mysql_primary svc_mysql_1

bind lb vserver Lb_vsr_mysql_secondary svc_mysql_2

bind lb vserver Lb_vsr_mysql_secondary svc_mysql_3

bind lb vserver Lb_vsr_mysql_secondary svc_mysql_4

add cs vserver Cs_vsr_mysql_1 mysql 3306

add cs policy Cs_select –rule "MYSQL.REQ.QUERY.COMMAND.contains(\"select"\)"

bind cs vserver Cs_vsr_mysql_1 Lb_vsr_mysql_primary

bind cs vserver Cs_vsr_mysql_1 Lb_vsr_mysql_secondary –policy Cs_select –priority 10

bind service Svc_mysql_1 -monitorName lb_mon1

bind service Svc_mysql_2 -monitorName lb_mon1

bind service Svc_mysql_3 -monitorName lb_mon1

bind service Svc_mysql_4 -monitorName lb_mon1
Use Case 1: Configure DataStream for a primary/secondary database architecture