OpenEdge Server Type SQL best practices

Posted by renaud.claeys on 06-Apr-2016 10:40

Dear all,

We have several OpenEdge databases within our company and we want to access all these databases with ODBC connections. It means that we also need to have a SQL server, in addition to the existing ABL server. I have several open questions that I wanted to share with you:

1) Is it a good idea to have ABL and SQL requests accessing the same database or is it better to create a database copy that we use with a server SQL only ? But this means that we duplicate the data on a regular basis. 

2) If the requests (SQL & ABL) access the same database, is it a good practice to have a secondary broker for SQL requests ?

3) Because we prioritize ABL (application) requests, is it possible to stop the secondary broker (SQL type) without stopping the primary broker (ABL type) ? For example, if a SQL user is launching a huge SQL query and it has an impact on our operationnal database.

4) If we open the database in both mode (SQL & ABL), I suppose it is a good practice (if possible) to create a new SQL user (different than ABL) that has READ-ONLY privileges on the database. 

Thanks for sharing your knowledge with me.

Best Regards,

Renaud

All Replies

Posted by gus on 06-Apr-2016 12:40

1) yes.

2) yes. or maybe 2 secondary brokers if you have two classes of users with different requirements.

3) no, not in a practical way.

4) yes. also accounts that are sql only.

Posted by George Potemkin on 06-Apr-2016 13:18

> is it possible to stop the secondary broker (SQL type) without stopping the primary broker (ABL type) ?

Yes.

Stop the servers spawn by broker: promon/R&D/4/7/7. Terminate a server

Stop the secondary login broker: kill -15 <pid>

Above promon's menu shows the login brokers but it ignores the requests to terminate them.

Posted by steve pittman on 06-Apr-2016 15:56

 
The earlier uggestions by Gus and George are very valuable.
 
About (4), let me reinforce Gus’s suggestion of creating sql-only users.   This is do-able in the OE 11.* releases.
A sql-only user has no effect on ABL usage or ABL  behavior.
 
To get read-only user access,   this has to be done by doing GRANTs of Select  permission to either  specific users or to PUBLIC.
PUBLIC means  any userid, now or future.  
By default, ordinary sql users do not have any access to tables, so the access they get is determined by the GRANT that you do.
 
We (sql development) recommend that ordinary user NOT be made DBA’s ,  and that a userid should not be created for the builtin  system DBA  “sysprogress”.  DBA’s can do any sql operation, and that clearly affects security.
It is more work to do table GRANTs, but that gives security and is very important.
 
Thanks for asking this important questions.
 
… steve pittman  [OE sql software architect]
 
 
 

Posted by bremmeyr on 07-Apr-2016 12:18

Will you expound on the statement "A sql-only user has no effect on ABL usage or ABL  behavior."

Does this statement extend to impacts due to load? Does heavy SQL read only activity impact performance on the ABL side?

Posted by gus on 07-Apr-2016 12:50

if you add general _user records to a database that has none, then that

causes a change in 4GL behaviour due to different authentication.

When you add _user records that are marked as ³sql only², this does not

occur.

Posted by bremmeyr on 07-Apr-2016 12:54

How about when you have both _user and sql only users in the same database?

Posted by steve pittman on 07-Apr-2016 13:00

 
My statement was much too general – sorry about that!
 
The sql-only user has no effect on ABL login or connection behavior – specifically, the behavior caused by the existence of _User records does not occur.
When _User records exist, there is usually a password prompt from the ABL – that will not happen if all users in_User are sql-only users.
 
In terms of workload, IO behavior, cpu/memory  use, all aspects of runtime execution – the sql-only user is exactly like any other user.
 
Hope this clarifies,          …..sjp
 

Posted by bremmeyr on 07-Apr-2016 13:09

Thank you. I think my imagination ran away with me. Of course that makes sense.

Posted by Dmitri Levin on 11-Apr-2016 14:16

I use login brokers (4GL and SQL) practice that George shared with me many years ago. It is to set a dummy login broker, the one port number I do not disclose to public, with the main preserve command. And then set up additional login brokers (preserve -m3), one for 4GL only and one for SQL only. That way I can stop either one 4GL or SQL (with kill -15) if I need to. If I stop all login brokers I bring database in kind of restricted mode.

Posted by Rob Fitzpatrick on 11-Apr-2016 14:20

Dmitri,

Just curious, why do you want/need to specify -S with your primary broker?

Posted by renaud.claeys on 12-Apr-2016 08:36

Thanks guys

Posted by Dmitri Levin on 12-Apr-2016 10:51

Hi Rob,

I think you are right, I do not need to specify -S with primary broker then.

Posted by Rob Fitzpatrick on 12-Apr-2016 11:02

I haven't done this (three-broker configuration) myself but I like the approach.  I think I will do this in future.

Posted by George Potemkin on 12-Apr-2016 11:10

IMHO, it's a good idea to start the primary database borker with the -Mn twice larger than the sum of the -Mpb. Just to be able to restart any login broker while keeping the existent sessions connected to the database.

This thread is closed