SQL Server Backup Encryption Impact on AutoSys

Products

• Autosys Workload Automation

Issue/Introduction

Is it OK to enable SQL Server backup encryption on AutoSys database server? 

Resolution

Enabling backup encryption on SQL Server using the WITH ENCRYPTION option when performing a BACKUP DATABASE or BACKUP LOG operation will not have a direct impact on the connecting application (AutoSys)

Here's why and what might be indirectly affected:

1. No Direct Application Impact:
   • The connecting application (whether it's a web application, desktop client, reporting tool, etc.) interacts with the live, running SQL Server database.
   • Backup encryption is an operation performed by SQL Server to secure the backup file as it's written to disk or tape. It encrypts the data as it leaves the database for the backup media.
   • The application continues to connect to the database, query data, insert data, update data, and delete data as it normally would, completely unaware that a backup is being encrypted or even performed. The data within the database itself is not encrypted by this operation.
     
     
2. Potential Indirect Impacts (on the SQL Server host/operations):
   • Performance Overhead During Backup:
     • Encrypting data is a CPU-intensive operation. While the backup is running and encrypting, it will consume additional CPU resources on the SQL Server host.
     • This could indirectly impact the performance of the entire SQL Server instance if the server is already running near its capacity and the CPU becomes a bottleneck during the backup window. This might manifest as slightly slower query execution times or reduced throughput for the connecting applications during the backup period.
     • The I/O subsystem might also see a slight increase in load due to the encryption process.
     • Mitigation: Schedule encrypted backups during off-peak hours if possible, and ensure your SQL Server has sufficient CPU and I/O resources.Backup Duration:Due to the encryption overhead, the backup process itself might take slightly longer to complete compared to an unencrypted backup of the same size. This primarily impacts your backup window, not the connecting application.
       
       
   • Key Management:
     • While not an application impact, it's a critical operational consideration. You will need to manage the encryption keys (typically a Database Master Key and a Certificate or Asymmetric Key) used for the backup. Losing these keys means you cannot restore your encrypted backups.
     • The connecting application doesn't care about these keys, but the DBA or operations team does.
       
       
   • Restore Process:
     • When you need to restore an encrypted backup, you must have access to the encryption key that was used. The restore operation itself will also incur CPU overhead for decryption. This impacts the recovery process, not the live application. 

In summary:

Enabling backup encryption is a security best practice for data at rest on your backup media. It's an internal SQL Server operation that secures the backup file itself. From the perspective of the application connecting to the live database, there is no direct impact. The only potential indirect effect would be if the increased resource consumption during the backup operation itself leads to contention on an already overstretched SQL Server instance, which might subtly affect all activities on that server, including application queries.

It's always recommended to test any significant configuration changes, including enabling backup encryption, in a non-production environment first to understand its specific performance characteristics in your environment.