SQL Server Replication

CDC is the mechnism to capture chaning from primary to offload on secondary.
---------------------------logshipping
database mirroring
always on
clustering
all are database level or instance leve
--------------------------------------replicaiton is on object level, we can select tables/columns as candidate for re
plication.
-------------------------------------four type of replication
snapshot
(it is one way, set some interval for sanpshot, it is not recommned for big data
base as in each snapshot it will take the full database copy from primary to sec
ondary, very small databases are ok for this type of rep)
transactional
(it is one way replicaiton, changed data/transactions are moving to the secondar
y server.
merge
it is two way replication/bi directional
conflict resolution is very high and it will solve many conflicts.
peer to peer
it is two way replication/bi directional
it is easy but need some
-------------------------------------- terms -----------------------keyword
publisher
--- Creates publication in the publisher, creates articles (tabl
es, views, stored proceudre)
subscription
--- create subscription on subscriper for the publication.
Distributor
--- takes articles from publisher and sends to subscriber
log reader agent -- this component will read the changes occuring on the publish
ers and writes it in the distribution database (it is a queue database) updating
by a windows process. it is better to have a seperate server for distibutor dat
abase.
distribution agent -- this component will read the changes from distributed data
base and apply on the subscriber.it can be on the subscriber server. when the di
stribution agent will be on subscriber server its mode will be PULL SUBSCRIPTION
.
If the distributed agent is on the publisher it will do the PUSH SUBSCRIPTION.

if the distribution agent is on a dedicated server it will do the PUSH SUBSCRIPT
ION.
subsriber and agent distrubtion are one to one, if more subsrtiber then same num
ber of distribution agent.
PUSH Subsription :- if the distribtion agent is on Pulisher or on dedicated serv
er it is PUSH Subsription.
SOURCE : is also called publisher
TARGET : is also called subscriber
Log Reader : will reads from active ldf file using sp_replcomds to read log reco
rd and write in the distribution database. sp_replcomds write in
msrepl_commandd
msrepl_transactions
tables in distributed database.
sp_replbrowse command is is to browse the commands from distribution database.
Distribution agent is reading database from
msrepl_commandd
msrepl_transactions
tables in the distribtion database and write in the Target server databases
----------------------------------------------------------------------------------------snapshot replicaiton
publisher/Source uses BCP command using SNAPSHOT AGENT to generate snapshot of t
he whole database and will put the flat files in //snapshot folder on the distri
butor database server. if the distributor is on the same as Source database it w
ill create the //snapshot folder on the Source server.
source and distributor are same then //snapshot folder will be on the same.
distributor agent will take the flat files and will apply on the subscriber serv
er.
---- agents in snapshot replication ---------------there are two agents working in the snapshot replication.
Sanpshot agent on the source/publisher server
Distributor agent on the target/subscriber server
Peer to peer replicaiton
source
log writer agent
distributor agent reads from distributor database
target server
it has four agents
Logwriter agent ( 2 agents, one on each server (source and traget))

Distributor agent ( 2 agents, one on each server (source and traget))
================================================================================
========================
how to remove replication
first step is to remove the subscription
second step is to remove the publication
third step is to disable the distributor (if never required any type of replicai
ton then remove the distributor)
================================================================================
======================
creating replicaiton
first step is to configure Distributor.
if will create a snapshot folder and a distribution database.
Note: if participating servers have been applied some patch/service pack, it is
recommended to upgrade the DISTRIBUTOR database.

SQL Server Replication

Uploaded by

Copyright:

Available Formats

SQL Server Replication

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Server Replication

Uploaded by

Copyright:

Available Formats

What are the different types of replication?

What are the agents involved in snapshot replication?

CDC is the mechnism to capture chaning from primary to offload on secondary.

If the distributed agent is on the publisher it will do the PUSH SUBSCRIPTION.

Logwriter agent ( 2 agents, one on each server (source and traget))

You might also like