Pros and Cons of modelling a ready only query system with Multiple databases vs Multiple schemas?
I have the following scenario where an SQL Server database (D1) is being replicated to D1_p (on-premise SQL Server). I have another on-premise oracle database (D2) being replicated to D2_p (on-premise SQL Server). I am trying to understand the pros and cons of setting up D1_p and D2_p on two separate DB servers vs a single SQL Server database and have 2 separate schemas on it. Would love to hear your thoughts on the same. Also please note that there is a constant sync happening in an asynchronous manner between (D1 and D1_p via SQL Log Shipping) and (D2 and D2_p via Golden Gate).