It is not necessary to create and use a key persisted in the database for encryption. If you are willing to manage the key yourself—and take responsibility for keeping the secret secret—use the EncryptByPassPhrase and DecryptByPassPhrase functions. These functions require only a pass phrase as the first parameter and either clear text or ciphertext as the second, and they do not let you select the algorithm used.
It means you just need to have the phrase, functions and data (encrypted or decrypted); it does not matter in which SQL Server instance or database you are.
Make sure that you use a strong password. SQL Server does not do any kind of password checking, even if you are using the password validation features available for SQL logins.
Note The encryption algorithm used by EncryptByPassPhrase is undocumented.
I created two functions one to encrypt data and second for decryption. Further from security point of view you can encrypt these functions just to avoid seeing their body text.
-- Data Encryption Functions
Create Function DBO.EncryptBPData(@Data varchar(Max)) Returns varbinary(Max)
Declare @v1 varbinary(Max)
Set @v1 = EncryptByPassphrase('#$MyTestEncryption$#',@Data)
-- Data Decryption Functions
Create Function DBO.DecryptBPData(@Data varbinary(Max)) Returns varchar(Max)
Declare @v1 varchar(Max)
Set @v1 = Convert(varchar(Max),DecryptByPassphrase('#$MyTestEncryption$#',@Data))
-- Usage of above functions
DECLARE @VAR1 VARCHAR(100)
DECLARE @VAR2 VARBINARY(100)
SET @VAR2 = DBO.EncryptBPData('This is a test phrase to encrypt.')
SET @VAR1 = DBO.DecryptBPData(@VAR2)