I have a question regarding DB Split. Say have 3 systems:
• Client DB: maintain client & organisation info
• Accounting DB: Account Information
• Rate DB: getting exchange rates from a 3rd party system
We need to use tables from these 3 databases in the other databases. Is this a good idea to split the DBs, if we need to put the data back together?
If we do split the DB, is it a good idea to copy data across databases? Or linked servers are preferred?
Whether something is a good idea is a question of opinion. What is less so is a question of what the tradeoffs are, and here I can discuss those.
Splitting databases between departments gives departments greater freedom in deciding their domain models. One of the valid insights of the DDD school of thought is that teams form bounded contexts which may use vocabulary in a way slightly different than others. If giving the various teams more flexibility in deciding their own terminology and data models is a positive thing, that is going for this.
On the other hand, there are a number of performance drawbacks. Each system knows less about the data distribution in each database, and therefore is less able to effectively plan. Cross-node joins are always expensive. So you get some real downsides as well.
I think copying data tends to work against the bounded context advantages of division btw and that is a warning. But then this is a tradeoff between autonomy and performance.
A couple other things to consider: