Skip to main content
Matrix42 Self-Service Help Center

Migration III: On-Premise to Azure SQL Database

On-Premise to Azure SQL Database Migration

This guide describes overall a database migration from a local installed SQL Server instance to an Azure SQL based database. This guide covers not all parts of networking, access management, cost controls, backups and uses the easiest approach to generate a quick going forward process for this scenario.

What you will need 

  • Administrative Access to Azure and a valid subscription
  • Access to your current SQL Server and Database 
  • Administrative privileges on your current Silverback Server
  • Domain Administrative privileges in case your current Server is Domain Joined
  • Admin Account for the Silverback Management Console

Azure SQL 

Create new Database

  • Login to Azure Portal under portal.azure.com
  • Login with your credentials
  • Click Create a resource
  • Search for SQL Database
  • Select SQL Database
  • Click Create

Basics

  • Select your Subscription
  • Select your Resource Group
  • Enter your Database name, e.g. Silverback
  • Select your Server

In case it will be your first server, review Create new Server

  • Decide if you want to use SQL elastic pool
    • Select your Elastic pool 

In case it will be your SQL elastic pool, review Create new elastic pool

  • Select and apply your desired Compute + storage

Please remind our current System Requirements and take your current database size into account. 

  • Proceed with Next: Networking

Networking

  • Select your desired connectivity method
    • For this documentation scenario, we enable Public endpoint and enable both Firewall Rules
    • You might need to change network access later on for production and security purposes
  • Proceed with Next: Additional settings

Additional Settings

  • Ensure None is selected for Data source
  • Ensure your collation is set to  Latin1_General_CI_AS

Review first your current one (SQL Management Studio -> Right click your Database > Properties) and ensure that you select here the same collation.

  • Proceed with Next: Tags

Tags

  • Add your SQL Database Tags (optional)
  • Proceed with Next: Review + create

Review + create

  • Review your settings
  • Press Create 
  • Wait until the deployment process is finished
  • Click Go to resource
  • Proceed with Database Connection

Create new Server 

  • Select Create New
  • Add a server name, eg. Imagoverum
  • Enter a Server admin login, e.g. superuser
  • Enter a password, e.g. Pa$$w0rd
  • Confirm your password
  • Select your Location, e.g. (Europe) Germany West Central
  • Click OK
  • Proceed with Basics

Create new elastic pool

  • Select Create new
  • Enter a name, e.g. Silverback
  • Click OK
  • Proceed with Basics

Database Connection

After creation of our new cloud hosted database, we will check the connection through the Azure Portal and we will recognize our new server address, which we will need later on during the database setup of Silverback. 

Check Connection

  • Select Query editor (preview)
  • Enter your SQL Server authentication credentials
  • Click OK
  • You should have now access

Connection String

  • Navigate in the left panel to Overview
  • Confirm you draft changes and Click OK
  • Copy your Server Name into your clipboard and into any Text Editor

Database Migration

This sections describes how to perform a database migration from an On-Premise scenario to an Azure SQL based database with the Data Migration Assistant. Please note that the database deployment may take a lot of time. During the creation of this guide, the migration took for a 6GB Database ~ 45 minutes with a 100 DTU sizing for the database. We will perform the migration from the current On-Premise SQL instance, which is in this current scenario the Silverback Server, too.

Microsoft Data Migration Assistant 

The Data Migration Assistant (DMA) enables us to upgrade to a modern data platform. It allows us to not only move your schema and data, but also uncontained objects from our source server to the target server.

Download and Installation

  • Download the Data Migration Assistant v3.3 or later.
  • Run the executable
  • Click Next
  • Select I Accept the terms in the License Agreement
  • Click Next
  • Click Install
  • Enable Launch Microsoft Data Migration Assistant
  • Click Finish 

New Project

  • Click +
  • Change Project type to Migration
  • Enter a Project Name, e.g. Silverback Database Migration
  • Select as Source server type SQL Server
  • Select as Target server type Azure SQL Database
  • Select as Migration scope Schema and data 
  • Click Create

Select Source 

  • Enter as Server Name your current On-Premise SQL server, e.g. sql.imagoverum.com or localhost
  • Select your Authentication Type, e.g. SQL Authentication
  • Enter your Username
  • Enter your Password
  • Click Connect
  • Select your current Silverback Database
  • Proceed with Next

In case your have trouble with connection, please take into account to open your Windows Firewall to allow the Azure Database Migration Service to access the source SQL Server, which by default is TCP port 1433.

Select Target

  • Enter as Server Name your current Azure SQL server, e.g. imagoverum.database.windows.net
  • Select your Authentication Type, e.g. SQL Authentication
  • Enter your Username
  • Enter your Password
  • Click Connect
  • Select your target Silverback Database
  • Proceed with Next

In case you have trouble with the connection to Azure SQL, adjust your Firewall and virtual networks settings for the Azure SQL Instance and add your Client IP

Select Objects

  • Wait until the process is finished
  • Click Generate SQL Script

Script & deploy schema

  • Wait until the process is finished
  • Press Deploy schema
  • Wait until the process is finished
  • Press Migrate Data

Select Tables

  • Press Start data migration 
  • Wait until the migration process is finished

Connection String

When the Database migration has been finished, it is time to replace your current Silverback Connection string from your current On-Premise Database to the new mirrored Azure SQL cloud hosted database.

Connection String Backup and Deletion

  • Connect via RDP to your Silverback Server
  • Right the Search Icon and open the Registry Editor
  • Confirm with Yes
  • Expand HKEY_LOCAL_MACHINE
  • Expand Matrix42
  • Right Click Silverback
  • Select Backup
  • Enter a file name, e.g. Silverback Registry Backup
  • Click Save or save the backup to a different location
  • Now right click SilverbackConnection 
  • Select Delete
  • Confirm with Yes

Restart Internet Information Services

  • Open Internet Information Services Manager
  • Select your Server
  • Click in the right pane under Actions Restart
  • Wait until the restart process is finished

Create your new Connection String

  • Open your Browser
  • Open localhost/admin
  • Enable Use Azure SQL
  • Enter your new Data Server Address, e.g. imagoverum.database.windows.net
  • Enter the new Database Name, e.g. Silverback
  • Enter your new Username, e.g. superuser
  • Enter your new Password, e.g. Pa$$w0rd 
  • Ensure that your currently used Web Settings Certificate is selected
  • Press Save 

If everything works as desired you should see now the Silverback Login Page and the migration has been accomplished successfully. 

Next Steps

  • Login to Silverback with your administrative credentials
  • Login to Silverback Self Service portal with user credentials

 

  • Was this article helpful?