I wondered if I could ask for fellow Stack Overflow users for their advice? I have a scenario where we have multiple SQL Server databases with the same schema and we'd like to create a centralised database containing the data from each of the databases but with a stripe to differentiate the source of the data.
The centralised database would also be on SQL Server and would need updating in real or near real-time.
There are plenty of options available such as SQL Server Integration Services, SQL Server's Service Broker or possibly Microsoft Sync Framework and others too.
What's technology would you suggest to achieve this? If you're doing this successfully, would you mind sharing your experience?
Thank you in advance.