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.

1. Backup single database.

   1. 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.

   2. 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';

2. Uninstall YSoft SafeQ Management Server completely. Uninstall YSoft SafeQ Management Server including database.

3. Clean install YSoft SafeQ Management Server. Install YSoft SafeQ Management Server in multi server and multi database configuration.

   1. 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

4. Get configuration data.

   1. 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;

   2. Write it down, you will use these values later for Update configuration data in step 7.

5. Stop YSoft SafeQ services. For detailed instructions see Updating from MU/Build to Build, section Stop YSoft SafeQ services.

6. Drop new production database and restore the old one.

   1. 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];

   2. 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

7. Update the configuration data.

   1. 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.

   2. Update Server Link credentials in the data warehouse database (SQDB6_DWH):

      1. Log in to Microsoft SQL Server Management Studio.

      2. Navigate to Server Objects > Linked Servers > SQDB6_LINKED_SERVER, right-click on the Linked Server and choose Properties.
         

         

      3. In the Linked Server Properties window, select the Security page in the Select a page navigation.

      4. 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).
         

         

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;

9. Drop and clean up schema.

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

   2. 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.

   3. Execute procedure at production database (SQDB6):

      exec CleanUpSchema 'dwhtenant_1','w'

10. Go to Warehouse SQL Server engine.
    
    

11. Restore the old one database and copy data into warehouse database.
    

    
    

    1. 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

    2. 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

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

12. Update warehouse sequences.

    1. 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'

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

13. Drop old database (SQDB6) from warehouse server.

    1. 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];