Warning: call_user_func_array() expects parameter 1 to be a valid callback, no array or string given in /home/kalofero/public_html/blog/wp-includes/class-wp-hook.php on line 286
Migrating the vRO SQL Database to New Server (SKKB1032) | Spas Kaloferov's Blog

Migrating the vRO SQL Database to New Server (SKKB1032)

Posted on by Spas Kaloferov

In this article we will look into how to migrate the VMware vRealize Orchestrator (vRO) database to new server. In particular we will migrate from Microsoft SQL Server 2014 to a new SQL Server 2016 server. 

Introduction

In this article we will look into how to migrate the VMware vRealize Orchestrator (vRO) database to new server. In particular we will migrate from Microsoft SQL Server 2014 to a new SQL Server 2016 server. 
For the purpose I have a vRO 7.1 (vor-a-02) configured with SQL Server 2014 DB running on a Windows Server (lan1dc1.vmware.com). I will migrate the Database to a new SQL Server 2016 DB running on a second Windows Server (lan1dc4.vmware.com). I will use an SQL Server Migration Test workflow very similar to the Cluster DB Test workflow we used in Clustering the vRO Cluster Database using SQL Server (PoC) (SKKB1027) with a couple of differences explained below.

The vRO database migration process can be summarized in the following points:

  • Load Balancer Pre-Migration Tasks
    • Drain out Load Balancer connections to the vRO server/cluster.
  • vRO Pre-Migration Tasks
    • Verify there are no more running workflows.
    • (Opitonal) Start the SQL Server Migration Test WF
    • Shutdown vRO
  • SQL Server Migration
    • Perform a full backup of the source database from the source database server (SQL Server 2014 running on lan1dc1.vmware.com)
    • Perform a full backup of the source database transaction log from the source database server
    • Perform a full backup restore of the source database to the destination database server (SQL Server 2016 running on lan1dc4.vmware.com)
    • Perform a full backup restore of the source database transaction log to the destination database server
    • Put the source database in an offline mode.
  • vRO Post-Migration Tasks
    • Startup vRO
    • Change the database configuration from vRO Control Center.
    • Restart vRO
    • (Opitonal) Check the SQL Server Migration Test WF state
    • Check vRO Logs for errors
  • Load Balancer Post-Migration Tasks
    • Enable Load Balancer connections to the vRO server/cluster.
  • Final Step

 

Lab Environment

The logical design of this lab can be seen HERE.

 

LB Pre-Migration Tasks

Most load balancers, even the most basic once , offer option to drain existing LB connections and at the same time block new once until you enable them back again.
At this step drain out connections to the LB address of the vRO/vRO Cluster.

vRO Pre-Migration Tasks

After draining out the connections to the vRO/vRO Cluster from your LB, monitor the execution of existing WF’s and make sure they all finish before you proceed.
If you are not using load balancer and you are not the only administrator using vRO you should find a way to inform all other vRO users to finish work and close all existing client connections.
One way to check if there is someone connected to vRO using the vRO Client is to do an SSH to the vRO appliance and run :

netstat -np tcp | grep "<vRO_IP>:8281"

The following screenshot shows that there are no running wf’s .

The following screenshot shows the existing database configuration before the migration.
Note that I’m not using SSL for the DB Connection. If you use SSL there is one additional step you have to perform on the destination database server as described later in this article.

The following step is not mandatory.
I will use an SQL Server Migration Test workflow very similar to the Cluster DB Test workflow we used in Clustering the vRO Cluster Database using SQL Server (PoC) (SKKB1027) with a couple of differences.

For the purpose of these tests I’ve buid a very simple SQL Server Migration Test WF that increments a number from 0 to 300. Each time it does this the WF creates a couple of System Logs (stored on vRO Appliance and seen on Logs tab) and a couple of Server Logs (stored on DB and seen on the Events tab). I’ve split the action in multiple scriptable tasks. I’ve done this because each time a scriptable task executes and finishes it reads and writes to the DB. I wanted to have a constant communication to the DB.
This workflow differs from the Cluster DB Test workflow only in that has an additional Timer taks that sleeps the WF for 5 seconds. I don’t want to overfill the logs .

Following screenshots shows the WF schema.

I want the WF to continue execution after vRO reboots (connected to the New DB) and by thus monitor if any DB transactions were lost. For the purpose done the following general changes to the WF:

  • Set the Server Restart Behavior to Resume workflow run
  • Set the Resume from failed Behavior to Enabled

The following screenshot shows those changes.

For more information about the WF refer to the article above.
Now, as a final step before shutting down the vRO appliance, I will start our SQL Server Migration Test WF.

I will not wait for it to complete but rather shutdown vRO right away.

 

SQL Server Migration

At this point of the migration, vRO should be completely shut down.
On the source database server perform a full backup of the vRO database and of the transaction logs (in that order) as shown on the following screenshots.

On the destination database server perform a full backup restore of the vRO database with the RESTORE WITH NORECOVERY option as shown on the following screenshot.

On the destination database server perform a restore of the transaction log backup as shown on the following screenshot.

If you are using SSL for the connection b/n vRO and the SQL server make sure to configure the destination database server to accept SSL connections.
To learn how to configure an SSL connection b/n vRO and an SQL Server, visit Configuring vRO to use SSL Connection to the SQL Server Database (SKKB1031)

On the source database server put the vRO database in offline mode as shown on the following screenshots.

 

This completes the SQL Server migration.

 

vRO Post-Migration Tasks

At this point you should have migrated the SQL database to a new server.
Next we need to point vRO to the new database server name.
Power on the vRO appliance and change the databse configuraiton from Control Center.
Point to the new database server as shown on the following screenshot

Restart the vRO Server Service.

Optionally, login to vRO and check the SQL Server Migration Test state. As you can see from the following screenshot after vRO rebooted (now connected to the new DB) it resumed the WF execution from the last point. Nothing was lost.

You can check the vRO Logs for any errors. I’ve done so and haven’t seen any.

 

 

LB Post-Migration Tasks

Enable connection to  the LB address of the vRO/vRO Cluster.

 

Final Step

If all went well, go grab a beer.

DISCLAIMER; This is a personal blog. Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated. Any views or opinions are not intended to malign any religion, ethnic group, club, organization, company, or individual.
All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
Photos
Unless stated, all photos are the work of the blog owner and are licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License. If used with watermark, no need to credit to the blog owner. For any edit to photos, including cropping, please contact me first.
Recipes
Unless stated, all recipes are the work of the blog owner and are licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License. Please credit all recipes to the blog owner and link back to the original blog post.
Downloadable Files
Any downloadable file, including but not limited to pdfs, docs, jpegs, pngs, is provided at the user’s own risk. The owner will not be liable for any losses, injuries, or damages resulting from a corrupted or damaged file.
Comments
Comments are welcome. However, the blog owner reserves the right to edit or delete any comments submitted to this blog without notice due to
– Comments deemed to be spam or questionable spam
– Comments including profanity
– Comments containing language or concepts that could be deemed offensive
– Comments containing hate speech, credible threats, or direct attacks on an individual or group
The blog owner is not responsible for the content in comments.
This policy is subject to change at anytime.

Leave a Reply

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