SQL Server Synchronize data between two database

Question

I am using SQL Server 2008 R2. I want to synchronize data between two databases. The primary database is where all insert, update and delete operations take place & it is kept on a different server on the network. The other database is that reflects the state of primary database at the time of synchronization.

I want this activity to be done automatically. When any record inserted, deleted or updated in the primary database, then it should reflect in the other database at the same time.

Should I use custom scripting or is there a tool that can take care of the process ?

Answer 1

Your best bet is to use Transactional Replication.

As best practice, the performance will depend on

• How much data you are replicating ? Always replicate only the data that you need (instead of the entire database).
• The network bandwidth between publisher - distributor and subscriber.
• Remember that initial snapshot will lock the tables on the publisher side, so its better to do a snapshot during minimal activity time.

If the database is too big, then you can initialize it from backup.

Also, refer to Transactional Replication Concerns.