Options for Data Encryption in SQL Server 2008 R2 Standard Edition?

Since you are using Standard Edition, you cant use TDE. So other options are

Using encryption keys at instance/database level :

SQL Server has two kinds of keys: symmetric and asymmetric. Symmetric keys use the same password to encrypt and decrypt data. Asymmetric keys use one password to encrypt data (called the public key) and another to decrypt data (called the private key).

SQL Server has two primary applications for keys: a service master key (SMK) generated on and for a SQL Server instance, and a database master key (DMK) used for a database.

Also, you can have encryption at column level by creating a MASTER KEY ENCRYPTION along with CREATE CERTIFICATE and then CREATE SYMMETRIC KEY.

An example of how this can be done is described at Encrypt a Column of Data

Reference : SQL Server and Database Encryption Keys (Database Engine)

At Drive level :

Using BitLocker as it is a Drive Encryption data protection feature available Windows Server 2008 R2. Refer to : BitLocker Drive Encryption Overview There are many opensource or third party software to do the same job but at additional cost.

Note: The most important bit is ALWAYS backup your encryption keys.

You can use third party software like Redgate's sql backup which allows you to encrypt backups using passwords.

Depending on what level you need encryption will determine if it is worth upgrading to enterprise edition or not. You have to evaluate native TDE encryption vs encryption keys and certificates vs open source vs disk encryption.

Old question, I understand, but an answer in addition to the above answers exists since SQL Server 2016 SP1.

SQL Server 2016 introduced "Always Encrypted" as well as other security features like Dynamic Data Masking and Row Level Security. These were features originally released in Enterprise Edition Only.

They now exist as features available in any SQL Server 2016 SKU - Enterprise, Standard or even Express.

Worth checking out and worth looking at the upgrade process now. You can read more about this on Microsoft's post about SP1 for SQL Server 2016..

First, ask the question "why am I encrypting this database?"

If the answer is "to hide data from the database administrators" then SQL Server encryption of any kind is not the answer. In that case the client application should be encrypting data and storing the ciphertext in the database. That way neither the database server nor the database administrators (nor the network administrators, as well) ever see unencrypted data or encryption keys.

Encrypting the data on the client before sending it to the server means that the database files and database backups are all encrypted and are protected against someone stealing a backup tape or hard disk and are also protected against rogue or compromised administrator accounts.

See Laurentiu Cristofor's blog article Who needs encryption? and HIPAA Compliance with SQL Server 2008 on the SQL Server Security Blog