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:
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:
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:
In the Azure key vault details page, click on Keys as shown in the below image. Click on the Generate/Import button:
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:
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:
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:
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:
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:
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.
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019