Pre-requisites for Configuring an eG Database on a Microsoft SQL Server

Before even commencing the configuration process of the eG manager using an Microsoft SQL Server database, ensure that the following are in place:

  1. The Microsoft SQL Server should allow ‘unlimited’ concurrent connections

    Given below are the steps to be followed to fulfill this requirement on an Microsoft SQL Server 2014:

    • Login to the Microsoft SQL server that you want to use as the eG backend, as an administrator
    • Open the Microsoft SQL Server Management Studio.
    • In the Object Explorer that appears next, right-click on the node that represents the SQL server you are logged into, and then select the Properties option from the shortcut menu that appears.

      Figure 1 : Selecting the Properties option from the server shortcut menu

    • The Server Properties dialog box will then appear. From the Select a page panel in the left, select the Connections page, as depicted by Figure 2. The right panel will then change to display many options.

      Figure 2 : Setting the maximum concurrent user connections

    • In the right panel, make sure that the Maximum concurrent user connections field is set to unlimited (0).
    • Then, click the OK button to save the settings and to close the dialog box.
  2. Multi-protocol support should be configured on the Microsoft SQL server

    Ensure that Multi-protocol support is enabled on the Microsoft SQL server to be used as the backend for the eG manager. To ensure this, do the following:

    • Open the SQL Server Configuration Manager by following the menu sequence depicted by Figure 3.

      Figure 3 : Opening the SQL Server Configuration Manager

    • Figure 4 will then appear. Expand the SQL Native Client 11.0 Configuration node in the SQL Server Configuration Manager (Local) tree structure in the left panel of Figure 4. Then, click the Client Protocols sub-node within. The right panel will then change to display the client protocols and their current state – i.e., whether enabled/disabled.

      Figure 4 : Client protocols and current state

    • Make sure that all the three protocols displayed in the right panel of Figure 4 are Enabled. If any protocol is disabled, then, right-click on that protocol in Figure 4 and select the Enabled option from the shortcut menu that appears.
    • Next, expand the SQL Server Network Configuration node and select the Protocols for <SQL_Server_Name> sub-node within (see Figure 5). The right panel will then change to display the server protocols and their current state – i.e., whether enabled/disabled.
    • Make sure that all the three protocols displayed in the right panel of Figure 5 are Enabled. If any protocol is disabled, then, right-click on that protocol in Figure 5 and select the Enabled option from the shortcut menu that appears.

      Figure 5 : Server protocols and their current state

    • After you enable any client/server protocol, make sure the Microsoft SQL server is restarted.
  3. If you want to use a port-based Microsoft SQL server as the eG backend, then make sure that the IP address of the SQL server is configured with that port.

    • Open the SQL Server Configuration Manager by following the menu sequence depicted by Figure 3.
    • Figure 6 will then appear. Expand the SQL Server Configuration Manager node in the tree structure in the left panel of Figure 6, expand the SQL Server Network Configuration node, and click on the Protocols on <SQL_Server_Instance> option under it. The right panel will then change to display the network protocols that the chosen instance supports.

      Figure 6 : The network protocols supported by the chosen SQL server instance

    • Right-click on the TCP/IP protocol in the right panel and select the Properties option from the shortcut menu that pops out (see Figure 7).

      Figure 7 : Choosing to view the Properties of the TCP/IP protocol

    • Figure 8 will then appear. Click the IP Addresses tab page in Figure 8.

      Figure 8 : Viewing the Properties of the TCP/IP protocol

    • If the Microsoft SQL Server is configured with multiple IP addresses - say, one for every application that uses the server as its backend - then each of these IP addresses will be listed in the IP Addresses tab page (see Figure 9). Scroll down the tab page to identify the IP address that will be used by the eG manager application. Check whether any TCP Port has been configured for that IP address. If a port is already configured, then make sure that you set the same port as the Microsoft SQL Server port when configuring the eG database on this SQL server. If a port is not configured, then manually assign a port to this IP address. For this, click on the blank area corresponding to the TCP Port parameter in Figure 9 and then specify a port number here.

      Figure 9 : Assigning a TCP Port on the Microsoft SQL server for the eG manager application

      Alternatively, you can set a single port as the global port for all IP addresses on the Microsoft SQL server. To achieve this, scroll down the IP Addresses tab page until you find the IPAll section. Specify a TCP Port here, if no port pre-exists (see Figure 10). If a global port is already set, then when configuring the eG database on the Microsoft SQL server, make sure that this port is set as the SQL Server port.

      Figure 10 : Assigning a global TCP Port for all IP addresses on a Microsoft SQL server

  4. The SQL server should be configured to allow long-running queries.

    To ensure this, do the following:

    • Login to the Microsoft SQL server that you want to use as the eG backend, as an administrator.
    • Open the Microsoft SQL Server Management Studio.
    • In the Object Explorer that appears next, right-click on the node that represents the SQL server you are logged into, and then select the Properties option from the shortcut menu that appears (see Figure 1).
    • The Server Properties dialog box will then appear. From the Select a page panel in the left, select the Connections page, as depicted by Figure 11. The right panel will then change to display many options.
    • Make sure that the Use query governor to prevent long-running queries option in the right panel is disabled. If not, then uncheck the check box to disable it.

      Figure 11 : Enabling long-running queries to be executed on the Microsoft SQL server

    • Finally, click the ok button in Figure 11.

  5. If the Microsoft SQL Server 2008 is used as the eG backend, then ensure that the VIA protocol is disabled on the server

    To achieve this, do the following:

    • On the Microsoft SQL Server 2008 host, open the SQL Server Configuration Manager.
    • In the left panel of the configuration manager, click on the Protocols for <SQLSERVERname> node as depicted by Figure 12. The list of protocols that the SQL server supports will then be displayed in the right panel (see Figure 12).

      Figure 12 : The list of protocols on the SQL Server Configuration Manager

    • Check whether the status of the via protocol in the list is Enabled. If so, proceed to disable it by right-clicking on the via protocol and selecting the Disable option (see Figure 13).

      Figure 13 : Disabling the VIA protocol

    • Next, check whether the other protocols listed in the right panel of Figure 12 are Enabled. If not, then enable those protocols. 
  6. Ensure that the ‘SQL Server’ service is running on the SQL Server host.
  7. If the Microsoft SQL Server uses named instances (instead of port number), then, before configuring that server to function as the eG backend, make sure that the ‘SQL Browser service’ is up and running on the SQL Server host.