Encrypt backups for log shipping

High Availability
1

Hi,

Since SP1 was released for SQL Server 2016 we have been able to encrypt all our backups which we then just copy to our secondary site in case they are needed to be restored onto servers in the case of us having to invoke our DR plans.

Our company is wanting to increase our levels of availability and one option that I am looking at is log shipping (I have to work a solution within a budget) to give us a higher level of RTO however it does not appear that there is an option to encrypt the transaction log backup files.

We use the Ola Hallengren maintenance plans which work very nicely and encrypt all our backups but I am wondering if anyone has a method they use to acheive this or maybe the option is to build a bolt on to Ola's maintenance plans that will just run restores periodically on our DR servers.

2

I have not tried it but this may be worth looking at:

https://www.brentozar.com/archive/2017/06/introducing-sp_allnightlog-log-shipping-scale-open-source/

3

Are you running Enterprise Edition or Standard Edition? Is this for a single database - or multiple databases?

If you are on Enterprise Edition you can setup and configured AlwaysOn - with an availability group to handle this in near real-time. There shouldn't be any additional cost to setup a DR server...

Since the goal is to have a DR server - then setting the replica as asynchronous and manual fail over would be the better option. It does have some risk of data loss - but it wouldn't be any more than log shipping.

Note: if you have Standard Edition - and a single database - you can setup a Basic Availability Group which will support that one database.

4

Just a question.
If the available bandwidth and throughput from and to DR server is good enough, can we go with synchronous mode?

5

Yes - you could use synchronous if the network between sites is good enough, just be aware that synchronous requires the transaction to commit on the secondary before it can commit on the primary and that could cause performance issues.

1 Like
6

The Enterprise Edition vs Standard Edition question is a good one. If you are running Enterprise, you can encrypt the database and then the backups will be encrypted.

7

Sorry, my fault, although I inferred it when I mentioned the release of SP1 for SQL 2016 i forgot to specify I am asking because we run Standard Editions with multiple databases.