Ranga Babu
Transparent data encryption (TDE) using your own key

Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases

October 24, 2019 by

In this article, we will review how to set up Geo-Replication on a Transparent Data Encryption (TDE) enabled Azure SQL database.

Please refer to the Azure SQL database Geo-Replication article to configure geo-replication on an Azure SQL database. Configuring geo-replication with default Transparent Data Encryption settings i.e. using the service-managed key is straight away as explained in the above article.

Configuring geo-replication is a bit different when you use your own key to encrypt the Azure SQL databases.

The following are the steps involved in configuring geo-replication on the Azure SQL database that is encrypted using your own key:

  • Creating an Azure key vault in both primary and secondary regions
  • Creating your own key in both regions that are used for Transparent Data Encryption (TDE)
  • Configuring primary server and secondary to use your own key for the encryption
  • Configuring Geo-replication

Let’s go over these steps one by one.

Creating an Azure key vault in both primary and secondary regions

Log in to the Azure portal, search for the Azure key vaults as shown in the below image and navigate to the Azure Key vaults page:

Azure key vaults

Click on the Add button and create an Azure Vault in the region that is the same as the primary Azure SQL database. Click on Review + create and then Create:

creating azure key vault in primary region

Similarly, create an Azure vault in the secondary region. Once the Azure vaults are created in both regions, enable soft delete option on both Azure key vaults. Soft delete option can be enabled using the Azure PowerShell module only. The Azure portal does not have the option to change the soft delete option on an Azure vault as of now.

Refer to the Enabling soft delete option on the Azure Key Vault section in the following article for more details: Transparent Data Encryption (TDE) on Azure SQL database

Creating your own key in both regions that are used for Transparent Data Encryption (TDE)

Now you need to create your own key in both regions and the key should be the same in both regions. Log in to the Azure portal, navigate to the key vaults page, and click on the key vault that is created in the primary region:

Azure key vault in primary region

In the Azure key vault details page, click on Keys as shown in the below image. Click on the Generate/Import button:

creating key by importing .pem file

You can create the key in different ways. Below are the three different ways to create a key in the Azure key vault:

  • Generate – you can generate the key from the Azure portal itself by specifying the name and other parameters
  • Import – you can create a key by importing files like .pem, .pfx, .byok
  • Restore from backup – creating a key from the backup that is generated from Azure key vault

In this case, I am importing the .pem file to create a key that will be used for enabling Transparent Data Encryption (TDE) the database:

importing key in Azure key vault

Similarly, navigate to the key vault created in the secondary region and create a key by importing the same .pem file that you used in the primary region.

Configuring primary server and secondary to use your own key for encryption

Log in to the Azure portal, click on All Resources and click on the primary SQL Server:

Azure SQL Server in primary region

Click on Transparent Data Encryption as shown in the below image. Click on Yes under Use your own key. Select the key vault in the primary region and then select the key. Click on the Save button to apply the new settings. Once the change is applied successfully, the database is encrypted using the key you created. Please refer to the below image:

Transparent data encryption (TDE) using your own key

Similarly, navigate to the secondary SQL Server located in a different region. Configure the Transparent Data Encryption settings to use your own key in the key vault located in the secondary region:

secondary key vault

Configuring Geo-replication

Log in to the Azure portal and click on the database that you want to geo-replicate. In this case, I am using the SampleDB database.

In the Azure SQL database details page, click on Geo-Replication as shown in the below image:

geo-replication on TDE enabled database

Now in the Geo-Replication page, under the map, select the secondary region. In the secondary setup pane, select the target secondary server and click OK. Once the deployment is successfully completed, the geo-replication is configured between the primary and secondary databases successfully.

Failover the database to the secondary server and verify the data and database encryption settings. To failover click on the secondary and then click on forced failover.

Conclusion

In this article, we explored how to set up geo-replication on a Transparent Data Encryption (TDE) enabled Azure SQL database. In case you have any questions, please feel free to ask in the comment section below.

To continue your learning more about Transparent Data Encryption (TDE), please refer to the Transparent Data Encryption category.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
153 Views