Is it ok for databases in the Physical Layer to share ODBC connections?

Question

Is it ok for databases in the Physical Layer to share ODBC connections? Or should each database in the Physical Layer have it's own ODBC connection built (System DSN)?

I'll try to explain --

I have inherited a RPD that was built previous to my employment.

In this RPD I am seeing that multiple databases that were set up in the Physical layer often hit the same SQL Server databases & same schemas. By that I mean...

RPD_DataBaseA -> SQLServer_DatabaseA.SchemaA

RPD_DataBaseB-> SQLServer_DatabaseA.SchemaA

RPD_DataBaseC-> SQLServer_DatabaseA.SchemaA

I don't see a problem with this. However, I see that a different ODBC connection was created for each RPD_Database. So they have it like...

RPD_DataBaseA -> ODBC_ConnectionA (this ODBC connection connects to SQLServer_DatabaseA.SchemaA)

RPD_DataBaseB -> ODBC_ConnectionB (this ODBC connection connects to SQLServer_DatabaseA.SchemaA)

RPD_DataBaseC -> ODBC_ConnectionC (this ODBC connection connects to SQLServer_DatabaseA.SchemaA)

Is the above good & proper practice?

I am thinking that I should simplify things & lowering the number of ODBC connections needed. I would like to change it to the following...

RPD_DataBaseA -> ODBC_ConnectionA

RPD_DataBaseB -> ODBC_ConnectionA

RPD_DataBaseC -> ODBC_ConnectionA

Is there a reason why I should not do this?

[Deleted User] · Answer

Any time

Chris Arnold · Answer

Thanks Christian. This gives me something to think about & bring back to my team for discussion.

Thanks for clearing this up for me!

[Deleted User] · Answer

As always - it depends. Do you want to be extremely specific from day 1 and prepare immediately for potential changes in connections? Or are you ok with starting more generic and changing things after while in case differing requirements appear?

I'd say the following: You should not do this if you see a possibility that databases A/B/C are in risk of changing their respective connectivity details. E.g. if they will move to a different server.

The security is all in the RPD objects anyways so using a generic ODBC isn't much of an issue.