SQL Server Replication versus DTS
Anjan wrote in asking us to explain the difference between replication and Data Transformation Services (DTS).
Replication keeps two (or more) SQL Servers in sync with each other. When you add records in one database, they’re automatically added to the other SQL Servers with that same database. SQL Server manages the process of moving the records around, and it happens in the background.
Data Transformation Services is also a process that can move data from one place to another, but it’s more typically used for transforming the data at the same time as it’s moved. For example, DTS can be used to take records from a text file and import them into SQL Server. In the process, while importing the data, we might check it against business rules to make sure it’s valid, and send alerts to people if invalid data is coming in.
DTS is also a little different because this import process doesn’t happen all the time: it’s usually set up to run periodically, like checking once a day to see if files exist, or to see if records exist on our source server.
DTS has also been deprecated – meaning, it’s no longer supported in newer versions of SQL Server. If you’re thinking about developing new projects in DTS, I’d recommend using SQL Server Integration Services (SSIS) instead. That’s the replacement for DTS.
