How to create merge replication in SQL Server?
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.
Specify the SQL Server version that will be used by subscribers for this publication.Click Next.
Select the tables to publish as articles.Click Next.
If you don’t have unique identifier in the table.Then SQL Server will automatically add unique identifier to the table.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.
Specify the type of subscription as client or server and click Next.
Check create the subscription and click Next.
Click Finish and complete the wizard.
Close the window after creating subscription.
Successfully we have created publisher,distributor and subscriber for merge replication.