Moving Workspace One Access database to a new MS SQL Server

On my new MS SQL Server I created a new database (same name) :

USE master;
IF EXISTS(SELECT * FROM sys.databases WHERE NAME = N'ws1access_db')
BEGIN
       ALTER DATABASE ws1access_db
       SET single_user WITH
       ROLLBACK immediate;
       DROP DATABASE ws1access_db;
END 
go 
CREATE DATABASE ws1access_db COLLATE latin1_general_cs_as; 
ALTER DATABASE ws1access_db 
SET read_committed_snapshot ON; 
go

At this point, I restored my database and I asked to “Overwrite existing database”

Once the database is restored we had to delete the user database otherwise we’ll not pas able to assign a login user, however the user is dbowner of the schema and without some sql command we’ll not be able to delete it :

USE ws1access_db;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('ws1access_user_dba');
ALTER AUTHORIZATION ON SCHEMA::saas TO dbo; 

Now we can simply delete the user :

And recreate it with a login user :

USE ws1access_db;
IF NOT EXISTS (SELECT NAME FROM master.sys.server_principals WHERE NAME = N'ws1access_user_dba')   
BEGIN 
      CREATE login "ws1access_user_dba" WITH password = N'P@ssword123!';  
 END 
go 
USE ws1access_db; 
CREATE USER "ws1access_user_dba" FOR login "ws1access_user_dba" WITH default_schema = saas; 
go 
EXEC sp_addrolemember 
  N'db_owner', 
  N'ws1access_user_dba' 
go
ALTER AUTHORIZATION ON SCHEMA::saas TO ws1access_user_dba;

Then connect to each node to change database setting and point the new server.

Revert nodes on primary site to R/W and restart workspace One service

Now the official way and recommended method is :

• Take downtime. (stop horizon-workspace service on all service and connector nodes)
• Migrate DB (following customer’s DBA approved procedure).
• Encrypt SQL user password using hznEncrypt
• Change JDBC url and password directly in runtime-config.properties on all service appliances
• Bring up first node. Verify the DB connectivity.
• Then bring up all other nodes 1 by 1
• Verify failover to DR works and fallback to primary works
• End downtime