I was assigned a database replication projects to setup database replication that supported ETL projects for a Fire and Policy department.

The Source and Target database are located in the same domain.

I used my previous experiences and I know how to setup database replication. My previous working experiences were setting – up a Transactional replication to replication wind farm database.

It’s important to understand the database replication terminology terms like:

• • Publisher
    • The MS SQL Server Database instance that provided data available to Subscribers. The Articles like Tables, and Views which are to be replicated are defined at the publisher.
  • Distributor 
    • The MS SQL Server Database instance that distributes data from one or more Publisher to one or more Subscribers
  • Subscriber
    • The MS SQL Server Database that going to receive data from Publishers.
  • Pull Distribution
    • Setup and located in Subscriber MS SQL Server
  • Push Distribution
    • Setup  and located in Publisher MS SQL Server.
  • Articles
    • An article is a SQL Server object like Table, View, Function, stored procedure, etc.
  • The Snapshot Agent
    • Distributes data exactly as of a point in time.
    • Prepares snapshot files containing schema and data of publication articles
    • Stores the files in the snapshot folder
  • The Distribution Agent
    • Copies the initial snapshot files from the snapshot folder
    • Copies transactions from the distribution database to one or more subscribers.
    • https://docs.microsoft.com/en-us/sql/relational-databases/replication/configure-distribution
  • The Log Reader Agent
    • Analyzes the transaction log of the publication database
    • And looks for transaction marked for replication
    • Copies the transactions marked for replication into the distribution database
    • https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-log-reader-agent