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?