Tuning Parallelism on SQL Server

Parallelism and MAXDOP

There’s no doubt that parallelism in SQL is a great thing. It enables large queries to share the load across multiple processes and get the job done quicker.

However it’s important to understand that it has an overhead. There is extra effort involved in managing the separate streams of work and synchronising them back together to – for instance – present the results.

That can mean in some cases that adding more threads to a process doesn’t actually benefit us and in some cases it can slow down the overall execution.

We refer to the number of threads used in a query as the DOP (Degree of Parallelism) and in SQL Server we have the setting MAXDOP (Maximum Degree of Parallelism) which is the maximum DOP that will be used in executing a single query.
Microsoft generally recommend caution setting MAXDOP above 8:
https://support.microsoft.com/en-gb/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server

Here’s a nice post here from Kendra Little talking about how having higher settings can actually slow down your query execution time:
https://www.brentozar.com/archive/2013/12/q-can-high-maxdop-make-a-query-slower/

Out of the box, MAXDOP is set to 0, which means there is no limit to the DOP for an individual query. It is almost always worth changing this to a more optimal setting for your workload.

CXPACKET waits

Often in tuning a SQL Server instance we will look at wait stats – which tell us what queries have been waiting for when they run. CXPACKET waits are usually associated with parallelism and particularly the case where multi-threaded queries have been stuck waiting for one or more of the threads to complete – i.e. the threads are taking different lengths of time because the load hasn’t been split evenly. Brent Ozar talks about that here:
https://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/

High CXPACKET waits can be – but aren’t necessarily – a problem. You can cure CXPACKET waits by simply setting MAXDOP to 1 at a server level (thus preventing parallelism) – but this isn’t necessarily the right solution. Though in some cases in can be, SharePoint for instance is best run with MAXDOP set to 1.

What you can definitely deduce from high CXPACKET waits however is that there is a lot of parallelism going on and that it is worth looking at your settings.

Cost Threshold for Parallelism

This is another setting available to us in SQL Server and defines the cost level at which SQL will consider a parallel execution for a query. Out of the box this is set to 5 which is actually a pretty low number. Query costing is based on Algorithm’s from “Nick’s machine” the box used by the original developer who benchmarked queries for Microsoft.

Nicks_Machine

(Nick’s Machine)

Compared to modern servers Nick’s machine was pretty slow and as the Cost Threshold hasn’t changed for many years, it’s now generally considered too low for modern workloads/hardware. In reality we don’t want all our tiny queries to go parallel as the benefit is negligible and can even be negative, so it’s worth upping this number. Advice varies but generally recommendations say to set this somewhere in the range from 30 to 50 (and then tuning up and down based on your production workload).

There are many articles in the SQL Server community about how the out of the box setting is too low, and asking Microsoft to change it. Here’s a recent one:
http://www.scarydba.com/2017/03/13/change-the-cost-threshold-for-parallelism/

Tuning Parallelism

In tuning parallelism we need to think about how we want different sized queries to act on our server.

Small Queries

In general we don’t want these to go parallel so we up the Cost Threshold to an appropriate number to avoid this. As discussed above 30 is a good number to start with. You can also query your plan cache and look at the actual costs of queries that have been executed on your SQL Instance to get a more accurate idea of where you want to set this. Grant Fritchey has an example of how to do that here:
http://www.scarydba.com/2017/02/20/estimated-costs-queries/

As he mentions in the post, this assessment can be quite expensive to run – so do it when things are quiet.

Medium to Large Queries

This is where we want to take advantage of parallelism, and do so by setting MAXDOP to an appropriate level. Follow the guidelines from the Microsoft article referenced above. Here it is again:
https://support.microsoft.com/en-gb/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server

Often the answer is going to be simply to set it to 8 – but then experiment with tuning it up and down slightly to see whether that makes things better or worse.

Very Large Queries

If we have a mixed workload on our server which includes some very expensive queries – possibly for reporting purposes – then we may want to look at upping the MAXDOP for these queries to allow them to take advantage of more processors. One thing to consider though is – do we really want these queries running during the day when things are busy? Ideally they should run in quieter times. If they must run during the day, then do we want to avoid them taking over all the server power and blocking our production workload? In which case we might just let them run at the MAXDOP defined above.

If we decide we want to let them have the extra power then we can over-ride the server MAXDOP setting with a query hint OPTION(MAXDOP n):
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query

You will want to experiment to find the “best” value for the given query. As discussed above and as shown in Kendra Little’s article, just setting it to the maximum number of cores available isn’t necessarily going to be the fastest option.

Closing Thoughts

Parallelism is a powerful tool at our disposal, but like all tools it should be used wisely and not thrown at every query to its maximum – and this is often what happens with the out of the box settings on SQL Server. Tuning parallelism is not a knee-jerk reaction to high CXPACKET waits, but something we should be considering carefully in all our SQL Server implementations.

I personally have seen numerous instances where a server is going crazy, timing out all over the place, where changing the parallelism settings has instantly brought everything back to earth, operating smoothly and quickly once more.

Working with Data in Always Encrypted

In this post we’re going to create some encrypted columns in a table in a test database and look at some of the practicalities and limitations of working with Always Encrypted (AE).

There are actually a fair few limitations, but not because there anything wrong with the technology, rather they naturally fall out of logic of working with encrypted data.

Let’s just bash on with it and you’ll see what I mean.

Setting up a table with encrypted columns

I’ve got a database called AlwaysEncryptedTest, and it’s sitting on a remote instance – i.e. not my local machine. I’ve also got the Keys I created in the last post (Understanding Keys and Certificates with Always Encrypted). I’m now going to create a table with a couple of encrypted columns:

CREATE TABLE [dbo].[EncryptedTable](
Id INT IDENTITY(1,1) CONSTRAINT PK_EncryptedTable PRIMARY KEY CLUSTERED,
LastName [nvarchar](32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = MyColumnKey,
ENCRYPTION_TYPE = Deterministic,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NULL,
FirstName [nvarchar](32) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (
COLUMN_ENCRYPTION_KEY = MyColumnKey,
ENCRYPTION_TYPE = Randomized,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);

There’s plenty to discuss there already. You might first of all ask why am I specifying a particular collation for those columns (Latin1_General_BIN2) and overriding my database collation settings. I hate people who do that! The answer is I don’t have a choice, text columns encrypted with AE must have a BIN2 collation. BIN2 collations are (apart from anything else) case-sensitive.

If you think about it, it makes sense that you can’t have a case-insensitive comparisons on an encrypted column – and therefore those collations cannot be supported. When you perform a comparison based on encrypted data, what the engine is doing is comparing one encrypted value with another. To enable a case-insensitive comparison there would have to be some deterministic pattern so that you can tell that two different encrypted values differ only by case, that would be more complicated to implement, would weaken the encryption, and isn’t supported by the algorithm used by AE. The requirement to use a BIN2 collation is driven by what will happen in practice when you compare two encrypted values looking for an exact match.

So there’s something to think about. Any searches you do against encrypted text data are going to be case sensitive. So say your application allows you to search for people by name, and you’re looking for “McGiffen” (my name) then you’re not going to find it if you type “Mcgiffen” or “mcgiffen”. For someone like myself who often see mis-captitalization of their name, as little as I care , it may be a struggle to find me if my name (as it’s Personal Identifiable Information) has been encrypted.

So how do you deal with this? I don’t know – you’ll have to think about that – maybe maintain an upper case copy for searching against and transform search terms into upper case BEFORE sending them to the database.

There are also a whole bunch of other data types that you can’t encrypt which I would imagine are to do with similar practicalities of the encryption implementation. Rather than list those here you can find that set and other limitations in this MSDN article:
https://msdn.microsoft.com/en-GB/library/mt163865.aspx?f=255&MSPPError=-2147217396

You can see in the SQL above I’ve specified that the COLUMN ENCRYPTION KEY “MyColumnKey” which I created in the previous blog post. But then for one column I’ve specified ENCRYPTION TYPE of “Deterministic” and for one it is “Random”. What’s the difference?

Well, Deterministic means that every time you encrypt a given value with a given key, the encrypted result will be the same. Randomized means it will be different. With deterministic it might be possible for someone to perform statistical analysis based on the distribution of your data to infer what some common values are – with Randomized that’s not going to happen.

However, if your encryption type is Randomized then you can’t search on that column and you certainly can’t join on it (or group or index on it either).

This makes perfect sense if you realise that SQL only knows the encrypted value of your data, so if it is to return results by comparing one encrypted value with another then the values must match for anything to be returned, for that to happen they must both have been encrypted using deterministic encryption.

Finally, in the column definition I’ve specified the encryption algorithm – the one you see is the only algorithm currently supported by AE, so just use the same one.

Inserting Encrypted Data

Now we’ve got the table let’s try inserting some data.

It should be no big surprise that if I want to run the following query from SSMS it doesn’t work:
INSERT INTO dbo.EncryptedTable (LastName, FirstName)
VALUES ('McGiffen','Matthew ');

You get a longish error message that basically tells you you’re trying to stick unencrypted data in an encrypted column:
Msg 206, Level 16, State 2, Line 24
Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyColumnKey’, column_encryption_key_database_name = ‘AlwaysEncryptedTest’) collation_name = ‘Latin1_General_CI_AS’

To be able to insert data, there’s a few things you need to do.

First of all, this isn’t actually supported in SSMS 2016, so you need to download and install the vNext version which has the new feature “Parameterization for Always Encrypted”. Prior to this version if you want to test/play (beyond a few basics) with always encrypted data you had to write code in C# or another language.

Once you’ve got SSMS vNext, you can connect to your database. You have to enable Column Encryption for your connection – you’ll need to do this in your connection string whatever client you are querying your database from if your query might touch encrypted data – be that your application, SQLCMD or SSMS.

In SSMS you do that when you’re connecting to the instance in the “Connect to Server” dialog. Select “Options”, and go to “Additional Connection Parameters” and enter into the box “Column Encryption Setting = Enabled” – as per the image below:

AE_ConnectToServer

(One thing to note, is that SSMS will then try to use this setting for all subsequent connections you make until you go in and remove it. As the setting is only supported on SQL 2016 onward you get an error connecting to older instances, which is little bit of a pain…)

Now we’ll open a new query window against our database, and then we have to actually enable “Parameterization for Always Encrypted”. Right-click over the query and select “Query Options”, then “Advanced” and enable the option as shown below:

AE_Parametization.png

There’s an MSDN article about this feature here:
https://blogs.msdn.microsoft.com/sqlsecurity/2016/12/13/parameterization-for-always-encrypted-using-ssms-to-insert-into-update-and-filter-by-encrypted-columns/

In simple terms, it allows SSMS to perform additional parameterization on your query and encrypt any literal values that need encrypting before sending them to the database. In the previous versions of SSMS this would have just been sent plain text – and so the engine barks at you as in my error above.

The final step to get this to work is that you have to rewrite your original query to use variables rather than literal values in the insert. This helps SSMS to be able to manage the rest of the parameterization process. I’m going to show you a screen-shot of the updated code as it gets displayed in SSMS as there’s something else to notice:

AE_InsertSQL

See that my variable declarations and assignments are underlined in squiggly blue. If I hover over one of them, then I get this message:

AE_ToolTip

This shows me that the Parameterization is in action and working correctly.

If I run the insert now it works fine. Woo-hoo.

There are some implications of this if we think it through. You are only going to be able to do inserts based on values held in a variable or parameter. So you can only do one row at a time – and no, you can’t use a table variable for this. That means you can’t use value lists in an insert or merge statement, but logically, you probably don’t need to. Remember this is likely to be only sensitive personal information we’re encrypting – not reference data for which we may have scripts with lots of values. As such we would generally be inserting this one row at a time, and ideally through a stored procedure using parameters. So everything should be peachy.

It’s also logical to realise you can’t insert from one table to another unless the values in both tables are encrypted (hopefully using the same keys!). This goes back to the fact that the engine can’t encrypt or decrypt values – that has to happen in the client. The above insert only works because SSMS (our client) is doing all the work.

Let’s just run a select and check that we can see the data:

SELECT * FROM dbo.EncryptedTable

AE_Select1

Cool, this works. Note this will also work in SSMS 2016 as the query is simple enough to not require parameterization.

Let’s just have another look at the same data, but this time I’m going to run it direct from the server where the certificate for the Column Master Key doesn’t exist:
AE_Select2
So here you can only see the encrypted version of the data.

Out of interest let’s look at the full value for the LastName field:
0x0180B908F995B86C64511991CDE834DD4888A058D1B482E5E14297FFDA9D3C8E0828E3B80F3C58F98197AC73F5867E2837DD2E6C266FFAA95E38A7B08C111AF7EBEEC476EE6BBDB9704F1AC007CE1F613B

That’s just a little bit longer than “McGiffen” – so you can see there is going to be some storage overhead on your encrypted columns.

I’m just going to chuck a few extra rows into the table (including a duplicate record for me) so I can demonstrate a couple of points mentioned earlier. Here’s what we have when I’m done:

AE_Select3

Now let’s look again at the encrypted version of that:

AE_Select4

Remember in my table definition, I set LastName to be Deterministic Encryption, but FirstName to be randomized. If you look at the encrypted values above we can see some repeated values in the LastName column – even if we didn’t have the Ids to compare with the first recordset it’s pretty straightforward to work out which is “McGiffen” and which is “Rubble”.

Then look at FirstName. We know both records 1 and 2 though have a first name of “Matthew”. If we check the encrypted values in the FirstName column we can see they are both different – this is Randomized Encryption in practice.

Querying Encrypted Data With a Predicate

I’m going to go back to my encrypted (and parameterized) connection and try running some queries against those tables:

DECLARE @LastName NVARCHAR(32) = 'McGiffen';
SELECT * FROM dbo.EncryptedTable
WHERE LastName = @LastName

Results:

AE_Select5

So that worked fine, let’s just try and isolate those first two duplicates at the beginning though:

DECLARE @LastName NVARCHAR(32) = 'McGiffen';
DECLARE @FirstName NVARCHAR(32) = 'Matthew';
SELECT * FROM dbo.EncryptedTable
WHERE LastName = @LastName
AND FirstName = @FirstName;

This time I have no luck. I get the following error message that is telling me in a long-winded manner that I can’t compare against a column with Randomized encryption:

Msg 33299, Level 16, State 2, Line 35
Encryption scheme mismatch for columns/variables ‘@FirstName’, ‘FirstName’. The encryption scheme for the columns/variables is (encryption_type = ‘RANDOMIZED’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyColumnKey’, column_encryption_key_database_name = ‘AlwaysEncryptedTest’) and the expression near line ‘8’ expects it to be (encryption_type = ‘DETERMINISTIC’) (or weaker).
Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 27]
Statement(s) could not be prepared.
An error occurred while executing batch. Error message is: Internal error. The format of the resultset returned by sp_describe_parameter_encryption is invalid. One of the resultsets is missing.

Of course this is exactly what we expected to happen.

What I want to get across is that this all makes perfect sense. If you understand what encryption is doing, then the limitations you face naturally arise from that. Key is understanding that the encryption occurs at the client side, so if you want to do anything in a query that requires the SQL engine to interact with an encrypted column then you are going to be limited. Mostly all it can do is compare two encrypted values and verify they are the same – and only if they are using the Deterministic setting. So, if you’re implementing AE, just think carefully about what columns you need to encrypt and particularly about how you need to interact with those columns.

What’s occurring in the background?

In the last post about certificates I mentioned the extra steps that AE has to go through when querying your data. Now we’ve got a table to play with, let’s look at a Profiler trace so we can see exactly what that involves in terms of interactions with SQL. The actions that occur within the client side libraries are a black box to us, but there is still some interesting stuff to be observed at the SQL end.

I’ll just set Profiler going using a default trace and insert another record into my table.

What we see when we do this is that there is an extra call to the database before the query is executed:

EXEC sp_describe_parameter_encryption
N'
DECLARE @LastName AS NVARCHAR (32) = @pce03080ba9844c4182a38c82216e4ad1;

DECLARE @FirstName AS NVARCHAR (32) = @pba60ad612001418aac8496e355e03892;

INSERT INTO dbo.EncryptedTable (LastName, FirstName)
VALUES (@LastName, @FirstName);

,N’@pce03080ba9844c4182a38c82216e4ad1 nvarchar(32),@pba60ad612001418aac8496e355e03892 nvarchar(32)’

This call is the client (SSMS) asking the server for details about any encryption that might be involved in the query. You’ll notice that SSMS has parameterized the query, replacing the literal values with dynamically named parameters.

The stored procedure returns two result-sets. The first contains a row for each Encryption Key involved in the query. In this case that’s just one, thus one record, so I’ve transposed it to make it easier to read:

AE_Desc_Param1

You can see this is sending back to client the encrypted value of the Column Encryption Key – and also telling it where to find the certificate of the local machine to decrypt that key.

AE_Desc_Param2

The second set describes each parameter, which of the identified Keys (from the first set) it is using, and also tells us whether it is using Deterministic or Randomized encryption (this is the “column_encryption_type” value).
Armed with that information, the client can then perform any encryption required and execute the actual query:

EXEC sp_executesql N'DECLARE @LastName AS NVARCHAR (32) = @pce03080ba9844c4182a38c82216e4ad1;
DECLARE @FirstName AS NVARCHAR (32) = @pba60ad612001418aac8496e355e03892;
INSERT INTO dbo.EncryptedTable (LastName, FirstName)
VALUES (@LastName, @FirstName);
'
,N'@pce03080ba9844c4182a38c82216e4ad1 nvarchar(32),@pba60ad612001418aac8496e355e03892 nvarchar(32)'
,@pce03080ba9844c4182a38c82216e4ad1=0x01F2CD73FDD15216E2D4DC89EB6DC046EBE3FFC70A967BB6A3F822B57EA840F9D60410825455391AAFE7DF161CE78F4C3D4DE65ED8FC2435115C4E0F81E49AD820
,@pba60ad612001418aac8496e355e03892=0x01C4FBE035F600CBF61B2589028863DF732973166D9752B29CBBF1C7814DF6E8BDAD0D95897D79E28884E103F350506B5465A0914AD22EE8C4BB6630FF02F39DB4

You can see that the parameterized query has now been executed via a call to sp_executesql. You can also see that those parameters have been set to the long binary values which represent the encrypted versions of the data I specified – which in this case was “Smith” and “John”.

I covered this briefly in the last post, but it’s good to understand that the extra step before executing the actual query has to happen even when there is no encryption involved as there is no way for the client to know which columns are encrypted and which are not. As such, once you are working with AE and have the Column Encryption Setting enabled for your connections, there will be some overhead on pretty much all your queries. Important to note though – if you are doing this via .NET code then all this extra process just happens automatically for you in background. From your point of view, you just code as normal.

Encrypting Existing Data

This post wouldn’t be complete unless I touched on this subject at least briefly. So that’s what I’ll do. Touch on it briefly.

So, how do you change an existing column containing data, so that it is encrypted?
If you’ve got this far then you can probably figure out for yourself that it’s not going to be as simple as an ALTER TABLE statement. Encryption is a client side function, so if you want to encrypt some data then that data’s got to go via a client.

There are various ways you can do that. You could hand crank something and do it that way. Generally your best bet is going to be a process that involves creating a new table with the encryption set up as you want it, copying over data from the old table into the new, than dropping the old table and renaming the new one. So no, this is not going to be a particularly online activity.

It can be a fairly straightforward one though as Microsoft has provided the tool to do that for you via SSIS using the SQL Server Import Export Wizard.

Here’s a nice MSDN post that takes you through that process
https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/

Understanding Keys and Certificates with Always Encrypted

Always Encrypted on SQL 2016 is pretty easy to set up. There’s even a single wizard to guide you through the whole process. However, the best way to get into trouble with this technology is by not clearly understanding what is going on.

I personally find I understand things best when I am solid on the basics. It also means I can then usually logically extrapolate to answer any questions that come up.

So here we’ll go into a little detail about the keys and certificates involved…

To get up and running with Always Encrypted you need three things:

  • An Always Encrypted Certificate to store on your application server / client machine
  • A Column Master Key in your database
  • A Column Encryption Key in your database

You can actually create all of these through SQL Server Management Studio, in fact the first two items get created in the same step, so let’s run through that process, and we can look at what these items are, and what they get used for in more detail as we go along.
Creating the Certificate and the Column Master Key
You can find Always Encrypted Keys under the Security folder for your database. Right-click over column master keys and select to create a new one:

SSMS_AE

And up comes the GUI:

AE_ColumnMasterKey

The Name field is the name for the Column Master Key within your database – you can see I’ve decided to call mine “Wibble”.

Beneath that you can select – or create – the certificate to be associated with the Key. You can see I already have a couple in the selected key store as I’ve been through this process before, and the selected certificate is highlighted. If you don’t have one, you can select from the “Key Store” drop down where you want the certificate to be stored, and then click the “Generate Certificate” button to generate a new certificate in that store.

It’s worth noting at this point that the certificate is created on your local machine – not the server hosting SQL Server. We’re used to SSMS being the tool through which we interact with and do things at the server end, so this can be a bit counter-intuitive, but in terms of what we are trying to achieve it makes sense. We want to create a certificate that exists on the client – so in this case that’s your local machine.

Click okay and the Column Master Key and certificate get created. You can see your new key in SSMS:

AE_Wibble

To understand what this key actually is, I’m going to right-click and generate the SQL definition:

CREATE COLUMN MASTER KEY [Wibble]
WITH
(
KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
KEY_PATH = N'CurrentUser/My/D492BA86737FECDABB14D13476122C1E4BD217D2'
)
GO

We only have two values, KEY_STORE_PROVIDER_NAME and KEY_PATH. All this is telling us is where to find the certificate we just created. So you can see that the Column Master Key is just a pointer to a certificate stored on client machines.

Creating the Column Encryption Key
This is the last thing we need before we can set up encryption on our columns. You can find “Column Encryption Keys” underneath “Column Master Keys” in SSMS. Again, right-click and bring up the GUI:

AE_ColumnKey

You can see I’ve called mine MyColumnKey, and I’ve selected from the drop down that it should be created using the Column Master Key I created earlier – “Wibble”. Click okay and the key gets created.
Again I’m going to generate the SQL definition for my new Column Encryption Key so we can see what’s inside it:
CREATE COLUMN ENCRYPTION KEY [MyColumnKey]
WITH VALUES
(
COLUMN_MASTER_KEY = [Wibble],
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006400340039003200620061003800360037003300370066006500630064006100620062003100340064003100330034003700360031003200320063003100650034006200640032003100370064003200A6296213760796AA271152F6D1F1D8C5B4B84ADDB660EC700936B010918CF62FA63C9F716BCB4337D4D48E7F5ABB879F09EA0ABF2ABFDB54E4740DCD88631C533BE530A1CF491A63B0A334FBBA1971C4B22C18E7B8DFCB9975754F9232E47C5DED5857B6032D4BB95B3C1232D3168366B6A1DA4FFC637849FBC3226FFB78981BD1596DE0CD3ED894B127855FD93F3F40E6DB9602482A9AD69CE96C3D879F544534135FFCCA789CB69E3C52C3A7CCB3AFC55850DA7DD82BE963A36A3AC77ECB26CE77A01F3CC43CEA26757548785A4C5A538250C325CEB106495211A625CDAFF5E9EED96EDE3AFFC8E6518E0E29C4F796209EE5ED35B04257844E6ACF74B0EEF907F667D699FE01BCF29BDA275BC9260E03130DA7008F30CD6CBB12F60AD6B47E1B5338D18FAF50DD6EBE3712FC4E814E71CA1E4F605F97F6DB51B1BBB6819E5BF5E8DBB6EB453B268173DB0B69B6CE78A57BC7C98FBBC34BA205B7DB59FEECCEB235A38B089D31C4892FB870A305D5FB1FC69A91C009B9F9D2B572C0DA2DCAC76C6AF48DF54B3C84D7110BD903F06817FD9BC100773028F357966F029B89DF49B8564DD901349F1304124B3DC691A626568C30224766C18B631CD42097DCE0B927A9A401A51EFE05BD5DC6CB9F7F2F3166A5D2A1DECC7D060C26B41D42BF2EF9ACA5919568979F713EFA655EEEF2A4280060D0D73553633B0BEA4925E595805C
)
GO

We can see it tells us what Column Master Key this is based on, what Algorithm has been used, and a long binary Encrypted Value. So what is this?

What has happened is that in the background a value has been created that will act as the Column Encryption Key when MyColumnKey is used to encrypt (or decrypt) data in a column. That value has then itself been encrypted using the certificate that the Master Column Key is based on. The encrypted value of the Column Encryption key is what then gets actually stored in the database.

That might seem overcomplicated. If we can encrypt data using the certificate, then why do we need a separate key in the database?

It actuality it’s very important that we have both. Imagine the scenario where we just use a certificate as the key (or just a single key in another form) and that expires, or worse there is a breach and it gets copied. In this case we are going to have to change our certificate or key. But if we change the key, how are we going to read the existing data in the table?

There is only one way, which is to decrypt all the existing data, and then re-encrypt it with the new key. Let’s say you have tens of millions of records, this might take a while, and will certainly require the application to be taken offline. Also, while this process is being undertaken, the data is unencrypted and at risk.

With the approach taken by Always Encrypted, we have a way around this. Let’s say we have to replace our certificate. All we need to do is create a new COLUMN MASTER KEY based on the new certificate, then we can decrypt our COLUMN ENCRYPTION KEY, ENCRYPTED_VALUE using the old certificate and re-encrypt that value with our new certificate. Because the actual Key value itself hasn’t changed then we can continue to read the existing data using the new certificate.

In practice this is done for you in the background through a process called “Key Rotation”. A given COLUMN ENCRYPTION KEY can actually support having two COLUMN MASTER KEYS at the same time – each of which would have it’s own encrypted version of the same underlying key. This allows the process to add a new master key and associated encrypted value, before removing the old one – which allows the key rotation process to occur fully online.

There is (understandably) a performance overhead to having two Keys at once so avoid doing this for any other reason.

I’m not going to go any further into the process of Key Rotation here, but it is generally seen as good practice to retire and refresh your certificates regularly – when you create them through SSMS they get a default expiry data one year in the future. So if you are implementing Always Encrypted, it is important to be confident you know how you will approach things when your certificate expires.

So how do the keys get used in practice?
When you run a query over a connection with column encryption enabled, the following steps occur:

  1. The client sends a preliminary call to the SQL Server Instance containing the query text. In this call it is asking for details of potential encryption on any columns involved in the query and what keys may be involved.
  2. If there are encrypted columns involved, SQL supplies that information, as well as the Column Master Key (which tells the client where to find the relevant certificate locally), and the encrypted value of the Column Encryption Key for each encrypted column.
  3. Using the information and keys, the client can then encrypt any values it needs to before sending to the database. For instance, if it is inserting data into, or looking for a particular value, in an encrypted column.
  4. The modified query is now sent to SQL for execution.
  5. SQL returns any results – with values in encrypted columns remaining in their encrypted form.
  6. If a resultset is returned, the client uses the information and keys supplied in step 2 in order to decrypt any encrypted values.

So you can see there are a few extra steps over and above your normal query execution, and that does create some performance overhead.

Final thoughts
Once you get into the business of having encryption keys then the process by which you manage them is going to be critical. If they are too easy for anyone in your organisation to access, then you may sabotage your efforts in encrypting the data in the first place.

You are going to want to have different keys in your production environments to those you have in test and development as a minimum.

Think about how you are going to manage that through your deployment processes. Do you want to generate new keys and certificates with fresh deployments? In which case where are they backed up, and who has access to them? You want to be absolutely sure you can recover the certificate your Master Column Key is based on if it is lost from your production environment. Otherwise your encrypted data is gone for good.

If you are shipping a product to a client with Always Encryption enabled – do they have the knowledge and processes to manage the keys moving forward?

And what is your plan for key rotation? How will the right people be reminded before certificates expire that they need to do something about it?

This is a great technology, and Microsoft has made it as simple as possible to use in practice. Nonetheless, it does need to be used with care.