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