Replicate data of some tables of a local sql server to a database in Azure

Question

We have a SQL Server which runs on a machine in our office with a database and tables which get written to daily.

Due to some business requirements we need some of these tables replicated on to a database on Azure. There will be not too many rows, only some hundred / thousand rows per day.

I have several ways to do this:

• Create a job in our application which gets the data from the local SQL Server and writes it to the Azure DB via Entity Framework

• SQL Server Replication (could this be used?)

• Stretch Database (unfortunately not possible because some of the tables can't be stretched apperently..)

• Linked server in the Azure-DB with a view that selects our local DB? (is this possible?)

Did you have such requirements before and what did you use to solve this?

What different option would you suggest?

Thanks in advance :-)

Additional infos:

• SQL Server v13.0.4435.0
• Microsoft SQL Server Standard (64-bit) Edition

Answer 1

My answer is assuming "we need some of these tables replicated on to a database on Azure" you are referring to Azure SQL Database.

First of all you need to make sure that you requirement can tolerate the generic restrictions with Azure SQL DB.

Azure SQL Database resource limits

I did deploy these 2 methods with similar requirement you have. Again you need to remember some of the on prem feature will not be supported.

1. Sync data across multiple cloud and on-premises databases with SQL Data Sync (Preview)

General limitations

• A table cannot have an identity column that is not the primary key.
• A primary key cannot have the datetime data type.
• The names of objects (databases, tables, and columns) cannot contain the printable characters period (.), left square bracket ([), or right square bracket (]).

Azure Active Directory authentication is not supported.

• Unsupported data types FileStream
• SQL/CLR UDT
• XMLSchemaCollection (XML supported)
• Cursor, Timestamp, Hierarchyid

2. Transactional Replication.

You can also use transactional replication to migrate a subset of your source database. The publication that you replicate to Azure SQL Database can be limited to a subset of the tables in the database being replicated. For each table being replicated, you can limit the data to a subset of the rows and/or a subset of the columns.