Since TDE relies on a certificate stored in master (which is used to encrypt the database encryption key), then this would work only work if you could restore the master database to another server in such a way that the certificate could be decrypted.
This is the TDE encryption hierarchy:
- Service master key (protected by Windows; tied to the service account credentials, and a machine key)
- Database master key (in this case, the one for the master database)
- Certificate
- TDE encryption key
The first three items are stored in the master database, and can all be backed up. The fourth is stored (encrypted by the certificate from #3) in the header of the encrypted database.
So in a failure scenario, you would have to restore enough of the encryption hierarchy to allow you to read the TDE key. SQL Server creates the service master key at installation; thus while restoring the master database to a different instance will also restore items 2 and 3, the necessary key(s) to decrypt them will not be present. Result: unreadable data.
The two best options are to either restore the certificate (#3) from a backup (a good option if master cannot be restored for whatever reason), or restore your master database and its master key (#2) from a backup. Restoring the master key may be a better option if you have a lot of certificates/keys protected by this key, and need to make them all accessible at once. This comes with the same precautions normally associated with restoring the master database (collations, logins, database names and file paths, etc.)
Generally, I'd only recommend restoring master in a recovery scenario. For a migration/scale-out scenario (such as using Availability Groups/mirroring with a TDE-encrypted database), it's better to backup/restore the certificate (#3) so that it's encrypted using the master keys unique to each instance it's moving to. You will need to include the private key with the certificate backup.
In any case, you're going to have to make key/certificate backups, so guard them well, and store them in redundant, secure locations. Simply having a backup of master will not get you out of a TDE disaster; you're going to need a backup of at least one key or certificate.
1) If we can set up the transactional replication between server A of version 2014 and server B of version 2012. Tried to Google out but could not find the exact documents because may be am looking somewhere wrong. Please suggest if it can be set up?
For your replication to work, the PUBLISHER
and DISTRIBUTOR
should be on same version. The subscriber can be on lower version.
From BOL :
For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)
For transactional replication, a Subscriber to a transactional publication can be any version within two versions of the Publisher version.
So, essentially, your SERVER A
should be PUBLISHER
and DISTRIBUTOR
and your SERVER B
should be SUBSCRIBER
.
2) We need to implement encryption on server A, but this encrypted data needs not to go on server B as that is SAN encrypted. Is there a way we can do this, if replication can be successfully implemented as asked in point 1?
For your scenario, you should enable TDE
on PUBLISHER
database ONLY.
From BOL :
Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted intermediate files; for example, the bcp files. During transactional or merge replication, encryption can be enabled to protect the communication channel.
Best Answer
From MSDN:
Also, a suggested read here.
Yes, you can use TDE with replication as explained here.
TDE is file level encryption. This meaning, TDE encrypts and decrypts at the disk level where the data and log files are retained. Replication is managed in a combination of the system databases and the user database level and in reality, has no awareness of the file storage. This is the same when we would discuss any type of replication, such as Peer-to-peer, Transactional or Merge replication.