Transactional Replication from SQL Server to Azure SQL – How to Set Up

azure-sql-databasereplicationsql server

I'm attempting to setup transactional replication between an on-premises SQL Server and an Azure SQL database. Unfortunately, it looks like Azure SQL doesn't support SQL Server Replication, and Azure Data Sync seems like it's been abandoned (and even if not, it chokes on databases with over 200 tables).

What are some alternatives to setup continuous transactional replication between an on-premises SQL Server and an Azure SQL database? My goal is to only replicate a handful of tables, and I'd like it to happen in near-real-time (as opposed to on a schedule).

Best Answer

This is supported (if I understand your question correctly).

See Transactional Replication to Azure SQL DB now in public preview

Below is the article written by Jean-Yves (JY) Devant, a Senior Program Manager for SQL Server.

Copying Entire Article ,so to avoid link rot

This new capacity of Transactional Replication is available starting with the following versions\updates of SQL Server:

Community Technology Preview (CTP) 3.0 of SQL Server 2016
SQL Server 2014 Service Pack 1 Cumulative Update 3
SQL Server 2014 RTM Cumulative Update 10
SQL Server 2012 Service Pack 2 Cumulative Update 8

This enables two main scenarios:

Migrate your data to Azure SQL DB with no downtime.
Bridge SQL Server on-premises/on VMs to Azure SQL DB.

The way we enable Azure SQL DB as a subscriber is by extending existing logic of Transactional Replication. From the perspective of your experience with it, the only difference you’ll notice is when you create a subscriber: you give the URL to your Azure SQL DB instead of giving the name of a server or an instance. Because we designed the feature so you have a seamless experience, and you already know about Transactional Replication, the learning curve to use this capacity is 0. It will be very easy and quick for you to start replicating your data to Azure SQL DB.

How does it work? There is "no replication" service per say in Azure SQL DB. The actual replication of data is performed through the Distribution agents.

The chart below shows the architecture from a high level.

Related Solutions

Sql-server – Replication between SQL Server 2005 and Azure database

I'm not sure about the core of your question ("Can you set up replication between SQL Server 2005 and SQL Azure Database?"), but my assumption is a resounding "NO".

There are tools to migrate from SQL Server to Azure though - it's been discussed on Stack Overflow here and here.
The consensus seems to be that This SQL Azure Migration Wizard (CodePlex hosted project) is the way to go. It doesn't appear to be a live-sync migration though, so you will probably need to take an outage window unless I'm missing something...

Sql-server – Transactional Replication guidance

There are many ways by which you can transfer data and all depends on this part of question..

copy them regularly to a SQL Azure database

1.If you want once a day,you can write ssis package and do the logic of checking/merging data in that

2.You also can write C# program which does this for two tables and run this through SQLServer agent..

3.if you are looking for near realtime data transfer,i would go with Transactional replication.This is supported now..

Setting up is very easy task and same as Onpremises ,but with one exception of no pull subsribers .

This article from Joe Sack will help you on how to set up..

There are also some limitations which you need to account for..

The publisher and distributor must be at least at one of the following versions:

SQL Server 2016

SQL Server 2014 SP1 CU3

SQL Server 2014 RTM CU10

SQL Server 2012 SP2 CU8

You can read this link for more details..

Best Answer

Related Solutions

Sql-server – Replication between SQL Server 2005 and Azure database

Sql-server – Transactional Replication guidance

Related Question