I want to move (copy + delete) data collected on many (~100) identitcal systems into one worker repository. The records all have UNIQUEIDENTIFIER primary keys. I need this as automated as possible so that when I make table changes or transfer changes, I don't need to constantly update all 100+ worker systems.
I currently have a DTS job on each worker. I can script the copy of the DTS job so that DTS updates are fairly easy to automatically propagate. However, I'd like a more automated and centralized method.
Replication jumpts to mind but wouldn't I have to individually configure a publication on each worker system and manually update that publication when necessary?