APEX Architecture
I'm not talking about how APEX internally or within the database works, I'm talking about how to best manage it from an infrastructure point of view.
I recently raised the issue with my client about how they were using APEX. I'm not here as an APEX consultant, but they didn't have a lot of experience in-house and the DBA who got it implemented had been trying for years. So I wrote up my thoughts and passed them along to my supervisor who then shared them with others.
The current setup was a stand-alone instance that was only used for APEX applications using database links and synonyms to access the source tables. I was arguing to have APEX installed on the database where the data lives.
When I got on the call, I listened to their arguments and concerns.
Then I began to think...what
do you do if you have, say, 100 databases? Do you install APEX on each and every one? That's just another piece that has to be supported and maintained right?
After the call, I began to do a little research on the subject...and couldn't find a whole lot of information out there. Perhaps I wasn't searching on the right terms...not really sure.
Thinking about it further and talking with some friends, I came up with a short list of potential architectures.
- Install it Locally - This is my preferred method. The APEX metadata (application) lives in the same database as the data it manipulates.
- Database Links - Target Tables Remote, Source Code Local - This is the configuration I spoke of above. There is a single database where APEX is installed which accesses tables across database links. Synonyms would be used for tables and views to "hide" the long names and make them visible. Any source code (PL/SQL) would be local.
- Database Links - Target Tables Remote, Source Code Remote - Same as #2, but the source code lives in the remote database. Again, synonyms would need to be used to make database objects visible to APEX.
Number 2 and 3 both limit the functionality of APEX. I am aware of the Automated Row Fetch not working across database links. I know, for #3, you can't use the
RETURNING clause, which isn't a
huge deal...just annoying.
For #2 and #3, the synonyms add additional maintenance and complexity to your application(s).
Also with #2 and #3, you have additional network traffic, which, depending on the environment and/or the number of users, could be significant.
Like I said, my first thought was to install it locally. In addition, just put it on the databases that you will use it, not every single one. I still believe that, but am trying to give a balanced and reasoned answer.
Ultimately, maybe it just depends on
how you use it. If this is going to be a "professionally" written application (as opposed to something a power user might build), I think having it installed on the databases where your data lives. If it's going to be driven by those power users as a replacement for the Excel and Access databases, I
think the best approach might just be the single instance accessing tables remotely would be the way to go.
Anyway, just my thoughts. Please scream (write) loudly if you have thoughts on the matter.
Labels: apex, design, development, oradb
