MS SQL Database and Data Warehouse Recovery

Please note that this is just a quick guide for MS SQL Studio. The latest documentation is always available on Microsoft's web pages.

Before proceeding with the recovery, make sure that current backup files are available before you start with the restore procedures.

You can backup your database following the guide in MS SQL Database and Data Warehouse Backup.

Preparation

Stop related services in the environment

YSoft SafeQ services need to be stopped before a database restore is performed and the cache on Spooler Controllers needs to be cleared before they are started again to avoid possible inconsistencies.

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

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)

      images/download/attachments/195304113/MSdelete.png

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 YSoft SafeQ 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.

      images/download/attachments/195304113/MSrestore.png

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

      images/download/attachments/195304113/MSrestore2.PNG

    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.

        images/download/attachments/195304113/MSrestore3.PNG

    4. Click OK.

    5. Backup will be restored.

      images/download/attachments/195304113/MSrestore4.PNG

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 YSoft SafeQ 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 <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).

      images/download/attachments/195304113/linked_server_password.png


      .

  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

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

Cache on Spooler Controllers needs to be deleted after Database restore to avoid possible inconsistencies.

Delete Spooler Controller cache directory on all servers

Delete YSoft SafeQ Spooler Controller cache by deleting whole folder <SAFEQSPOC_DIR>\SpoolCache (e.g. c:\SafeQ6\SPOC\SpoolCache)

Start Management service again

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

  1. Start YSoft SafeQ Management Service

Verify the Spooler Controller cache recovery settings

Go to tab System > Configuration on YSoft SafeQ management interface and set orsCacheRecovery property to enabled.

In case of YSoft SafeQ Spooler Controller cache data corruption, cache can be manually deleted and all job-related metadata will be recovered from YSoft SafeQ Management Server.

If you omit this step, all jobs stored on the YSoft SafeQ Spooler Controller might be lost after the end of procedure.

Start the remaining services on all servers

Do not start YSoft SafeQ Spooler Controller Group Service manually. If YSoft SafeQ Spooler Controller is part of Spooler Controller group then this service will start automatically when its configuration is ready.

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

You can use the following PowerShell script to perform the task:

Get-Service *YSoft* | Where-Object {$_.Name -ne 'YSoftSQ-SPOCGS'} | Start-Service

Verify the correct Spooler Controller functionality

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