Migrating the vRO SQL Database to New Server (SKKB1032)

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.

Leave a Reply

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