I have come across the following scenario in a practice test:

  • Data from DB1 is accessed periodically by an external application.
  • Data from DB1 will be sent to another DB, DB2, as soon as changes occur to the data in DB1.
  • All data in DB1 is recreated each day and does not change until the next data creation process.

To synchronize DB1 to DB2, should you use snapshot replication or transactional replication? Transactional replication seems fine, but isn't snapshot replication suitable as well and you do a snapshot everyday since DB1 data changes just once a day?

closed as too broad by LowlyDBA, Marco, John Eisbrener, MDCCL, hot2use Sep 16 at 5:38

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    I wouldn't use replication at all, following the KISS concept. Why complicate things? Just backup DB1 after the changes and restore into DB2. This assumes, of course, that DB2 should be an exact replica of DB1, or you can do the changes to DB2 using a post-restore T-SQL script. – Tibor Karaszi Apr 29 at 7:38
  • You have not mentioned the size replicated objects. That will be a factor in determining your solution. As your replicated object size grows snapshot will take longer and can your DB2 be not 100% accessible while you are running the snapshot job everyday? – SqlWorldWide Apr 29 at 12:56

Depends, If the only data that changes in tables has the primary key and then transactional is the way. Snapshot should be ok otherwise as it will consequently done for changed part only. The replication will be fast for snapshot. As the initally the transactional will still take the the snapshot. Also the frequency is very low, once a daily, so snapshot will be better.

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