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.

1. Backup single database and dwhtenant schema only.

   Backup Dispatcher Paragon 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.

2. Uninstall Dispatcher Paragon Management Server completely. Uninstall Dispatcher Paragon Management Server including database.
3. Clean install Dispatcher Paragon Management Server. Install Dispatcher Paragon Management Server in multi database configuration.
4. 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.

5. Stop Dispatcher Paragon services. See Stop Dispatcher Paragon services for detailed instructions.
6. 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
7. 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.

8. 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];
9. 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)".

10. 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)".

11. Drop dwhtenant schema from production database (SQDB6).

    Create a procedure from the cleanUpSchema.sql file into the production database (SQDB6). The file is distributed in the installation package under \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.