Many DBAs have data that must be encrypted at rest, but do not have the ability to migrate to SQL Server Enterprise for TDE or to SQL Server 2016 or newer to take advantage of its Always-Encrypted feature.  In these cases, a solution to which many companies turn is disk encryption.

Disk Encryption seems easy enough.  Take the SQL Server down, encrypt the disk, give the service account access to decrypted data, and it’s done.  And if you believe that, I’d like to sell you the Eiffel Tower for scrap metal.  There are several things that you need to carefully consider and monitor.  Disk Encryption of SQL Server databases is a Pandora’s box if you approach it casually.

Disk Encryption: An Overview

Disk Encryption is just what its name implies:  The hard drives on which SQL Server files reside are encrypted at the drive level.  Usually this is accomplished with a software product that stands between the I/O subsystem and the application programs, including SQL Server.

I/O requests are received by the encryption program.  Reads pull encrypted data from the disk, decrypt it and present it to the requestor.  Writes take the data fom the requestor, encrypt it, and write it to the disk.  If a bad guy gets in and is not using a credential authorized to decrypt, he or she won’t even see the files.  If the credential is OK to read encrypted (e.g., a utility to copy encrypted backup files to a tape drive), the file will be presented encrypted with no processing.  Finally, the credentials that have decrypted access will receive the data decrypted.  This is strong protection against an intruder.

SQL Server Must Be Dependent Upon Encryption

Almost all disk encryption solutions run as a Windows service.  If you bring up SQL Server before the Encryption Service, all databases with one or more files on an encrypted drive will come up as “pending recovery.”  If master or tempdb happens to be on the encrypted drive (I recommend that you never do this), then SQL Server will crash.

The solution is to make SQL Server’s service dependent upon the Encryption Service.  This is not that difficult.  Here are the steps:

1. Open an elevated command prompt (If you are running Windows Server 2003R2 or older, the elevated command prompt isn’t available; please tell me where to send the sympathy card).
2. There is a command that you run:

   sc config [service name] depend= <Dependencies(separated by / (forward slash))>

   For example, if you are using SafeNet ProtectFile and have the default instance of SQL Server, you would use this command:

   sc config MSSQLSERVER depend="SafeNet ProtectFile Service"

   Look for the “success” message in the command window.

3. Restart the service.

Subsequently, any system restart will hold SQL Server until the encryption file service is running. You should also set the same dependency for Integration Services and Analysis Services.

You Should Adjust SQL Server Memory Usage

The encryption service will need RAM for its operations.  The software vendor should be able to give you a number that represents a balance between performance and hardware consumption.  Reduce SQL’s memory consumption by that amount.  This may require adding RAM to the server in question.  Measuring the effect on SQL server (e.g., Page Life Expectancy) versus encryption overhead (usually seen in longer I/O delay times) is critical.

You Might Carefully Consider Reducing SQL’s CPU Usage

You might want to consider – with great caution – changing the processor affinity setting to relinquish one or two cores back to the operating system.  Do this with great caution and much testing in lower environments! The idea is to keep SQL Server and the encryption service from fighting over CPU, and thereby managing the processor queue length down to improve encryption performance.

If you decide to do this, I would not attempt to manually set the affinity masks.  Instead, I would use the server properties in Management Studio, click on processors and uncheck the automatic settings, and then uncheck one processor for affinity and I/O affinity and click ok.  Carefully monitor your performance!  If it does not help your encryption performance then roll it back.

Backup and Restore Performance Will Take a Hit

Brace yourselves.  When you do a BACKUP DATABASE or BACKUP LOG command, the data flow through this path:

1. SQL asks for the page
2. Encryption reads the data from the disk and decrypts
3. Encryption presents the decrypted page to SQL
4. SQL Writes the backup page

And it gets better (meaning worse) if you are backing up to a drive on the same machine that is also encrypted:

5. Encryption receives the backup page and encrypts
6. Encryption writes the encrypted page to the disk.

Invert the process for a restore.  With some disk encryption solutions, I/O intensive operations like backup and restore see run times jump by as much as 1200%!  Be prepared to adjust backup schedules.  If you have a nightly full backup, you may be forced into a weekend full and nightly incremental runs.

Memory-Starved Machines Will See Significant Performance Degradation

The key to preserving SQL Server performance in a disk encryption environment is to let the lazy writer do I/O asynchronously.  You have to give SQL Server enough RAM to hold pages while it waits for the Encryption service to do its writing.  If you don’t or are unable to do this, large selects and updates will slow SQL server down as its I/O is running through encryption and decryption.  Be bold and ask for as much RAM as you can cram into the server – before you implement disk encryption.

Disk Encryption will dramatically affect Large selects and table scans.  Any operation that causes a large amount of I/O to and from the disk will cost you dearly.

Benchmark known “hog” queries before and after disk encryption in a lower environment in order to get a rough idea of the impact.  You can also test how much effect CPU affinity and RAM usage has on such I/O.

Are You Sure that You Can’t Avoid This?

Disk Encryption offers security, but it comes at a price in performance.  Are there alternatives?

• Can you upgrade SQL Server and use TDE? – This involves a licensing cost, but the performance will certainly be better. Moreover, you have security in that the database cannot be attached or restored to a different server without the requisite key.  If your shop must also encrypt non-SQL Server data, this solution falls short.
• Can you move to 2016 and use Always Encrypted? – This solution is more elegant since you can encrypt only the columns that contain sensitive data. Also, the data are moved encrypted to the client.  Finally, a person with Management Studio who queries the database won’t see the unencrypted data.  The downsides come if your application cannot handle the SQL upgrade, your licensing budget won’t handle the SQL upgrade, if you must encrypt all tables and columns in the database, or if you need non-SQL Server data protected.
• Can you do this in the SAN? – If your SAN offers encryption at rest within the SAN, that will likely be much faster than a client-side EAR tool, and perhaps without any noticeable degradation in performance.

Conclusion

As security threats proliferate, the need for encryption will continue to rise.  We who are the Guardians of the Data must be ready to make SQL Server respond and to manage the performance issues that are inherent in every data encryption solution.

----

John F. Tamburo is the Chief Database Administrator for Landauer, Inc., the world's leading authority on radiation measurement, physics and education. John can be found at @SQLBlimp on Twitter. John also blogs at www.sqlblimp.com.