Set up a query routing policy group

You can leverage the query routing feature to route to a database server or to a data segment of a database; alternatively, you can choose to do both. Note that in the event of a conflict, Read/Write split decision takes precedence over a query routing decision. For example, if a stored procedure matches a routing rule which has only read database node(s) but if that stored procedure is not part of the Write Ignore rule it does not get sent to the read database node; instead the system generates a custom error message for the client. If all the database nodes matching the query routing rule are marked unhealthy then the normal queuing logic kicks in.

1. On the ScaleArc dashboard, click Clusters > Settings > Load Balancing and Routing.
   
    
    
2. Click on the Query Routing Policies tab.
   
    
   
   
3. Turn ON the Query Routing option.
4. Add a query-routing rule by selecting the database(s), user(s), and one or more IP addresses. This creates a group to which you can add more granular rules as per you requirement by providing the exact query pattern.
5. Click Add.
6. Click the Gear icon against the policy group of choice to edit the rules.

Create specific query-routing policy rules

You can create specific rules manually to direct SQL statements to databases or data segments of databases if they are very complex and specific to your requirements. Alternatively,  you can use the ScaleArc GUI to generate a query-routing rule for a specific data segment.

Set the rule manually

Follow these steps to manually create a rule for query-routing to databases or to a data segment:

1. Click the Gear icon against the database of choice to edit the rules.
   
   
   
   
2. Turn ON Rule Status.
3. Click Add.
   
    
   
    

4. Complete the entries as follows:
   

   Field/Button	Description	User input
   Order	Specifies the order in which the SQL statment should be executed. Note that you cannot edit the order number when you define a policy group. You can edit the order number for the rule associated with a policy.	Enter the order.
   Source Pattern	The query routing pattern.	Enter the query routing pattern.
   Route To	The target destination for the pattern. You have a choice to send the SQL statements to Read-Only or to a Read/Write server. Once the SQL statement gets matched with the rule it gets diverted to the database server, based on the type of server added in the cluster.   Tip Refer to your cluster configuration to confirm which IP is Read-Only or Read/Write. A destination cannot have both role-based and IP-based values in a single rule.	Route the SQL statement to an IP address or a type of server.
   Check box	Indicates whether the rule is enabled or disabled.	Select the checkbox to enable.
   AND/OR rule	The AND rule uses the default load-balancing logic that has been defined in the cluster. The database servers do not have any priority, with respect to the route. The SQL statements are load-balanced among all the selected database nodes.  The OR rule is a prioritized list of servers. The specified order indicates the priority in which a database server is chosen to route the SQL statement. The SQL statements are always sent to first database node in the list if it's healthy; otherwise it's sent to next database node in the list. And/Or field cannot be edited when the Route to field has role-based ( Read + Write , Read Only) values.	Choose a rule type.

   
   

   
   

5. Click Save.

Set policy precedence 

Rules from all policy groups in the query-routing module are put in a single linear list, sorted in the ascending order of the order value, irrespective of the policy group. Use the rule-editing function to set policy precedence for executing an order when you have overlapping policies (for example, when you have multiple policies with rules that have the same order numbers). 

Consider the following two policy groups (Policy group 1 and Policy group 2):

Policy group 1

If you want the rule(s) in this group to be evaluated first, before checking in the next policy group(s), for example Policy group 2, assign the order value of the routing rule(s) lower then the values assigned to the rules in the other policy group(s). For example, assign the order values for rules in this group from 1 to 100.

Policy group 2

To ensure the rule(s) in Policy group 2 are evaluated/tested after Policy group 1, enter an order value higher then the other group(s). For example, assign order values for rules in this group from 101 to 200.

Follow these steps to create shard (data segmentation) rules:

1. Click the Gear icon in the Add Rule screen to open the screen.
   
   
   
   
2. Select Generate Shards and configure appropriately. 
   
   
   
   
3. Click Generate Shards to open the screen. 
    
   
   
   

4. Then, complete the entries as follows: 

   Item	Description	User input
   Database column	Select the database.	Select a database from the drop down.
   Order	Specifies the order in which the SQL statement should be executed.	Enter the order.
   Table column	The database table to which the SQL statement is routed.	Select from a drop down menu.
   Key column	The table column key for the query routing rule.	Select from a drop down menu.
   Number of shards	The number of data segments you would like to create.	Enter a number.
   From and To range	Entries allow the key column to generate routing patterns.	Specify the range.
   Route To	The target destination for the pattern. You have a choice to send the queries SQL statement to Read-Only or to a Read/Write server. Once the query gets matched with the rule it gets diverted to the database server, based on the type of server added in the cluster.   Tip Refer to your cluster configuration to confirm which IP is Read-Only or Read/Write. A destination cannot have both role-based and IP-based values in a single rule.	Route the SQL statement to an IP address or a type of server.
   Checkbox	Indicates whether the rule is enabled or disabled.	Select the checkbox to enable.
   AND/OR rule	The AND rule uses the default load-balancing logic that has been defined in the cluster. The database servers do not have any priority, with respect to the route. The SQL statements are load-balanced among all the selected database nodes.  The OR rule is a prioritized list of servers. The specified order indicates the priority in which a database server is chosen to route the SQL statement. The SQL statements are always sent to first database node in the list if it's healthy; otherwise it's sent to next database node in the list.  And/Or field cannot be edited when the Route to field has role-based ( Read + Write , Read Only) values.	Choose a rule type.

   
   

   
   

5. Click Generate Rules and save.