{"id":31,"date":"2020-04-03T00:14:12","date_gmt":"2020-04-02T22:14:12","guid":{"rendered":"http:\/\/my-virt.alfadir.net\/?p=31"},"modified":"2023-03-29T16:30:49","modified_gmt":"2023-03-29T14:30:49","slug":"moving-workspace-one-access-database-to-a-new-ms-sql-server","status":"publish","type":"post","link":"https:\/\/my-virt.alfadir.net\/index.php\/2020\/04\/03\/moving-workspace-one-access-database-to-a-new-ms-sql-server\/","title":{"rendered":"Moving Workspace One Access database to a new MS SQL Server"},"content":{"rendered":"\n<p>I had to validate how to migrate WS1 Access database to another SQL Server.<\/p>\n\n\n\n<p>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).<\/p>\n\n\n\n<p>The first thing I did, was to set all nodes to passive (so read-only for all) so no more update will be done on the database.<\/p>\n\n\n\n<p>After that I made a SQL backup of my database and copied it the new MS SQL Server.<\/p>\n\n\n\n<!--nextpage-->\n\n\n\n<p>On my new MS SQL Server I created a new database (same name) :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE master;\nIF EXISTS(SELECT * FROM sys.databases WHERE NAME = N'ws1access_db')\nBEGIN\n       ALTER DATABASE ws1access_db\n       SET single_user WITH\n       ROLLBACK immediate;\n       DROP DATABASE ws1access_db;\nEND \ngo \nCREATE DATABASE ws1access_db COLLATE latin1_general_cs_as; \nALTER DATABASE ws1access_db \nSET read_committed_snapshot ON; \ngo\n<\/code><\/pre>\n\n\n\n<p>At this point, I restored my database and I asked to &#8220;Overwrite existing database&#8221;<\/p>\n\n\n\n<p>Once the database is restored we had to delete the user database otherwise we&#8217;ll not pas able to assign a login user, however the user is dbowner of the schema and without some sql command we&#8217;ll not be able to delete it :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE ws1access_db;\nSELECT s.name\nFROM sys.schemas s\nWHERE s.principal_id = USER_ID('ws1access_user_dba');\nALTER AUTHORIZATION ON SCHEMA::saas TO dbo; <\/code><\/pre>\n\n\n\n<p>Now we can simply delete the user :<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img decoding=\"async\" src=\"http:\/\/my-virt.alfadir.net\/wp-content\/uploads\/2020\/04\/image-4.png\" alt=\"\" class=\"wp-image-32\" width=\"220\" height=\"291\"\/><\/figure>\n\n\n\n<p> And recreate it with a login user :<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE ws1access_db;\nIF NOT EXISTS (SELECT NAME FROM master.sys.server_principals WHERE NAME = N'ws1access_user_dba')   \nBEGIN \n      CREATE login \"ws1access_user_dba\" WITH password = N'P@ssword123!';  \n END \ngo \nUSE ws1access_db; \nCREATE USER \"ws1access_user_dba\" FOR login \"ws1access_user_dba\" WITH default_schema = saas; \ngo \nEXEC sp_addrolemember \n  N'db_owner', \n  N'ws1access_user_dba' \ngo\nALTER AUTHORIZATION ON SCHEMA::saas TO ws1access_user_dba;\n<\/code><\/pre>\n\n\n\n<p>Then connect to each node to change database setting and point the new server.<\/p>\n\n\n\n<p>Revert nodes on primary site to R\/W and restart workspace One service<\/p>\n\n\n\n<p>Now the official way and recommended method is :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Take downtime. (stop horizon-workspace service on all service and connector nodes)<\/li>\n\n\n\n<li>Migrate DB (following customer\u2019s DBA approved procedure).<\/li>\n\n\n\n<li>Encrypt SQL user password using hznEncrypt<\/li>\n\n\n\n<li>Change JDBC url and password directly in runtime-config.properties on all service appliances<\/li>\n\n\n\n<li>Bring up first node. Verify the DB connectivity.<\/li>\n\n\n\n<li>Then bring up all other nodes 1 by 1<\/li>\n\n\n\n<li>Verify failover to DR works and fallback to primary works<\/li>\n\n\n\n<li>End downtime<\/li>\n<\/ul>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[28,30,11,12],"class_list":["post-31","post","type-post","status-publish","format-standard","hentry","category-identity-manager","tag-database","tag-move","tag-vmware","tag-workspace-one"],"_links":{"self":[{"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/posts\/31","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/comments?post=31"}],"version-history":[{"count":6,"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/posts\/31\/revisions"}],"predecessor-version":[{"id":360,"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/posts\/31\/revisions\/360"}],"wp:attachment":[{"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/media?parent=31"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/categories?post=31"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/my-virt.alfadir.net\/index.php\/wp-json\/wp\/v2\/tags?post=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}