SQL Server: pulling and updating local data

Question

Hi guys,

I have two SQL Server 2008 databases called Anna and Bob.

• Bob has to pull and transform data from Anna to keep updated his tables.
• Ideally Bob will be always synchronized with Anna, but some delay would be acceptable.

What is the best way to do this?

Thanks in advance.

Answer 1

Use SSIS (SQL Server Integration Services) to regularly update Bob. Use proper SQL Mechanisms so the SSIS task can know which data to touch (only), like change timestamps.

This is a pretty standard Data Warehouse scenario. SSIS was made to support those.

Answer 2

First Anna needs to give Bob her phone number and we'll see where it goes from there. Delay may be perfectly acceptable if Anna has to wash her hair or something.