In this article, I am going to demonstrate how to create transactional replication.
Transactional replication generated a snapshot of publication DB objects and data within it. After the snapshot is generated, all the data changes and schema changes occurred on the publisher database are delivered to subscriber databases. In transactional replication, data and schema changes are almost real-time, hence transactional replication can be used to offloading reports and sometimes can be used as DR Site.
When we create transactional replication, SQL Server creates the following SQL Jobs
- Snapshot Agent Job: Snapshot Agent Job generates a snapshot of publication database objects. The snapshot is stored either in the network location or the hard
- Log Reader Job: Log Reader Job continuously monitors publications. It detects schema changes and Insert, Update and Delete queries and marks them for replication. The log reader uses “Sp_Replcmds” to apply the commands marked for replication.
Demo Setup:
For this demo, I have created two SQL Server instances on the same database server. The instance names and details are the following:
When setting up the demo, I haven’t created a distributor, so the publisher instance will also act as a distributor. I have created the “Customer” table in the AdventureWorks2014 database. I am going to replicate the data of the Customer table. Therefore, it will be called an article by the publisher.
Configure Publisher
To configure the publisher, open SQL Server Management Studio, expand the database instance, expand Replication node and right-click Local Publication and select New Publication.
The New Publication Wizard starts. On the dialog box, a short summary of the wizard is provided. You can skip further appearance of this dialog box by checking the Do not show this starting page again checkbox. Click Next to move to the next dialog box.
In the next dialog box, select the database which acts as the publisher. In this demo, I am using the AdventureWorks2014 database, so select AdventureWorks2014 from the database list and click Next.
In the next dialog box, select the publication type from the list of publication types, and their descriptions are also provided in the Publication type descriptions text box. Select Transactional publication and click Next.
In the next dialog box, select the publication type from the list of publication types, and their descriptions are also provided in the Publication type descriptions text box. Select Transactional publication and click Next.
If you want to filter the data of the table, you can specify the condition in the Filter Table Rows dialog box. To add the filter, click the Add button. Another dialog box Add Filter opens. In this dialog box, you can specify the desired filter statement in the Filter statement text box.
For this demo, we are not filtering data of the Customer table, so skip this dialog box and click Next.
To initialize transactional replication, we must generate the snapshot. In the Snapshot Agent dialog box, configure the snapshot generation interval. You can immediately generate the snapshot or you can schedule the specific time to generate the database snapshot. By default, the snapshot generates every hour, but we can change it. To generate the snapshot in a specific interval, click Change. The New Job Schedule dialog box opens. Configure the desired schedule and click OK.
In this demo, I will generate the snapshot immediately, so check the Create a snapshot and keep the snapshot available to initialize subscription option and click Next.
In the next dialog box, configure the SQL Agent security. To configure the Agent security, click the Security Settings button. The Snapshot Agent Security” dialog box opens. In the dialog box, specify the account under which the subscriber connects to the publisher. Moreover, specify the account information under which the SQL Server agent job will be executed. For this demo, SQL Server jobs execute under SQL Server Agent service account, so select Run under the SQL Server Agent service account option. Subscribers will be connected to the publisher using the SQL login, so select Using the following SQL Server login option and specify the SQL login and password. In this demo, connect using the “sa” login. Click OK to close the dialog box and click Next.
In the next dialog box, you can choose to create a publication or generate scripts to create the publication. Select Generate a script file to create the publication option to generate the scripts of replication steps. We will create the publication, so select Create the publication option and click Next.
In the next dialog box, specify the name of the publication, review the summary of the tasks to be performed to create replication and click Finish to create the publication and close New Publication Wizard.
As I mentioned at the beginning of the article, when we create a transactional replication, it creates the Log reader agent job, snapshot agent job and cleanup job for expired subscriptions.
To view them, expand SQL Server Agent, then expand Jobs.
Once the publication is configured, configure the subscribers.
Configure the Subscriber
To configure the subscriber, first, connect to another SQL instance. After connecting to the instance, expand Replication and right-click Local Subscription.
The New Subscription Wizard opens. In the dialog box, a short summary of the wizard is provided. You can skip further appearance of this screen by checking the Do not show this starting page again checkbox. Click Next to move to the next dialog box.
In the next dialog box, choose the publication server. Click the Publisher drop-down box and click Find SQL Server Publisher. A dialog box to connect to the publisher opens. In the Server name text box, specify the host name of the publisher server and click Connect.
Once the connection is established with the publisher, Customer_Publication and AdventureWorks2014 will be shown in the Databases and publication text box. Select Customer_Publication and click Next.
In the next dialog box, choose the distribution agent location. For this demo, I will use Pull Subscription, so select the Run each agent at its subscriber option and click Next:
In the next dialog box, select a subscription database. I have created a database named Customer_DB, which acts as a subscriber database. In the Subscription database drop-down box, select the Customer_DB database from the list. After selecting the subscription database, click Next.
In the next dialog box, configure distribution agent security. To configure the security, click the […] button. The Distribution Agent Security dialog box opens. In the first section, specify the domain account under which the Distribution Agent process will run. The Distribution Agent will run under the SQL Server Agent service, so select Run under the SQL Server agent service account option.
Here I did not configure the distribution server, so the publisher acts as a distributor. The subscriber will connect to Publisher/Distributor using an SQL Login. To connect the Distributor, use the “sa” login and password.
Click OK to close the dialog box. In the Distribution Agent Security dialog box, click Next.
In the next dialog box, configure the subscription synchronization schedule. You can choose it to run continuously or configure the scheduled execution. Select Run Continuously in the Agent schedule drop-down box. Click Next.
In the next dialog box, choose whether to initialize the subscription immediately with the snapshot of publication data and schema or after the first synchronization. We will initialize subscription immediately, so choose Immediately from the Initialize When drop-down box. Click Next.
In the next dialog box, you can choose to create subscription immediately or generate the scripts to create a subscription. Select Generate a script file to create the subscription option to generate the scripts of replication steps. We will create a publication, so select Create the subscription option and click Next.
In the next dialog box, a list of tasks performed to create the subscription is given. You can review them and click Finish.
Once the subscription is created, you can see the subscription under the Local Subscription node.
View replication status
You can use Replication Monitor to view the replication status. To open Replication Monitor, expand Replication, and then expand Local Publications. Right-click Customer_Publication and select Launch Replication Monitor.
Summary:
In this article, I explained:
- A high-level summary of transactional replication.
- Demo setup.
- How to configure replication publication.
- How to configure replication subscribers.
Useful tools:
dbForge Data Compare for SQL Server – powerful SQL comparison tool capable of working with big data.
dbForge Schema Compare for SQL Server – reliable tool that saves your time and effort when comparing and synchronizing databases on SQL Server.
