Cross Subscription Database Restore for SQL Managed Instance Database with TDE enabled using ADF

Our customers need to refresh their production databases to their non-production environments on a daily basis. The database, which is approximately 600 GB in size, has Transparent Data Encryption (TDE) enabled in production. Disabling TDE before performing a copy-only backup is not an option. Because it takes hours to disable and then re-enable. To meet customer requirements, we use customer-managed keys stored in Key Vault. You then leverage Azure Data Factory to schedule and run an end-to-end database restore process.

Permission is required

• To perform backup and restore operations, the SQL Managed Instance managed identity must have “Contributor, Storage Blob Data Contributor” permission on Blob Storage.
• To transfer backup files between two storage locations, the ADF managed identity needs “Storage Blob Data Contributor” permission on the Blob storage.
• To perform backup and restore operations, the ADF management identity requires ‘sysadmin’ permissions on SQL Managed Instance.
• To enable Transparent Data Encryption (TDE) using customer-managed keys, ensure that you have contributor access to the resource group (RG) where your SQL managed instance is located.
• Grant full key permissions by setting an Azure Key Vault access policy for the user who configures the managed identity and Transparent Data Encryption (TDE) for your SQL managed instance.

Step 1

Create a TDE key in non-production Azure Key Vault dev-kv-001 Within the same subscription as the non-production SQL Managed Instance.

Name the key, select RSA with a 2048-bit key size, and leave the Active Date and Expiration Date unset for this demo. Make sure your keys are active and don’t set a rotation policy. Finally, click Create.

Step 2

Grant full key permissions by setting an Azure Key Vault access policy for the user who configures the managed identity and Transparent Data Encryption (TDE) for your SQL managed instance.

Step 3

Back up the TDE key you just created in your non-production key vault.

Step 4

Create a new Key Vault dev-kv-002 Proceed to restore the key within the newly created repository. Make sure the name matches the backed up key name and the status is set to Enabled.

Step 5

move something new dev-kv-002 Azure Key Vault development (non-production) subscription to production subscription. This process may take a few minutes as it validates the Key Vault move feature.

Step 6

You have successfully moved your Key Vault. dev-kv-002 For your production subscription, you will now proceed to backup your keys (follow step 3) to restore from the actual production Key Vault. prod-kv-001

Step 7

You are now ready to associate the keys you created in both your development and production environments with the corresponding Azure SQL Managed Instance. The goal is to maintain the same keys across backups so that you can seamlessly update your production environment to your development environment.

We will run these jobs simultaneously on both production and development SQL managed instances. Get started by accessing the portal blade for SQL Managed Instance. Then go to the SQL Managed Instance blade and select Transparent Data Encryption in the Security section.

To successfully perform a production refresh of your development environment, you must switch from service-managed keys to customer-managed keys.

Step: 8

Create server-level credentials. Credentials are records that contain the authentication information needed to connect to resources external to SQL Server.

USE master
CREATE CREDENTIAL [https://.blob.core.windows.net/]
    WITH IDENTITY='Managed Identity'
GO

Step: 9

• Create ADF Link service connections to both SQL Managed Instance and your storage account.
• Creating an ADF dataset using both SQL Managed Instance and the Storage Account Link Service

Stage: 10

If you utilize private endpoints, set up the ADF integration runtime and follow the Manage links. Create an Azure Data Factory managed private link

Step: 11

Create an ADF pipeline to take database backups from the source.

• Split backup files into multiple files for faster backups
• Use the script below to perform a copy-only database backup.
• Use the Script activity to run the backup script using the Source SQL MI Link service.

BACKUP DATABASE [@{pipeline().parameters.source_database_name}]
  TO URL = N'https://.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak',
  URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak'
WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION, STATS = 10

Allow one minute for the backup to be transferred to Blob storage, and adjust the period to suit your specific needs.

Step: 12

Create an ADF pipeline to copy database backup files from the source storage account to the target storage account.

• Use the copy activity to copy backup files from a source storage account to a destination storage account.
• Allow one minute for the backup to be transferred to Blob storage, and adjust the period to suit your specific needs.

Step: 13

Create an Azure Data Factory pipeline that restores a database from a specified storage account to a target SQL Managed Instance backup.

• Use the script below to restore the database from the specified storage account.
• Use the Script activity to run a restore script using the target SQL MI Link service.

USE [master]

RESTORE DATABASE [@{pipeline().parameters.target_database_name}] FROM  
URL = N'https://.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak',
URL = N'https://.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak',
URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak',
URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak',
URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak',
URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak',
URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak',
URL = N'https:// .blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak'

Step: 14

Use the appropriate activity actions to remove orphaned database users, provide user access, or set up additional pipelines to perform any additional tasks required after the restore.

Step: 15

Create an ADF pipeline workstation that executes steps 4 > 5 > 6 > 7, all in that order.

• To enable dynamic operation of your pipeline on different databases, set parameters for source_database_name and target_database_name.