Deciding Between Direct OLEDB Connections or Routing Through an External Database

Document created by kduffy on Nov 5, 2018Last modified by kduffy on Aug 14, 2019
Version 8Show Document
  • View in full screen mode

Business Case:

When connecting an application to the PI System for SQL data access, there are multiple architecture options, but there are three most common options:

  1. Connecting directly from the application to the PI System
  2. Brokering the connection through a Microsoft SQL Server linked server (other third party database engines may have similar options; this course will focus on the MS SQL Server's Linked Server)
  3. Pushing the data (shaped and filtered) to an external database and having clients query that data source instead

 

There are pros and cons for each route, and the correct decision depends entirely on the given situation.

 

Options:

 

Direct Connection from the Application to the PI System

  1. The application connects directly to the PI System and requests the data
    • This can use any of the PI SQL Framework products, such as PI SQL Client, PI OLEDB Provider, PI OLEDB Enterprise, etc
  2. The PI System serves the data to the application

 

Broker the Connection Through a Microsoft SQL Server via a Linked Server

  1. The application connects to a SQL Server as if the SQL Server is the data source itself
    • This will probably use either the MS OLE DB Provider for SQL Server or the MS SQL Native Client
  2. The SQL Server connects to the PI System on behalf of the application, requesting the data the client has requested
    • This can use any of the PI SQL Framework products, such as PI SQL Client, PI OLEDB Provider, PI OLEDB Enterprise, etc
  3. The PI System serves the data to the SQL Server
  4. The SQL Server serves the data to the application

 

Push the Data (Shaped and Filtered) to an External Database to be Queried by the Client

This involves two separate data flows:

  • Pushing the data
    1. The PI data from the PI System is shaped and filtered before being pushed an external database (such as a MS SQL Server)
  • Querying the data
    1. The application connects to a SQL Server as if the SQL Server is the data source itself
      • This will probably use either the MS OLE DB Provider for SQL Server or the MS SQL Native Client
    2. The SQL Server serves the data to the application

 

Overview:

The following table provides an overview of the pros and cons of each options. The sections that follow go into more detail about each item:

ItemDirect OLEDB ConnectionLinked Server
Push Data to External Database
NetworkApp server ports open to PI SystemApp server ports open to SQL; SQL Server ports open to PI SystemApp server ports open to SQL; SQL Server ports open to PI System
SecurityPI Security is used (WIS, Trust, Explicit)Additional layer of SQL Security (WIS, SQL login) adds functionality and complexityPI Security only pertains to the data extraction service. Data security at the application level is handled entirely by the external database engine
Client InstallationsPI SQL needs to be installed and maintained on all app serversPI SQL needs to be installed and maintained on SQL Server; MS SQL providers installed by default on clientsPI software needs to be installed and maintained on server running the data extraction service; MS SQL providers installed by default on clients
Machine Resources

Machine resources consumed at runtime.

If using PI OLEDB Provider/Enterprise, app server is thick client

If using RTQP, app server is thin client

Machine resources consumed at runtime.

Regardless of query engine, app server is thin client

If using PI OLEDB Provider/Enterprise, SQL Server is thick client

If using RTQP, SQL Server is thin client

Machine resources consumed at scheduled extraction time.

At runtime, app server is thin client retrieving data from external database

Data UpdatesThe PI System serves every request, so data updates will be seen automatically by the clientThe PI System serves every request, so data updates will be seen automatically by the clientThe data extraction mechanism is responsible for updating the external data source with new or edited data. The application will see stale data until the refresh mechanism has taken place.
CompatibilityRequires app to allow generic OLEDB data sourcesOnly requires app to allow connections to SQL Servers (more common)

Only requires app to allow connections to external database engine

Does not require database engine to be MS SQL Server (or any other

 

Note: All below diagrams assume Microsoft SQL Server as the external database engine. Where it says "application", this can be generalized to any database engine that fits the functionality described.

 

Network:

When using a direct connection, the application will need to be able to connect directly to the PI System using the necessary ports.

 

If your network does not allow for this connection from the app server directly, then going through a SQL Server (linked server or not) allows for the usage of a different network route which may alleviate this concern:

  • Note: the exact port usage depends on the specific architecture, such as SQL Server connecting to PI System directly, or having a one way data push from the PI System to the SQL Server

 

Security:

When connecting directly to the PI System, the application will be authenticating with the PI System using one of the available methods (Windows Integrated Security for the AF Server, and WIS, Trusts, or Explicit login for the PI Data Archive).

 

When routing through an external database or linked server, the application now has the option of still using Windows Integrated Security to connect to the SQL Server, which would then be passed to the PI System, or using a SQL Server login which could be configured to pass certain credentials to the PI System on its behalf for the second step. This added layer can add some functionality but it also adds a configuration burden.

 

Client Installations:

When connecting directly from the application to the PI System, the PI SQL endpoint being used (PI SQL Client, PI OLEDB Provider, PI OLEDB Enterprise, PI ODBC Driver, PI JDBC Driver) needs to be installed and maintained on every application server.

 

This isn't typically a problem, but the deployment and upgrade task may be a burden for some IT departments, especially on a wide deployment across a large enterprise, or a third party provider may simply not be allowed to be installed due to IT policies for this server. In either case, the SQL Native Client is typically installed by default on a Windows operating system and updated by routine Windows patches. Configuring the application to route through a linked server could alleviate these concerns if they apply to your situation.

 

Machine Resources:

Options 1 and 2:

When using the PI SQL Client (or PI ODBC or JDBC Drivers), the PI SQL endpoint is a thin client. Regardless of where this endpoint is located, whether on the application server or the SQL Server, the AF Server is handling the load and all other servers are considered thin clients:

 

However, when the PI OLEDB Provider or PI OLEDB Enterprise are used, the endpoint is a thick client as it's doing the query processing and handling the PISDK or AFSDK communication to the PI System. In this situation, the application server can be turned into a thin client by using a linked server, relocating the processing burden to the SQL Server as well as the bandwidth burden to the SQL - PI system connection.

 

Note: When analysis outputs are queried, using the AF Analysis service to output to a PI tag can significantly speed up data retrieval at query runtime. Analysis data reference attributes will always be recalculated on demand so can result in substantial processing load placed on the query processing engine. See the following two KBs for more details:

 

Option 3:

The topic of machine resources is where the split between option 2 (linked server) and 3 (external data storage) occurs.

 

At runtime, the data is already shaped and filtered so the SQL Server (or similar) is most likely going to have an easy time serving the data to the client, and there will not be heavy processing loads anywhere:

 

At data extraction time, the processing load is dependent on the exact architecture and product selection (the diagram below, for example, assumes the SQL Server is running the extraction and is using PI SQL Client).

 

The point of note is that the processing is done one time to extract the PI data, translate/shape it into the final desired form, and load it into the external database. This requires processing power but is handled on a scheduled basis by servers and services that can be designed to handle it, and not done at a time when the query output is waiting on it. Done efficiently, this can result in fast queries for even complex analysis outputs.

 

Data Updates:

 

When displaying the data in the application, it's important to know if this data is up to date, and how to update stale data if it exists.

 

Options 1 and 2:

For the connections to the PI System (either by the application itself [Option 1] or by the SQL Server's linked server [Option 2]), the query output is recalculated every time the query is executed. This means that if the application refreshes the query after the data on the PI System is updated, the application will show the updated data.

Note: The following diagrams use Option 2, but they hold true for Option 1 if the MS SQL Server layer is simply removed.

 

  1. Application requests data from the PI System
  2. PI System sends the current data
  3. Data is updated on the PI System
  4. Application refreshes the query
  5. Application receives the newly updated data

 

Note: When analysis outputs are queried, using the AF Analysis service to output to a PI tag can prevent queries from returning updated values. If the historical values of the input tags are altered, these analysis output tags will need to be backfilled. Analysis data reference attributes will always be recalculated on demand so are always showing the most to date value. For further information, see the following KBs:

 

Option 3:

For the external data storage option, the query is evaluated against the external database engine and not against the PI System. This means that if the application refreshes the query after the data on the PI System is updated, the application will show stale data until the data extraction process is able to update the data on the external database. Once this has happened, then the application will show the updated data.

 

This issue can be mitigated by adjusting the frequency of the data extraction process (once per day, once per minute, etc)

 

  1. Data is pushed to the external database during a data extraction cycle
  2. Application requests data from the external database
  3. External database sends the current data
  4. Data is updated on the PI System
  5. Application refreshes the query
  6. Application receives the now stale data
  7. Data is pushed to the external database on the next extraction cycle
  8. Application refreshes the query
  9. Application receives the stale data

 

Note: This update cycle assumes that the data extraction process is capable of handling any change to the data. These changes can mean new values added or old values are changed.

 

Compatibility:

Many applications allow for any external OLEDB data source. In this case, the application can connect directly to the PI System:

 

Some applications, however, were not developed to connect to any generic OLEDB data source, but instead can only connect to specific data sources, such as MS SQL Servers. If this is the case with your application, then a linked server will allow for the PI System to still be used as a data source, while the external data storage is also possible.

1 person found this helpful

Attachments

    Outcomes