We are using sql 2008. We have large amounts of data sources (mainframe files that we import as flat files using ssis) and many of the data sources has confidential information.
The confidential data is in many tables and they are all not related as each table is independent of any other table. We have encrypted the confidential data columns (for ex: ssn) in all these tables.
We have written a .net clr assembly to encrypt the individual columns and it works fine. But I read somewhere on the web that individual column level encryption is not recommnded since it can have a huge performance hit. And we are having a huge performance hit. Our data sources are huge and the data is imported every day. The encrypt and then the data load takes a long time every day. (we have verified the .net clr is fine, its just the huge volume of data that is causing the performance issue. Without the encrypt, the data load takes an hour and with the encrypt it takes over 3 hours).
Also, this approach takes a hit on the subsquent processing logic as some of these sensitive data are key columns in the joins of many sql queries. (Of course we have a decrypt function also).
I would like get suggestions, recommendations or best practices used in the implementation of sql encryption in real world scenario. Also, would like to hear how we can restrict the access to these for the sql developers involved in this team. (Just assume that we cannot mask the data as many of these practical business scenarios cannot be validated with dummy or data masked values. The developers needed one days worth of real world data to develop/produce the results needed for the business)
Please appreciate if responses included best practices/ implementations in practical business situations