I am not sure how to formulate this question, so I will try to explain. I have a third-party database on SQL Server 2005. I have another SQL Server 2008, which I also want to "publish" some data to a third-party database. Then I will use this database as the source code for the portal and reporting services - it should be a data warehouse.
On the target server I want to store data in different table structures in the same way as in a third-party db. Some tables that I want to denormalize, and there are many columns that are not needed. I also need to add additional fields to some of the tables that I will need to update based on data stored in the same rows. For example, there are varchar fields that contain information that I want to fill in with other columns. All this should clear data and facilitate reporting.
I can write the query (s) to get all the information I want in a specific destination table. However, I want to be able to keep it up to date with the source on another server. It does not need to be updated immediately (although this would be good), but I would like it to be updated, perhaps every 10 minutes. There are 100 thousand rows of data, but changes to the data and the addition of new rows, etc. Not big.
I looked around, but I'm still not sure if this is the best way to achieve this. As far as I can tell, replication will not do what I need. I could manually write t-sql to make updates, possibly using the Merge statement, and then plan it as working with an sql server agent. I also looked at SSIS, and it seems like being ETL oriented.
, , , ? .