Our requirement is that the data at rest is to be encrypted. MDF/LDF files to be encrypted, backup files to be encrypted. I found 3 options. We use Sql Server 2016 (SP1)
We hesitate to go with option 1 as it is very slow. Moreover our requirement is data at rest must be protected. We are looking for high performance solution.
Please point me right direction here. Are there any alternative tools available?
Make sure you consider Transparent Data Encryption (TDE) as well; that would be my recommendation for your needs:
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).
Further links:
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.
You can use TDE (Transparent Data Encryption) is what you should look and test. It provides encryption at rest with some CPU hit. I wrote about my experience here with TDE.
Since you are using SQL 2016 SP1, you should upgrade to CU4 as I encountered a bug and its fixed in 2016SP1CU4 and up.
Make sure you backup your certs as well so that you can ensure that a restore is doable when you need.
