This document describes how to move Microsoft SQL database from one server to another server.

This article describes moving a single database (i.e. Single server Single database deployment scenario). In case you want to perform moving of another database deployment scenario, please contact our Customer Support Service.

Before you start, verify port 1433 is open between the Management and the new Database server (MS SQL)

Stop Dispatcher Paragon services and backup the Dispatcher Paragon databases

Select and remmeber tenant database user logins and passwords from current Management Service database (SQDB6):

select db_login, db_pass from cluster_mngmt.tenants;
select db_login, db_pass from cluster_mngmt.tenant_warehouses;

Follow the MS SQL Database and Data Warehouse Backup documentation chapter to backup the databases. Do not follow the "After the backup" section of the documentation chapter mentioned (we do not want the Dispatcher Paragon services to be started at this point yet).

Create the database SQL Logins

This step only applies when you are using standard SQL Server authentication and database without containment, but that is the most common scenario.

SQL Logins that need to be added in this step are cluster_guest , cluster_mngmt, tenantuser_1 and dwhtenantuser_1. For tenantuser_1 and dwhtenantuser_1 use passwords from previous step. Also, if you don't use sa user to connect to the database, please, add the user you will want to connect to the database with as well.

Please, perform the following steps for all the SQL Logins mentioned above:

Log in to Microsoft SQL Server Management Studio.
Navigate to Security > Logins.
Right-click on Logins and select New Login...
In the New Login window, fill in the following values:
1. Enter Login name of the login you want to create (e.g. cluster_mngmt).
2. Choose SQL Server authentication option.
3. Choose a Password for the login. Please, write down the password as you will need this password in the next section of this procedure.
4. Uncheck the Enforce password expiration option.
5. Click OK to create the login.

Caution with multi-tenancy

Only SQL Logins for single tenant are mentioned here. It is necessary to add all tenant logins in case of multi-tenancy. You can check cluster_mngmt.tenants and cluster_mngmt.tenant_warehouses database tables for the list of tenant logins required.

Change the configuration of Dispatcher Paragon to point to the new database server

Change the configuration of the Management Service

In the file "<SafeQ_folder>\Management\conf\safeq.properties", update the following configuration properties:

database.host property should point to the new SQL Server.
database.cluster.management.password property should be updated with a password of the cluster_mngmt SQL Login (created in the previous section).
database.cluster.guest.password property should be updated with a password of the cluster_guest SQL Login (created in the previous section).
In case you will use a different main SQL Login to connect to the database, both database.global.management.username.without.domain and database.global.management.password properties should be updated with the new SQL Login credentials.
In case you will be changing the database name when restoring it on the new SQL Server, database.name property should be updated with the new database name.

Change the configuration of the Database validator

In the file "<SafeQ_folder>\Management\validator\conf\DBValidator.properties", update the following configuration properties:

connectionInfoSQ.databaseIP , connectionInfoDW.databaseIP, connectionInfoSQ.databaseServerName and connectionInfoDW.databaseServerName properties should point to the new SQL Server.
connectionInfoSQ.userManagementPassword and connectionInfoDW.userManagementPassword properties should be updated with a password of the cluster_mngmt SQL Login (created in the previous section).
In case you will use a different main SQL Login to connect to the database, then connectionInfoSQ.userLogin, connectionInfoSQ.userPassword, connectionInfoDW.userLogin and connectionInfoDW.userPassword properties should be updated with the new SQL Login credentials.
In case you will be changing the database name when recovering it on the new SQL Server, both connectionInfoSQ.databaseName and connectionInfoDW.databaseName properties should be updated with the new database name.

Change the configuration of the Infrastructure Management Service

In the file "<SafeQ_folder>\Management\ims\application.properties", update the following configuration properties:

in the value of the spring.datasource.url property, change the IP address to point to the new SQL Server.
In case you will use a different main SQL Login to connect to the database, then both spring.datasource.username and spring.datasource.password properties should be updated with the new SQL Login credentials.
In case you will be changing the database name when restoring it on the new SQL Server, property dbName should be updated with the new database name. Also, in the value of the spring.datasource.url property, the databaseName parameter should be updated.

Change the configuration of Dispatcher Paragon Payment System

If you have payment system installed, then also in the file "<SafeQ6_folder>\YPS\ps-conf\environment-configuration.properties" , update the following configuration properties:

In the value of the database.url property, change the IP address to point to the new SQL Server.

In case you will use a different main SQL Login to connect to the database, then database.username and databse.password properties should be updated with the new SQL Login credentials.

Change the configuration of ETCD

Follow the How to Change the Password of a Database User.

Update the following configuration properties: encryptedClusterGuestPassword, encryptedUserPassword, dbHost and encryptedClusterPassword;

Restore the Dispatcher Paragon databases on the new database server

Follow the MS SQL Database and Data Warehouse Recovery documentation chapter to restore the database on the new server. This documentation chapter contains all the steps necessary for completing the restoration of the database including steps to make Dispatcher Paragon fully running again.