How to create transactional replication in SQL Server?

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber.

In  transactional replication  snapshot of the publication database objects and data is saved.If there is any data changes and schema modifications made at the Publisher,it will be delivered to the Subscriber. The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher.Therefore, within a publication, transactional consistency is guaranteed. 

Transaction replication has three agents namely,Snapshot agent,Log reader agent and distribution agent .

The Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder.

The  Log Reader Agent monitors the transaction log of each database that is configured for transactional replication and copies the transactions from the transaction log into the distribution database.

The  Distribution Agent is an executable that moves the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication”

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

 

New publication wizard appears.Click Next and continue.

Select the database that contains the data or objects you want to publish.Click Next.

There are different types of publication.

Select the publication type that supports your application.Click Next.

Select the tables to publish  as articles.Click Next.

Add filters to exclude unwanted rows from published tables.Click Next.

Specify when to run the snapshot agent and click Next.

Select  security settings

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

After selecting the account for snapshot agent and log reader agent,click Next.

Check create the publication.Click Next.

Give the publication name and finish the wizard.

Close the window after creating publication successfully.

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

New subscription wizard window appears.Click Next and continue.

Choose the publisher and click next.

Select the distributor agent location.Click Next.

Select subscription database which you created.The database should be empty database.Click Next.

Specify the process account and connection option for each distribution agent.

Specify the domain or machines account under which the distribution agent process will run and click ok.

Schedule the agent and click Next.

Initialize the subscription as immediately and click next.

Check  create the subscription and click Next.

Click Finish and complete the wizard.

Close the window after creating subscription.

Launch replication monitor to view the connection.

To open Replication monitor Right click Replication->Launch replication monitor. 

Successfully we have created publisher,distributor and subscriber.