Updating YSoft SafeQ Management Server database architecture - MS SQL MSMD
Manual update from single database to multi server multi database architecture at Microsoft SQL Server
Update procedure for one tenant with examples for the first tenant. Target database architecture is multi 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.
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 also run command line command, for example:
BACKUP
DATABASE
[SQDB6]
TO
DISK = N
'SQDB6.bak'
WITH
NOFORMAT
See Backup of Databases for detailed instructions.
In case of non-default database collation following query can be used to get the current collation:
SELECT
collation_name
FROM
sys.databases
WHERE
name
=
'SQDB6'
;
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 server and multi database configuration.
In case of non-default database collation create the databases manually before new installation - Installing YSoft SafeQ Management Server on server with specific database collation for MS-SQL database
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. For detailed instructions see Updating from MU/Build to Build, section Stop YSoft SafeQ services.
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 also run sql command, for 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 also run sql command, for 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.
Update Server Link credentials in the data warehouse database (SQDB6_DWH):
Log in to Microsoft SQL Server Management Studio.
Navigate to Server Objects > Linked Servers > SQDB6_LINKED_SERVER, right-click on the Linked Server and choose Properties.
In the Linked Server Properties window, select the Security page in the Select a page navigation.
On the Security page, update the Remote Password field for all the tenant users (i.e. update all the login mappings with Local Login in format "dwhtenantuser_i"). Use the tenant passwords from step number 4 (the values of '@tenant_password@' placeholder).
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;
Drop and clean up schema.
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\Data Mart Mode.
Execute procedure at production database (SQDB6):
exec
CleanUpSchema
'dwhtenant_1'
,
'w'
Go to Warehouse SQL Server engine.
Restore the old one database and copy data into warehouse database.
On the warehouse server in SQL Server Management Studio restore whole database (SQDB6) from step 1. Right click on Databases (SQDB6) and choose Restore Database. You could also run sql command, for example:
RESTORE
DATABASE
[SQDB6]
FROM
DISK = N
'SQDB6.bak'
WITH
FILE = 1, NOUNLOAD, STATS = 5
Run queries from the select at warehouse database (default name is SQDB6_DWH):
select
'insert into dwhtenant_1.'
+TABLE_NAME+
' ('
+ (
select
stuff(list,1,1,
''
)
from
(
select
',['
+
cast
(c.COLUMN_NAME
as
varchar
(64)) +
']'
as
[text()]
FROM
INFORMATION_SCHEMA.COLUMNS c
where
c.TABLE_NAME = t.TABLE_NAME
AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA
ORDER
BY
c.ORDINAL_POSITION
for
xml path(
''
) )
as
Sub(list)) +
') select '
+ (
select
stuff(list,1,1,
''
)
from
(
select
',['
+
cast
(c.COLUMN_NAME
as
varchar
(64)) +
']'
as
[text()]
FROM
INFORMATION_SCHEMA.COLUMNS c
where
c.TABLE_NAME = t.TABLE_NAME
AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA
ORDER
BY
c.ORDINAL_POSITION
for
xml path(
''
) )
as
Sub(list)) +
' from SQDB6.dwhtenant_1.'
+TABLE_NAME+
' where id not in (select id from dwhtenant_1.'
+TABLE_NAME+
')'
from
INFORMATION_SCHEMA.TABLES t
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 sequences.
On the warehouse server 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 old database (SQDB6) from warehouse server.
Drop old database (SQDB6) in SQL Server Management Studio on warehouse server. Right click on old database and choose drop database. You could also run sql command, for example:
DROP
DATABASE
[SQDB6];
Multitenant environment
If you have multitenant environment repeat steps 8-12 for remaining tenants.
Spooling Management
If you have spooling Management server you need to update SPOC guid according to new installation (<install_dir>\SPOC\conf\modules\guid.conf).
UPDATE
tenant_1.spooler_controllers
SET
spooler_controller_guid =
'@new_spoc_guid@'
WHERE
id = 1;