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
Dispatcher Paragon 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 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
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 Dispatcher Paragon 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.
- Right-click the database name and select Tasks > Restore > Database... option.
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 Dispatcher Paragon 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
All the SQL queries in this section have to be executed on all MS SQL server nodes if the customer uses the SQL Server Failover Cluster.
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 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.
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 Dispatcher Paragon installation directory\Management\conf\safeq.properties file.
Reconfiguring the SQDB6 Database
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 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
- Reconfigure the tenantuser_1 user:
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
Run the following procedure on the SQDB6 database:
EXEC
cluster_mngmt.spu_clean_validator_tables;
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;
- Run DB Validator Tool
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 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:
- 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.
In case of Dispatcher Paragon Spooler Controller cache data corruption, cache can be manually deleted and all job-related metadata will be recovered from Dispatcher Paragon Management Server.
If you omit this step, all jobs stored on the Dispatcher Paragon Spooler Controller might be lost after the end of procedure.
Start the remaining services on all servers
Do not start Dispatcher Paragon Spooler Controller Group Service manually. If Dispatcher Paragon Spooler Controller is part of Spooler Controller group then this service will start automatically when its configuration is ready.
- 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.