Introduction

This article is for people who need to replicate the SQL Server Databases. SQL Server includes several types of replications to synchronize the databases across different SQL Servers.

Sometimes we need to replicate the data to have a Backup. Sometimes we need to synchronize Servers that are located in different cities or countries.

In this article, we will introduce you to the SQL Server Replication Theory and then we will show a step-by-step tutorial to replicate a Table using the Merge replication.

Types of Replications

In SQL Server, the main types of replication are SQL:

• Snapshot
• Merge
• Transactional

You also have the Heterogeneous Replication with Oracle, which is out of the scope of this article.

Roles in replication

There are three roles in a replication:

• The Publisher is the SQL Server, which publishes the information to replicate (usually tables).
• The Distributor is the SQL Server that distributes de information in each Subscriber. The distributor can be installed at the Publisher, in the Subscriber or in a separate SQL Server.
• The Subscriber is the SQL Server that receives the information Published.

Snapshot Replication

The snapshots replicates the complete table or tables selected in the Snapshot replication process. When the snapshot replication is applied, the entire snapshot is replicated.

Transactional Replication

In the transactional method, one of the SQL Servers is the publisher, which publishes the tables or objects to replicate, and the other is the subscriber (or the subscribers if multiple SQL Servers are being replicated).

Merge Replication

For the first time, a snapshot is used to replicate the information and after that, all the data and schema is replicated every time is changed using triggers.

Prerequisites

1. Two SQL Server or at least two SQL Server instances.
2. Make sure that the SQL Server Agent Account is running under a common account for both Servers.
3. A table to be replicated.

Getting started

1. In the SQL Server Management Studio (SSMS), open the node of the SQL Server with the table to be published. In this example, we are going to publish a table named emails installed in the DB1 database.

   
   Figure 1

2. In the Replication Node right click on the Local Publication and select New Publication.

   
   Figure 2

3. In the new publication Window, press Next.

   
   Figure 3

4. Select the Server that will be used as a distributor. In this example, the Publisher and the Distributor will be the same Server,

   
   Figure 4

5. You can optionally configure if you want the Agent to start automatically. This is recommended if you constantly require synchronization between SQL Servers.

   
   Figure 5

6. All the replication types require a Snapshot. The Snapshot Folder allows you to specify the Path where the folder will be published.

   
   Figure 6

7. Select the Database where you have the information to be published.

   
   Figure 7

8. Select the Replication type

   
   Figure 8

9. Specify the SQL Server version of the Subscriber.

   
   Figure 8a

10. Select the tables or tables to Replicate.

    
    Figure 9

11. You can optionally add filters to Rows or Columns. This is common when you have some internal information that you do not want to replicate. Press Add if you want to add filters.

    
    Figure 10

12. In the Filter statement you can configure the T-SQL Sentences to filter the columns or to restrict the rows usually with a where clause.

    
    Figure 11

13. You can create the snapshot immediately or Schedule at a specific time.

    
    Figure 12

14. With the Security Settings button, you will specify the account under which the connection will run.

    
    Figure 13

15. You can run under a specific account, impersonate the process Account, use a SQL Server Login.

    
    Figure 14

16. Once selected the security settings press Next.

    
    Figure 15

17. Select a publication type. In this example, Merge Replication.

    

18. Select the version of the SQL Server Subscriber.

    

19. Create the publication and press Next.

    
    Figure 16

20. Specify a name for the Publication and press Finish.

    
    Figure 17

21. If you open the SQL Server, you will be able to see a publication named db1: pub1.

    
    Figure 18

22. In the other server used as a Subscribers, go to Replication>Local Subscription and select New Subscriptions.

    
    Figure 19

23. In the New Subscription Wizard, press Next.

    
    Figure 20

24. Select the Publisher Server where you published the article.

    
    Figure 21

25. You can select where you want the Merge Agent to run. It can run at the Distributor or at each subscriber.

    
    Figure 22

26. At the subscriber database, you can create a new database to be used as the database subscriber to receive the data replicated.

    
    Figure 23

27. Create a Database with the same name that the database used as the publisher.

    
    Figure 24

28. You can choose the subscribers and the subscription database. Press Next.

    
    Figure 25

29. Click in the ellipsis button to specify the security accounts.

    
    Figure 26

30. You can specify the Schedule for the Synchronization.

    
    Figure 27

31. You have to Schedule when you want to initialize the replication with the snapshot publication.

    
    Figure 28

32. You also need to specify the type of subscription

    
    Figure 29

33. Finally, create the subscription.

    
    Figure 30

34. If everything is OK, add a new row to the table replicated at the publisher.

    
    Figure 31

35. At the publisher, go to Replication>Local Publication and select the article. Right click and select View Synchronization Status.

    
    Figure 32

36. If you want to merge inmediately, press the start button. You will check that 1 insert is detected

    
    Figure 33

37. If everything is OK, you will be able to check the replication at the subscriber. Your replication is now ready!

    
    Figure 34

Conclusion

As you can see, replicating a database is a straightforward process. You can replicate any database that you want anytime. In this article, we teach the basis of the replication, the replication types, roles and finally, we show how to create a Merge Replication.

• Author
• Recent Posts

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023