I often show the Transparent Data Encryption (TDE) feature in SQL Server 2008, and then I get asked “Is that in Standard edition?” and it’s only in Enterprise.  Also TDE is very prescriptive; if you apply it to your database, it automatically applies to your backups, which then can’t be compressed (another Enterprise Edition only feature),  Also TDE doesn’t work on filestream filegroups. So where does that leave you if you can’t justify Enterprise edition in your business or you can’t use it for other reasons?

You have 2 choices:

• If you’re running Windows Server 2003 you can use the Encrypted File System (EFS).   This is good because you can decide which files to lock down, but takes 5-8% overhead on your system.   This will work on any file used by SQL Server any version, any edition.
• If you are using Windows Server 2008, then there is the BitLocker feature (introduced in Vista).  This encrypts a whole volume and will work on all IDE & e-SATA volumes attached to your server. BTW why is a Vista feature in Windows Server 2008?  Simply because of customer demand, apparently servers get stolen from small offices as well as the PC’s and laptops, and the tea money.

Finally if you only want to protect part of your database you can encrypt individual columns in SQL Server 2005. This would require you to re-write the access mechanisms to this data, where the other options above are transparent, provided the data is where it is supposed to be. The details on how to do apply encryption to individual columns are here. I would add that column encryption might be needed in addition to other forms of encryption described above to protect such things as credit card details , NI numbers and personal address information, from unauthorised access by parts of the business who aren’t allowed access.

So lot’s of choice, as ever with Microsoft, but also lots of advice on the sites above. There is  also our very own evangelist Steve Lamb on hand for advice as well.