How to sync two tables which are on different databases and servers using SSIS?

Question

I am relatively new to SQL Server's tools. I am on the following situation:

On several stored procedures on a database D1 on server S1, I need to use data that is stored on a database D2 on server S2, on a table that is being constantly updated.

As far as I've read, I can use SQL Server Integration Services to automate this task and sync two tables between two different servers/databases.

What I want to do is to have a "mirror" table from S2/D2 on S1/D1. So my stored procedures could use that table transparently.

Could you give me some any guidance? Thanks so much.

Answer 1

It's not clear what you're try to do.

It might be that you want a read-only copy for reporting; in which case you should probably consider log shipping, db mirroring or the like.

If you just want to be able to query data from another server, linked servers are an option. The queries would always return fresh data, but there's a performance hit to consider.

SSIS is the correct tool, if you want periodic secondary copies updated batch-style.

Service Broker is almost certainly far too complex for your scenario, but addresses asynchronous cases.