Grid for SQL Data Services and Web Services

SQL data services

When you connect to an SQL data service from a third-party client tool to run queries against the service, the Data Integration Service dispatches the connection directly to a worker service process. To ensure faster throughput, the Data Integration Service bypasses the master service process. When you establish multiple connections to SQL data services, the Data Integration Service uses round robin to dispatch each connection to a worker service process. When you run multiple queries against the SQL data service using the same connection, each query runs on the same worker service process.

Web services

When you submit a web service request, the Data Integration Service uses an external HTTP load balancer to distribute the request to a worker service process. When you submit multiple requests against web services, the Data Integration Service uses round robin to dispatch each query to a worker service process.

To run web service requests on a grid, you must configure the external HTTP load balancer. Specify the logical URL for the load balancer in the web service properties of the Data Integration Service. When you configure the external load balancer, enter the URLs for all nodes in the grid that have both the service and compute roles. If you do not configure an external HTTP load balancer, web service requests are not distributed across the nodes in the grid. Each web service request runs on the node that receives the request from the web service client.

Previews

When you preview a stored procedure output or virtual table data, the Data Integration Service uses round robin to dispatch the first preview query directly to a worker service process. To ensure faster throughput, the Data Integration Service bypasses the master service process. When you preview additional objects from the same login, the Data Integration Service dispatches the preview queries to the same worker service process.

Example Grid that Runs Jobs in the Service Process

• •On Node1, the master service process manages application deployment and logging. The master service process also acts as a worker service process and completes jobs. The Data Integration Service dispatches a preview request directly to the service process on Node1. The service process creates a DTM instance to run the preview job. SQL data service and web service jobs can also run on Node1.
• •On Node2, the Data Integration Service dispatches SQL queries and web service requests directly to the worker service process. The worker service process creates a separate DTM instance to run each job and complete the request. Preview jobs can also run on Node2.
• •On Node3, the Data Integration Service dispatches two preview requests from a different user login than the preview1 request directly to the worker service process. The worker service process creates a separate DTM instance to run each preview job. SQL data service and web service jobs can also run on Node3.

Rules and Guidelines for Grids that Run Jobs in the Service Process

• •If the grid contains nodes with the compute role only, the Data Integration Service cannot start.
• •If the grid contains nodes with the service role only, jobs that are dispatched to the service process on the node fail to run.
• •Configure environment variables for the Data Integration Service processes on the Processes view for the service. The Data Integration Service ignores any environment variables configured on the Compute view.

Configuring a Grid that Runs Jobs in the Service Process

1. 1. Create a grid for SQL data service and web service jobs.
2. 2. Assign the Data Integration Service to the grid.
3. 3. Configure the Data Integration Service to run jobs in the service process.
4. 4. Configure load balancing for web services.
5. 5. Configure a shared log directory.
6. 6. Optionally, configure properties for each Data Integration Service process that runs on a node in the grid.
7. 7. Optionally, configure compute properties for each DTM instance that can run on a node in the grid.
8. 8. Recycle the Data Integration Service.

Step 1. Create a Grid

1. In the Administrator tool, click the Manage tab.
2. Click the Services and Nodes view.
3. In the Domain Navigator, select the domain.

4. On the Navigator Actions menu, click New > Grid.
The Create Grid dialog box appears.
5. Enter the following properties:

Property	Description
Name	Name of the grid. The name is not case sensitive and must be unique within the domain. It cannot exceed 128 characters or begin with @. It also cannot contain spaces or the following special characters: ` ~ % ^ * + = { } \ ; : ' " / ? . , < > | ! ( ) ] [
Description	Description of the grid. The description cannot exceed 765 characters.
Nodes	Select nodes to assign to the grid.
Path	Location in the Navigator, such as: DomainName/ProductionGrids

6. Click OK.

Step 2. Assign the Data Integration Service to the Grid

1. On the Services and Nodes view, select the Data Integration Service in the Domain Navigator.
2. Select the Properties tab.
3. In the General Properties section, click Edit.
The Edit General Properties dialog box appears.
4. Next to Assign, select Grid.
5. Select the grid to assign to the Data Integration Service.
6. Click OK.

Step 3. Run Jobs in the Service Process

1. On the Services and Nodes view, select the Data Integration Service in the Domain Navigator.
2. Select the Properties tab.
3. In the Execution Options section, click Edit.
The Edit Execution Options dialog box appears.
4. For the Launch Job Options property, select In the service process.
5. Click OK.

Step 4. Configure Load Balancing for Web Services

1. Complete the following steps in the Administrator tool to configure the Data Integration Service to communicate with the external HTTP load balancer:
1. a. On the Services and Nodes view, select the Data Integration Service in the Domain Navigator.
2. b. Select the Properties tab.
3. c. In the Web Service Properties section, click Edit.
The Edit Web Service Properties window appears.
4. d. Enter the logical URL for the external HTTP load balancer, and then click OK.
2. Configure the external load balancer to distribute requests to all nodes in the grid that have both the service and compute roles.

Step 5. Configure a Shared Log Directory

1. On the Services and Nodes view, select the Data Integration Service in the Domain Navigator.
2. Select the Processes tab.
3. Select a node to configure the shared log directory for that node.
4. In the Logging Options section, click Edit.
The Edit Logging Options dialog box appears.
5. Enter the location to the shared log directory.
6. Click OK.
7. Repeat the steps for each node listed in the Processes tab to configure each service process with identical absolute paths to the shared directories.

Related Topics:
• Log Directory

Step 6. Optionally Configure Process Properties

Related Topics:
• Data Integration Service Process Properties

Step 7. Optionally Configure Compute Properties

Related Topics:
• Data Integration Service Compute Properties

Step 8. Recycle the Data Integration Service