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

Before even commencing the configuration process of the eG manager using a 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 a 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. 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 will then appear. Expand the SQL Server Configuration Manager node in the tree structure in the left panel of Figure 3, 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 3 : 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 4).

     

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

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

     

     Figure 5 : 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 6). 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 6 and then specify a port number here.

     

     Figure 6 : 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 7). 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 7 : Assigning a global TCP Port for all IP addresses on a Microsoft SQL server

3. Ensure that the ‘SQL Server’ service is running on the SQL Server host.

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

   The eG manager requires a special database user account for storing measures in the eG database. You can:

   1. Instruct eG manager setup to automatically create this user account, (OR);

   2. Instruct eG manager setup to use an account that pre-exists for this purpose, (OR);

   3. Manually create a special account for monitoring purposes, and configure the eG manager to use this account;

   In the case of options (b) and (c) above, make sure that you choose or create a user with the following privileges to the Microsoft SQL database that you set as the eG database:

   • DBOwner

   • VIEW SERVER STATE

   • VIEW ANY DEFINITION