Setting up replication from on-premise SQL Server to Azure SQL DB

This article was recently published on dev.getroadmap.com:

 

For performance reasons we are looking for a way to split our write-operations from our read operations. A good way to do that is by duplicating the database onto another server (on-premise) or platform (Azure SQL DB). When you look at the options (Availability Groups (AG), database mirroring, clustering, replication, Azure sync groups, etc.) the easiest way to quickly duplicate a database and keep the data up-to-date at the same time is SQL Server replication.

Replication to another on-premise instance is easy. You just follow the steps in the wizard, it works out-of-the-box, and the chances of this process failing are small. With replicating data to an Azure SQL database it’s a bit more of a struggle. Just one single word took me a few HOURS of investigation and a lot of swearing…

 
Setting up replication and databases
Before you start with setting the whole thing up, make sure that the replication is installed on your instance. If this is not done yet, install the needed components via the installer. This component can be installed without rebooting the server, or the need to restart the instance.

If you needed to install replication, make sure you restart the SQL Server Agent. For some reason, the configuration change works on the instance without any problems, but the Agent can’t run the needed jobs until you restart it.

The next step is creating 2 databases: a publisher (on the on-premise instance) and a subscriber (an Azure SQL database):

 
And create a table that can be replicated:

CREATE TABLE dbo.SomeObject
	(ID INT IDENTITY(1,1),
	 SomeValue VARCHAR(100),
	 PRIMARY KEY (ID))

 
Setup the publisher
Now that you have 2 databases, it’s time to setup the publisher. I’m not guiding you through the whole replication setup (there are plenty of tutorials out there that are WAY better than my explanation), but I’ll show you the highlights.

First, start by clicking “New Publication” on your on-premise instance:

 
I chose to run the distributor on the same server.

If you follow the wizard, it will prompt you with the question on where to store the snapshots:

 
Copy this link into for example notepad, because it comes in handy later in the process.

As publication type, choose “Transactional publication”:

 
Again, follow the wizard and choose the objects you want to replicate (in my case just that single table):

 
For the security settings, I chose to use the SQL Server Agent

After finishing the steps in the wizard, give your publication a name, and finish this setup.

 
Create a subscriber
On the on-premise instance, right-click the newly created publication, and add a new subscriber:

 
The distribution agent will remain on the distributor (push subscription).

When you’re prompted to add a subscriber, choose “Add SQL Server Subscriber”:

 
Here comes the part that took me a while to figure out. When you try to replicate changes to an Azure SQL database, and you use the normal connection, it will fail to connect in the end. But if you use a secure connection, replication works like a charm. So make sure you use the following server name ([Server Name].database.secure.windows.net):

 
Adding that single word took me an hour or so to figure out. Hopefully this will save you a few hours of investigation, looking for dodgy error messages, ending up in finding an “errorcode 40852”, and leading you to the solution.

When this is set up, select the target database (subscriber):

 
When you’re prompted for the distribution agent security information, make sure you use a SQL Server login that has the correct permissions on the Azure SQL database:

 
Start the replication
Now that everything is in place and configured, it’s time to create the initial snapshot. This snapshot will be used to create all the objects and data that are supposed to be replicated on the Azure SQL database. Open the snapshot agent by clicking “View Snapshot Agent Status” on the publication:

 
The snapshot agent hasn’t run yet, so press “Start” to start the process:

 
When this process has completed, you should see something similar like this:

 
Now it’s time for a final test. Insert a record on the source database (publisher):

INSERT INTO dbo.SomeObject
	(SomeValue)
VALUES
	('Test value')

 
You should see this record pop-up on the subscriber after a few seconds (default latency is 5 seconds):

SELECT *
FROM dbo.SomeObject

 
You can also check the “Synchronization Status”, and you should see a command being replicated:

 
Conclusion
Out of all the available options, I still think replication is the easiest way to replicate data into Azure. The only downside is that there doesn’t seem to be a lot of information about this, especially when you look at the “database.SECURE.windows.net” part.

If you need more than just the one-way traffic, or you want a bit more functionality, maybe replication isn’t your weapon of choice. But for me, it worked like a charm… Eventually…

Advertisements

14 Responses to Setting up replication from on-premise SQL Server to Azure SQL DB

  1. Pingback: Replication: Snapshot Agent fails on date conversion | SQL from the Trenches

  2. Hi, Will this be secure, what level of TLS(Transport level security) can be implemented for the data that is replicated from on premise to Azure

  3. Pingback: Automatically convert contained database to non-contained for replication | SQL from the Trenches

  4. Pim Brouwers says:

    This is absolutely amazing. Great work. And thank you for sharing this.

  5. Pingback: Cleanup sysobjects after disabling replication | SQL from the Trenches

  6. Waaw. This is exactly what I was looking for. You saved lot of effort.

    I had trouble with permission issue for \ReplData folder. Resolved by granting full access to SQLServer user account.

    Thanks Once again

  7. It does not look like using Replication_Subscriber database. It is empty even after the sync is complete. Are you sure we need this?

  8. Vlad says:

    It does not work for me. After following these step I get the error “The process could not connect to Subscriber “Databasename.Database.Secure.Windows.Net” Any help would be appreciated. I’ve spent hours looking around.

    • DevJef says:

      Hi Vlad,
      I would love to help you out, but I think I need some more info.
      Have you double-checked the connectionstrings, firewalls, etc.?
      And does it fail in the setup-wizard, or before/during the initial snapshot process?

      If you want to discuss this offline, you can always send me an email, or DM me on Twitter btw 🙂

      • Vlad says:

        Thank you.

        The wizard runs fine. The Snapshot and logreader agents runs fine. It’s the sync process that fails saying it can’t connect to server. The “server” is a linked server. When I check the connection on the linked server is connects fine to the Azure database.

      • DevJef says:

        I’m not sure if I understand you correctly, but are you trying to replicate to a linked server (as in: the server object on an instance)? Or are you directly interacting with the Azure database, where you configured the subscriber based on the connectionstring ([databasename].database.secure.windows.net)?

        It could be that your linked server works, and replication doesn’t. As far as I know, these 2 use different protocols to talk to a SQL instance or Azure database (but not completely sure though).

        And have you checked for the common causes of issues:
        – Is it a permissions issue (for example, can you replicate when you use the sqladmin account of the Azure instance)?
        – Is the Azure database large enough to accommodate the schema, data and (potentially) snapshot files?
        – Have you checked the snapshot agent log, and logs for distributor, SQL Server Agent, SQL Server error log, etc?

        I’m pretty sure you did all this, but I’m trying to paint a complete picture, hoping we can solve your issue.

        If we can’t work it out, I might know some people who can help you out further, and I’ll connect you with them 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: