How to configure DSN on Linux Server for MSS SQL Server..

Posted by kumar alok on 23-Aug-2017 13:22

Hello All,


I want to configure DSN on linux/redhat server and trying to make conncetion with MSS sql server that is
installed on window machine for migration from progress to MSS.
1) What other software required..
2) Any driver needed..
3) How to configure DNS & what configuration should be entered in progress odbc.ini or odbcinst.ini file.. (any doc)
4) MSS Data server licence required..?
Please share..
Any help will be appreciated.

Thanks

All Replies

Posted by kirchner on 23-Aug-2017 15:36

Hi kumar, if you want to connect from Progress 4GL to MSS *and* use native 4GL commands you need the MSS DataServer and that's a Windows-only product.

So you install DS on a Windows machine and use the remote dataserver configuration. Then on your client you don't need any driver or additional component. Just a "larger than normal" connection string in your PF file.

Posted by dbeavon on 24-Aug-2017 08:35

When connecting from a Linux/redhat client to SQL, does the MSS dataserver get installed on the *same* windows host as the SQL database itself?

Assuming a well-configured 10 Gbit network, what type of response times can you expect when using a SQL db from ABL in linux (given there seem to be two hops to get to the pro dataserver then to the database).

Is there any way to download a trial of dataserver on windows for SQL?  We already have a number of Progress development-related licenses such as PDSOE and workgroup database and progress appserver.  It would be nice to try out dataserver before moving ahead with it.  Performance may be a concern.  My recollection is that epicor built the earlier versions of their stuff based on this and it didn't perform as well as they liked.

Posted by kumar alok on 24-Aug-2017 08:42

Thank you kirchner.

However my concern is I have a MSS sql database on window machine and my Openedge database is on linux server.

I want to access MSS sql database from linux server to window machine.. Here my concern is :

1. which driver is needed on Linux side ?

2. what configuration required in odbc.ini or odbcinst.ini ?

Please share..

Thanks

Posted by kirchner on 24-Aug-2017 11:27

The MSS dataserver does not need to run in the same host as the database. You can do as you please.

But if you have 3 different machines you pay at least 2 network hops for each db access.

In my experience with MSS DS (ORA DS as well) your bandwith does not matter much. It's much more a latency thing. It also depends on you code.

A "FOR EACH bigtable NO-LOCK" will stream the data to the client and you get reasonable throughput. In this case the client process (prowin32, _progres) is usually the bottleneck and will 100% one of you CPU cores.

The same code with EXCLUSIVE-LOCK will request one record at a time from the database and you get 0 performance.

The more chatty you code is to the database the worse your perf. Just monitor how many interactions with the server your client is doing.

Ideally you would do everything in bulk, but you can't do that for data modifications unless you use SPs or SEND-SQL-STATEMENT.

Posted by kirchner on 24-Aug-2017 11:28

kumar, do you want to access the SQL database from a 4GL in Linux?

If so, you need DataServer in a Windows machine and no drivers or ODBC.ini or odbcinst.ini in Linux.

Posted by Libor Laubacher on 24-Aug-2017 13:21

>>

However my concern is I have a MSS sql database on window machine and my Openedge database is on linux server.

I want to access MSS sql database from linux server to window machine.. Here my concern is :

1. which driver is needed on Linux side ?

2. what configuration required in odbc.ini or odbcinst.ini ?

Please share..

<<

You have MS SQL on windows. You have MS SQL Dataserver and schema holder on Windows.

From Linux you simply connect to schema holder and dataserver on Windows. You do not need any driver on Linux. You need C/N license on your Linux.

Posted by kirchner on 24-Aug-2017 18:23

The Schema Holders can be on Linux as well. They are just plain progress dbs, just connect them.

Only the DataServer bits must run on Windows.

Posted by dbeavon on 25-Aug-2017 08:05

Is there any way to download a trial of dataserver on windows for SQL?  

We already have a number of Progress development-related licenses such as PDSOE and workgroup database and progress appserver.  It would be nice to try out dataserver before moving ahead with it and including it in our licenses.

We are a mixed shop using both OE databases and SQL.  Our Microsoft .Net programs can connect to either types of database (OE or SQL Server) but the ABL programs are very limited and can only connect to certain types of resources including the local file system, the OE database, they can run OS-COMMAND, and get at a couple more resources to boot.  But we've often had the need to go further and make LDAP queries or SQL Server connections from ABL but are currently unable to do so.

Aside from the need to review performance, there are a number of other reasons why Progress customers may want to try dataserver before buying it.  The ongoing maintenance of the "schema-holder" sounds like it will have a learning curve and may not be straightforward (ie. on the other side of this story, when I'm writing a .Net program to connect to the Progress database, its not necessary for me to create and manage a "schema-holder" in .Net to shadow all the tables and fields available in an OE database).

Finally, certain longtime consultants for Progress have only negative things to say about MS Dataserver.  I'm not sure if the reasoning is because the product is immature or has a small user base or is buggy.  I should note that Progress consultants sometimes get a fair amount of their work by supporting OE databases (ie paid for adding new database extents) so there may be some bias which would make them uncomfortable to hear that their customers are moving some of their data into SQL Server.

Posted by kumar alok on 25-Aug-2017 10:12

hello every one,

thanks a lot for your valuable time. Could you plz guide me about steps to create DSN on linux server that is used to make connection with the MSS sql database (mss sql database on window machine).

Thanks!

Posted by kirchner on 25-Aug-2017 11:41

I know there is a OE Trial you can download for free, but I'm not sure it includes DataServer. Subscribe to the trial and check if the serial/control codes are there.

You can connect to SQL Server even without the DataServer through COM using ADO or any other data access technologies. You can even invoke .Net classes such as SqlConnection and SqlDataReader and SqlCommand provided you are running on Windows. In this case you don't need a schema holder but then I can't say you have a pure 4GL program anymore. You average FIND or FOR won't work.

The real magic of DataServer is portability and the performance is sacrificed for that. You can take up pretty much any 4GL code, few exceptions, and run it without a single modification on Oracle or SQL Server. You need to jump through many hoops to get better performance with DataServer than you would get from OpenEdge DB.

I wouldn't say DataServer is immature. We have had our share of bugs but it usually works.

But the DataServer thing is "source code portability" over "performance".

I have some material from training I gave my team but all of them are in Brazilian Portuguese. Don't know how much use that can be to you guys :)

But if we get few people interested I might be able to host a webminar on this subject. Would that be interestitng?

Posted by Thomas Mercer-Hursh on 25-Aug-2017 11:49

Dataservers are not included in OEDK until one gets to the Corporate Edition, two levels up from the free Classroom Edition.  But, you could contact your salesrep for a test copy to use for a month in order to do a proof of concept.  You should also look for whitepapers on Dataserver because there are some ABL constructs which do not work smoothly across the barrier.  Some bodies of code may have little or no problem code, but others may use those constructs a lot.

This thread is closed