1

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:

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?

Improve this question
2
  • I don't think there are any off the shelf things that are going to do all of this for you, and based on your specific requirements I'd roll your own. "Near real time" can mean a lot of different things and you'll have to extensively test any option yourself. It also depends on why you're using Azure and what that data is being fed into... Commented Dec 20, 2017 at 21:22
  • 1
    I built my own solution based on SSIS package, it works limited to schema changes but I have idea how to resolve it. I will post this resolution here shortly. Commented Dec 21, 2017 at 10:25

1 Answer 1

Reset to default
2

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.