Partager via

SQL Server 2005 Replication

  • Article

SQL Server database replication is a set of technolgoes that allows to distribute or copy the schema and data from a database on one server to one or more servers in unidirectional or bidirectional manner. Replication is one of the many ways in which SQL Server provides lets you develop highly available applications. SQL Server 2005 provides 3 types of replication: Snapshot, Transactional and Merge.

Snapsot replication is easiest to understand as it is similar to database backup and restore in a way. Everytime a snapshot replication happens, it copies all the specified articles/ data from publisher to subscribers. Instead of writing only the changed data, snapshot replication rewrites the whole data. This means that you should use this type of replication only when the amount of data is small and rather static.

Transactional replication suites more volatile scenarios and is one of the mostly used replication type. It initially copies the whole set of data and from then onwards copies only the modified data. It uses transaction logs to copy the modified data and keeps the publisher and subscribers in sync.Peer-to-peer transactional replication is a new kind of transactional replication that lets multiple servers subscribe to the same schema and data, permitting simultaneous changes in multiple servers. Limitations of transactinal replication is that it does not allow to change the data in subscribers.

When the solution requires both publisher and subscriber to allow changes and keep them in sync, Merge replication is required. Merge replication does not rely on transaction logs to sync the data, rather in this each server modifies the replicated data.

To replicate the data, SQL Server uses programs called replication agents which are sql server agent jobs. When replication is configured these jobs are automatically created for you. The replications agents include snapshot agent, Log reader agent, distribution agent, merge agent and queue reader agent.  All replication types use snapshot agent initially to create the starting copy of the database.

Replication involves 2 types of subscriptions: Push and Pull subscription. In push subscription, distributer is responsible for distributing the data to subscribers. Pull subscriptions run on subscriber servers that retrieve the publications from the distributors.

Comments