Greetings,
Brace for newbie question. The business is clamoring for database encryption of backups before storing them off site in an encrypted S3 bucket, while still having our daily onsite restore for reporting reasons. Backups are currently done via the Ola scripts and automated restores are handled via a powershell script run as a sql job.
Our onsite restore includes the backups from 24 separate availability groups, onto one server.
My question is: Is it possible to create a database master key and certificate on another system, like my local host and have each member of our prod layout and the restore target use the same certificate? Are there any cross-compatibility issues I would need to worry about i.e 2016 certs wont work on 2014, or certificates created on one domain wont work on another domain? Are there any other gotchas that you have run into that I should be aware of?
Thanks all
If using encrypted backups ( available in sql 2014 onwards ) you would need to restore the certificate from the source server to any servers where you wish to restore copies of the databases.
On the target instances before restoring the certificate, you would need to create a database master key in the master database if one does not exist already.
See my article at the following link
http://www.sqlservercentral.com/articles/Encryption/109028/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉