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:
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.
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.
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).
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.
- Two SQL Server or at least two SQL Server instances.
- Make sure that the SQL Server Agent Account is running under a common account for both Servers.
- A table to be replicated.
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.
In the Replication Node right click on the Local Publication and select New Publication.
In the new publication Window, press Next.
Select the Server that will be used as a distributor. In this example, the Publisher and the Distributor will be the same Server,
You can optionally configure if you want the Agent to start automatically. This is recommended if you constantly require synchronization between SQL Servers.
All the replication types require a Snapshot. The Snapshot Folder allows you to specify the Path where the folder will be published.
Select the Database where you have the information to be published.
Select the Replication type
Specify the SQL Server version of the Subscriber.
Select the tables or tables to Replicate.
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.
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.
You can create the snapshot immediately or Schedule at a specific time.
With the Security Settings button, you will specify the account under which the connection will run.
You can run under a specific account, impersonate the process Account, use a SQL Server Login.
Once selected the security settings press Next.
Select a publication type. In this example, Merge Replication.
Select the version of the SQL Server Subscriber.
Create the publication and press Next.
Specify a name for the Publication and press Finish.
If you open the SQL Server, you will be able to see a publication named db1: pub1.
In the other server used as a Subscribers, go to Replication>Local Subscription and select New Subscriptions.
In the New Subscription Wizard, press Next.
Select the Publisher Server where you published the article.
You can select where you want the Merge Agent to run. It can run at the Distributor or at each subscriber.
At the subscriber database, you can create a new database to be used as the database subscriber to receive the data replicated.
Create a Database with the same name that the database used as the publisher.
You can choose the subscribers and the subscription database. Press Next.
Click in the ellipsis button to specify the security accounts.
You can specify the Schedule for the Synchronization.
You have to Schedule when you want to initialize the replication with the snapshot publication.
You also need to specify the type of subscription
Finally, create the subscription.
If everything is OK, add a new row to the table replicated at the publisher.
At the publisher, go to Replication>Local Publication and select the article. Right click and select View Synchronization Status.
If you want to merge inmediately, press the start button. You will check that 1 insert is detected
If everything is OK, you will be able to check the replication at the subscriber. Your replication is now ready !
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.
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