Use Case 4: Database Specific Load Balancing
数据库服务器农场必须负载不平衡啊nly based on the states of the servers, but also based on the availability of the database on each server. A service might be up, and a load balancing device might show it as being in the UP state, but the requested database might be unavailable on that service. The request is not served if a query is forwarded to a service on which the database in unavailable. Therefore, a load balancing device must be aware of the availability of a database on each service. And when making a load balancing decision, it must consider only those services on which the database is available.
As an example, consider that database servers server1, server2, and server3 host databases mydatabase1 and mydatabase2. If mydatabase1 becomes unavailable on server2, the load balancing device must be aware of that change in state. It must load balance requests for mydatabase1 across only server1 and server3. After mydatabase1 becomes available on server2, the load balancing device must include server2 in load balancing decisions. Similarly, if mydatabase2 becomes unavailable on server3, the device must load balance requests for mydatabase2 across only server1 and server2. It must include server3 in its load balancing decisions only when mydatabase2 becomes available. This load balancing behavior must be consistent across all the databases that are hosted on the server farm.
The Citrix ADC appliance implements this behavior by retrieving a list of all the databases that are active on a service. To retrieve the list of active databases, the appliance uses a monitor that is configured with an appropriate SQL query. If the requested database is unavailable on a service, the appliance excludes the service from load balancing decisions until it becomes available. This behavior ensures uninterrupted service to clients.
Note
Database specific load balancing is supported for only MSSQL and MySQL service types. This support is also available for Microsoft SQL Server 2012 high availability deployment.
To set up database specific load balancing, you must configure the following:
- Enable the load balancing feature, and configure a load balancing virtual server of type MSSQL or MySQL.
- Configure the services that host the database, and bind the services to the virtual server. The monitor needs valid user credentials to log on to the database server, so you must configure a database user account on each of the servers and then add the user account to the Citrix ADC appliance.
- Then, you configure an MSSQL-ECV or MYSQL-ECV monitor and bind the monitor to each service.
- Finally, you must test the configuration to ensure that it is working as intended. Before you perform these configuration tasks, make sure you understand how database specific load balancing works.
How Database Specific Load Balancing Works
对于数据库特定的负载平衡,你configure a monitor that periodically queries each database server for the names of all the active databases on it. The Citrix ADC appliance stores the results, and regularly updates the records based on the information retrieved through monitoring. When a client queries a particular database, the appliance uses the configured load balancing method to select a service, and then checks its records to determine whether the database is available on that service. If the records indicate that the database is not available, it uses the configured load balancing method to select the next available service, and then repeats the check. The appliance forwards the query to the first available service on which the database is active.
Enabling Load Balancing
You can configure load balancing entities such as services and virtual servers when the load balancing feature is disabled. The entities do not function until you enable the feature.
To enable load balancing by using the command line interface
At the command prompt, type the following command to enable load balancing and verify the configuration:
enable ns feature LB show ns feature
Example:
> enable ns feature LoadBalancing Done > show ns feature Feature Acronym Status ------- ------- ------ 1) Web Logging WL OFF 2) Surge Protection SP ON 3) Load Balancing LB ON . . . 24) NetScaler Push push OFF Done
To enable load balancing by using the configuration utility
Navigate toSystem > Settingsand, inConfigure Basic Features, selectLoad Balancing.
Configuring a Load Balancing Virtual Server for Database Specific Load Balancing
To configure a virtual server to load balance databases based on availability, you enable the database specific load balancing parameter on the virtual server. Enabling the parameter modifies the load balancing logic that the Citrix ADC appliance refers the results of the monitoring probe sent to the selected service, before forwarding the query to that service.
To configure a load balancing virtual server for database specific load balancing
At the command prompt, type the following command to configure a load balancing virtual server for database specific load balancing and verify the configuration:
加磅vserver -dbsLb ENABLED show lb vserver
Configuring Services
After you enable the load balancing feature, you must create at least one service for each application server that is to be included in your load balancing setup. The services that you configure provide the connections between the Citrix ADC appliance and the load balanced servers. Each service has a name and specifies an IP address, a port, and the type of data that is served.
If you create a service without first creating a server object, the IP address of the service is also the name of the server that hosts the service. If you prefer to identify servers by name rather than IP address, you can create server objects and then specify a server’s name instead of its IP address when you create a service.
Configuring Database Users
In databases, a connection is always stateful, which means that when a connection is established, it must be authenticated.
You need to configure your database user name and password on the Citrix ADC. For example, if you have a user John configured on the database, you need to configure the user John on the ADC too. When you add the database user names and passwords on the ADC, the same are added to the nsconfig file.
Note
Names are case sensitive.
The ADC uses these user credentials to authenticate the clients, and then authenticate the server connections with the database servers.
To add a database user by using the command line interface
At the command prompt, type
add db user - password
Example:
add db user nsdbuser -password dd260427edf
To add a database user by using the configuration utility
Navigate to系统用户管理> >数据库用户, and configure a database user.
If you have changed the password of the database user on the database server, you must reset the password of the corresponding user configured on the Citrix ADC appliance.
To reset the password of a database user by using the command line interface
At the command prompt, type
set db user -password
Example:
set db user nsdbuser -password dd260538abs
To reset the password of database users by using the configuration utility
Navigate to系统用户管理> >数据库用户, select a user, and enter new values for the password.
If a database user no longer exists on the database server, you can remove the user from the Citrix ADC appliance. However, if the user continues to exist on the database server and you remove the user from the ADC appliance, any request from the client with this user name does not get authenticated. Therefore, the user name does not get routed to the database server.
To remove a database user by using the command line interface
At the command prompt, type
rm db user
Example:
rm db user nsdbuser
To remove a database user by using the configuration utility
Navigate to系统用户管理> >数据库用户, select a user, and click删除.
Configuring a Monitor to Retrieve the Names of Active Databases
You can create a monitor to retrieve the list of all active databases on a database instance. The monitor logs on to the database server by using a valid user credentials and runs an appropriate SQL query. The SQL query you need to use depends on your SQL server deployment. For example, in an MSSQL database mirroring setup, you can use the following query to retrieve a list of active databases available on a server instance.
select name from sys.databases where state=0
In a MySQL database setup you can use the following queries to retrieve a list of active databases available on a server instance.
Show databases:
You also configure the monitor to evaluate the response for an error condition, and to store the results if there is no error. If the response contains an error, the monitor marks the service as DOWN. The appliance excludes the service from load balancing decisions until an error is no longer returned.
Note
The database specific load balancing feature is supported only for the MSSQL and MySQL service types. Therefore, the monitor type must be MSSQL-ECV or MYSQL-ECV.
To configure a monitor to retrieve the names of all the active databases hosted on a service by using the command line
At the command prompt, type the following commands to retrieve the names of all the active databases hosted on a service and verify the configuration:
加磅monitor -userName -sqlQuery -evalRule -storedb ENABLED show lb monitor
To configure a monitor to retrieve the names of all the active databases hosted on a service by using the configuration utility
- Navigate toTraffic Management > Load Balancing > Monitorsand configure a monitor of type MSSQL-ECV or MYSQL-ECV.
- InSpecial Parameters, specify a user name, query, and a rule. For example, for MSSQL-ECV, the query must be “select name from sys.databases where state=0”), and a rule must be MSSQL.RES.TYPE.NE(ERROR). For MYSQL-ECV, the query must be “show databases” and a rule must be MYSQL.RES.TYPE.NE(ERROR).
Availability Groups Deployment Support for MSSQL
Consider the following scenario in which database specific load balancing is configured in a high availability group deployment. S1 through S5 is the services on the ADC appliance. DB1 through DB4 is the databases on the servers represented by the services S1 through S5. AV1 and AV2 are the availability groups. Each availability group contains up to one primary database server instance and up to four secondary database server instances. A service, representing the servers in the availability group, can be primary for one availability group and secondary for another availability group. Each availability group contains different databases and one listener, which is a service. All requests arrive on the listener service that resides on the primary database. AVI contains databases DB1 and DB2. AV2 contains databases DB3 and DB4. L1 and L2 are the listeners on AV1 and AV2 respectively. S1 is the primary service for AV1 and S2 is the primary service for AV2.
Service | List of Active Databases on the Service |
---|---|
S1 | DB1, DB2, DB3, DB4 |
S2 | DB3, DB4 |
S3 | DB3, DB4 |
S4 | DB1, DB2 |
S5 | DB1, DB2 |
Availability Group | Databases | Services representing the Servers in Availability Group |
---|---|---|
AV1 | DB1, DB2 | S1, S4, S5 |
AV2 | DB3, DB4 | S1, S2, S3 |
Queries flow as follows:
- A READ query for AV1 is load balanced between S4 and S5. S1 is the primary for AV1.
- A WRITE query for AV1 is directed to L1.
- A READ query for AV2 is load balanced between S1 and S3. S2 is the primary for AV2.
- A WRITE query for AV1 is directed to L2.
Sample Configuration
- Configure load balancing and content switching virtual servers.
加磅vserver lbwrite -dbslb enabled
加磅vserver lbread MSSQL -dbslb enabled
add csvserver csv MSSQL 1.1.1.10 1433
- Configure two listener services, one for each availability group, and five services S1 through S5 representing databases DB1 through DB4.
add service L1 1.1.1.11 MSSQL 1433
add service L2 1.1.1.12 MSSQL 1433
add service s1 1.1.1.13 MSSQL 1433
add service s2 1.1.1.14 MSSQL 1433
add service s3 1.1.1.15 MSSQL 1433
add service s4 1.1.1.16 MSSQL 1433
add service s5 1.1.1.17 MSSQL 1433
- Bind the services to the load balancing virtual servers.
bind lbvserver lbwrite L1
bind lbvserver lbwrite L2
bind lbvserver lbread s1
bind lbvserver lbread s2
bind lbvserver lbread s3
bind lbvserver lbread s4
bind lbvserver lbread s5
- Configure database users.
add db user nsdbuser1 -password dd260427edf
add db user nsdbuser2 -password ccd1234xyzw
- Configure two monitors, monitor_L1 and monitor_L2 for each listener service, to retrieve the list of active databases in that availability group. Add a monitor, monitor1 to retrieve the list of databases for the secondary database server instance.
加磅monitor monitor_L1 MSSQL-ECV -userName user1 -sqlQuery "SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id INNER JOIN sys.availability_group_listeners c on b.group_id = c.group_id INNER JOIN sys.availability_group_listener_ip_addresses d on c.listener_id = d.listener_id WHERE b.role = 1 and d.ip_address like '1.1.1.11'" -evalRule "MSSQL.RES.TYPE.NE(ERROR)” –storedb ENABLED
加磅monitor monitor_L2 MSSQL-ECV -userNameuser1 -sqlQuery "SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replicca_states b ON a.replica_id=b.replica_id INNER JOIN sys.availability_group_listeners c on b.group_id = c.group_id INNER JOIN sys.availability_group_listener_ip_addresses d on c.listener_id = d.listener_id WHERE b.role = 1 and d.ip_address like '1.1.1.12'" -evalRule "MSSQL.RES.TYPE.NE(ERROR)" -storedb ENABLED
加磅monitor monitor1 MSSQL-ECV -userNameuser1 -sqlQuery "SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id WHERE b.role = 2" -evalRule "MSSQL.RES.TYPE.NE(ERROR)" -storedb ENABLED
- Configure read and write policies.
add cs policy pol_write -rule "MSSQL.REQ.QUERY.TEXT.CONTAINS("insert")"
add cs policy pol_read -rule "MSSQL.REQ.QUERY.TEXT.CONTAINS("select")"
- Bind the policies to the content switching virtual server.
bind csvserver csv -targetLBVserver lbwrite -policyName pol_write -priority 11
bind csvserver csv -targetLBVserver lbread -policyName pol_read -priority 12
- Bind monitors to the services. Bind monitors to services L1 and L2 to get the list of active databases for the availability group for which it is the listener. Bind monitors to all the services that are bound to the read-only virtual server.
bind service L1 -monitorName monitor_L1
bind service L2 -monitorName monitor_L2
bind service s1 -monitorName monitor1
bind service s2 -monitorName monitor1
bind service s3 -monitorName monitor1
bind service s4 -monitorName monitor1
bind service s5 -monitorName monitor1
Configuration examples for MSSQL virtual server
To configure a load balancing virtual server for database specific load balancing:
加磅vserver DBSpecificLB1 MSSQL 192.0.2.10 1433 -dbsLb ENABLED Done show lb vserver DBSpecificLB1 DBSpecificLB1 (192.0.2.10:1433) - MSSQL Type: ADDRESS . . . DBS_LB: ENABLED Done
To configure services:
add servicemsservice1 5.5.5.5 MSSQL 1433
To configure a monitor to retrieve the names of all the active databases hosted on a service by using the command line:
加磅monitor mssql-monitor1 MSSQL-ECV -userName user1 -sqlQuery "select name from sys.databases where state=0" -evalRule "MSSQL.RES.TYPE.NE(ERROR)" -storedb EN Done show lb monitor mssql-monitor1 1) Name.......: mssql-monitor1 Type......: MSSQL-ECV ... Special parameters: Database.....:"" User name.....:"user1" Query..:select name from sys.databases where state=0 EvalRule...:MSSQL.RES.TYPE.NE(ERROR) Version...:70 STORE_DB...:ENABLED Done
Configuration examples for MySQL virtual server
To configure a load balancing virtual server for database specific load balancing:
加磅vserver DBSpecificLB1 MYSQL 192.0.2.10 3306 -dbsLb ENABLED Done show lb vserver DBSpecificLB1 DBSpecificLB1 (192.0.2.10:3306) - MYSQL Type: ADDRESS . . . DBS_LB: ENABLED Done
To configure services:
add service msservice1 5.5.5.5 MYSQL 3306
To configure a monitor to retrieve the names of all the active databases hosted on a service by using the command line:
加磅monitor mysql-monitor1 MYSQL-ECV -userName user1 -sqlQuery "show databases" -evalRule "MYSQL.RES.TYPE.NE(ERROR)" -storedb ENABLED Done show lb monitor mysql-monitor1 1) Name.......: mysql-monitor1 Type......: MYSQL-ECV State....: ENABLED ... Special parameters: Database.....:"" User name.....:"user1" Query..:show databases EvalRule...:MYSQL.RES.TYPE.NE(ERROR) STORE_DB...:ENABLED Done
In this article
- How Database Specific Load Balancing Works
- Enabling Load Balancing
- Configuring a Load Balancing Virtual Server for Database Specific Load Balancing
- To configure a load balancing virtual server for database specific load balancing
- Configuring Services
- Configuring Database Users
- Configuring a Monitor to Retrieve the Names of Active Databases
- Availability Groups Deployment Support for MSSQL
- Configuration examples for MSSQL virtual server
- Configuration examples for MySQL virtual server