---
title: How to Implement SQL Server Replication
description: How to Implement SQL Server Replication
image: https://blog.e-zest.com/hs-fs/file-3395401939-png/blog-files/advantages-of-replications.png
---

[![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/)

- [WORK](https://www.e-zest.com/our-work)
- [SERVICES](https://www.e-zest.com/our-services) 
    - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services)
    - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services)
    - [Digital Commerce](https://www.e-zest.com/digital-commerce-services)
    - [Digital Operations](https://www.e-zest.com/digital-operations)
    - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services)
    - [Digital Experience Design](https://www.e-zest.com/digital-experience-design)
- [INSIGHTS](https://blog.e-zest.com/)
- [CAREERS](https://www.e-zest.com/careers)
- [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services)
- [GENERATIVE AI](https://www.e-zest.com/generative-ai)
- [Healthcare](https://healthcare.e-zest.com)
- [Contact Us](https://www.e-zest.com/contact-us)

- ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg)

  [BLOGS](https://blog.e-zest.com)
  
  #### Elevating Product Engineering with AI and ML

  As technology evolves and customer choices broaden, there is an increasing ...

  [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml)
- ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg)

  [NEWS](https://blog.e-zest.com/news)
  
  #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024

  We are delighted to announce that e-Zest – An Accion Labs Company has been ...

  [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024)
- ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png)

  [CSR](https://blog.e-zest.com/csr)
  
  #### e-Zest Solutions supports Digital Pune Mission

  **Pune, India:** e-Zest Solutions, a leading digital technology innovation ...

  [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission)

- [Work](https://www.e-zest.com/our-work)
- [Services](https://www.e-zest.com/our-services)
- [Insights](https://blog.e-zest.com)
- [Careers](https://www.e-zest.com/careers)
- [Enterprise Data](https://www.e-zest.com/enterprise-data-services)
- [Generative AI](https://www.e-zest.com/generative-ai)
- [Innovations](https://www.e-zest.com/innovations)
- [Healthcare](https://healthcare.e-zest.com)
- [About](https://www.e-zest.com/about-us)
- [Contact](https://www.e-zest.com/contact-us)

- [Blog](https://blog.e-zest.com/)
- [News](https://blog.e-zest.com/news)
- [CSR](https://blog.e-zest.com/csr)

- [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/)
- [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest)
- [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638)
- [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/)
- [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate)

[![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/how-to-implement-sql-server-replication/#top)

Hit enter to search or ESC to close

Insights

e-Zest members share technology ideas to foster digital transformation.

- [Blog](https://blog.e-zest.com)
- [News](https://blog.e-zest.com/news)
- [Podcasts](https://www.e-zest.com/podcasts)
- [Healthcare](https://blog.e-zest.com/healthcare)
- [CSR](https://blog.e-zest.com/csr)
- [Whitepaper](https://blog.e-zest.com/whitepaper)
- [Playbook](https://blog.e-zest.com/playbook)
- [Media Kit](https://www.e-zest.com/media-kit)

# How to Implement SQL Server Replication

[Swapna Patne](https://blog.e-zest.com/author/swapna-patne)  Feb 13, 2015

[Technology](https://blog.e-zest.com/topic/technology)

- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/how-to-implement-sql-server-replication/&title=&summary=&source=>
- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/how-to-implement-sql-server-replication/&text=How%20to%20Implement%20SQL%20Server%20Replication>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/how-to-implement-sql-server-replication/>
- <https://www.instagram.com/ezestsolutions/?u=https://blog.e-zest.com/how-to-implement-sql-server-replication/>

SQL Server replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency. A distributed database management system ([DDBMS](http://searchsqlserver.techtarget.com/definition/DDBMS)) ensures that changes like addition, modification and deletion performed on the data at any given location are automatically reflected in the data stored at all the other locations.

**Advantages of Replication**  
 Replication provides various benefits depending on the type of replication and the options you choose. The main benefit of SQL Server replication is the availability of data as required.

- Data Consistency: Its helps in maintaining consistency in the data by allowing multiple sites to keep copies of the same data. This is useful when multiple sites need to read the same data or need separate servers for reporting applications.
- Load balancing: Replication allows you to separate the data to a number of servers and then distribute the query load among those servers.
- Reporting: Replication helps in reporting applications. Replication can increase the performance, speed and efficiency of reporting without impacting daily operations
- Database Security: Replication provides security, stability and availability while providing access to data located at distributed locations. Replication also distributes new data entered by external users and minimizes delays and data loss.
- High Availability: Replication allows multiple users and sites to make changes and then merge the data modifications together, identifying and resolving conflicts. Replication makes data available at all times.

There are many more advantages of replications. I have mentioned the more important ones above.

![SQL Server replication](https://blog.e-zest.com/hs-fs/file-3395401939-png/blog-files/advantages-of-replications.png)

**Replication Model:**  
 In any replication scenario, there are two main components:

**Publishers:** The Publisher is a type of source database server that makes data available to other servers through replication. The publisher can have one or more publications where each has a logically related set of objects and data to replicate.

**Distributor:** The Distributor is the database server that contains the set of jobs responsible for queuing replicated data from the publisher. The role of the distributor varies depending on the type of replication you implement. Database replication can be done in at least three different ways:

- Snapshot replication: Data on some server is simply copied to another server, or to another [database](http://searchsqlserver.techtarget.com/definition/database) on the same server. The Snapshot replication is time and resource consuming compared to the other two SQL replication types and is used when you need to fully overwrite the database at the subscriber or when you do an initial database replication update, which will be refreshed using one of the other two SQL replication types after the initial copy.
- Merging replication: Data from two or more databases is combined into a single database. Of course when using merging replication, you should be aware that there might be conflicts, eg duplicated primary keys. If there is a conflict, then the merge replication follows a predetermined conflict resolution plan to correct the issue.
- Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes. Transactional replication is mostly used with databases where data changes frequently and there’s a need for constant refreshing of the data. The replication process watches the publisher’s database for changes. If there are changes, it distributes them over to the replication subscribers.

**Subscribers:** Subscribers are the destination database for data coming from the single or multiple publishers and publications. Subscribers are database servers that receive updates from the publisher when data is added, modified or deleted.

Now let’s look at the implementation of SQL Replication:

**Step 1:** Create the database with the table as MyMasterTable having primary key with identity column “ON” and Not for Replication contraint “ON”.

```
CREATE DATABASE MyReplicationMaster
GO

Use MyReplicationMaster

Create Table MyMasterTable
(
MasterID int NOT NULL IDENTITY(1,1) NOT FOR REPLICATION CONSTRAINT MyMasterTablePK PRIMARY KEY,
	Name varchar(100) NOT NULL,
	City varchar(100) NOT NULL,
)

GO
Insert into MyMasterTable(Name, City)
VALUES('Sample1', 'Delhi'),
	('Sample2', 'Mumbai'),
	('Sample3', 'Pune'),
	('Sample4', 'Hyderabad')
```

**Step 2:** Configure the Distributer:

```
use master
exec sp_adddistributor @distributor = 'Servername' (Please add your sql server name), @password = N''

GO
exec sp_adddistributiondb @database = N'distribution',
				  @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data',
				  @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data',
				  @log_file_size = 2,
				  @min_distretention = 0,
				  @max_distretention = 72,
				  @history_retention = 48,
				  @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
	create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
else
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = 'Servername' (Please add your
						  server name),
				 @distribution_db = N'distribution',
				 @security_mode = 1,
				 @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData',
				 @trusted = N'false',
				 @thirdparty_flag = 0,
				 @publisher_type = N'MSSQLSERVER'

GO
```

**Step 3:** Next Configure the Publisher:

```
use [MyReplicationMaster]
exec sp_replicationdboption @dbname = N'MyReplicationMaster', @optname = N'publish', @value = N'true'
GO

use [MyReplicationMaster]
exec sp_addlogreader_agent @job_login = N'Username'(Database user who have admin access), @job_password = N'Password' (Database user password who have admin access), @publisher_security_mode = 1, @job_name = null
GO
-- Adding the transactional publication
   use [MyReplicationMaster]
   exec sp_addpublication @publication = N'MyLocalPublisher',
@description = N'Transactional publication of
database ''MyReplicationMaster'' from Publisher,
			  @retention = 0,
			  @allow_push = N'true',
			  @allow_pull = N'false',
			  @repl_freq = N'continuous',
			  @status = N'active',
			  @independent_agent = N'true',
			  @immediate_sync = N'false',
			  @replicate_ddl = 1,
			  @allow_initialize_from_backup = N'true'
```

**Step 3:** Now add Article:

```
use [MyReplicationMaster]
exec sp_addarticle @publication = N'MyLocalPublisher',
			    @article = N'MyMasterTable',
			    @source_owner = N'dbo',
			    @source_object = N'MyMasterTable',
			    @type = N'logbased',
			    @description = null,
			    @creation_script = null,
			    @pre_creation_cmd = N'drop',
			    @schema_option = 0x000000000803509F,
			    @identityrangemanagementoption = N'manual',
			    @destination_table = N'MyMasterTable',
			    @destination_owner = N'dbo',
			    @vertical_partition = N'false',
			    @ins_cmd = N'CALL sp_MSins_dboMyMasterTable',
			    @del_cmd = N'CALL sp_MSdel_dboMyMasterTable',
			    @upd_cmd = N'SCALL sp_MSupd_dboMyMasterTable'
GO
```

**Step 4:** Take backup and restore DB. Also, take Log backup:

```
USE [master]
backup database MyReplicationMaster to disk = 'C:\Personal\Shared\MyReplicationMaster.bak'

USE [master]
RESTORE DATABASE [MyReplicationMasterARC] FROM
	DISK = N'C:\Personal\Shared\MyReplicationMaster.bak' WITH  FILE = 1,
	MOVE N'MyReplicationMaster' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyReplicationMasterARC.mdf',
	MOVE N'MyReplicationMaster_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyReplicationMaster_logARC.ldf',
	NOUNLOAD,  STATS = 5

GO

backup LOG MyReplicationMaster to disk ='\\SHARED\MyReplicationMasterLOG.bak'
<pre>
<strong>Step 4:</strong> Finally add Subscription:
<pre lang="sql">
use [MyReplicationMaster]
exec sp_addsubscription @publication = N'MyLocalPublisher',
  @subscriber = 'Servername' (Please add your sql
     server name),
			  @destination_db = N'MyReplicationMasterARC',
			  @subscription_type = N'Push',
			  @sync_type=N'initialize with backup',
			  @backupdevicetype=N'disk',
                    @backupdevicename='\\SHARED\MyReplicationMasterLOG.bak',
			  @article = N'all',
			  @update_mode = N'read only',
			  @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'MyLocalPublisher',
			  @subscriber = 'Servername' (Please add your sql server \						    name),
			  @subscriber_db = N'MyReplicationMasterARC',
			  @job_login = N'Username'(Database user who have admin
					   access),
			  @job_password = = N'Password' (Database user password who
						  have admin access),
		        @subscriber_security_mode = 1,
			  @frequency_type = 64,
			  @frequency_interval = 0,
			  @frequency_relative_interval = 0,
			  @frequency_recurrence_factor = 0,
			  @frequency_subday = 0,
			  @frequency_subday_interval = 0,
			  @active_start_time_of_day = 0,
			  @active_end_time_of_day = 235959,
			  @active_start_date = 20140824,
			  @active_end_date = 99991231,
			  @enabled_for_syncmgr = N'False',
			  @dts_package_location = N'Distributor'
GO
```

To monitor the Replication, right click Replication -\> Open Launch Replication moniter  
![Open Launch Replication moniter](https://blog.e-zest.com/hs-fs/file-3395401949-png/blog-files/open-launch-replication-moniter.png)  
 You can monitor the errors, traces, job agent status and warnings.  
![errors, traces, job agent status and warnings](https://blog.e-zest.com/hs-fs/file-3395401959-png/blog-files/errors-traces-job-agent-status-and-warnings.png?width=300&height=213&name=errors-traces-job-agent-status-and-warnings.png)

I hope the steps mentioned above will guide the readers through a smooth implementation process. Would like to hear your thoughts in the comments section.  
  
 *References:  
 http://www.sql-tutorial.net/sql-replication.asp*

- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/how-to-implement-sql-server-replication/&text=How%20to%20Implement%20SQL%20Server%20Replication>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/how-to-implement-sql-server-replication/>
- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/how-to-implement-sql-server-replication/&title=&summary=&source=>

### Similar Blog

[![Managing a New Job in the Pandemic](https://blog.e-zest.com/hubfs/Managing%20a%20New%20Job2.jpg) Managing a New Job in the Pandemic A new job and a pandemic, well this has been my story so far. A recent graduate from Pune University, I was really happy when I ... By Sumedh Pathak Jun 01, 2020](https://blog.e-zest.com/managing-a-new-job-in-the-pandemic)

[![12 Technology Trends to watch for in 2020](https://blog.e-zest.com/hubfs/Technology%20Trends%202020-200108.png) 12 Technology Trends to watch for in 2020 Technology has evolved at an unimaginable pace in the last decade: what seemed futuristic and impossible until a few years ago is ... By Devendra Deshmukh Jan 10, 2020](https://blog.e-zest.com/12-technology-trends-to-watch-for-in-2020)

[![Horizon 2020: How technology is influencing human lives, and its impact on organizations](https://blog.e-zest.com/hubfs/blog-files/Horizon%202020_%20How%20technology%20is%20influencing%20human%20lives%2c%20and%20it%E2%80%99s%20impact%20on%20organisations.png) Horizon 2020: How technology is influencing human lives, and its impact on organizations As we step into a new decade, technologies across the board, from AI to cryptocurrency and online shopping are changing. We are ... By Devendra Deshmukh Dec 12, 2019](https://blog.e-zest.com/horizon-2020-how-technology-is-influencing-human-lives-and-its-impact-on-organisations)

[![5 core elements of a Design Checklist!](https://blog.e-zest.com/hubfs/blog-files/5%20Core%20Elements%20for%20A%20Design%20Checklist.png) 5 core elements of a Design Checklist! Every design project is unique, and every project has its unique outcomes based on what eventual customer experience we are ... By Corporate Jul 10, 2019](https://blog.e-zest.com/5-core-elements-of-a-design-checklist)

[![Installing Kubernetes cluster on Ubuntu 16.04](https://blog.e-zest.com/hubfs/blog-files/azure-devops.png) Installing Kubernetes cluster on Ubuntu 16.04 **Introduction:**Use of containers for deploying applications and micro-services has been a common practice widely. Orchestration of ... By Nikhil Garge Jun 26, 2019](https://blog.e-zest.com/installing-kubernetes-cluster-on-ubuntu)

[![How to read the spreadsheet (\*.xls,\*.xlxs and \*.csv) and import into dataset using DotNet](https://blog.e-zest.com/hubfs/How%20to%20read%20%20the%20spreadsheet%20and%20%20import%20into%20dataset%20%20using%20DotNet%20.jpg) How to read the spreadsheet (\*.xls,\*.xlxs and \*.csv) and import into dataset using DotNet **Introduction** In my previous blog, i shared details on how to generate the spreadsheet dynamically without using Microsoft ... By Swapna Patne Nov 16, 2017](https://blog.e-zest.com/how-to-read-the-spreadsheet-.xls.xlxs-and-.csv-and-import-into-dataset-using-dotnet)

[![How to generate the dynamic spreadsheet in DotNet](https://blog.e-zest.com/hubfs/How%20to%20generate%20the%20dynamic%20spreadsheet%20in%20DotNet_.jpg) How to generate the dynamic spreadsheet in DotNet **Introduction** In this blog, I would like to share some of my interesting findings to generate the spreadsheet dynamically ... By Swapna Patne Nov 01, 2017](https://blog.e-zest.com/how-to-generate-the-dynamic-spreadsheet-in-dotnet)

[How to improve the performance of the application in ASP .Net **Introduction** By Swapna Patne Aug 25, 2014](https://blog.e-zest.com/how-to-improve-the-performance-of-the-application-in-asp/)

[How to Retrieve Active Directory Information for Authentication in ASP.NET **Introduction** By Swapna Patne Jan 14, 2013](https://blog.e-zest.com/how-to-retrieve-active-directory-information-for-authentication-in-asp-net/)

## Helping Organizations Thrive in the Digital Age

[Contact Us](https://www.e-zest.com/contact-us)

- [About](https://www.e-zest.com/about-us)
- [Services](https://www.e-zest.com/our-services)
- [Work](https://www.e-zest.com/our-work)
- [Insights](https://blog.e-zest.com/)
- [Healthcare](https://healthcare.e-zest.com/)
- [Accion Labs](https://www.accionlabs.com/)

- <https://www.linkedin.com/company/e-zest-solutions/>
- <https://twitter.com/ezest>
- <https://www.facebook.com/eZest/>
- <https://www.instagram.com/ezestsolutions/>
- <https://www.youtube.com/user/ezestcorporate>

- Dallas
- Detroit
- Hannover
- Vienna
- London
- Pune

 © 2025 e-Zest Solutions

- [Privacy Policy](https://www.e-zest.com/privacypolicy)
- [Cookie Policy](https://www.e-zest.com/cookie-policy)

 Contact us

 Contact us

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.