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
Ensure that the Database Name does not contain a "-".
- 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