0

I have a different source and destination database server with the same name.

On a periodic interval, we push "new" data configuration to the source database and manually update the data in the destination database.

Instead of handling it manually, we are trying to do one time "new" data distribution from source db to the listed linked server destination db's.

I presume that if we use "Linked Server"s it can help to achieve the data distribution by validating missing (or) unavailable records in the destination database servers.

Scenario : New price data distribution from source database to the client databases. We might have new price for new products (or) new price for existing products. Based on the "new" data changes or "new" data in the source db, we will have to distribute the client db.

How do I distribute data from the source database to the destination database in SQL?

Improve this question
9
  • i guess you want/need SQL Server Database Replication learn.microsoft.com/en-us/sql/relational-databases/replication/…
    – Raymond Nijland
    Aug 25, 2018 at 19:39
  • Replication will not help.. because .. all the client (or) dest database .. will have Insert / update / delete . We need to distribute only the new configuration data to the destination data. Assume, new price file for a certain list of products. Price will vary periodically. There could be daily price change or weekly for a product or different products.
    – goofyui
    Aug 25, 2018 at 19:45
  • When we have the replication on the destination database... any changes on the destination databases such as insert, update , delete will immediately break the replication
    – goofyui
    Aug 25, 2018 at 19:46
  • "Instead of handling it manually, we are trying to do one time "new" data distribution from source db to the listed linked server destination db's ?" Radical thought can't you make a trigger that copies the data from the source database to the destination database.. That should work if you keep track which tables and record (id's) are copied.
    – Raymond Nijland
    Aug 25, 2018 at 19:54
  • 1
    How does trigger will help for multiple client databases. As long as if it is one destination server / db; we can easily map it on the trigger / linked server mode. In this case, we have around 8 to 12 client servers / databases. And the list will grow eventually
    – goofyui
    Aug 25, 2018 at 19:56

1 Answer 1

Reset to default
1

Replication is the right solution here, despite the comment above. Simply copy the published table into a separate staging table at each subscriber. Then, on a schedule of your choosing, copy the data from the staging table to wherever it needs to go in the subscriber database.

You can use SQL Server Transactional Replication for this, or just copy the tables on a schedule with SSIS or over Linked Server connections.

If you use SSIS, your SSIS package you can use a Foreach loop to load multiple databases, or you can configure a separate SQL Agent job or job step for each target database, which would enable you to schedule them differently, and run just one. A SQL Agent job step can specify the target connection string.

Improve this answer
2
  • I prefer SSIS Package .. and run it on a scheduled interval. But the problem with SSIS Package is , i will have to develop each package for each server. Or, every time - whoever is going to run the package has to make the changes for the destination server
    – goofyui
    Aug 25, 2018 at 21:52
  • see edit re: SSIS package.
    – David Browne - Microsoft
    Aug 25, 2018 at 22:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.