Azure SQL Managed Instance, Azure database Health Check using Azure SQL Analytics

-> Azure SQL Analytics uses Azure log Analytics and provides several reports with regards to Azure SQL tasks and performance. It supports below Azure SQL resources,

  1. Azure SQL Elastic Pools
  2. Azure SQL Databases
  3. Azure SQL Managed Instance and databases

-> Let’s setup Azure SQL Analytics and check using below steps.

-> Click “Create a resource”, type “Azure SQL Analytics” and select it.

-> Once you click “Create”. You will be advised to select an existing Log Analytics Workspace or create a new one. In our case, I will be creating a new Log Analytics Workspace,

-> Azure SQL Analytics is created. Now data should be sent to Log Analytics workspace.

-> All Azure SQL resources like Azure SQL Managed Instance, Azure SQL Database or Elastic pool can be configured to send its data to Log Analytics workspace by configuring “Diagnostic settings” under “Monitoring” section.

  1. Azure SQL Managed Instance

-> Open appropriate Azure SQL Managed Instance from Azure portal. Click “Add diagnostic setting”,

1.1 Azure SQL Managed Instance Database

-> Open Azure SQL Managed Instance database from Azure portal. Click “Add diagnostic setting”,

2. SQL Elastic Pool

-> Open SQL elastic pool from Azure portal. Click “Add diagnostic setting”,

3. Azure SQL Databases

-> Open required Azure SQL database from Azure portal. Click “Add diagnostic setting”,

-> Let us try opening Azure SQL Analytics and check what details we have.

-> Open “Log Analytics Workspaces”,

-> Click on “Workspace Summary” and you will see below screen,

-> Click on “Azure SQL databases” and you will get details collected so far,

-> Click on “Resources per utilization Bucket” and you will see more details,

-> You can modify queries to get more details,

-> Now details for “Azure SQL Managed Instance”,

-> You can get more details from this solution that would give you some good information to complete a health check of Azure SQL database or managed instance

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Enabling Transparent data encryption using Customer-managed key on Azure SQL Managed Instances that are part of failover group

Environment

-> I have two Azure SQL Managed Instances that are part of a failover group. Currently these two instances have Transparent data encryption (TDE) enabled using service-managed key.

-> Please note that the SQL Managed Instances as part of failover group as depicted in above diagram are not in correct paired regions. I have created this for testing only. SQL managed instances that will be deployed in a failover group should use paired regions for performance reasons. Managed instances residing in geo-paired regions have much better performance compared to unpaired regions. For example Managed instances deployed in East US and West US will be a good fit for a filover group as they are in paired regions. For more details on paired regions, refer this article.

-> My requirement is to configure Transparent data encryption (TDE) using Customer-Managed key as I have a requirement to backup the database in one Managed Instance that is part of a failover group and restore it to a different Managed Instance that is part of a different failover group. The restores are basically required to configure Transactional replication between Managed Instances using “Initialize from LSN”.

-> I am using this article as a reference to configure Transparent data encryption (TDE) using Customer-Managed key on SQL managed Instances.

-> Below are my Managed Instances configured,

-> Creating a key vault in East US to be used for SQL Managed Instance jbmi-eastus-db-primary using below steps,

-> If Managed Instance uses Azure AD, then below prerequisites should be followed.

-> In my case I dont have an Azure AD, so skipping above prerequisites.

-> Wait for the key vault in East US to get created.

-> Create another Key Vault using above procedure. But make sure you create the key vault in the region same as Managed Instance jbmi-eastus2-db-secondary. In my case it is East US2.

-> Below are the key vaults created in my case,

-> Check failover group portal and verify which instance is Primary and secondary. In my case jbmi-eastus-db-primary is primary and jbmi-eastus2-db-secondary is secondary.

-> Create a key in Key vault that is in East US which will be utilized by primary managed instance jbmi-eastus-db-primary using below procedure,

-> Now click on the key created. In my case it is MI-Key1. Click “Download Backup” and download the key backup.

-> Key backup file looks as below,

-> Now open key vault in East US2 that is associated with Managed instance jbmi-eastus2-db-secondary. Click on “Restore Backup” and navigate to the key backup file that was performed in earlier step.

-> Now its time to enable TDE using Customer-Managed key in Azure SQL Managed Instance.

-> Open Secondary managed instance jbmi-eastus2-db-secondary from azure portal and navigate to “Transparent data encryption” and follow below procedure,

-> Click on “Change Key”,

-> Click on “save”.

-> Getting below error,

Failed to save Transparent Data Encryption settings
Failed to save Transparent Data Encryption settings for SQL resource: jbmi-eastus2-db-secondary. Error message: The encryption protectors for all servers linked by GeoDR must be in the same region as their respective servers.Please upload key ‘https://eastus2-mi-keyvault.vault.azure.net/keys/MI-Key1/b1e1ceea175d43deb8ee2c29d039e0cd’ to a Key Vault in the region ‘East US’ as server ‘jbmi-eastus-db-primary’

-> The error states that the key is missing in East US key vault that is utilized by Primary Managed Instance. But we know that the key is there already. As it was first created there and then it was ported to Key vault in East US2.

-> Lets follow the below procedure now. Open Primary managed instance jbmi-eastus-db-Primary from azure portal and navigate to “Transparent data encryption” and follow below procedure,

-> IMPORTANT : In above screenshot, remember to uncheck “Make the selected key the default TDE protector.

-> Click “Save”,

-> Now open Secondary managed instance jbmi-eastus2-db-Secondary from azure portal and navigate to “Transparent data encryption” and follow below procedure,

-> IMPORTANT : In above screenshot, remember to check “Make the selected key the default TDE protector.

-> Now open Primary managed instance jbmi-eastus-db-Primary from azure portal and navigate to “Transparent data encryption” and follow below procedure,

-> IMPORTANT : In above screenshot, this time remember to check “Make the selected key the default TDE protector.

-> It is complete now. We have configured Transparent data encryption (TDE) using Customer-Managed key on Azure SQL Managed Instances that are part of a failover group.

-> Lets validate the setting once and try a failover and failback to check everything works fine.

-> First lets check if we are able to query the database JBDB on primary Managed Instance,

-> Next on Secondary Managed Instance. We get below error for the insert statement because secondary instance does not allow writes.

Msg 3906, Level 16, State 2, Line 5
Failed to update database “JBDB” because the database is read-only.

-> Lets perform a failover now,

-> Lets perform a failback now,

-> Failback completed fine. Lets query the database once and confirm,

-> Failover and failback completed fine. We are able to query the database without any issues.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Restore a Copy_Only backup taken in an Azure SQL Managed Instance onto another Azure SQL Managed Instance

-> I am trying to restore a Copy_Only database backup taken on Azure SQL Managed Instance JBMI-Pub to another Azure SQL Managed instance JBMI-Sub.

-> Please check this article on how to perform Copy_Only backup on an Azure SQL Managed Instance.

-> The copy_only backup for Managed Instance JBMI-Pub is placed on a storage account. Lets try a restore on Managed Instance JBMI-Sub as below,

-> Sign into the Azure portal after clicking Add,

TITLE: Microsoft SQL Server Management Studio

Restore of database ‘JBDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot find server asymmetric key with thumbprint ‘0xB48BED6BFEC0D4E9E8B1E9542A50F5225B6F6045’. (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.41011.9+(SqlManagementObjects-master-APPLOCAL)&LinkId=20476

BUTTONS:
OK

-> Tried executing the restore command as a TSQL and got same error as below,

Msg 33111, Level 16, State 4, Line 2
Cannot find server asymmetric key with thumbprint ‘0xB48BED6BFEC0D4E9E8B1E9542A50F5225B6F6045’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

-> The restore failure is due to the fact that Transparent Data Encryption (TDE) is enabled on Managed Instances JBMI-Pub and JBMI-Sub with different keys.

-> Lets change the TDE option for Managed Instance JBMI-Sub where we are restoring the backup from Service-Managed key to Customer-managed key and use the same key as Managed Instance JBMI-Pub from where copy_only backup was performed.

-> Make sure we select the same key vault, key and version for Managed Instance JBMI-Sub which is same as JBMI-Pub.

-> Click Save and start the restore again,

-> Restore completed fine this time.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.