Replication is a way to copy data from one database to another and keep them in sync. This is useful when you want the same data available in multiple places or to improve performance by sharing the load.
There are three main types of replication:
1. Snapshot Replication:
- In this type, a snapshot of the entire database or selected data is taken at a specific point in time and sent to subscribers. It is useful for small datasets or scenarios where the data changes infrequently.
2. Transactional Replication:
- Sends changes (like new data, updates, or deletes) to other databases as they happen.
- It's good for keeping databases up-to-date in real-time, especially when the data changes frequently.
3. Merge Replication:
- In this type, both the publisher and the subscriber can make changes independently, and changes are synchronized periodically.
- Conflicts might occur if both sides update the same data, and a conflict resolution mechanism is used to resolve them.
- It is typically used in scenarios with mobile or distributed systems where subscribers can work offline.
Key Terms in Replication:
- Publisher: The source database that provides the data for replication.
- Subscriber: The database that receives replicated data.
- Distributor: An intermediary server that stores the replication data, processes the changes, and forwards them to subscribers.
Replication is useful for:
- Distributing data across multiple servers or geographical locations.
- Offloading query processing to subscriber databases to reduce the load on the publisher.
- Supporting reporting and analytics with near real-time data.