Stop related services in the environment

Stop all YSoft SafeQ services in the whole environment (Management Servers, Site Servers) except YSoft 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:

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

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

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

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 <install_dir>\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 <install_dir>\Management\conf\safeq.properties file.

Reconfiguring the SQDB6 Database

1. If you have YSoft SafeQ 6 MU9 or later and 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 YSoft SafeQ 6 MU8 or older or if you have encrypted database user passwords, follow these manual steps:
   

   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. If you have YSoft SafeQ 6 MU9 or later, run the following procedure on the SQDB6 database:

   EXEC cluster_mngmt.spu_clean_validator_tables;

2. If you have YSoft SafeQ 6 MU8 or older build, follow these manual steps:
   

   1. Delete the records from all validator related tables.

      delete from dbo.smartq_validator;

      delete from cluster_mngmt.smartq_validator;
      Foreach tenant_id do:

      delete from tenant_%i.smartq_validator;

      delete from dwhtenant_%i.smartq_validator;

3. If you have multiple database deployment and you have YSoft SafeQ 6 MU9 or later 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;

   
   

4. If you have multiple database deployment and you have YSoft SafeQ 6 MU8 or older build please connect to warehouse database server and delete the records from all validator related tables, otherwise skip this paragraph:
   
   

   delete from dbo.smartq_validator;

   delete from cluster_mngmt.smartq_validator;
   Foreach tenant_id do:

   delete from dwhtenant_%i.smartq_validator;

   
   

5. Run DB Validator Tool

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.

Clear the Cache on Spooler Controllers and Restart All Services