Apply SQL Server Transparent Data Encryption

  • 6 minutes

There are several customers who are using SQL Server Transparent Data Encryption (TDE) when deploying their SAP SQL Server databases in Azure. The SQL Server TDE functionality is fully supported by SAP (see SAP Note #1380493 ).

In cases where you perform a heterogeneous migration from another DBMS, running on-premises, to Windows/SQL Server running in Azure, you should create your empty target database in SQL Server ahead of time. As a next step you would apply SQL Server TDE functionality, while you're still running your production system on-premises. The reason you want to perform the process in this sequence is that encrypting the empty database can take quite a while. The SAP import processes would then import the data into the encrypted database during the downtime phase. The overhead of importing into an encrypted database has a lower time impact than encrypting the database after the export phase in the down time phase. Negative experiences were made when trying to apply TDE with SAP workload running on top of the database. Therefore, treating the deployment of TDE as an activity that needs to be done without an SAP workload on the database is recommended.

In cases where you move SAP SQL Server databases from on-premises into Azure, we recommend testing on which infrastructure you can get the encryption applied fastest. For this keep these facts in mind:

  • You can't define how many threads are used to apply data encryption to the database. The number of threads is majorly dependent on the number of disk volumes the SQL Server data and log files are distributed over. This means the more distinct volumes, the more threads will be engaged in parallel to perform the encryption. Such a configuration contradicts the disk configuration suggestion on building one or a smaller number of storage spaces for the SQL Server database files in Azure Virtual Machines. A configuration with a small number of volumes would lead to a small number of threads executing the encryption. A single thread encrypting is reading 64 KB extents, encrypts it, and then writes a record into the transaction log file, telling that the extent got encrypted. As a result, the load on the transaction log is moderate.
  • In older SQL Server releases, backup compression did not get efficiency anymore when you encrypted your SQL Server database. This behavior could develop into an issue when your plan was to encrypt your SQL Server database on-premises and then copy a backup into Azure to restore the database in Azure. SQL Server backup compression usually achieves a compression ratio of factor 4.
  • With SQL Server 2016, SQL Server introduced new functionality that allows compressing encrypted databases as well in an efficient manner.
  • Treating the application of TDE encryption with no or little SAP workload only, you should test in your specific configuration to determine whether it's better to apply TDE to your SAP database on-premises or to do so in Azure. In Azure, you certainly have more flexibility in terms of over-provisioning infrastructure and shrink the infrastructure after TDE got applied.

Azure Key Vault

Azure offers the service of a Key Vault to store encryption keys. SQL Server on the other side offers a connector to use Azure Key Vault as a store for the TDE certificates.