It is true that you don't get additional databases when hosting websites in DXC. But you can always add custom table to your Episerver database, using whatever ORM you are confident with and you can create the Stored Procedures you like. If you go with SPs you just need to send the create scripts to Episerver Service Desk before deploying your code.
However, I do not recommend that you go down that route, since you you will be storing duplicates of internal data in the website's database. Besides having to implement continous replication of data from your custom database, you also need to consider whether you would be storing secret or Personally Identifiable Information in those tables.
In your case, I would definitely recommend wrapping your custom database in an API, and use it as a single source of truth. As you mention, developing such new APIs will require a lot effort. But building a good replication mechanism will also require a lot of effort. And so it is often better and safer to not replicate at all.
If you use the API approach, you can cache the loaded data for some time in Episerver. This will speed up load times a lot and conserve your API call allowance.
Personally I've worked with solution with databases in seprate Azure Instances. As long as you try to keep the database in the same region there shouldn't be any latency issues in my experience, the DXP (formally DXC Cloud) is after all just built on top of standard Azure resources. So IMO that's the easiest solution, then you can asess the impact of migrating in to the Epi database all that add a dependancy layer on that DB so depending on your commitment to episerver as a platform I wouldn't add them in to the epi database unless you have to. I'd also make sure to press the DXP team as they sometimes will allow you to add in custom elements at a cost although this may be higher that the standard Azure free.
Agree with @stefan. I'd go with your option 3 and get the data via API as it will give you more possibilities in the future. It might take a lot effort to add the APIs and fetch the data, but replicating or mirroring databases will require additional infrastructure to maintain and a lot of effort too.
Thank you everyone for your thoughtful feedback and ideas.
Based on our timeline and other constraints, we're planning to go the Azure route for the database hosting and use our same third-party service to handle the replication and syncing with the cloud source. The API piece is certainly on our roadmap though, as that would be the ideal solution.
Scott, thanks for sharing that - we had overlooked the region setting and it seems that if we match the region our Episerver site and database is being hosted in, latency should be negligible. Great tip.
We're in the process of moving our site into the DxC environment hosted by Episerver, however we're not allowed to host any of our custom databases alongside our Episerver CMS database. Would be curious to hear what solutions anyone else here has used to overcome this limitation.
Anything else we should be considering?