Noetix QueryServer (NQS) servers and connections
This chapter provides an introduction to the Noetix QueryServer (NQS) servers and connections, along with a high-level overview of working with these objects.
Overview
When NQS connects to a physical database or other data source to retrieve the data for a query, it must locate and log on to the server using valid credentials. NQS connects to the database using the location and credentials you supply as a server and connection. A server is a pointer to a particular physical database or other data source (for example, an Oracle or a Microsoft SQL Server database, or even an Excel spreadsheet), set up in NQS to let NQS know the data source exists. A server can point to a server pool as well as a single server.
To log on to a data source, NQS uses a connection. The connection contains logon credentials for the database and determines how NQS connects to the physical database. To create a connection, you can manually enter a user name and password into NQS, or use a more flexible method of determining the credentials based on the logged-on user, a User Lookup Map, or by prompting the user for the user name and password at run-time. For information about these options, see Comparison of Connection Types.
The Servers and Connections Wizard walks you through the task of setting up servers and connections. You need to have at least one server set up before you move on to other wizards.
Unless otherwise specified, the tasks in this chapter are performed using NQS Administrator.
Servers
A server tells NQS about a particular data source on your network. A server usually corresponds to a physical server unless you have multiple databases on a single computer. Server information consists of a directory path or database instance name, and the ODBC driver to be used to interact with the source.
A server can also correspond to more than one physical server; this is called a server pool.
Create a server to:
Set up a server, database, or other data source so you can then start working with its contents in NQS.
Server Groups
A server group is a collection of servers—you define how you want to group your servers to make them more manageable.
Create a server group to:
Logically organize your servers for easier administration.
Grant access to a server group, effectively granting access to every server and connection in the group, instead of manually to each individual server.
Server Pools
A server pool points to multiple data sources for load balancing purposes. Any server in NQS can be set up to behave as a server pool by adding multiple driver configurations. When queries are run against a server pool, NQS alternates between the enabled driver configurations, balancing the query requests in a round-robin fashion.
Each driver configuration must point to a physical server or instance with identical data for all objects referenced in NQS.
IMPORTANT: Server pools cannot contain databases of different types and versions. Otherwise, some queries might not work when routed to certain servers.
Create a server pool to:
Scale a server in NQS.
Utilize an offline data source (ODS) or backup server already maintained in your enterprise.
Provide an alternative for servers during down-time to keep queries from failing.
Help lessen the load on a server that is currently overutilized.
The NQS Monitor, as well as the performance statistics on the physical database, can help you determine how much query traffic a data source is getting. For more information about using the NQS Monitor, see the NQS Help.
Connections
A connection is set up to define the database user account with which NQS will log on to a particular server. A server can have multiple connections, for instance, one for each table owner within a database.
Create a connection to:
Define how NQS should log on to a server you set up.
Before You Beginfor your enterprise.
Install ODBC driver(s)
Determine server and table setup
Determine security needs
Obtain account information
Gather user information
Setup Tasks
Set up Servers and Connections
Set up Simple Virtual Tables
Set up Complex Virtual Tables (optional)
Set up users and Roles
Set up Virtual Views (optional)
After setting up and configuring NQS, refer to Chapter 8 for information on setting up and configuring NWQ.
Install ODBC drivers: Any ODBC driver that will be used to connect to an RDBMS must be installed and configured on the computer where the Noetix QueryServer service is running before setting up any objects from that data source. You should validate that the ODBC driver is working properly prior to using it within NQS. This will help isolate any potential issues you may encounter while setting up NQS.
Determine server and table setup: Talk to your network or database administrator and gather information about the database servers in your environment. You may need to know database names (for example, for SQL Server). NQS has quick setup routines for IBM DB2, SQL Server, and Oracle Database. Other ODBC-compatible databases may require additional connectivity data to set up.
You will also need to determine a naming convention for the servers and server groups. Your users will never see the servers and connections that you define in NQS, so you should name servers and server Groups in a way that helps you organize the data sources in your enterprise. If any of the servers you plan to connect to have a corresponding backup server or ODS that you would like to utilize, note this as well.
Obtain account information: Since a connection requires a valid user name and password for the target database, you will need to talk to the associated DBA to get this connection information for each server. This includes user names and passwords of database logons that have access to the tables you want to import into NQS.
Servers
Creating a Server
To set up a server, use the Servers and Connections Wizard in NQS Administrator. The wizard will walk you through each step of setting up your data source. If you need more information about any of the steps involved, click the Help button on any wizard page.
You will need to know connection information for this server. The ODBC driver that will be used must be installed and configured on the computer where the Noetix QueryServer service is running, and must support the SQLBrowseConnect function. For information about checking for the support of SQLBrowseConnect function, see the NQS Help.
To create a server
In NQS Administrator, click on the toolbar.
The Administrator Wizards dialog box appears. On the Basic Setup tab, double-click Setup Servers and Connections.
The Servers and Connections Wizard will begin and the welcome page appears. Click Next.
The Definitions page appears with definitions of NQS servers and connections. Click Next to continue.
The Options page appears. Select Add new server, and then click Next.
The Pick a Server Group page appears. Do one of the following:
If there is an existing server group that you want to add the new server to, click the server group. Click Next to continue.
If you want to create a new server group, click Add a new group. A new node will appear. Type the new name for the group, and then click Next to continue.
For more information about server groups, see Creating a Server Group.
The Server Name page appears. Enter the following information, and then click Next:
Server Name: Enter a name for the new server.
Connection: Defaults to a new connection. The name is created from the Server Name followed by _conn1. You can change this default information if you want.
Description: Enter a description for the new server.
Enable Server: To make this new server available, check this box.
The Choose RDBMS page appears. Select one of the following options:
If your data source is a Microsoft SQL Server, IBM DB2, or Oracle Database, select the type from the Quick Set-up Databases drop-down list. Click Next, and then continue on to step 10.
If your data source is something other than those in the drop-down list and is ODBC-compliant, select Other. Click Next, and then continue on to step 9.
If not using a quick setup database, the Choose ODBC Driver page appears. All ODBC drivers running on the NQS computer that support the SQLBrowseConnect function will be listed. Note that the NQS computer is not necessarily the computer from which you are running NQS Administrator.
Select a driver, and then click Next to continue.
If you do not see an ODBC driver listed here, it likely is not installed on the NQS computer or does not support the SQLBrowseConnect function. You will need to exit the wizard and set up the server manually. From the left-hand list in NQS, right-click the Servers item and click New > Server. Use the property sheet to define the new server.
The Get Connection Information page appears. Enter the following information, and then click Next. The fields will be marked Required if needed.CHECK SCREENSHOT TO SEE IF DIFFERENT FOR “OTHER” OPTION - This looks different in Annette’s NQS Tutorial - NQS Admin Help says “the information requested will be different for each driver”???.
Server: The name of the data source server. VERIFY THIS
Login ID: The logon ID for the data source.
Password: The password associated with the Login ID.
AppName: The name of the application.
WorkStation ID: The workstation ID.
A second Get Connection Information page may appear. Enter the information, and then click Next to continue. The fields will be marked Required if needed.CHECK THIS INFO
VERIFY THIS DISPLAYS EVERYTIME - Different in Annette’s tutorial. and if you add more, do 2 more screens appear??? Different in NQS Admin Help.The Driver Configurations page appears, displaying the default Driver Configuration. If you want to set up this server as a server pool, you can add additional Driver Configurations on this page.
To add a driver configuration, click on a blank line, then enter a name and the server-specific section of the ODBC connect string used to connect to the RDBMS (for example: SERVER=myserver; or SERVER=myserver/instance;).
Click Next to continue.
The last wizard page appears, displaying an overview of the server and connection you are creating. Review the information, and then click Finish. The new server and connection will now be created.
After the wizard completes setting up the new server and connection, you can verify the new objects were created as expected.
Navigate to the new server group (if created) and server under the Server root.
You can view the new connection information by clicking the connection in the upper right-hand of the page.
Creating a Server Group
You may want to organize your servers for easier maintenance or to more efficiently grant permissions. You can create as many server groups (collections of servers) as you would like, although each server can belong to only one group.
Create a server group to:
Logically organize your servers for easier administration.
Grant permission to a server group, effectively granting permission to every server and connection in the group, instead of manually to each individual server.
To create a server group
In NQS Administrator, right-click the Servers item in the left-hand list, then click New > Server Group. The New Server Group property sheet will display.
On the General tab, type a name for the new server group and a description, if required.
Click Save.
You can also create a server group using the Servers and Connections Wizard, during the process of creating a server.
Creating a Server Pool
Setting up a server pool is done by creating a server with multiple driver configurations. You can add these configurations while creating the server or at any time afterward.
IMPORTANT: Server pools cannot contain databases of different types and versions. Otherwise, some queries might not work when routed to certain servers.
Create a server pool to:
Utilize an offline data source (ODS) or backup server already maintained in your enterprise.
Provide an alternative for servers during down-time to keep queries from failing.
Help lessen the load on a server that is currently overutilized.
The NQS Monitor, as well as the performance statistics on the physical database, can help you determine how much query traffic a data source is getting. For more information about using the NQS Monitor, see the NQS Help.
To create a server pool
In NQS Administrator, navigate to an existing server, double-clicking it in the Navigator window so its property pages are displayed.
Click the Driver Configurations tab to open it.
In a blank line in the list of driver configurations, click the Name field. Enter a name for the driver configuration.
Click the Driver Configuration field, then enter the driver configuration. The format of this should be the server-specific section of the ODBC connect string used to connect to the RDBMS (for example: SERVER=myserver; or SERVER=myserver/instance;).
Check the Enabled box to make this server available.
Click the Save button.
You can also add multiple driver configurations when first creating a server using the Servers and Connections Wizard. On the Driver Configurations page, click on a blank line, then enter a name and the server-specific section of the ODBC connect string used to connect to the RDBMS (for example: SERVER=myserver; or SERVER=myserver/instance;).
Connections
At run-time, after the Noetix QueryServer service has transformed an end user’s logical query into a physical query, it connects to the physical database and runs the query. The type of connection being used determines how Noetix QueryServer (NQS) connects to the physical database. For a comparison of the connection types, see Comparison of Connection Types.
The Default Connection: The first type of connection is called a general or default, connection. To create any connection, you must provide the default user information. This is a valid user name and password combination for the server you are creating this connection for. This user will also typically be the user that owns the tables, views, procedures, and so on.
If you leave the Use Default User Information button selected, NQS will connect to the physical database using the Default User Information. From the database's point of view, this means that this will be the only user connected to the database regardless of how many NQS users employ this connection.
The Pass Through Connection: When you choose the Pass Through option for a connection, NQS sends the exact information that the end user used to log on to NQS to the database. This can be helpful if you already have a database application developed that uses the native database security.
The Prompt Connection: When you choose the Prompt option for a connection, NQS will prompt end users for connection information whenever they use a virtual table (VTable) that uses this connection. A window is displayed prompting for a valid user name and password.
This connection type should be used sparingly because of the added burden on the end user.
Do not use this connection type in conjunction with NWQ or any other web-based reporting tool. You also should not use this connection type if you are accessing NQS from within the code of a custom application, for example, Visual Basic and Active Server Pages.
The Lookup Connection: When you choose the Lookup option for a connection, you must build a User Lookup Map that maps an NQS user to a database user. When this connection is used, NQS attempts to look up the information it should use to connect to the physical database.
Each server can have a User Lookup Map that can be used by all of its connections. You provide the DB User Name and DB Password that you want NQS to use if a particular user uses any connection of the lookup type. If you do not want to store the DB Password in the NQS Repository or if you do not know it, you can check the box Prompt User. Every time the user accesses the connection, he or she will be prompted for a password. You can also set this up as a pass through connection, and the password the user entered at logon will be passed to the connection being used.
This type of connection can be useful if NQS Administrator wants users to log on to the corporate databases using different database user accounts. If the Prompt User option is not used, this can be completely transparent to the end user.
All passwords are encrypted before they are stored in the NQS Repository.
Comparison of Connection Types
The following table discusses the different connection types and lists the pros and cons of each:
Table 1.1 Comparing the Four Connection Types
Creating a Connection
When you set up a server using the Servers and Connections Wizard, you set up a default connection as well. If you want to create additional connections to the server, you can run the wizard again, using the following procedure.
Create a connection to:
Define how NQS should log on to a server you set up.
To create an additional connection
In NQS Administrator, click on the toolbar. The Administrator Wizards dialog box appears.
On the Basic Setup tab, double-click Setup Servers and Connections. The Servers and Connections Wizard will begin.
After the introductory pages, the wizard will ask you whether you want to add a new server or add a connection to an existing server. Select the button next to Add a connection to an existing server. Click Next to continue.
Select the server group, then the server you want to add a connection to. Click Next to continue.
For a quick setup, select the Typical option and enter the user name and password. Click Next, then skip to the last step of these instructions (step 8).
If you want to specify a certain ODBC driver or language for the connection, instead click Custom, then click Next to continue.
For a custom setup, enter the required information, then click Next.
Enter any other required connection information, then click Next. The required information will depend on the type of data source.
The last page shows you an overview of the connection you are creating. Click Finish.