2005 introduced real column encryption and it works, provided you respect its key hierarchy and one operational rule that decides whether encrypted data survives disasters.

The hierarchy in one breath: the Service Master Key at instance level protects the Database Master Key, created per database with CREATE MASTER KEY ENCRYPTION BY PASSWORD, which protects a certificate from CREATE CERTIFICATE, which protects the symmetric key that actually encrypts your data, CREATE SYMMETRIC KEY IDKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE IDCert. Layers exist so the fast symmetric encryption of data sits under slower asymmetric protection of the keys.

Daily use is three verbs: OPEN SYMMETRIC KEY IDKey DECRYPTION BY CERTIFICATE IDCert, then EncryptByKey(Key_GUID('IDKey'), @value) writing varbinary into the column and DecryptByKey(column) reading it back, then CLOSE SYMMETRIC KEY. The column type becomes varbinary sized for ciphertext growth, indexes on it become useless for ranges, equality lookups survive via a separate hash column if the application needs them. Every code path touching the column changes, which is the honest cost of column encryption in any era.

The rule that must not be gotten wrong: back up the keys, now, before the first row encrypts. BACKUP CERTIFICATE IDCert with its private key and BACKUP MASTER KEY to files stored away from the database backups, because a restore onto a rebuilt server without those key backups turns the encrypted column into permanent noise, the database backup alone does not save you. Test the full restore including key restoration once, deliberately, before compliance signs off. And for the record 2005 is deep past end of life, the encryption above protects the column while everything around it goes unpatched, ammunition for making next year's conversation this year's.