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
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 NetScaler appliance.
| Entity Type | Name | IP Address | Protocol | Port | Expression |
|---|---|---|---|---|---|
| Services | Svc_mysql_1 | 198.51.100.5 | MYSQL | 3306 | NA |
| Svc_mysql_2 | 198.51.100.6 | MYSQL | 3306 | NA | |
| Svc_mysql_3 | 198.51.100.7 | MYSQL | 3306 | NA | |
| Svc_mysql_4 | 198.51.100.8 | MYSQL | 3306 | NA | |
| Monitor | lb_mon1 | NA | MYSQL-ECV | NA | mysql.res.atleast_rows_count(1) |
| Load balancing virtual servers | Lb_vsr_mysql_primary | 198.51.100.201 | MYSQL | 3306 | NA |
| Lb_vsr_mysql_secondary | 198.51.100.202 | MYSQL | 3306 | NA | |
| Content switching virtual server | Cs_vsr_mysql_1 | 198.51.100.161 | 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 198.51.100.5 mysql 3306
add service Svc_mysql_2 198.51.100.6 mysql 3306
add service Svc_mysql_3 198.51.100.7 mysql 3306
add service Svc_mysql_4 198.51.100.8 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 198.51.100.201 3306
add lb vserver Lb_vsr_mysql_secondary mysql 198.51.100.202 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 198.51.100.161 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