Migration II: On-Premise SQL Database

SQL to SQL Database Migration

This guide describes overall a database migration from an On-Premise or local installed SQL Server instance to another On-Premise or local installed SQL Server instance. Silverback remains in this guide on the current host. In case your SQL and Silverback are running on the same Host and you want to migrate both, please refer to Migration I: Silverback Server to Server as well to ensure you will migrate all your required certificates as well.

This guide covers not all parts of networking, access management etc. and uses the easiest approach to generate a quick going forward process for this scenario.

What you will need

Access to your current SQL Server and Database
Access to your new SQL Server
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
A valid connection possibility from your current Silverback Server to your new SQL Server

SQL Migration

Server Preparation

Prepare the new SQL Server by reviewing the System Requirements for SQL Servers from Installation Guide I: System Requirements
Verify that your new SQL Server is running on the same date and time as the Silverback Server
Verify your current connection settings in the Silverback Management Console
- Open your Silverback Management Console
- Login as Settings Administrator
- Navigate to Connection String
- Review and note down your current Connection settings
Ensure that on the new SQL Server you have the same user account with the same permissions as on your current SQL Server

Stop Services

Start a CMD or Powershell Command
Type: stop-service w3svc,silv*,epic*,mat* on the Silverback Server

From this point your current Silverback Management will be offline and no changes will be made to your current database

Database Migration

This sections describes how to perform a database migration from an On-Premise installed SQL Server instance to another On-Premise installed SQL Server instance with the Data Migration Assistant. Please note that the database deployment may take a lot of time. We will initiate the migration from the current On-Premise SQL instance.

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 SQL Server
Click Create

Enter 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 from your current Silverback Connection String
Enter your Password from your current Silverback Connection String
Uncheck Encrypt Connection
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.

Enter Target

Enter as Server Name your new On-Premise SQL server, e.g. database.imagoverum.com
Select your Authentication Type, e.g. SQL Authentication
Enter your Username
Enter your Password

Depending on your access level, this user might differ from your current connection string. The current user will be anyway migrated through the Wizard later on.

Uncheck Encrypt Connection
Click Next

In case you have trouble with the connection to the new SQL Server, check your Firewall and virtual networks settings. Also review that the SQL Server Agent is running and that the TCP/IP Protocol is enabled in your SQL Server Configuration Manager

Select Database

After a successful connection you need to specify the Silverback Database which you want to migrate. But before you need to create a shared location accessible by the source and the target servers for the backup operation.

Create Network Share

Create a new Network Share, e.g. \\Fileshare\Migration with the following permissions
- Service Account running source SQL Server must have write privileges
- Service Account running target SQL Server must have read privileges

For fast forward you can also grant temporary Everyone Read/Write Access

Navigate back to the Data Migration Assistant

Add Database

And your previously created shared backup location, e.g. \\Fileshare\Migration
Ensure that you select only your target Silverback Database
Proceed with Next

Select Logins

Select your current database user

Login to your Silverback Management Console as Settings Administrator and navigate to Connection String to review your current database user

Click Start Migration
Wait until the Migration process is finished

Database Verification

When the Database migration has been finished, we want to ensure the presence of the database on the new SQL Server and check the user login with our previously used user account.

Open your Microsoft SQL Server Management Studio
Enter your Server name and your authentication information
Click Connect
Expand your Server
Expand Database
Locate your mirrored Silverback Database
Right Click properties of the Database and review file size (optional)
Proceed with Connection String

Connection String

When the Database migration has been successfully finished and you are able to connect to the database, it is time to replace your current Silverback Connection string from your current On-Premise Database to the new mirrored database and location.

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 to Computer\HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\
Right Click Silverback
Select Export
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
Ignore the certificate warning message and continue with access to Silverback Website
Enter now your new SQL Database settings

Setting	Description	Mandatory
Use Azure SQL	Ensure that this setting is not enabled	No
Data Server Address	Network location of the new SQL Server. Enter the new IP-Address, FQDN or localhost	Yes
Failover Database Server Address	If using, enter an optional SQL server that will be used if the primary is not available	No
Database Name	Enter here the previously used Database name	Yes
Use SQL Authentication	By default, SQL Authentication is enabled. Enter your previously used Username and Password for SQL Authentication. By disabling the Windows permissions will be used to access the SQL Server.	No
Web Settings Certificate Thumbprint	Ensure that your currently used Web Settings Certificate is selected	Yes

Perform a cross-check
Click Save

In case you entered a wrong Database settings, delete again the ConnectionString Registry Key and restart the IIS and re-open localhost/admin

Start Services

Start a CMD or Powershell Command
Type: restart-service w3svc,silv*,epic*,mat*
Refresh your Localhost Browser Session to see the Admin Login Page for Silverback Console
Then continue with entering the settings admin credentials to login into console
Navigate to "Connection String" and check if all settings are presented correctly

Next Steps

Try to enroll a new device or send Tags to existing devices to see if anything works as before