Updating YSoft SafeQ Management Server database architecture - MS SQL SSMD
Manual update from single database to multi database architecture at Microsoft SQL Server
Update procedure for one tenant with examples for the first tenant. Target database architecture is single server and multiple database, database deployment is with SQL Server authentication. You could update all tenants data analogically. Create system backup before the update.
Backup single database and dwhtenant schema only.
Backup YSoft SafeQ Management Server database (default name is SQDB6) in SQL Server Management Studio utility. Right click on database and choose Tasks > Back Up. You could run command line command also, at example:
BACKUP
DATABASE
[SQDB6]
TO
DISK = N
'SQDB6.bak'
WITH
NOFORMAT
See Backup of Databases for detailed instructions.
Uninstall YSoft SafeQ Management Server completely. Uninstall YSoft SafeQ Management Server including database.
Clean install YSoft SafeQ Management Server. Install YSoft SafeQ Management Server in multi database configuration.
Get configuration data.
Get necessary configuration data from production database (SQDB6):
-- get tenant password and tenant schema name [@tenant_password@, @tenant_schema_name@]
select
db_pass, schema_name
from
cluster_mngmt.tenants;
-- get dwhtenant password and dwhtenant schema name [@dwhtenant password@, @dwhtenant_schema_name@]
select
db_pass, schema_name
from
cluster_mngmt.tenant_warehouses;
-- get server guid and id [@server_guid@, @server_id]
select
server_guid, id
from
cluster_mngmt.cluster_server;
Write it down, you will use these values later for Update configuration data in step 7.
Stop YSoft SafeQ services. See Stop YSoft SafeQ services for detailed instructions.
Drop new production database and restore the old one.
Drop production database (SQDB6) in SQL Server Management Studio. Right click on production database and choose drop database. You could run sql command also, at example:
DROP
DATABASE
[SQDB6];
In SQL Server Management Studio restore whole database (SQDB6) from step 1. Right click on Databases (SQDB6) and choose Restore Database. You could run sql command also, at example:
RESTORE
DATABASE
[SQDB6]
FROM
DISK = N
'SQDB6.bak'
WITH
FILE = 1, NOUNLOAD, STATS = 5
Update the configuration data.
Update necessary configuration data in production database (SQDB6):
-- update server guid
update
cluster_mngmt.cluster_server
set
server_guid =
'@server_guid@'
where
id = @server_id@;
-- update tenant password
update
cluster_mngmt.tenants
set
db_pass =
'@tenant_password@'
where
schema_name =
'@tenant_schema_name@'
;
-- update dwhtenant password
update
cluster_mngmt.tenant_warehouses
set
db_pass =
'@dwhtenant password@'
where
schema_name =
'@dwhtenant_schema_name@'
;
Replace placeholders by values from step number 4.
Alter users and grant permissions.
-- Alter user with login
ALTER
USER
cluster_mngmt
WITH
LOGIN = cluster_mngmt;
ALTER
USER
cluster_guest
WITH
LOGIN = cluster_guest;
ALTER
USER
tenantuser_1
WITH
LOGIN = tenantuser_1;
ALTER
USER
[dwhtenantuser_1]
WITH
LOGIN = [dwhtenantuser_1], DEFAULT_SCHEMA = [tenant_1];
-- Grant permissions
EXEC
(
'exec sp_addrolemember db_datareader, dwhtenantuser_1'
);
EXEC
(
'exec sp_addrolemember db_datawriter, dwhtenantuser_1'
);
EXEC
(
'exec sp_addrolemember db_ddladmin, dwhtenantuser_1'
);
GRANT
DELETE
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
GRANT
EXECUTE
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
GRANT
INSERT
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
GRANT
REFERENCES
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
GRANT
SELECT
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
GRANT
UPDATE
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
GRANT
VIEW
DEFINITION
ON
SCHEMA
::[tenant_1]
TO
[dwhtenantuser_1];
Copy data into warehouse database.
Run queries from the select at warehouse database (default name is SQDB6_DWH):
select
'insert into dwhtenant_1.'
+TABLE_NAME+
' select * from SQDB6.dwhtenant_1.'
+TABLE_NAME+
' where id not in (select id from dwhtenant_1.'
+TABLE_NAME+
')'
from
INFORMATION_SCHEMA.TABLES
where
TABLE_SCHEMA =
'dwhtenant_1'
and
TABLE_TYPE =
'BASE TABLE'
and
TABLE_NAME
not
like
'smartq_jobs%'
order
by
case
when
TABLE_NAME
like
'%dimension'
then
2
when
TABLE_NAME
like
'%measures'
then
3
when
TABLE_NAME
like
'%junction'
then
4
else
1
end
,TABLE_NAME
Example of the insert returned by the query above: "insert into dwhtenant_1.color_type_accids select * from SQDB6.dwhtenant_1.color_type_accids where id not in (select id from dwhtenant_1.color_type_accids)".
Update warehouse sequencies.
Run queries from the select at warehouse database (default name is SQDB6_DWH):
SELECT
'DECLARE @x_sql'
+
CAST
(object_id
as
NVARCHAR(32))+
' as NVARCHAR(1024) select @x_sql'
+
CAST
(object_id
as
NVARCHAR(32))+
' = '
'ALTER SEQUENCE ['
+SCHEMA_NAME(schema_id)+
'].['
+
name
+
'] RESTART WITH '
' + (select CAST(coalesce((max(id)+1),1) AS NVARCHAR(32)) from ['
+SCHEMA_NAME(schema_id)+
'].['
+
SUBSTRING
(
name
,1,(LEN(
name
)-7))+
'] WHERE id != 0) EXECUTE(@x_sql'
+
CAST
(object_id
as
NVARCHAR(32))+
')'
FROM
sys.sequences
WHERE
name
like
'%_id_seq'
AND
SCHEMA_NAME(schema_id) =
'dwhtenant_1'
Example of the insert returned by the query above: "DECLARE @x_sql14623095 as NVARCHAR(1024) select @x_sql14623095 = 'ALTER SEQUENCE [dwhtenant_1].[dm_v2_device_dimension_id_seq] RESTART WITH ' + (select CAST(coalesce((max(id)+1),1) AS NVARCHAR(32)) from [dwhtenant_1].[dm_v2_device_dimension] WHERE id != 0) EXECUTE(@x_sql14623095)".
Drop dwhtenant schema from production database (SQDB6).
Create procedure from the cleanUpSchema.sql file into production database (SQDB6). The file is distributed inside installation package under path [Installation package] \Complementary Solutions\Enterprise Reporting.
Execute procedure at production database ( SQDB6 ):
exec
CleanUpSchema
'dwhtenant_1'
,
'w'
Multitenant environment
If you have multitenant environment repeat steps 8-11 for remaining tenants.Spooling Management
If you have spoolinh Management server you need to update SPOC guid according to new installation.