Enabling Transparent Data Encryption (TDE)
Transparent Data Encryption (TDE) is a feature added in SQL Server 2008 which allows you to encrypt an entire database at a time. A major potential benefit is that when TDE is enabled, all backups are automatically encrypted, which may be worthwhile if you have concerns that copies of the backup files may end up on insecure file servers.
If you need TDE, or want to play with it on a development system, I've attached a script to this post to help you enable it as quickly and easily as possible. In fact, that script is my main purpose for this post, but I figured I'd add a few relevant thoughts to help folks decide whether or not to download and look at the script.
Database encryption encodes the data at-rest (on the hard drives) and helps protect against file-level attacks. It makes it difficult or impossible (for most folks) to read the data without the key, but it comes with a cost – in extra CPU cycles both to encrypt and decrypt. Which leads into a set of precautions...
Warning: Don't turn on TDE just because you can. Here are some issues to consider before you do. First, all encryption/decryption increases CPU utilization, so it should not be used unless it is needed. Secondly, if you use TDE, you must backup the encryption keys (and optional passwords) because your backups will be useless without them. Third, if any database is encypted by TDE, the tempdb will be encrypted, and if tempdb encryption/decryption causes performance problems on non-encrypted databases, you should consider having your encrypted databases on a separate instance of SQL Server from the non-encrypted databases.
Other Comments: For a database that is small enough to fit entirely into memory, or for databases with infrequent inserts and updates, the extra CPU cycles will usually be an insignificant additional cost. Even for in-memory databases, though, encrypting data on disk is a low priority because file access protections provide a strong first-line-of-defense, and encrypted data on disk is useless if someone can pull it out through conventional queries. Even on a laptop, full-disk encryption such as BitLocker can provide a better alternative in many cases. Finally, note that TDE will not encrypt data stored in the file system using SQL Server FileStream.