OpenEdge to SQL Server DB ODBC Driver - Forum - OpenEdge Development - Progress Community

OpenEdge to SQL Server DB ODBC Driver

 Forum

OpenEdge to SQL Server DB ODBC Driver

This question is answered

Hi, everyone. 

I have a project requirement that needs an OE 11.7 codebase (Red Hat Linux 7.6) running against a OE 11.7 database (also Red Hat Linux 7.6) to connect to a SQL Server database (Windows Server 2016) and be able to view the SQL Server schema as a native Progress 4GL application would. 

Can someone please recommend a non-JDBC approach to doing this? Ideally an ODBC driver that allows me to do this would be fantastic. If there is such an ODBC driver, please point me to what that is and how to obtain it. 

Thank you! 

v

Verified Answer
  • >> I have never seen a dataserver match a well constructed client/server ABL with native Progress DB installation

    Have you ever looked for it or tested dataserver for yourself?  SQL Server is born-and-raised as a client-server database.  Data is transmitted to the client in an entire batch of rows, rather than communicating with a bunch of tiny TCP exchanges that contain a single row or a single column or a single byte at a time.   Server-side *join* functionality is available in progress dataserver (see PS), as well as other sophisticated features like server-side stored *procedures*.

    On the other hand when you are using client-server against a native Progress DB, you have to format your FOR-EACH-NO-LOCK loops in precisely the correct way for data to be transferred in larger batches. Otherwise you end up with 2-3 network round-trips per *record* (capping out at about 2-3000 records per second on the LAN).  Server-side joins were never possible (until OE 12 - and even here I suspect it is based on a certain set of special circumstances).   

    I only evaluated SQL Dataserver for a short period of time, but my experience with it was very positive.  Progress put some serious talent into the development of DataServer.  I ran it in the dataserver-self-service mode with a schema-holder that was available on the local SSD (-RO -1) of the machine running the AVM client sessions.  There was only one hop between the ABL logic and the SQL Server database.  I never had the feeling that the database was "holding back" on my requests. (Whereas the Progress DB, on the other hand, seems to spend a lot of its time *pausing* between time slices - even when there is more work waiting to be done.  And there is effectively a CPU *quota* that you see yourself running into - which is easily observed in the CPU usage of the _mprosrv processes.).

    Hope this is clear.  Again, whether you benefit from the advantages of dataserver may depend on how the ABL code is written, and how much data is used.  If your application interacts with only a handful of database records on each screen then you won't have any performance challenges to begin with, no matter what the underlying technologies are.

    David

    PS.

    Here is an old screenshot showing that dataserver has long had the ability to do server-side query processing. 

    ... from ..

    http://www.pugchallenge.us/downloads2016/450%20-%20DataServer%20Best%20Practices%20and%20Performance%20Considerations%20with%20OpenEdge%2011.pptx

All Replies
  • you need the Progress DataServer for Microsoft SQL add-on.

    this product makes the SQL Server database look like an OpenEdge database to your 4GL code.

  • Thanks, Gus. With that approach, is it possible to use any Progress 4GL language feature as you would with a native Progress RDBMS database, or are there some limitations?

  • Short Answer is yes, however, there may be SQL schema constructs that are not supported by the Database schema . You will find out quickly enough by importing the SQL schema into the Dataserver schema holder. Also, actually processing and speed may not equate to how a native Progress DB & 4GL interact. Also, your Dataserver Install will need to be on a SQL Server box, although your schema holder can live on Linux. This could introduce additional lag due to network traffic.

  • Check availability guide for Opededge Version to MS SQl Server version for the dataserver. Dataserver is a separate licensed product.  The Migration tools only work when you have a Development product installed also..

  • "Also, your Dataserver Install will need to be on a SQL Server box".  Sorry, this should be on a Windows Server box, that could be be the SQL Server box.

  • >> processing and speed may not equate to how a native Progress DB & 4GL interact.

    I played with it briefly for a time.  Depending on how the code is written, performance can be equal to or better than client/server ABL on a native Progress DB.  There are a lot of moving parts to configure.  I had an advantage in that everything was running in windows, and dataserver was runing in self-service mode.

    See:

    docs.progress.com/.../Additional-Features-to-Enhance-DataServer-Performance.html

  • "Depending on how the code is written, performance can be equal to or better than client/server ABL on a native Progress DB."  As Gus reminds us, Your Mileage May Vary with any configuration.

  • >> I have never seen a dataserver match a well constructed client/server ABL with native Progress DB installation

    Have you ever looked for it or tested dataserver for yourself?  SQL Server is born-and-raised as a client-server database.  Data is transmitted to the client in an entire batch of rows, rather than communicating with a bunch of tiny TCP exchanges that contain a single row or a single column or a single byte at a time.   Server-side *join* functionality is available in progress dataserver (see PS), as well as other sophisticated features like server-side stored *procedures*.

    On the other hand when you are using client-server against a native Progress DB, you have to format your FOR-EACH-NO-LOCK loops in precisely the correct way for data to be transferred in larger batches. Otherwise you end up with 2-3 network round-trips per *record* (capping out at about 2-3000 records per second on the LAN).  Server-side joins were never possible (until OE 12 - and even here I suspect it is based on a certain set of special circumstances).   

    I only evaluated SQL Dataserver for a short period of time, but my experience with it was very positive.  Progress put some serious talent into the development of DataServer.  I ran it in the dataserver-self-service mode with a schema-holder that was available on the local SSD (-RO -1) of the machine running the AVM client sessions.  There was only one hop between the ABL logic and the SQL Server database.  I never had the feeling that the database was "holding back" on my requests. (Whereas the Progress DB, on the other hand, seems to spend a lot of its time *pausing* between time slices - even when there is more work waiting to be done.  And there is effectively a CPU *quota* that you see yourself running into - which is easily observed in the CPU usage of the _mprosrv processes.).

    Hope this is clear.  Again, whether you benefit from the advantages of dataserver may depend on how the ABL code is written, and how much data is used.  If your application interacts with only a handful of database records on each screen then you won't have any performance challenges to begin with, no matter what the underlying technologies are.

    David

    PS.

    Here is an old screenshot showing that dataserver has long had the ability to do server-side query processing. 

    ... from ..

    http://www.pugchallenge.us/downloads2016/450%20-%20DataServer%20Best%20Practices%20and%20Performance%20Considerations%20with%20OpenEdge%2011.pptx

  • Thank you, everyone for your inputs on this. I really appreciate it!