How can I synchronise Always Encrypted data from On-Premises to Azure SQL Database (near real time)? 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?
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.