This blog is the first in a series on the topic of SQL Server replication. The topics are the same for the most part for SQL Server 2008 R2 or 2012. In case there are any obvious differences we will call them out as we go along.
Replication is one of Microsoft SQL Servers high availability solutions. It copies data from one server (publisher) to multiple other servers (subscribers) using a facilitator database called the distributor. Replication uses terminologies used within the publishing industry such as publisher, article, subscriber etc. Replication is usually not implemented as a standalone high availability solution because it doesn’t support automatic failover. However this drawback is easily overcome by implementing mirroring on the publisher. The biggest advantage of using replication is that it supports scalability within SQL Server. A common scenario where replication is used is when dealing with multiple users, usually 100’s or 1000’s of users connecting to a datawarehouse. Replication is usually implemented in the below three formats:-
Snapshot Replication: – provides and as is copy of the database at the time the snapshot was taken. This from of replication is usually a precursor to the other two forms of replication which require an existing copy of the database as the base on which the rest of the solution is based on.
Transactional replication: – a mechanism of moving over changes from the publisher or primary server into multiple destination servers called subscribers; this creates multiple read only copies of the data which helps offload read only loads on the database tables.
Merge Replication: – A form of replication where the data can flow in both directions. Usually used when dealing with remote applications that needs to send information such as order status back to a central database. This is usually implemented in mobile application.
Replication supports moving data from heterogeneous database such as Oracle etc. All the above methods of implementing replication depends on the use of agents which are scheduled to run and move changes either from one server to another or from a server to a client.
Some common terms of replication are:-
Publisher: – The server which is the source of the changes which will be published to other systems. The publisher is the server on which changes need to occur in order to have the cascade to other systems downstream. More specifically publisher is the database in which changes are been captured and moved to other systems.
Publication: – A publication is group of tables which are going to be published. Tables which are logically related are usually part of the same publication.
Article: – A table which is being monitored for changes or basically the table in which changes are made that need to be sent or replicated to other systems is called a Article. A group of such article is called a publication.
Subscriber: – The downstream system to which changes are copied over is called subscriber. The changes from the articles hosted on the publisher are synchronized with the tables hosted on the subscriber.
Subscription: – A subscription is a kind of rule that decides which objects from the publisher get synced with what objects in the subscriber. Essentially it plays the opposite role of the publication.
Please Consider Subscribing
