Daniel Calbimonte

SQL Server Replications

November 30, 2014 by

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.

    Publish Table
    Figure 1

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

    Replication Node
    Figure 2

  3. In the new publication Window, press Next.

    New Publication
    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,

    Select 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.

    Configure Start
    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.

    Snapshot Folder
    Figure 6

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

    Database Selection
    Figure 7

  8. Select the Replication type

    Replication Type
    Figure 8

  9. Specify the SQL Server version of the Subscriber.

    Server Version
    Figure 8a

  10. Select the tables or tables to Replicate.

    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.

    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.

    FilterStatement
    Figure 11

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

    Specific snapshot
    Figure 12

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

    Security Settings
    Figure 13

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

    Impersonate Account
    Figure 14

  16. Once selected the security settings press Next.

    Agent Security
    Figure 15

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

    Merge Publication

  18. Select the version of the SQL Server Subscriber.

    Subscriber Types

  19. Create the publication and press Next.

    Create The Publication
    Figure 16

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

    Name Specification
    Figure 17

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

    db1 pub1
    Figure 18

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

    Subscribtions
    Figure 19

  23. In the New Subscription Wizard, press Next.

    New Subscribtion Wizard
    Figure 20

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

    Publisher Server
    Figure 21

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

    Merge Agent Locationr
    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.

    Replicated Data
    Figure 23

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

    Same Database
    Figure 24

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

    Choose Subscribers
    Figure 25

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

    Elipsis Button
    Figure 26

  30. You can specify the Schedule for the Synchronization.

    Spec Schedule
    Figure 27

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

    Initialize Subscribtions
    Figure 28

  32. You also need to specify the type of subscription

    Spec Subscription Type
    Figure 29

  33. Finally, create the subscription.

    Final Subscription Type
    Figure 30

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

    New Row
    Figure 31

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

    Sync Status
    Figure 32

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

    merge Inmediately
    Figure 33

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

    Replication 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.

Daniel Calbimonte

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
Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

Replication

About 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

168 Views