Moving Workspace One Access database to a new MS SQL Server

I had to validate how to migrate WS1 Access database to another SQL Server.

This test concerns a dual-site configuration with 3 active nodes (R/W) on Site-A et 3 passive nodes on site B (R/O).

The first thin I did, was to set all nodes to passive (so read-only for all) so no more update will be done on the database.

After that I made a SQL backup of my database and copied it the 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

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *