Why Not Transactional Replication?

You might wonder why I didn't consider transactional replication for distributing just the changes within a large snapshot to target servers. Let's look at a real-world example that illustrates the reason for my choice. Although using transactional replication to copy only the changes instead of the whole snapshot would reduce the data size dramatically, transactional replication might not be an option for many environments. For example, the municipal corporate geographic information system (GIS) group I work in supports a GIS application that integrates tax-lot geometry data with property-assessment and building-permit attribute data. However, the city doesn't maintain this data. Instead, two separate groups in each of three counties' assessment and taxation offices (as well as the city's Bureau of Building) maintain the records in different formats. The complete snapshot of geometry data comes in Microstation's CAD file format, and the snapshot of assessment and building-permit data arrives in delimited text-file format. The GIS application expects tabular data from SQL Server rather than from these files, so I have to import the files into the database, transform data from different sources into a single set of schema, and eliminate duplicates. Also, because the imported files are complete snapshots instead of a series of changes and because the transformations are anything but a one-to-one mapping, keeping track of a consistent set of changes across data sources is too difficult and not worth the time. Reloading, transforming, and integrating complete sets of external data is easier and more efficient. To make this complete set of imported data available at multiple sites, we then copy the whole snapshot.