Skip to main content

Always Encrypted with Secure Enclaves - Column Encryption Key (CEK) rotation performance

 When using a data encryption mechanism , for example, Always Encrypted with secure enclaves,  we also facing a challenge that the encryption key should be rotated regularly. 

 To get a sense how long the key rotation process might take we performed the following test - Azure SQL Database was created in two tiers - DTU-based Standard S6 and S7.

A test table 

create table [aese].demo(
id int identity primary key
 , vc1 varchar(64) 
 , nvc1 nvarchar(64) 
 , nvc2 nvarchar(256)
 , vb1 varbinary(512)
 , d1 decimal(19,4)
 , m1 money 
 , i1 int
, bi1 bigint
 );

was populated with 1000000, 5000000, and 10000000 rows.
All the columns (except the primary key) were encrypted and then re-encrypted with a new CEK.
The re-encryption time was collected.

For varchar/nvarchar/varbinary columns the random number of the same character were loaded using operations like this 
replicate(N'作', ceiling( rand()*63)+1)

The 'var'-type columns were populated with data consuming on average almost exactly a half of column's declared maximum length. For nvarchar columns the amount of data was double due to Unicode nature of the replicated character.

For numeric types (int, bigint, money, decimal(19,4))


 

The data type doesn't make much difference for the encryption time.


Also for a small number of rows (1-5 millions) the database tier (S6 or S7) also makes a little difference.
But when the number of rows increases the higher tier (S7) significantly decreases the total time (in about 60%).

Thus, for the numeric data types a rough estimate re-encryption time is about 50 seconds per 1 million rows when using the S7 Database Tier.

For the 'var'-type columns (varchar and varbinary) the results



also show that for a small number of rows (1-5 millions) the database tier (S6 or S7) also makes a little difference.

The varchar(64) with about 33 bytes populated on average a rough estimate re-encryption time is about 43 seconds per 1 million rows when using the S7 Database Tier.
For varbinary(512) with average length about 257 bytes a rough estimate re-encryption time is about 88 seconds per 1 million rows when using the S7 Database Tier.


And finally the results for nvarchar columns















show that nvarchar(256) with average length about 257 bytes (because each character takes 2 bytes) a rough estimate re-encryption time is about 78 seconds per 1 million rows when using the S7 Database Tier.

The all test data attached below.



Comments

Post a Comment

Popular posts from this blog

Create 3-Node Windows 2012 Multi-subnet Cluster

Environment There are two Data centers connected via a WAN link. Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted). The final state will look like depicted above: -           Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster -           The servers at the Primary Data Center will have a vote (Vote=1) and the ...
2 comments
Read more

Joining Windows 10 to Azure AD Domain

As of October 2016 to join Windows 10 computers to Azure AD Domain service requires these steps: Create a VNET in the classic portal . The VNET must be placed to a region where Azure AD domain service is available (( https://azure.microsoft.com/en-us/regions/services/ )  In the classic portal  go to Directory -> Configure and enable the domain service. And wait for ~ 30 min When completed the IP address will be populated Go back to the VNET configuration and add a DNS server with the IP (10.0.0.4 in this case) Create the "AAD DC Administrator" administrators group (again in Directory -> Group). Members of this group are granted administrative privileges on machines that are domain-joined to the Azure AD Domain Services managed domain. Add to the group your users who are supposed to have the administrative access on a Windows 10 computer go to Settings -> Accounts (this is true for Windows 10 version  1607) then select 'Access...
1 comment
Read more

SQL 2012 AlwaysOn: Synchronous vs. Asynchronous commit. Performance impact

Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers. The configuration looks like this: Primary Data Center                         Secondary Data Center                        SQLDEV1                                        SQLDEV3          SQLDEV2 The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit. The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results Approximate round trip times in milli-seconds:     Minimum = 39ms, Maximum = 63ms, Average = 42ms I also created a very simp...
4 comments
Read more