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?
Best Answer
If your update/insert always happens on a single one of the n databases AND you read a low number of rows on each request, you can use Linked Servers and Distributed Partitioned Views (DPV) to create the illusion of a single database: http://technet.microsoft.com/en-us/library/ms188299(v=sql.105).aspx. This can be done without introducing the complexity of replication.
DPV have several limitations and may give you poor performance if you are not careful. Without knowing more details of the queries you want to run - I can only advise that you try it and see if solves your problem.