Migrating Aptify Database on Azure SQL Managed Instance

Release 7.0 enables clients to host Aptify database into cloud managed database offerings such as Azure Managed SQL Instance. The first step towards Azure enablement is to migrate the on-premises database to cloud. This document provides step-by-step instruction to migrate the existing database from a SQL Server instance to Azure SQL Managed Instance using Azure Data Studio. For more information on migration to Azure SQL Managed Instance refer to https://docs.microsoft.com/en-us/azure/azure-sql/migration-guides/managed-instance/sql-server-to-managed-instance-guide.

Prerequisites

• An Azure Subscription
• A Contributor role for Azure subscription.

• Existing APTIFYSA user in the database. Refer to AptifySA User creation documentation for instruction on how to create the user.

  If you do not create APTIFYSA user, you will not be able to login to database or Smart Client with Azure instance.

• Execute below queries by connecting to SSMS and selecting database to be migrated.

  • EXEC sp_changedbowner 'sa'ALTER DATABASE [dbase] SET trustworthy ON 

  • ALTER ASSEMBLY AptifyRecordHistorySecurity WITH PERMISSION_SET = UNSAFE;
  • ALTER ASSEMBLY AptifyDecryptionTool WITH PERMISSION_SET = UNSAFE;
  • ALTER ASSEMBLY AptifyHTMLUtility WITH PERMISSION_SET = UNSAFE;

  • Open store procedure spGetCurrentUserTypes from the Object Explorer. In the right-hand panel remove the double dots prefixed to vwGroupMembers.

    After completion of migration undo the changes done in spGetCurrentUserTypes on On-premises database instance only.

    

• Create Blob storage for database backup. Refer https://docs.microsoft.com/en-us/azure/storage/blobs/storage-quickstart-blobs-portal to create Blob storage.
  
  

Steps to Migrate database to Azure

1. Open Azure Portal by typing URL- https://portal.azure.com/ in the address bar.
2. Go to Resource groups page.

3. Click Create to create a new resource group.

   If you have already created a resource group for migration, search and select the same. 

   

4. On Create a resource group page enter the values for fields Subscription, Resource group, and Region.
   
5. Click Review + create button. The newly created resource group appears in the Resource groups page.
   
6. Go to SQL Managed Instances service and click Create to create a SQL managed instance for the new resource group.
   
7. On the Create Azure SQL Managed Instance, select the values for Subscription, Resource group and enter the Managed Instance name.
   
8. Click Review + create button. The newly created managed instance appears in the list of services.
   
9. Upload the on-premises database backup to blob storage before migration. There are two ways to upload backup on Azure: Directly through SQL Server Management Studio (SSMS) using URL or by uploading database backup on storage account through Azure portal.
   1. Directly through SQL Server Management Studio (SSMS) using URL
      1. Login to SSMS, open Object Explorer.

1. 1. 1. Right click on the database name, select Tasks->Back Up.
         
      2. On the General page, under Destination section, select URL for Back up to field, and click Add button.
         
      3. On the Select Backup Destination window, enter the Backup File name and then click New container.
          
      4. Click Sign In… button to connect to Microsoft Azure.
          
      5. Select a subscription to use from the drop-down and click Create Credential button.
         
      6. Shared Access Signature is generated. Click OK button.
         
      7. On the Media Options page, under Reliability section, select Verify backup when finished and Perform checksum before writing to media.
      8. Click OK to start backup on Azure storage account.
   2. By uploading database backup on storage account through Azure portal
      1. On the Storage accounts page, select the storage account.
         
      2. The Storage account details page opens. From the left-hand panel, under Data storage, click Containers.
         
      3. Select the container name for upload, this will open the container overview page.
         .
      4. Click Upload
         
         

10. Once on-premises database backup is uploaded to storage account, restore the Azure instance Aptify Database from the on-premises database backup. To restore the Azure instance Aptify Database follow below points.

• Connect to Azure instance database through SQL Server Management Studio (SSMS) and right Click Databases and click Restore Database.
   
• Now click three dots to select the backup devices (in our case it is storage account), select Backup media type as URL and click Add.
  .
• Select the container where database backup was uploaded.
  .
• If Azure storage container is not in list, then click Add and sign In into the Microsoft Azure and select an appropriate storage account where database backup up was uploaded.
• After selecting the Azure storage container click OK and select the backup file from containers.
  
• After selecting the database backup from storage account click OK to start the restore.
• Once restoration is complete Azure instance database is upgraded to latest version.