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

 

  1. On the first SQL server run the SQL setupappvolssql1
  2. Click on Installation, then click ‘New SQL Server stand-alone installation’appvolssql2
  3. 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.appvolssql9
  4. On the product key page, enter your key and click ‘next’appvolssql5
  5. Tick the box to accept the license terms and click ‘next’appvolssql6
  6. Leave the product updates page as default and click ‘next’appvolssql7
  7. Setup will scan and download some setup files. Once all 4 sections have completed, click ‘install’appvolssql8
  8. Some more pre-flight checks will run and again you need to check they all pass. Assuming they do, click ‘next’appvolssql4
  9. On the setup role screen leave the default selection ‘SQL Server Feature Installation’ and click ‘next’appvolssql10
  10. On the feature selection screen select database engine, sql replication, management tools-basic and complete, then click ‘Next’appvolssql11
  11. Another series of pre-flight checks will take place. This time check for 3 passes and click ‘next’appvolssql12
  12. On the instance configuration screen, select leave the default settings and click ‘next’appvolssql13
  13. Leave the server configuration page as defaults and click ‘next’appvolssql15
  14. A summary of the disk requirements will appear, click ‘next’appvolssql14
  15. 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’appvolssql16
  16. On the error reporting screen just click ‘next’appvolssql17
  17. The final set of pre-flight checks will complete. Assuming there are 4 passes, click ‘next’appvolssql18
  18. A summary screen will appear. Check the settings and click ‘install’. The installation will now begin.appvolssql19appvolssql20
  19. When the installation completes, check all the tasks have been successful and click ‘close’appvolssql22
  20. Repeat steps 1 to 19 on SQL servers 2 and 3. When you have completed this continue to step 21appvolsdb1
  1. 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’appvolsdb2
  1. 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’.appvolsdb3
  1. 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, Mirrorappvolsmir1
  1. 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.appvolsmir2

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

  1. 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 OKappvolsmir4
  2. Click ‘Files’ on the left hand side and select the checkbox to ‘relocate all files and foldersappvolsmir25
  3. 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 correctappvolsmir8.75
  4. Before we finish on the first SQL server, copy both the database and log backups to the second SQL server
  5. 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.appvolsmir24
  6. Click ‘files’ on the left side and ensure that the checkbox is ticked to relocate all files and folders.appvolsmir25
  7. Click Options on the left and ensure you select ‘restore with no recovery’, then click ‘OK’appvolsmir8.75

You should get a message saying the database was restored successfullyappvolsmir34

  1. 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.appvolsmir28
  2. Click options on the left and select the radio button to ‘leave the database non-operational….. (restore with nonrecovery). Then click ‘OK’appvolsmir16

You should get a popup saying the restore completed successfullyappvolsmir34

  1. 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’appvolsmir36
  2. Click on ‘configure security’ and a new wizard will launch. Click ‘next’ to beginappvolsmir37
  3. Select ‘yes’ to include a witness server and click ‘next’appvolsmir38
  4. Ensure that all 3 boxes are checked and click ‘next’appvolsmir39
  5. The principal server should be the first SQL server that you currently using. Click ‘next’                     appvolsmir40
  6. 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’appvolsmir41appvolsmir42appvolsmir43
  1. Next you must specify the witness SQL server. To do this, browse to it and click connect as before, then click ‘next’appvolsmir44appvolsmir45appvolsmir46
  2. Next leave all 3 boxes empty and click ‘next’appvolsmir48
  3. All 3 servers will have their endpoints configured. All 3 should be successful. If so, click ‘close’               appvolsmir49
  4. You’ll get a message asking you if you want to start mirroring. Click ‘start mirroring’appvolsmir50
  5. 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’appvolsmir52
  6. Now check the second SQL server. Its database status should read ‘Mirror, Synchronized/restoring’. Mirroring is now complete.appvolsmir51
  1. 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’appvolsclient1
  2. Accept the license agreement and click ‘next’appvolsclient2
  3. Leave the default selection and click ‘next’appvolsclient3
  4. Click ‘install’ to begin the installationappvolsclient4
  5. Once the install is complete, click ‘finish’appvolsclient5
  6. Now that the Native driver is installed, run the ODBC editor and click the ‘system DSN’tab                      appvolsclient6
  7. Select the ODBC driver 11 for SQL Server and click ‘finish’appvolsclient7
  8. Enter the name ‘svmanager’ (Description is optional) and enter the first SQL server name. Then click ‘next’appvolsclient8
  9. On the authentication page, leave the setting as Integrated Windows authentication and click ‘next’appvolsclient9
  10. 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’appvolsclient10
  11. Leave all settings on the next page as defaults and click ‘finish’appvolsclient11
  12. You can now test that your ODBC alias is working ok. Click Test data source.appvolsclient12
  13. You should see a successful message. Click ‘ok’appvolsclient13
  14. 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.appvolsclient16
  15. Click ‘add’ to add a new source, and select SQL Server Native Client 11.0. Then click ‘finish’                          appvolsclient17
  16. Enter the name of your alias, description and primary sql server as before, then click ‘next’          appvolsclient18
  17. Change the database to ‘svmanager’ and enter the mirror server which should be the secondary sql server. Then click ‘next’appvolsclient19
  18. Leave the default settings and click ‘finish’appvolsclient20
  19. Click test data source to test the obdb alias is working okappvolsclient22
  20. You should receive a successful message. Click ‘ok’appvolsclient23
  21. 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 listedappvolsclient24appvolsclient25
  22. 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.
  23. 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’ folderappvols1
  24. Right click setup.exe and click ‘run as administrator’appvols3
  25. The installation will launch. Click ‘next’ to continue.appvols4
  26. Agree to the license agreement and click ‘next’appvols5
  27. Select ‘install app volumes manager’ and click install. ** do not ever install the agent on a manager server **appvols6
  28. Click ‘next’ to continue againappvols4
  29. Select ‘connect to an existing SQL sever database’ and click ‘next’appvols8
  30. Browse to the location of the remote database.appvols12
  31. It should find the first SQL server from the ODBC alias already configured. Select this and click ‘OK’                                       appvols10
  32. Next, click browse to choose the database. Select the App Volumes database and click ‘OK’                                                     appvols11
  33. As this is the first App Volumes manager installation, check the box to ‘overwrite existing database’, then click ‘next’appvols12
  34. Leave the default ports and click ‘next’appvols13
  35. Leave the destination location as default and click ‘next’appvols14
  36. The installation is now ready to begin. Click ‘install’ to startappvols15
  37. The installation will progress for a few minutesappvols17
  38. Once the installation has completed, click ‘Finish’appvols18
  39. 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.appvols19
  40. Whilst waiting, check the services are runningappvols20
  41. After a short wait the page should refresh and you should get the Welcome page. Click ‘Get Started’appvols21
  42. Here you can add you license key. To do this click ‘edit’. Once done, click ‘next’appvols22
  43. Next, you can inset your active directory details. Enter the required fields and click ‘next’appvols23
  44. You can now add an AD group which will contain App Volumes admins. Search for this and choose the group. Once done, click ‘next’appvols24
  45. Next you configure your machine manager. This is your vCenter server. Enter all the details and click ‘Save’appvols25
  46. The page will refresh and show the vCenter server. Here you can add additional ones if required. If not, click ‘next’appvols26
  47. On the storage page, select a storage path for App Stacks and a path for Writable Volumes, then click ‘next’appvols27
  48. It will prompt you to either import volumes now or do it in the background. Select either option and click ‘set defaults’appvols28
  49. 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’appvols29
  50. You will receive a summary of the settings you selected. Click ‘upload’appvols30
  51. Lastly you will see a summary page of all the settings you have configured. Click ‘next’ to complete the wizardappvols31
  52. You are now logged into App Volumes managerappvols37
  53. 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.

After following all of these steps, you’re App Volumes installation should now be resilient to either a manager failure or an SQL failure!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s