Sql-server – How to synchronise Always Encrypted data from On-Premises to Azure SQL Database (near real time)

azure-sql-databasedata synchronizationreplicationsql serversql-server-2016

Let's say I have On Premises SQL Server 2016 SP1 and Azure SQL Database.

There is no way at this point to redirect whole solution directly to Azure SQL Database and I need to have a copy of the database in Azure (few tables in fact). It is required for some real-time analytics purpose. Data in Azure SQL Database can be decrypted (probably even should be).

Now, I know that there are solutions like:

Azure Data Factory (15 min delay)
Azure Sync (5 min delay)
Transactional Replication (near real time)
Self-developed application to read-decrypt-send data (near real time)

It looks like Replication fits our requirements. I tested it and I am able to establish this solution in my environment. Except that Replication doesn't support Always Encrypted feature and this is huge disadvantage.

Self-developed application is not good resolution either because a schema of tables can vary over time (replication resolve this by itself) and there will be probably other problems which pop up in the future. I tried it and it works but only with a hard-coded schema (number and names of columns).

Other solutions, I haven't tested them yet:

Strech Database (depends upon Azure Subscription, currently not available for me)
Third-party Software (which one? HVR?)
SSIS (looks like a good approach, but a schema can vary)

Is it possible to synchronise Always Encrypted data near real time using those tools or in any other way? Do you have any experience in that matter?

Best Answer

In general, any tool that copies data (e.g. using SELECT/INSERT or bulk select/copy) should work. If the target database (in SQL DB) uses a different set of keys than the original database (in SQL Server), the tool needs to have access to both keys (and it would need to re-encrypt the data during the migration). If the target database using the same keys as the source database, it should be possible to avoid re-encrypting the data (please, see https://blogs.msdn.microsoft.com/sqlsecurity/2016/01/07/best-practices-for-moving-data-encrypted-with-always-encrypted/).

SSIS support both options to migrate the data (with and without re-encryption), but I'm not sure if it meets your near-real time requirement. You will basically need to run the SSIS job periodically.

Stretch Database supports moving encrypted data (without re-encrypting), but does not replicate encryption metadata. To retrieve the plaintext values or run queries on the part of the table that "stretches" to SQL DB, an application needs to connect to SQL Server (which has encryption metadata and will delegate the query to SQL DB). Your app can't access plaintext by connecting to SQL DB directly.

Related Solutions

Sql-server – Approaches for Real time copy of database on same SQL Server instance

Transactional replication gets you close, but as you said, changing the subscriber schema is a recipe for disaster.

Transactional replication uses stored procedures to apply the data changes. You could, with every table change, change those procedures to deal with the underlying changes. That is a lot of additional manual work, but I don't think you will find a solution without that. Those procedures are created automatically when calling sp_addarticle (See value 0x02 for the @schema_option parameter and the @ins_cmd, @upd_cmd and @del_cmd parameters.)

However, you might be better of to take a copy at the beginning of the development cycle and then not apply new changes till the end of the cycle. That is a simple backup-restore with the added benefit that you do not have to constantly adjust testing scripts do deal with the changing data.

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

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.

Best Answer

Related Solutions

Sql-server – Approaches for Real time copy of database on same SQL Server instance

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

Related Question