1

I was wondering which one is the best way to replicate some data of a database to another.

I have a database in one computer and this one receives some transactions. I need to send this data to another server (in the same local network) but with a modified value (I need to add 11 years to a Timestamp value).

So I was looking for some options for my case, I can develop a windows service to do this but I don't know if the sql server replication can do this for me or if there is another option like some kind of magical trigger that can do that.

I'm using SQL Server 2005 on Windows Server 2003 R2.

Juan Enrique Riquelme
  • 385
  • 1
  • 5
  • 16
  • 1
    You could look at creating a trigger that uses a linked server to talk to the other database – Tanner Jun 23 '14 at 22:30
  • I have tried this solution but I'm having a lot of troubles trying to link the servers. They have windows authentication as I have seen in the web they mention something about the Active directory. Do you know if there some trick with Active directory + Linked servers? – Juan Enrique Riquelme Jun 25 '14 at 19:24
  • 1
    take a look at this answer: http://stackoverflow.com/a/10402764/57475 – Tanner Jun 25 '14 at 21:36

1 Answers1

2

This link should help you:

Selecting the Appropriate Type of Replication

Quoted summary from link:

Microsoft SQL Server offers three types of replication. Each type of replication is suited to different application requirements. Depending on the needs of your application, you can use one or more types of replication in a topology:

  • Snapshot replication
  • Transactional replication
  • Merge replication

I personally would replicate the database (transactional) and then use log shipping to update the replicated database (on your second server) with the latest data changes (from the primary server) then use a stored procedure running as a sql agent job to update the fields you need.

I personally am not a fan of triggers as you can end up having triggers activating other triggers and something that takes milliseconds to run can take seconds and if you have large volumes of data that can be painful (I manage a system that has exactly this issue - soon to be replaced thankfully)

hope this helps and if you have some follow up questions I'll be happy to help.

Tanner
  • 22,205
  • 9
  • 65
  • 83
David Shorthose
  • 4,489
  • 2
  • 13
  • 12