I have n SQL Server databases with the same data schema.
On the other hand i have a web application that needs to retrieve, for some entities, the union of the information stored into the n databases.
A possible solution is to apply the ETL operations (Extract, transform and load) using SSIS (SQL Server Integration Services) and BIDS (Business Intelligence Development Studio).
With the application of the ETL operations I obtain a database (warehouse) that can be used from my web application. See the picture below for a basic sketch.
The problem is that the web application not only access the warehouse for read, but also for write and update information.
If an entity is updating into the warehouse, the same update need to be applied into the original source.
NB. the information into the source and warehouse can be updating frequently.
Can replication be a good approach to sync the update into warehouse to the right source or there is another tools that I can use?
Maybe there is another strategy instead of ETL and warehouse that I can consider?