SQL Server Data Synchronization Between Two Databases
I am using SQL Server 2008 R2. I want to sync data between two databases. The main database is all the input, update and delete operations that are stored on another server on the network. The other database reflects the state of the primary database during synchronization.
I want this action to be performed automatically. When any record is inserted, deleted or updated in the primary database, it must reflect in another database at the same time.
Should I use custom scripts or is there a tool that can take care of the process?
+3
source to share
1 answer
It is best to use Transactional Replication .
As a best practice, performance will depend on
- How much data are you copying? Always copy only the data you need (instead of the entire database).
- Network bandwidth between publisher-distributor and subscriber.
- Remember that the initial snapshot locks tables from the publisher's side, so it is best to take a snapshot during the minimum time of activity.
If the database is too large, you can initialize it from a backup .
Also see Problems with Transactional Replication .
+7
source to share