Skip to main content
Matrix42 Self-Service Help Center

Migrating databases from SQL Server to a Managed Instance in Azure

  1. Last updated
  2. Save as PDF

Overview

This page covers the scenario of a one-step migration, at a point in time when backups of the databases are taken, the backups are restored into the Managed Instance and the existing application configuration will be switched to use the SQL Managed Instance.

The scenario of renaming any of the databases is not covered; they must be restored with the original name on the Managed Instance.

We assume that your file storage database is the main database (by default M42Production) and that your workflow tables are also stored in the Production database (you have no AppFabric databases like Instance Store, by default M42InstanceStore, or Workflow Monitoring, by default M42Monitoring).

The description is only valid for product versions 12.0.0 and above. It was tested with version 12.0.5, in case you use a newer version some database steps might be obsolete (it is planned to remove the Staging database).

We cannot cover any Extensions that might store connection strings in other places.

Prerequisites

  1. An Azure blob storage account for your database backups.
  2. Full backups of all product databases. In the following example are the default Matrix42 database names suggested during the product installation, which are:
    1. M42Production → M42Production.bak
    2. M42Archive → M42Archive.bak
    3. M42Staging → M42Staging.bak
    4. M42Warehouse → M42Warehouse.bak
  3. The database backups must be uploaded to an Azure blob storage account. Add all 4 backups into the same container. (You can also directly create the backups on the blob storage, please check the Microsoft documentation for this. Be aware that for a large database, you might need to split your backup into several files.)
  4. An Application Server that has access to the Managed Instance, by VPN into the Azure network or by using an Application Server hosted in the Azure network.
  5. A server that has access to the Managed Instance that is used to host SQL Reporting Services and SQL Analysis Services. This could be the Application Server itself or a different machine.
  6. All required credentials (Service Account, Managed Instance account, Installation Account) with the required access to the systems.

The how-to will not cover the migration to another Application Aerver or another Report and Analysis Server.

Steps

Restore the databases

Create a shared access token for the blob storage account container where your backups are stored:
SharedAccessTokenCreation.png
The shared access token URL normally looks as follows:

https://<blobstorage_account>.blob.core.windows.net/<containername>?<secret>

Connect to your Managed Instance and open a query window. Create a temporary Credential object to restore the databases with this SQL command.

Replace <blobstorage_account>, <containername> and <secret> with the values from your shared access token URL in the following SQL statements

CREATE CREDENTIAL [https://<blobstorage_account>.blob.core.windows.net/<containername>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<secret>'

Restore your databases with these commands (replace the names with your values):

RESTORE DATABASE [M42Production] FROM URL = 'https://<blobstorage_account>.blob.core.windows.net/<containername>/M42Production.bak'
RESTORE DATABASE [M42History] FROM URL = 'https://<blobstorage_account>.blob.core.windows.net/<containername>/M42History.bak'
RESTORE DATABASE [M42Staging] FROM URL = 'https://<blobstorage_account>.blob.core.windows.net/<containername>/M42Staging.bak'
RESTORE DATABASE [M42Warehouse] FROM URL = 'https://<blobstorage_account>.blob.core.windows.net/<containername>/M42Warehouse.bak'

All names in these commands are case-sensitive.

When the restore has finished you can remove the credential with this command

DROP CREDENTIAL [https://<blobstorage_account>.blob.core.windows.net/<containername>]

Prepare the Managed Instance and the Databases

Create a new SQL login for the application access.

Currently, only SQL login is supported for the service access. First, create a new login on the Managed Instance.

If you are running a failover solution with 2 Managed Instances, the same login must be created on the secondary node with the same SID, please check the Microsoft documentation how to do this.

CREATE LOGIN [<SQLAccountName>] WITH PASSWORD=N'<SQLAccountPassword>', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON

Make the new login dbowner on all 4 databases. Change the database names if necessary:

use M42History
ALTER AUTHORIZATION ON DATABASE::[M42History] TO [<SQLAccountName>]
use M42Production
ALTER AUTHORIZATION ON DATABASE::[M42Production] TO [<SQLAccountName>]
use M42Staging
ALTER AUTHORIZATION ON DATABASE::[M42Staging] TO [<SQLAccountName>]
use M42Warehouse
ALTER AUTHORIZATION ON DATABASE::[M42Warehouse] TO [<SQLAccountName>]

Change the configuration on the Application Server

Switch to your Application Server, and log in with an Administrator user. This user must also have full access to your Analysis Server and the Report Server folder of your application.

We will use a Powershell script to change the application configuration.

Download the mimigration.ps1 file and adjust the settings in this PowerShell to use your configuration values.

Before executing the PowerShell script:

  1. Stop the Application Services:
    • Matrix42 Engine Common
    • Matrix42 Engine Common X86
    • Matrix42 Engine Scheduler
    • Matrix42 Worker
  2. Also, stop any other additional trusted Worker you might have.
  3. Run an "IISRESET /STOP" command
  4. Adjust these values in the script to your configuration values: 
    $installationDir = "<path to your installation>" #the default is "C:\Program Files (x86)\Matrix42\Matrix42 Workplace Management"
$ServiceAccount = "<domain\accountname>"
$AzureSqlServerName = "<full DNS name of your managed instance>"
$AzureSqlAccountName = "<the new created SQL login>"
$AzureSqlAccountPassword = "<the password of the new SQL login>"
$productionDatabase = "<M42Production>"
$historyDatabase = "<M42Archive>"
$wareHouseDatabase = "<M42WareHouse>"

    After the execution of the script, the environment is switched to the new Managed Instance. Also, consider taking a backup of your Report Server database and the Analysis Cube.

    If you want to create a backup of the Application Server, this is the last chance!

    The script will not change anything on the old SQL Server, in case of an error you can switch back by restoring your Application.

  5. Run the PowerShell script as Administrator. The script will ask for the password of the Service Account.
  6. Check if any error messages are reported.
  7. Start the iis "IISRESET /START"
  8. Start the Application Services.

Validate the environment

Check if your environment is running properly, for instance:

  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Article type
    Topic
  2. Tags
    This page has no tags.