VMware App Volumes – Migrate database to AlwaysOn Sql Cluster

Welcome to my VMware App Volumes series. In this session I will describe how I migrated the App Volumes database to my new Always-On SQL Cluster. This exercise will involve the following tasks:

  1. Shutdown App Volumes Manager service
  2. Backup Database on existing SQL server
  3. Restore database in SQL cluster
  4. Create Login
  5. Create Availability Group and listener
  6. Change ODBC Settings on App Volumes Managers
  7. Edit database.yml file
  8. Start App Volumes Manager Service
  9. Verify functionality and test failover

 

Shutdown App Volumes Manager service

MigrateDB2SqlCluster-01

 

Backup Database on existing SQL server

Create a Copy-only-backup on existing SQL server and take database offline. In SQL Server Management Studio, right-click AppvolDB, Task, Back Up…

MigrateDB2SqlCluster-02

Tick “Copy-only-backup”, choose destination, OK

MigrateDB2SqlCluster-03

MigrateDB2SqlCluster-04

 

Copy the appvoldb.bak file to one of the SQL servers in the SQL AlwaysOn-cluster

MigrateDB2SqlCluster-05

 

Restore database in SQL cluster

Open SQL Server Management Studio on one of the nodes in SQL cluster, Restore Database…

MigrateDB2SqlCluster-06

 

Browse and select appvoldb.bak file

MigrateDB2SqlCluster-07

MigrateDB2SqlCluster-08

 

Create Login

Verify that the appvol user is added as security login on new database server

MigrateDB2SqlCluster-09

 

Create Availability Group

Take backup of the AppvolDB on the new SQL server prior to creating availability group.

MigrateDB2SqlCluster-10

MigrateDB2SqlCluster-11

 

Right click Availability Groups, select New Availability Group Wizard

MigrateDB2SqlCluster-12

 

I give the availability group a somewhat meaningful name, Next

MigrateDB2SqlCluster-13

 

Select the AppvolDB database, Next

MigrateDB2SqlCluster-14

 

I add my replica and check “Automatic Failover”, click the Listener tab…

MigrateDB2SqlCluster-15

 

I enter a Listener DNS Name and Port. I click Add to enter a static ip-address. This will automatically be added to DNS, Next

MigrateDB2SqlCluster-16

 

Automatic seeding looks good, Next

MigrateDB2SqlCluster-17

 

Validation looks green, great! Next.

MigrateDB2SqlCluster-18

 

Finish

MigrateDB2SqlCluster-19

 

Nice, successfully created the availability group, Close

MigrateDB2SqlCluster-20

 

I verify that I can make a connection on port 1433 to my new listener FQDN

MigrateDB2SqlCluster-21

 

Change ODBC Settings on App Volumes Managers

I log in on the App Volumes Manager server and change both 32-bit and 64-bit ODBC Settings. I have documented the 32-bit procedure below, but the same change applies to the 64-bit DSN. Select svmanager 32-bit, Configure

MigrateDB2SqlCluster-22

 

I enter my listener-address, Next

MigrateDB2SqlCluster-23

 

Enter SQL user-info, Next

MigrateDB2SqlCluster-24

 

I check and modify the default database setting, Next

MigrateDB2SqlCluster-25

 

Finish

MigrateDB2SqlCluster-26

 

I test the new ODBC settings

MigrateDB2SqlCluster-27

 

Successful change, OK

MigrateDB2SqlCluster-28

 

Finally I make the same changes to my 64-bit DSN

 

Edit database.yml file

Make a copy of the database.yml-file, in “C:\Program Files (x86)\CloudVolumes\Manager\config”,  and edit it

MigrateDB2SqlCluster-31

 

Edit the user, username and add password, save and close database.yml file.

MigrateDB2SqlCluster-32

 

Start App Volumes Manager Service

I start the App Volumes Manager service and check Event Viewer for errors

MigrateDB2SqlCluster-29

 

Verify functionality and test failover

I log in to App Volumes Manager and verify functionality and settings

MigrateDB2SqlCluster-30

 

Updating App Volumes Manager by changing the database credential (2101109)

VMware App Volumes Product Page

VMware App Volumes planning, deployment etc.

Disclaimer: Every tips/tricks/posting I have published here, is tried and tested in different it-solutions. It is not guaranteed to work everywhere, but is meant as a tip for other users out there. Remember, Google is your friend and don’t be afraid to steal with pride! Feel free to comment below as needed.