What is Transaction?
Transaction is the term we hear quite frequently and of course, almost everyone working with the IT world, is familiar with it. In a nutshell, it is a unit of work, which must show ACID properties (remember the syllabus during college days? Atomicity, Consistency, Isolation, and Durability).
Now, assume that we all have learned very well about ACID properties. Let’s move to the next term, Transactional Replication.
What is Transactional Replication?
Well of course, if you think logically, it means replicating or migrating the transactional changes to somewhere, which is exactly what it does. Basically, it is a feature of SQL Server, using which, you can migrate committed changes to the multiple targets.
Transactional Replication is one of the popular techniques, when you want to migrate your production databases to other environments like Azure with minimal downtime and when SQL is always on, setup is not in place.
When we refer to the term migration, which includes the transfer of the data from one point to another, it means there must be someone who can act as an emitter of the data and at other end, there must be someone to receive it. There must be a channel, under which the data will be sent from an emitter to receiver. If we were to give fancy names to the terms used above, we can call the data emitter as a publisher and receiver as a subscriber.
How Transactional Replication Works?
Transaction replication concept basically consists of three main entities-
- Snapshot agents
- Log reader agents
- Distribution agents
Snapshot agent is responsible for preparing the snapshot files containing schema and data of the database objects and creates the sync jobs on the distributor.
Log reader agent keeps monitoring the transactional logs of the databases, which are configured for the transactional replication and copies those to the distributor’s database.
Distribution agent basically distributes / copies all the snapshot files and log files from the distribution database to the subscribers.
The replication happens in near real time. We can easily visualize this system, as shown in the image, given below-
Demo
Now, with this understanding, let’s see how we can use this concept to replicate our On Premises database to SQL Azure database.
For this article, let’s assume that instead of actual On Premises SQL Server Enterprise database, we have Azure Virtual Machine running SQL Server 2014 Enterprise edition, which will act as a publisher and we have a brand new SQL Azure database, which will act as a subscriber.
We will be going through high level steps, given below-
- Creating Azure ARM VM with SQL Server 2014 Enterprise Edition Template.
- Creating SQL Azure Database.
- Creating SQL Publications.
- Creating Subscription for created publication.
Create Azure ARM VM with SQL Server 2014 Enterprise version
Let’s start by creating Azure ARM VM by selecting SQL Server 2014 Enterprise edition template. We will name the virtual machine as “SQLVM1”.
This article assumes that you have a basic understanding of Azure and have basic experience with provisioning resources on Azure, so instead of going through the detailed steps of creating virtual machines and SQL azure databases on Azure, this article will mainly focus on setting the required steps for enabling the transaction replication for SQL Server database, hosted in Azure ARM VM.
You can go through the detailed steps of creating Azure VM and Azure databases, using the links, given below-
Create SQL Azure Database
After SQL server VM is provisioned, let’s create a brand new SQL Azure database (PaaS), using Azure portal and name it as bgdb. We will be using this database to replicate the data and changes from our source database, which is hosted in an Azure VM, which we just created in the previous step.
Note that this database is created just for the demo and due to this, S0 standard pricing tier has been selected. You can choose the right tier, as per your requirement.
Remember the Server user name and password. We will be needing it later in this article.
Configure Azure VM database
Now, we have both source and destination Servers ready. Thus, let’s go ahead and create a sample database in source by logging in to the Azure VM created in the step 1.
Open SQL Server Management Studio in Azure VM and create a new database. Name it as “AzureVMDatabase”. Let’s create a new table and name it as “Person”, as shown below-
I have also added a few entries, so that we have some data in the table.
Now, we have a schema and the data of our sample table ready. Let’s check, if SQL Server Agent service is started or not. If it is not started, make sure you start it by going to SQL Server Configuration Manager.
Create and configure SQL Server Database Publication
Now, let’s start by creating a new publication for source SQL server-
Open SQL Management Studio in Azure VM and connect to the local instance of SQL Server database. Expand the replication node and right click on Publication. Click New Publication.
![]()
It launches a wizard, using which, you can configure your new publication before saving it. Click next.
In this step, we will configure the distributor settings. Since we have created only one virtual machine in Azure and it is hosting SQL Server, we will use same VM as a distributor but you can choose the different Server as your distributor (refer to the architectural image, given above).
Click next. The next step is about configuration of SQL Server agent Service. Choose SQL Server agent Service to start automatically.
Click next. It asks to select the database for which you want to enable the transactional replication. Objects within this database will be picked for the publishing.
Let’s select our database i.e. AzureVMDatabase and click next.
Next step is about selecting the publication type for the database. For this article, I will not go through the details of each publication type. We will simply select Transactional publication and click next.
On the next step, select the database objects from the selected database. We will select our Person table for the publication.
Click next. Now, it asks about the configuration of the snapshot agent and it’s frequency, i.e when it should run. We will select the settings, as shown in the image, given below-
Click next. It asks for the configuration of the account, which will be used to run the snapshot agent. Click Security settings to configure the settings, as shown below-
As a last step, it shows all the configuration settings and asks to give a name to the publication job. We will name it as “SQLAzurePublication”.
Click Finish. If you have setup everything correctly as per the article, on next step, you should see something like-
Creating Subscription
After creating the publication, refresh SQL server node, using SQL management studio, so that newly created publication appears under the local publications node for the database.
Right click on the publication and select new subscription. It launches a wizard to create the subscription.
On the next screen, make sure, the correct publisher and publication are selected from the list. Click next.
Next step is about the configuration of deploying the distribution agents. We will select the option to run an agent on the same Azure VM , which we have created.
Meaning of Push subscription model is the distribution agent will be pushing the transactional changes to the subscribers, instead of the subscribers pulling those from the agent.
Click next. This step is crucial and here, you are actually selecting the target database, where you want to push all your data and schema of the source database. Select Add SQL Server subscriber option.
It opens up standard SQL Server authentication wizard (similar to logging into SSMS)
Enter the SQL server name, user name and password of the SQL Azure database which we created in step-2. You can obtain SQL server connection string from Azure portal by navigating to your SQL Azure database and by selecting Show database connection strings option from the essentials section.
Select correct database name from the dropdown and click next.
Next step is about configuring the distribution agent security. We will use SQL Server agent Service account to connect to the source database i.e. SQL Server database running inside Azure VM and specify the credentials to connect to SQL Azure database (PaaS).
Click OK and click next.
Select agent schedule as continuous.
Click next, select immediate subscription initialization.
In the next step, select the option to create the subscription. Once the subscription is created, you should see the success message like-
It immediately creates all the required jobs, which you can monitor, using SQL Server agent job activity monitor.
Now, If everything has been setup correctly and the replication job has worked properly, we should be able to see the Person table with two records in our SQL Azure database.
Let’s validate it,
Log in to SQL Azure database, using SQL Server Management Studio and expand the bgdb, which was created as a blank database.
It has migrated all the data with the schema to SQL Azure.
Now let’s try to add one record in a source database and see, if it gets replicated on SQL Azure database.
Now, let’s see, if the same gets replicated to SQL Azure database. Let’s connect to it by SSMS and select all the records from Person table.
This validates that the transactional replication to SQL Azure database's setup is successful.