Set up log shipping on TDE encrypted database using solarwind SFTP

Question

Hi All,

I want to Set up log shipping on TDE encrypted database using solar wind SFTP.
So what will be the best approach,first enable encryption on database then setup log shipping or setup log shipping first then enabled encryption on database.
Also one more challenge using solar wind SFTP, will it work with log shipping as no shared path is available.

I am using Sql server 2019 Std

Thank in Advance for quick response and sharing the details for completing this.

GautamAK

Answer 1

TDE is all about the Encryption Hierarchy being established (Master Key (master database), Certificate (master database), Database Encryption Key (user database)) on the log shipping side. So if you created the Master Key on the secondary side and then back up the certificate on the primary database server and restore it on the secondary server, then you will be allowed to restore the copy of the database on the secondary. That is first.

Second, the SFTP, you could use PowerShell and WINSCP to automatedly send data to the SFTP server and on the secondary use it to pull the files down then restore them.

You will NOT be able to get the built-in Log Shipping mechanisms to interact with SFTP, so it is more of an automation than a feature built-in to SQL Server.

I have done this so many times before so I know you can do it. Let me know if you need more on TDE, etc. I will gather the PowerShell scripts and put them up on https://github.com/dbaduck in a LogShippingSFTP folder.

Answer 2

Hi @GautamAK ,

If the TDE was enabled on the primary database, then you need to create the database master key on the secondary server (if you don’t have one) and restore the certificate and the private key from primary server to secondary server before restoring the database on the secondary server, then configuring the log shipping. Please refer to this blog which might help.
For solarwinds SFTP issue, you can open a thread in the solarwinds SFTP Server forum so that people there will help you more effectively.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.