Preparation

Stop related services in the environment

Stop all Dispatcher Paragon services in the whole environment (Management Servers, Site Servers) except Dispatcher Paragon Bundled Etcd.

Recovering in a new SQL Server

In case you are recovering your database in a new SQL Server instance, you need to modify the following files, located in the Management Service installation folder:

• {DISPATCHER_PARAGON_INSTALLATION_FOLDER}/Management/conf/safeq.properties

• {DISPATCHER_PARAGON_INSTALLATION_FOLDER}/Management/validator/DBValidator.properties

• {DISPATCHER_PARAGON_INSTALLATION_FOLDER}/Management/ims/application.properties

Deleting the current databases

1. On the database server, run Microsoft SQL Management studio.

2. Click File - Connect to Object Explorer.

3. Choose a Database engine as a Server type and connect to the database server.
4. In the Object Explorer (menu on the left), click Databases.
5. Delete current databases: 
   1. SQDB6
   2. SQDB6_DWH (if it exists in the deployment scenario)
      
   3. SQDB6_IMS
   4. SQDB6_YPS (if Payment System is installed)
      

Deleting the current warehouse database in case of multiple server deployment

If you have multiple server database deployment please follow next steps to delete warehouse database otherwise skip this section.

1. On the warehouse  database server, run Microsoft SQL Management studio.

2. Click File - Connect to Object Explorer.

3. Choose a Database engine as a Server type and connect to the database server.
4. In the Object Explorer (menu on the left), click Databases.
5. Delete current database SQDB6_DWH.

Restoring Databases from Backup

1. Right-click Databases > New Database...

2. Create new databases with the same names as the original ones:

   If you are using domain users, create the databases according to the article Installing Dispatcher Paragon Management Server on external MSSQL using domain users.

   1. SQDB6
   2. SQDB6_DWH (if it exists in the deployment scenario)
      
   3. SQDB6_IMS

   4. SQDB6_YPS (if Payment System is installed)

3. Restore each database from its backup files, following the steps below (example for SQDB6).

   1. Right-click the database name and select Tasks > Restore > Database... option.

      

   2. Switch source to Device and select your backup file > enable the checkbox in the Restore column.

      

   3. Navigate to the Options page.
      1. Check the Overwrite the existing database (WITH REPLACE) option.
      2. Uncheck the Take a tail-log backup before restore option.

         

   4. Click OK.
   5. Backup will be restored.

      

Restoring warehouse databases from backup in case of multiple server deployment

If you have multiple server database deployment please follow next steps to restore warehouse database otherwise skip this section.

1. Right-click Databases > New Database...

2. Create new database with the same names as the original one SQDB6_DWH.

   If you are using domain users, create the databases according to the article Installing Dispatcher Paragon Management Server on external MSSQL using domain users.

3. Restore database SQDB6_DWH in same way like in previous section "Restoring Databases from Backup" indent 3.

SQL Server authentication - Reconfiguration section 

Restoring Databases final steps with SQL users

1. Run the following query on the SQDB6 database

   ALTER USER [cluster_mngmt] WITH LOGIN = [cluster_mngmt];

   ALTER USER [cluster_guest] WITH LOGIN = [cluster_guest];

2. If you do not use sa user, also run the following query on the SQDB6 database:

   ALTER USER [<safeq_user>] WITH LOGIN = [<safeq_user>];

   Replace <safeq_user> in both places with the value of the database.global.management.username.without.domain property from the Dispatcher Paragon installation directory\Management\conf\safeq.properties file.

Restoring warehouse databases final steps with SQL users

If you have multiple database deployment please follow next steps to finalize database restore otherwise skip this section. You need to connect to appropriate database server according to your deployment type.

1. Run the following query on the SQDB6_DWH database

   ALTER USER [cluster_mngmt] WITH LOGIN = [cluster_mngmt];

   ALTER USER [cluster_guest] WITH LOGIN = [cluster_guest];

2. If you do not use sa user, also run the following query on the SQDB6_DWH database:

   ALTER USER [<safeq_user>] WITH LOGIN = [<safeq_user>];

   Replace <safeq_user> in both places with the value of the database.global.management.username.without.domain property from the Dispatcher Paragon installation directory\Management\conf\safeq.properties file.

Reconfiguring the SQDB6 Database

1. If you have database user passwords in plain text (default configuration), run the following procedure on the SQDB6 database:

   EXEC cluster_mngmt.spu_recover_tenant_db_passwords;
2. If you have encrypted database user passwords, follow these manual steps:

1. 1. Reconfigure the tenantuser_1 user:

      1. Find the tenantuser_1 password by running the following query on the SQDB6 database.

         SELECT db_pass FROM cluster_mngmt.tenants WHERE db_login='tenantuser_1'

      2. Prepare the following query:

         IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = '@tenantUser@') BEGIN CREATE LOGIN [@tenantUser@] WITH PASSWORD = '@tenantPassword@'; END; ELSE BEGIN ALTER LOGIN [@tenantUser@] WITH PASSWORD = '@tenantPassword@'; END;

         IF NOT EXISTS(select * from sys.database_principals where name = '@tenantUser@') BEGIN CREATE USER [@tenantUser@] WITH DEFAULT_SCHEMA = [@tenantSchema@]; END; ELSE BEGIN ALTER USER [@tenantUser@] WITH LOGIN = [@tenantUser@]; END;

         EXEC('exec sp_addrolemember db_datareader, @tenantUser@');

         EXEC('exec sp_addrolemember db_datawriter, @tenantUser@');

         EXEC('exec sp_addrolemember db_ddladmin, @tenantUser@');

      3. Replace variables with real values:

         @tenantUser@ = tenantuser_1

         @tenantPassword@ = tenantuser_1 password from step a (which must be decrypted).

         @tenantSchema@ = tenant_1
      4. Run the modified query on the SQDB6 database.
   2. Also reconfigure the dwhtenantuser_1 user in a similar way:

      1. Find the dwhtenantuser_1 password by running the following query on the SQDB6 database.

         SELECT db_pass FROM cluster_mngmt.tenant_warehouses WHERE db_login='dwhtenantuser_1'

      2. Prepare the following query.

         IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = '@dwhTenantUser@') BEGIN CREATE LOGIN [@dwhTenantUser@] WITH PASSWORD = '@tenantPassword@'; END; ELSE BEGIN ALTER LOGIN [@dwhTenantUser@] WITH PASSWORD = '@tenantPassword@'; END;

         IF NOT EXISTS(select * from sys.database_principals where name = '@dwhTenantUser@') BEGIN CREATE USER [@dwhTenantUser@] WITH DEFAULT_SCHEMA = [@dwhTenantSchema@] END; ELSE BEGIN ALTER USER [@dwhTenantUser@] WITH LOGIN = [@dwhTenantUser@]; END;

         EXEC('exec sp_addrolemember db_datareader, @dwhTenantUser@');

         EXEC('exec sp_addrolemember db_datawriter, @dwhTenantUser@');

         EXEC('exec sp_addrolemember db_ddladmin, @dwhTenantUser@');

      3. Replace variables with real values:

         @dwhTenantUser@ = dwhtenantuser_1

         @tenantPassword@ = dwhtenantuser_1 password from step a (which must be decrypted).

         @dwhTenantSchema@ = dwhtenant_1
      4. Run the modified query on the SQDB6 database.
   3. If you have more tenants, repeat the steps from section a. for all additional tenantuser_X and the steps from section b. for all corresponding dwhtenantuser_X

Reconfiguring the SQDB6_DWH warehouse Database in case of multiple server deployment

If you have multiple database deployment please follow next steps to reconfigure database otherwise skip this section. You need to connect to appropriate database server according to your deployment type and follow these manual steps:

1. Reconfigure the dwhtenantuser_1 user:
   

   1. Find the dwhtenantuser_1 password by running the following query on the SQDB6 database.

      SELECT db_pass FROM cluster_mngmt.tenant_warehouses WHERE db_login='dwhtenantuser_1'

   2. Prepare the following query.

      IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = '@dwhTenantUser@') BEGIN CREATE LOGIN [@dwhTenantUser@] WITH PASSWORD = '@tenantPassword@'; END; ELSE BEGIN ALTER LOGIN [@dwhTenantUser@] WITH PASSWORD = '@tenantPassword@'; END;

      IF NOT EXISTS(select * from sys.database_principals where name = '@dwhTenantUser@') BEGIN CREATE USER [@dwhTenantUser@] WITH DEFAULT_SCHEMA = [@dwhTenantSchema@] END; ELSE BEGIN ALTER USER [@dwhTenantUser@] WITH LOGIN = [@dwhTenantUser@]; END;

      EXEC('exec sp_addrolemember db_datareader, @dwhTenantUser@');

      EXEC('exec sp_addrolemember db_datawriter, @dwhTenantUser@');

      EXEC('exec sp_addrolemember db_ddladmin, @dwhTenantUser@');

   3. Replace variables with real values:

      @dwhTenantUser@ = dwhtenantuser_1

      @tenantPassword@ = dwhtenantuser_1 password from step a (which must be decrypted).

      @dwhTenantSchema@ = dwhtenant_1
   4. Run the modified query on the SQDB6_DWH database.
2. Reconfigure the tenantuser_1 user in Linked Server:
   

   1. Find the tenantuser_1 password by running the following query on the SQDB6 database.

      SELECT db_pass FROM cluster_mngmt.tenants WHERE db_login='tenantuser_1'
   2. Open Linked Server SQDB6_LINKED_SERVER properties and update  tenantuser_1 password from step a (which must be decrypted).
      
      .
3. If you have more tenants, repeat the steps from section a. for all additional tenantuser_X and the steps from section b. for all corresponding dwhtenantuser_X
   
   

Re-validate database

1. Run the following procedure on the SQDB6 database:

   EXEC cluster_mngmt.spu_clean_validator_tables;

2. If you have multiple database deployment please connect to warehouse database server run the following procedure on the SQDB6_DWH database, otherwise skip this paragraph:

   EXEC cluster_mngmt.spu_md_clean_validator_tables;
3. Run DB Validator Tool

Windows authentication - Reconfiguration section

Restoring Databases final steps with domain users

1. Run the following queries on database SQDB6:

   CREATE USER [<domain>\<safeq_user>] FOR LOGIN [<domain>\<safeq_user>] WITH DEFAULT_SCHEMA=[cluster_mngmt]

   ALTER ROLE [db_owner] ADD MEMBER [<domain>\<safeq_user>]

   
   If USER [<domain\safeq_user>] already exists you need to drop this user.
   

   DROP USER [<domain>\<safeq_user>]

2. Run the following queries on all databases SQDB6_IMS, SQDB6_DWH (if it exists in the deployment scenario) and SQDB6_YPS (if Payment System is installed):

   CREATE USER [<domain>\<safeq_user>] FOR LOGIN [<domain>\<safeq_user>] WITH DEFAULT_SCHEMA=[dbo]

   ALTER ROLE [db_owner] ADD MEMBER [<domain>\<safeq_user>]

3. Drop old domain user if it is different from the current domain user on all databases SQDB6, SQDB6_DWH (if it exists in the deployment scenario),  SQDB6_IMS and SQDB6_YPS (if Payment System is installed):

   DROP USER [<old_domain>\<old_safeq_user>];

   Replace <domain> in all places with the value of the database.global.management.username.without.domain property from the <install_dir>\Management\conf\safeq.properties file.

   Replace <safeq_user> in all places with the value of the database.global.management.username.without.domain property from the <install_dir>\Management\conf\safeq.properties file.

   Replace <old_domain> in all places with the value of the database.global.management.username.without.domain property from the previous <install_dir>\Management\conf\safeq.properties file.

   Replace <old_safeq_user> in all places with the value of the database.global.management.username.without.domain property from the previous <install_dir>\Management\conf\safeq.properties file.

Re-validate database

If your database backup was an older version you need to run DB Validator Tool to update the database.

Finalization

Clear the Cache on Spooler Controllers and Restart All Services

Delete Spooler Controller cache directory on all servers

Delete Dispatcher Paragon Spooler Controller cache by deleting whole folder <SPOC_DIR>\SpoolCache (e.g. C:\DispatcherParagon\SPOC\SpoolCache)

Start Management service again

Open the Services window (e.g. Start > Run > services.msc) and start the following services:

1. Start Dispatcher Paragon Management Service

Verify the Spooler Controller cache recovery settings

Go to tab System > Configuration on Dispatcher Paragon Management web interface and set orsCacheRecovery property to enabled.

Start the remaining services on all servers

1. Start remaining Dispatcher Paragon services with Automatic startup type in no particular order

Verify the correct Spooler Controller functionality

Once the Dispatcher Paragon Spooler Controller cache is deleted and Dispatcher Paragon Spooler Controller services are running, verify the correct Dispatcher Paragon Spooler Controller functionality according to the article Spooler Controller Health Check.