App Volumes 2.10 with SQL high availability

If like me you’ve already read the VMware App Volumes documentation, you’ll have noticed that whilst it’s clear and easy to follow, it only shows you how to configure App Volume manager resiliency with multiple managers talking to the same SQL server. So I thought i’d take some time to explain how to make SQL resilient too.

To configure App Volumes with high availability using SQL mirroring, you’ll need the following servers:

At least 3 Windows servers for SQL 2012

At least 2 Windows Servers for the App Volumes manager

You’ll also need access to Active Directory to create some accounts and groups

On the first SQL server run the SQL setup
Click on Installation, then click ‘New SQL Server stand-alone installation’
Setup will run some pre-flight checks and confirm if the tests pass or fail. If it shows 8 passed click ‘ok’. If any fail, view the details and re-run till you get 8 passes.
On the product key page, enter your key and click ‘next’
Tick the box to accept the license terms and click ‘next’
Leave the product updates page as default and click ‘next’
Setup will scan and download some setup files. Once all 4 sections have completed, click ‘install’
Some more pre-flight checks will run and again you need to check they all pass. Assuming they do, click ‘next’
On the setup role screen leave the default selection ‘SQL Server Feature Installation’ and click ‘next’
On the feature selection screen select database engine, sql replication, management tools-basic and complete, then click ‘Next’
Another series of pre-flight checks will take place. This time check for 3 passes and click ‘next’
On the instance configuration screen, select leave the default settings and click ‘next’
Leave the server configuration page as defaults and click ‘next’
A summary of the disk requirements will appear, click ‘next’
On the engine configuration page, leave the defaults and click either ‘add current user’ or ‘add’ to add a different user that will become sql administrators, then click ‘next’
On the error reporting screen just click ‘next’
The final set of pre-flight checks will complete. Assuming there are 4 passes, click ‘next’
A summary screen will appear. Check the settings and click ‘install’. The installation will now begin.
When the installation completes, check all the tasks have been successful and click ‘close’
Repeat steps 1 to 19 on SQL servers 2 and 3. When you have completed this continue to step 21

On your first SQL server, login and run SQL Server Management Studio. Right click on ‘databases’ and select ‘new database’. Enter the name ‘svmanager’ and leave all other settings as defaults. Click ‘ok’

Next, expand ‘security’ and right click on ‘logins’ and select ‘new login’. The login name should be the computer account of your App Volumes manager server eg. Domainname\servername$. Now change the default database to ‘svmanager’. Next click ‘user mappings, select the svmanager database and select ‘db_owner’. This makes the account a database owner. Click ‘ok’.

Next we need to mirror the database to failover SQL server. To do this, log onto the first sql server and open SQL management studio. Righ click on the app volumes database and go to tasks, Mirror

First of all you need to make two backups. The first one is a full database backup. Specify ‘Full’ and select the path, then click ok to commence the backup.

After a few seconds you should get a message saying it completed successfully

Next, repeat the same steps but this time change the backup type to ‘Transaction Log’. Select a new path for the log backup but then do not click OK
Click ‘Files’ on the left hand side and select the checkbox to ‘relocate all files and folders
Then select ‘options’ on the left and change the recovery state to ‘restore with norecovery’. This is very important otherwise mirroring will fail if the database state isn’t correct
Before we finish on the first SQL server, copy both the database and log backups to the second SQL server
Now logon to the second SQL server, open SQL management studio and right click ‘databases’ followed by ‘restore’. Under device select the database file you just copied across. Then when the database and log files appear below remove the log checkbox so that only database is checked.
Click ‘files’ on the left side and ensure that the checkbox is ticked to relocate all files and folders.
Click Options on the left and ensure you select ‘restore with no recovery’, then click ‘OK’

You should get a message saying the database was restored successfully

Next, right click on databases and select ‘restore files and folders’. When you popup appears, select the appvolumes database and select the device from which to restore. This will be the log file backup. You should then see the log backup appear below. Ensure that it is checked.
Click options on the left and select the radio button to ‘leave the database non-operational….. (restore with nonrecovery). Then click ‘OK’

You should get a popup saying the restore completed successfully

Now we have a running database on the first SQL server and a restored copy on the second SQL server which is in nonrecovery mode. Go back to the first SQL server and right click the app volumes database and click ‘mirror’
Click on ‘configure security’ and a new wizard will launch. Click ‘next’ to begin
Select ‘yes’ to include a witness server and click ‘next’
Ensure that all 3 boxes are checked and click ‘next’
The principal server should be the first SQL server that you currently using. Click ‘next’
On the next page, select the mirror server which will be the second SQL server we performed the restore on. Do this by browsing to it and connecting, then click ‘next’

Next you must specify the witness SQL server. To do this, browse to it and click connect as before, then click ‘next’
Next leave all 3 boxes empty and click ‘next’
All 3 servers will have their endpoints configured. All 3 should be successful. If so, click ‘close’
You’ll get a message asking you if you want to start mirroring. Click ‘start mirroring’
Once mirroring has started, look on the first SQL server at the state of the database. After a few seconds it should say ‘principal, syncronized’
Now check the second SQL server. Its database status should read ‘Mirror, Synchronized/restoring’. Mirroring is now complete.

Now mirroring is completed, we need to configure the App Volumes servers ODBC settings. On you first App Volumes manager server, first you need to install the SQL native client. The Windows server ODBC client will work with App Volumes but it doesn’t support the failover configuration required for High Availability. To do this run the setup as administrator and click ‘next’
Accept the license agreement and click ‘next’
Leave the default selection and click ‘next’
Click ‘install’ to begin the installation
Once the install is complete, click ‘finish’
Now that the Native driver is installed, run the ODBC editor and click the ‘system DSN’tab
Select the ODBC driver 11 for SQL Server and click ‘finish’
Enter the name ‘svmanager’ (Description is optional) and enter the first SQL server name. Then click ‘next’
On the authentication page, leave the setting as Integrated Windows authentication and click ‘next’
Change the default database to ‘svmanager’ and enter the name of the mirror server. This should be your second SQL server. Once you’ve entered this, click ‘next’
Leave all settings on the next page as defaults and click ‘finish’
You can now test that your ODBC alias is working ok. Click Test data source.
You should see a successful message. Click ‘ok’
Now you need to add a 32bit ODBC alias. To do this go to a search or run prompt and type C:/Windows/SysWOW64/odbcad32.exe then click return.
Click ‘add’ to add a new source, and select SQL Server Native Client 11.0. Then click ‘finish’
Enter the name of your alias, description and primary sql server as before, then click ‘next’
Change the database to ‘svmanager’ and enter the mirror server which should be the secondary sql server. Then click ‘next’
Leave the default settings and click ‘finish’
Click test data source to test the obdb alias is working ok
You should receive a successful message. Click ‘ok’
Next there are a couple of registry settings that you should check. Please check them both and ensure that the correct database name, primary and failover partner SQL servers are listed
You now need to repeat steps 46 – 66 on an additional App Volumes manager servers. This will ensure that all managers are configured to use the primary SQL server with a secondary configured as well.
We can now install the App Volumes manager application. To do this login to the first App Volumes manager server and locate the installation files. Navigate into the ‘installation’ folder
Right click setup.exe and click ‘run as administrator’
The installation will launch. Click ‘next’ to continue.
Agree to the license agreement and click ‘next’
Select ‘install app volumes manager’ and click install. ** do not ever install the agent on a manager server **
Click ‘next’ to continue again
Select ‘connect to an existing SQL sever database’ and click ‘next’
Browse to the location of the remote database.
It should find the first SQL server from the ODBC alias already configured. Select this and click ‘OK’
Next, click browse to choose the database. Select the App Volumes database and click ‘OK’
As this is the first App Volumes manager installation, check the box to ‘overwrite existing database’, then click ‘next’
Leave the default ports and click ‘next’
Leave the destination location as default and click ‘next’
The installation is now ready to begin. Click ‘install’ to start
The installation will progress for a few minutes
Once the installation has completed, click ‘Finish’
Open a web browser and browse to the address of the App Volumes manager server. You should see a message saying the server is starting up for a short while. This is normal.
Whilst waiting, check the services are running
After a short wait the page should refresh and you should get the Welcome page. Click ‘Get Started’
Here you can add you license key. To do this click ‘edit’. Once done, click ‘next’
Next, you can inset your active directory details. Enter the required fields and click ‘next’
You can now add an AD group which will contain App Volumes admins. Search for this and choose the group. Once done, click ‘next’
Next you configure your machine manager. This is your vCenter server. Enter all the details and click ‘Save’
The page will refresh and show the vCenter server. Here you can add additional ones if required. If not, click ‘next’
On the storage page, select a storage path for App Stacks and a path for Writable Volumes, then click ‘next’
It will prompt you to either import volumes now or do it in the background. Select either option and click ‘set defaults’
Here you can upload prepackaged volumes via a specific host. Enter the details and check the boxes for the templates you require, then click ‘upload’
You will receive a summary of the settings you selected. Click ‘upload’
Lastly you will see a summary page of all the settings you have configured. Click ‘next’ to complete the wizard
You are now logged into App Volumes manager
Now you need to install App Volumes manager on all additional manager servers. To do this you need to complete steps 69 – 98 on each of them. The only step which changes is step 78. When you get to step 78, you should NOT select the overwrite checkbox. The only time this should be selected is during the first install. On all subsequent installs, leave this unchecked.