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
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
On the database server, run Microsoft SQL Management studio.
Click File - Connect to Object Explorer.
Choose a Database engine as a Server type and connect to the database server.
In the Object Explorer (menu on the left), click Databases.
Delete current databases:
SQDB6
SQDB6_DWH (if it exists in the deployment scenario)
SQDB6_IMS
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.
On the warehouse database server, run Microsoft SQL Management studio.
Click File - Connect to Object Explorer.
Choose a Database engine as a Server type and connect to the database server.
In the Object Explorer (menu on the left), click Databases.
Delete current database SQDB6_DWH.
Restoring Databases from Backup
Right-click Databases > New Database...
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.
SQDB6
SQDB6_DWH (if it exists in the deployment scenario)
SQDB6_IMS
SQDB6_YPS (if Payment System is installed)
Restore each database from its backup files, following the steps below (example for SQDB6).
Right-click the database name and select Tasks > Restore > Database... option.
Switch source to Device and select your backup file > enable the checkbox in the Restore column.
Navigate to the Options page.
Check the Overwrite the existing database (WITH REPLACE) option.
Uncheck the Take a tail-log backup before restore option.
Click OK.
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.
Right-click Databases > New Database...
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.
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
Run the following query on the SQDB6 database
ALTER USER [cluster_mngmt] WITH LOGIN = [cluster_mngmt];
ALTER USER [cluster_guest] WITH LOGIN = [cluster_guest];
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.
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];
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
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;
If you have YSoft SafeQ 6 MU8 or older or if you have encrypted database user passwords, follow these manual steps:
Reconfigure the tenantuser_1 user:
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'
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@'
);
Replace variables with real values:
@tenantUser@ = tenantuser_1
@tenantPassword@ = tenantuser_1
password
from
step a (which must be decrypted).
@tenantSchema@ = tenant_1
Run the modified query on the SQDB6 database.
Also reconfigure the dwhtenantuser_1 user in a similar way:
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'
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@'
);
Replace variables with real values:
@dwhTenantUser@ = dwhtenantuser_1
@tenantPassword@ = dwhtenantuser_1
password
from
step a (which must be decrypted).
@dwhTenantSchema@ = dwhtenant_1
Run the modified query on the SQDB6 database.
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:
Reconfigure the dwhtenantuser_1 user:
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'
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@'
);
Replace variables with real values:
@dwhTenantUser@ = dwhtenantuser_1
@tenantPassword@ = dwhtenantuser_1
password
from
step a (which must be decrypted).
@dwhTenantSchema@ = dwhtenant_1
Run the modified query on the SQDB6_DWH database.
Reconfigure the tenantuser_1 user in Linked Server:
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'
Open Linked Server SQDB6_LINKED_SERVER properties and update tenantuser_1 password from step a (which must be decrypted).
.
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
If you have YSoft SafeQ 6 MU9 or later, run the following procedure on the SQDB6 database:
EXEC
cluster_mngmt.spu_clean_validator_tables;
If you have YSoft SafeQ 6 MU8 or older build, follow these manual steps:
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;
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;
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;
Windows authentication - Reconfiguration section
Restoring Databases final steps with domain users
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>]
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>]
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:
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.
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.