Make sure you consider Transparent Data Encryption (TDE) as well; that would be my recommendation for your needs:
- Files are encrypted on disk
- Backups are encrypted
- No impact to clients
- No need to change anything in the applications themselves
Performance impact is not zero, but is only a one-time CPU hit as the SQL server opens the SQL pages from disk (or writes changed pages to disk). In my experience this impact is low to negligible.
EDIT: TDE is not column-level encryption, and requires no changes to any of your queries or stored procs (hence "transparent").
The SQL engine decrypts the data pages when they are loaded from disk into memory, and so are available for your queries to operate normally, while still leaving the data encrypted "at rest" (on disk, and in backups).
Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.
This technology was designed to have the entire encryption process be completely transparent to the applications accessing the database. It does this by using either Advanced Encryption Standard (AES), or Triple DES, encrypting the file pages and then decrypted as the information goes into memory. This inhibits limitations from querying the data in an encrypted database. This is essentially real time I/O encryption and decryption and does not increase the size of said database.
Also note, that as a result of TDE, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords.
Conclusion: The average elapsed-time overhead was 6.36%. This confirms that “in some of the testing ..., the overhead was measured to be less than 5%.” Given the benefit of having “at rest” data encrypted, this seems like a very reasonable cost.
Having run a number of similar tests on new features in SQL Server, I am amazed that TDE has such a minimal effect on the time to run a workload, and it has no obvious affect on CPU utilization. In my mind, this is a clear win. TDE adds very little cost to your processing, it costs nothing (assuming you’ve already paid for Enterprise Edition), and you don’t have to change your application at all. Bravo to the development team for delivering such a strong new feature.