CODEWITHSUNDEEP
c#sqlsql-serverdatabasetriggers
Juan Enrique Riquelme
Juan Enrique Riquelme

Reputation: 415

Which one is the best method to replicate a database in SQL Server?

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.

Upvotes: 1

Views: 2280

Answers (1)

David Shorthose
David Shorthose

Reputation: 4497

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.

Upvotes: 2

Related Questions