There are four main points to consider:
- What do I need to accomplish
- Cost of implementation
- Cost of rollback/removal
- Performance impact
As far as the second question is concerned, TDE seems like a no-brainer choice. It can be easily implemented on a database as it is "transparent" to any application or user accessing the database. As a result it is a low cost way of getting to the goal of protecting your database. So the cost of implementation is low.
There is however a known complication with rolling back TDE - essentially decrypting the database. And here is where, in regards to question 3, we need to evaluate the cost of rollback. The database log does not get fully decrypted. The reason for this is that the transaction log is meant to be written to only once. In order to ensure a fault free rollback it is recommended to rebuild the database log from scratch. This involves changing the recovery model from full (if used) to simple and the restarting SQL Server instance. Restarting SQL Server requires downtime, usually a very small window of downtime, but downtime nonetheless. For more detail on the issues with removing TDE and the steps above read Steve Perry's (not the singer from Journey, at least I'm pretty sure he isn't) very good article outlining the steps needed to ensure proper TDE rollback on sqlservercentral.com.
But the real complication comes in when failover strategies are employed, such as mirroring. When two databases are in a mirroring relationship the principal cannot be switched to simple recovery model. I still haven’t fully tested this scenario but, my understanding on my understanding and conversations I've had, mirroring needs to be removed – watch this space as I'll post my findings after testing. What most likely needs to be done is:
- Take databases out of mirroring and keep traffic pointed at the primary
- Backup the database and the log of the primary and then restore to the secondary.
- Before putting the secondary back into mirroring follow the steps to remove TDE but do not remove the Key of the certificate.
- Put the databases back into mirroring and then manually failover
- Repeat steps 1 - 3 on the new secondary (This was the primary until you failed over)
- Put the databases back into mirroring and then failback if needed
This type of encryption is, in a way, easier to consider and plan for. At the outset, if you have an existing application and database the cost of implementing cell-level encryption is high. In addition to setting up the various encryption keys you'll need to add the following functions to every select, update and insert statement that your application(s) use:
And, for existing data, new columns need to be created in each table where cells require encryption. So the applications will need to be modified to handle the new columns (unless they are named the same - but that depends on coding standards). This may also disrupt replication and reports or other ETL type procedures may need to be modified to use both the new columns and the encryption functions, if needed.
Rolling back cell-level encryption I would see as similarly resource intensive. All the steps mentioned above would have to be reversed.
In both cases, implementing and rolling back, cell-level encryption would also require a large amount of testing. Especially if the data to be encrypted is regularly accessed data, e.g.. commonly accessed personal details like names, dates of birth, phone numbers, addresses, etc.
This last consideration is a very important one. And as with all performance related issues, there are compromises to be made. this is not so much the case with TDE. It has been designed as a low impact feature of the database engine itself. So in terms of implementing, performance is not really a big consideration.
Not so cell-level encryption. It is very important that every column that is encrypted be carefully thought through. There are a couple of potentially serious performance issues if they are not well planned out:
- The encrypt/decrypt process adds additional overhead to the system.
- Mainly due to the encryption process’ introduction of randomness indexing on encrypted data columns is not possible.
In practice this means that any columns used in WHERE or JOIN clauses should be ruled out for encryption. This is probably not always possible. But the risk to performance needs to be understood.
- Database size may also be affected
Although this isn’t as much of an issue as it used to be, it is still worthy of consideration. As all encrypted columns need to be of the varbinary data type, they will most likely be larger than the column they replace.
For these reasons, I hope it is clear why planning for cell-level encryption is so important. A heavily taxed system may be tipped over the edge. While one that is efficient may be significantly degraded due to the necessary changes to the indexes. The performance considerations may, in the end, point to a higher cost of implementation/rollback as existing queries and indexes will require more modification than just adding the encrypt and decrypt functions. In order to maintain performance queries may need to be radically modified to change the way they access the data and join tables together.
My next post on encryption will deal with the data being sent to and from the database server.