Product Documentation
ADC
14.1 - Current Release 13.1 13.0 12.1
View PDF

Use Case 4: Database specific load balancing

August 1, 2023
Contributed by: 
S

A database server farm must be load balanced not only 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 NetScaler 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 NetScaler 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

For database specific load balancing, you configure a monitor that periodically queries each database server for the names of all the active databases on it. The NetScaler 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.

Enable 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.

Enable load balancing by using the CLI

At the command prompt, type the following command to enable load balancing and verify the configuration:

enable ns feature LB

show ns feature
<!--NeedCopy-->

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
<!--NeedCopy-->

Enable load balancing by using the GUI

Navigate to System > Settings and, in Configure Basic Features, select Load Balancing.

Configure 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 so that the NetScaler appliance refers the results of the monitoring probe sent to the selected service, before forwarding the query to that service.

Configure a load balancing virtual server for database specific load balancing using the CLI

At the command prompt, type the following command to configure a load balancing virtual server for database specific load balancing and verify the configuration:

add lb vserver <name> <serviceType> <ipAddress> <port> -dbsLb ENABLED

show lb vserver <name>
<!--NeedCopy-->

Configure 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 NetScaler 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.

Configure database users

In databases, a connection is always stateful, which means that when a connection is established, it must be authenticated.

Configure your database user name and password on the NetScaler. For example, if you have a user John configured on the database, you need to configure the user John on the ADC too. Database user names and passwords added to the ADC 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.

Add a database user by using the CLI

At the command prompt, type

add db user <username> - password <password>
<!--NeedCopy-->

Example:

add db user nsdbuser -password dd260427edf
<!--NeedCopy-->

Add a database user by using the GUI

Navigate to System > User Administration > Database Users, 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 NetScaler appliance.

Reset the password of a database user by using the CLI

At the command prompt, type

set db user <username> -password <password>
<!--NeedCopy-->

Example:

set db user nsdbuser -password dd260538abs
<!--NeedCopy-->

Reset the password of database users by using the GUI

Navigate to System > User Administration > Database Users, 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 NetScaler 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.

Remove a database user by using the CLI

At the command prompt, type

rm db user <username>
<!--NeedCopy-->

Example:

rm db user nsdbuser
<!--NeedCopy-->

Remove a database user by using the GUI

Navigate to System > User Administration > Database Users, select a user, and click Delete.

Configure 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
<!--NeedCopy-->

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.

Configure a monitor to retrieve the names of all the active databases hosted on a service by using the CLI

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:

add lb monitor <monitorName> <type> -userName <string> -sqlQuery <text> -evalRule <expression> -storedb ENABLED

show lb monitor <monitorName>
<!--NeedCopy-->

Configure a monitor to retrieve the names of all the active databases hosted on a service by using the GUI

  1. Navigate to Traffic Management > Load Balancing > Monitors and configure a monitor of type MSSQL-ECV or MYSQL-ECV.
  2. In Special 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.

HA deployment SQL

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 the Availability Group
AV1 DB1, DB2 S1, S4, S5
AV2 DB3, DB4 S1, S2, S3

Queries flow as follows:

  1. A READ query for AV1 is load balanced between S4 and S5. S1 is the primary for AV1.
  2. A WRITE query for AV1 is directed to L1.
  3. A READ query for AV2 is load balanced between S1 and S3. S2 is the primary for AV2.
  4. A WRITE query for AV1 is directed to L2.

Sample configuration

  1. Configure load balancing and content switching virtual servers.
    • add lb vserver lbwrite -dbslb enabled
    • add lbvserver lbread MSSQL -dbslb enabled
    • add csvserver csv MSSQL 1.1.1.10 1433
  2. 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
  3. 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
  4. Configure database users.
    • add db user nsdbuser1 -password dd260427edf
    • add db user nsdbuser2 -password ccd1234xyzw
  5. 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.
    • add lb 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
    • add lb 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
    • add lb 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
  6. 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")"
  7. 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
  8. 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:

add lb 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
<!--NeedCopy-->

To configure services:

add service msservice1 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:

add lb 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
<!--NeedCopy-->

Configuration examples for MySQL virtual server

To configure a load balancing virtual server for database specific load balancing:

add lb 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
<!--NeedCopy-->

To configure services:

add service msservice1 5.5.5.5 MYSQL 3306
<!--NeedCopy-->

To configure a monitor to retrieve the names of all the active databases hosted on a service by using the command line:

add lb 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
<!--NeedCopy-->
Use Case 4: Database specific load balancing
August 1, 2023
Contributed by: 
S
August 1, 2023
Contributed by: 
S

In this article

Site feedback | Your privacy choices footer linkYour Privacy Choices | Privacy and legal terms | Cookie preferences | Consent settings | docs.cloud.com
© 1999- Cloud Software Group, Inc. All rights reserved.