We have data in two different databases (both in sql) that we need to join and publish to Portal. I believe that currently, ESRI does not support a relationship class where the feature layer is in one database and the table is in a different one. We are trying to avoid using a linked database in sql so I was wondering if anyone else has had this problem and what they did for a workaround.
Thanks!
The biggest reason is that when we upgrade servers every couple of years or so, we have to rebuild everything so we want to avoid doing that.
Depending on how often the tabular data is updated, you could create a duplicate table in the SQL Enterprise geodatabase with the feature layer and just have a script scheduled to update the table data as needed. That would put it in a single EGDB and make your workflow easier.
Update: We decided to go with the linked database. The issue we have now is we can't register the view from the other database.
I've also read that non-spatial views can't participate in relationship classes. Is this true?
Just out of curiosity, why are you trying to avoid a linked database? This provides a live connection so you won't need to have some type of update script if you had to pull data from database to another.