VMware App Volumes Database Best Practices

Applies to VMware App Volumes When Using a Microsoft SQL Server Database

Introduction

Often when an application performs poorly or stops working altogether, the problem can be traced back to a database issue—the type and edition used, whether the host system has adequate resources, whether the database is sized properly, whether the data and log les are being managed properly, or whether high-availability strategies are being used eectively, if at all. In the case of the VMware App Volumes™ database, even though the database is relatively small, proper setup and maintenance are crucial.

Every App Volumes operation is scheduled using the database, and each operation requires multiple SQL queries.

In this guide, we have gathered all the pertinent App Volumes database best practices together and organized them into the following sections:

  • Database sizing
  • Database performance
  • High availability

Example Logical Architecture of an App Volumes Deployment

Figure 1: Example Logical Architecture of an App Volumes Deployment

Database Sizing

The following types of objects are contained in an App Volumes database:

  • Static conguration information – These settings, which are usually congured during initial setup and rarely change over the lifetime of the deployment, include App Volumes machine manager conguration, Active Directory (AD) conguration, and storage group conguration. These settings do not usually consume more than 5 MB.
  • Environmental information – Information about environmental objects—including VM registration and state, VMware ESXi™ hosts, datastores, writable volumes ZIP les, Active Directory users and groups, and domain controllers—is stored in the database permanently, even if the underlying physical object is removed.

    For example, when a user logs in to a VM that has the App Volumes Agent, information about the user is pulled from AD and stored in the App Volumes database. If the user is removed from AD, although the user is automatically hidden from the App Volumes Manager console, the user information is not deleted from the App Volumes database.

  • Information about assignments, AppStacks/packages, and writable volumes – Conguration information about AppStacks/packages, AppStack or package assignments, application programs in AppStacks/packages, writable volumes, and VMDK and VHD les is stored in the App Volumes database until the underlying conguration is removed.

    Important: The guidance in this document applies to both App Volumes 2.x and App Volumes 4 when using a Microsoft SQL Server database. The VMDK or VHD files that are referred to as AppStacks in App Volumes 2.x are now called App Volumes packages in App Volumes 4. Therefore, in this document, “AppStack/package” is used to mean either App Volumes 2.x AppStacks or App Volumes 4 packages.

  • Auditing information – Activity logs and system messages are retained indenitely or until cleared manually. The number of records created depends on how the environment is used and on how often the conguration is changed. Activity logs include events such as computer startup and shutdown, user login and logout, administrator activity, and AD synchronization.
  • Dynamic data – Information about administrator sessions, pending tasks, and delayed jobs is stored in the database temporarily to coordinate work between multiple App Volumes Manager servers. To estimate the size of the transaction log, assume that dynamic data requires the same amount of space as static data plus 20 percent.

Sizing Example 1

In this example, we have 1,000 users who log in once a day to access a single desktop and have one writable volume and two AppStacks/packages assigned per user. VMs are distributed across 20 ESXi hosts, which use the Mount Local option. We can use the following calculations for database sizing:

~5 MB of static conguration

Plus

  • 1 KB per VM * 1,000 = 1,000 KB
  • 1 KB per ESXi host * 20 = 20 KB
  • 4 KB per AD user account * 1,000 = 4,000 KB
  • 4 KB per AD computer account * 1,000 = 4,000 KB 3 KB per datastore * 20 = 60 KB
  • 1 KB per domain controller * 2 = 2 KB

~9 MB of environmental information

plus

  • 16 KB per AppStack/package * 2 = 32 KB
  • 6 KB per assignment * 1,000 = 6,000 KB
  • 2 KB per application * 20 = 40 KB
  • 22 KB per writable volume * 1,000 = 22,000 KB 5 KB per writable VMDK le * 1,000 = 5,000 KB
  • 5 KB per replicated AppStack/package VMDK le * 21 = 105 KB

~33 MB for assignments, AppStacks/packages, and writable volumes

This totals ~47 MB for conguration.

+20% of dynamic data

This gives us the approximate size of ~56 MB for 1,000 users. Additionally, auditing information will require:

0.5 KB per login/logout operation * (2+2+6) * 1,000 = 10,000 KB = ~5 MB/day = 1,825 MB/year

Note: The login/logout operations are composed of 1 pre-startup event + 1 startup event + 1 login event + 1 logout event + 3 attach-volume events + 3 detach-volume events.

0.5 KB per sync operation * (1,000 users + 1,000 computers) = 1,000 KB = ~1 MB/day = 365 MB/year

=~2 GB per year for 1,000 users

Based on these calculations, we could safely set the following size for the database les: 

  • 2 GB for the primary ROWS data le
  • 10 MB for the transaction log le if the simple recovery model is in use

Sizing Example 2

In this example, we have 1,000 users who log in twice a day to access a single desktop and have one writable volume and four AppStacks/packages assigned per user. VMs are distributed across 20 ESXi hosts, which use the Mount Local option. We can use the following calculations for database sizing:

~5 MB of static conguration

plus

  • 1 KB per VM * 1,000 = 1,000 KB
  • 1 KB per ESXi host * 20 = 20 KB
  • 4 KB per AD user account * 1,000 = 4,000 KB
  • 4 KB per AD computer account * 1,000 = 4,000 KB 3 KB per datastore * 20 = 60 KB
  • 1 KB per domain controller * 2 = 2 KB

~9 MB of environmental information

plus

  • 16 KB per AppStack/package * 4 = 64 KB
  • 6 KB per assignment * 1,000 = 6,000 KB
  • 2 KB per application * 20 = 40 KB
  • 22 KB per writable volume * 1,000 = 22,000 KB 5 KB per writable VMDK le * 1,000 = 5,000 KB
  • 5 KB per replicated AppStack/package VMDK le * 21 = 105 KB

~33 MB for assignments, AppStacks/packages, and writable volumes

This totals ~47 MB for conguration.

+20% of dynamic data

This gives us the approximate size of ~56 MB for 1,000 users. Additionally, auditing information will require:

0.5 KB per login/logout operation * (2+4+20) * 1,000 = 13,000 KB = ~13 MB/day = 4,745 MB/year

Note: The login/logout operations are composed of 1 pre-startup event + 1 startup event + 2 login events + 2 logout events + 10 attach-volume events + 10 detach-volume events.

0.5 KB per sync operation * (1,000 users + 1,000 computers) = 1,000 KB = ~1 MB/day = 365 MB/year

=~5 GB per year for 1,000 users

Based on these calculations, we could safely set the following size for the database les:

  • 5.1 GB for the database le
  • 10 MB for the transaction log le if the simple recovery model is in use

Sizing Example 3

In this example, we have 5,000 users who log in twice a day to access a single desktop and have one writable volume and ve AppStacks/packages assigned per user. VMs are distributed across 100 ESXi hosts, which use the Mount Local option. We can use the following calculations for database sizing:

~5 MB of static conguration

plus

  • 1 KB per VM * 5,000 = 5,000 KB
  • 1 KB per ESXi host * 100 = 100 KB
  • 4 KB per AD user account * 5,000 = 20,000 KB
  • 4 KB per AD computer account * 5,000 = 20,000 KB 3 KB per datastore * 100 = 300 KB
  • 1 KB per domain controller * 2 = 2 KB

~46 MB of environmental information

  • 16 KB per AppStack/package * 5 = 80 KB
  • 6 KB per assignment * 5,000 = 30,000 KB
  • 2 KB per application * 100 = 200 KB
  • 22 KB per writable volume * 5,000 = 110,000 KB 5 KB per writable VMDK le * 5,000 = 25,000 KB
  • 5 KB per replicated AppStack/package VMDK le * 101 = 505 KB

~ 166 MB for assignments, AppStacks/packages, and writable volumes

This totals ~217 MB for conguration.

+20% of dynamic data

This gives us the approximate size of ~261 MB for 1,000 users. Additionally, auditing information will require:

0.5 KB per login/logout operation * (2+4+24) * 5,000 = 75,000 KB = ~75 MB/day = 27,375 MB/year

Note: The login/logout operations are composed of 1 pre-startup event + 1 startup event + 2 login events + 2 logout events + 12 attach-volume events + 12 detach-volume events.

0.5 KB per sync operation * (5,000 users + 5,000 computers) = 5,000 KB = ~5 MB/day

= 1,825 MB/year

=~29 GB per year for 5,000 users

Based on these calculations, we could safely set the following size for the database les:

  • 30 GB for the database le
  • 10 MB for the transaction log le if the simple recovery model is in use

Important: These examples provide a high-level estimate and are intended to give you a good idea of the approximate size of the database. The actual size will vary based on the way SQL Server stores data.

Sizing of the transaction log with a dierent database recovery model (such as full or bulk) is much less precise and much more dependent on the environment.

Database Performance

Microsoft SQL Server is a high-resource-consuming application. Despite the relatively small size of the App Volumes database and the lack of critical customer data in it, availability of the database is crucial for App Volumes Manager performance. All operations are scheduled using the database, and all operations require multiple SQL queries.

Use the following guidelines for best performance:

  • For production App Volumes environments, use an Enterprise or Standard edition of Microsoft SQL Server. Do not use SQL Server Express.
  • When designing the SQL Server environment that supports App Volumes, be sure to follow Microsoft best practices. SQL Server limits, not App Volumes limits, apply to the number of objects per database.
  • With regard to transaction logs, VMware testing shows that when SQL Server is congured to auto-grow the transaction log, all transactions are delayed or stalled, causing an increase in response times. VMware recommends that if you use the full recovery model, set the size of the transaction log large enough so that the auto-grow option is used only as a contingency for unexpected growth, or set the transaction log to a xed size.

Transaction Log Set to a Fixed Maximum Size

Figure 2: Transaction Log Set to a Fixed Maximum Size

In this case, you should set up a SQL Alert so that when the transaction log reaches 50 percent full, the transaction log is backed up, thus freeing it. This strategy maintains the transaction log at a reasonable size without impacting SQL Server performance.

Note: SQL Server Agent must be enabled to send alerts.

Enabling SQL Server Agent to Send Alerts

Figure 3: Enabling SQL Server Agent to Send Alerts

  • If auditing data is not required, consider pruning the VMware App Volumes SQL database. To perform this operation, see the VMware knowledge base article Pruning the VMware App Volumes SQL database (2132454).
  • Use the correct conguration and number of App Volumes Manager servers for the number of end users. In most environments, multiple App Volumes Manager servers are deployed.
  • In large App Volumes deployments where you are using multiple App Volumes Managers, if you see a slowly increasing number of App Volumes background jobs in the App Volumes Manager console, you might need to adjust the interval at which these background jobs occur. You would see this increase in the number of background jobs only on rare occasions, when the rate of change in the environment is high. Contact VMware Technical Support to obtain new interval values.
  • For better performance and reliability, consider sizing and conguring App Volumes Manager servers as described in the VMware Knowledge Base article VMware App Volumes Sizing Limits and Recommendations (67354).

AppStacks/Packages at Scale

For best performance, limit the total number of AppStacks/packages attached to each virtual desktop or RDSH server, as described in the Storage Limits section of the VMware Knowledge Base article VMware App Volumes Sizing Limits and Recommendations (67354). Although App Volumes can support more attached AppStacks/packages, up to the vSphere limit for VMDKs attached to a VM, there can be performance implications when attaching more AppStacks/packages. In production environments, it is a good practice to combine multiple applications into each AppStack/package.

For additional guidelines with respect to both traditional storage and VMware vSAN™, see the Recommended Practices for Packages in Production Environments section in the App Volumes Architecture chapter of the VMware Workspace ONE and VMware Horizon Reference Architecture guide.

High Availability

High-availability solutions for Microsoft SQL Server include:

  • Always On availability groups – VMware recommends this method because it guards against disk failure and provides constant availability of the database.
  • Database mirroring – This method is supported when it is congured with a witness server. Note that Microsoft has deprecated this feature in favor of Always On availability groups.
  • Always On failover cluster instances – Microsoft recommends this method if you plan to use data protection through a third-party shared disk solution (a SAN) rather than through SQL Server.

For more information, see the Microsoft SQL Server documentation for High Availability Solutions (SQL Server).

Configuring App Volumes Manager to Use a Highly Available Database

This document does not provide instructions for installing and conguring SQL Server to use SQL Server mirroring or Always On availability groups. For instructions for SQL Server–side conguration, see the Microsoft SQL Server documentation.

The SQL Server mirroring and Always On availability-groups options for the App Volumes database can be congured only after the SQL database is created when you install the rst instance of App Volumes Manager. Also, manual conguration is required after upgrading App Volumes Manager.

Follow these steps to congure each App Volumes Manager server to use a highly available database:

  1. Congure the highly available database by following the Microsoft SQL Server documentation.
  2. On the App Volumes Manager server, install the SQL Server native client (64-bit version) that matches the version of the SQL Server.
  3. Using the ODBC control panel, congure the new system DSN to use the SQL Server native client and point to a primary and a failover SQL server.
  4. Use a text editor to open the le
    c:\Program Files (x86)\CloudVolumes\Manager\config\database.yml>/span>
  5. Change the line dsn: svmanager to use the name of the DSN congured in Step 3.
  6. Save your changes to the database.yml le.
  7. Reboot the App Volumes Manager server.
  8. Verify log access to the App Volumes Manager UI.
    1. In a new browser tab, go to https://<Server_Address>/log.
    2. Verify that log entries appear on the page, as shown in the following gure.

Database Maintenance

The SQL Server mirroring and Always On availability-groups options require that the database use the full recovery model. The transaction log must be backed up to prevent excessive growth and fragmentation.

VMware recommends conguring a SQL Server alert to monitor the following SQL performance counter: SQLServer:Databases - Percent Log Used – appvolumes_database

For more information, see the Microsoft SQL Server documents Monitor and Respond to Events and Implement Jobs.

Deploying App Volumes in Multi-Site Environments

Many organizations have a presence in multiple geographic locations. In scenarios that require App Volumes to be deployed in multiple locations across the globe, VMware recommends separate App Volumes deployments.

App Volumes Manager requires a reliable and constant connection to the SQL database. Any delays or loss of communication between App Volumes Manager and its SQL database will cause performance and stability issues. These issues include but are not limited to

  • Slower user logins and logouts
  • Delays in AppStack/package attachment
  • Duplicate jobs executed by multiple managers

VMware recommends that an App Volumes deployment not span data centers with a network latency that can aect communications between App Volumes Manager and SQL Server. Also consider the history of communications reliability and past performance of the connection.

In cases where the latency between App Volumes Manager and SQL Server is higher than 15 ms, use a separate App Volumes deployment. A pod architecture in which all components are local to the enclosure/server rack—including App Volumes Manager and SQL Server—provides the best results. For more information, see the Pod and Block section of the Horizon Architecture chapter in the VMware Workspace ONE and VMware Horizon Reference Architecture guide.

Appendix A: Disk Space Requirements for the App Volumes Database

The following table summarizes the requirements for the various types of objects in the App Volumes database.

Table 1: Approximate Size for Each Record in the App Volumes Database

STATIC DATA

TOTAL SIZE

Configuration information for App Volumes machine managers, AD, and storage groups

5 MB

ENVIRONMENTAL OBJECTS

SIZE FOR EACH RECORD

VM registration and VM state information

1 KB

ESXi host

1 KB

AD User, group, computer, or organizational unit

4 KB

VMware vSphere® datastore

3 KB

Domain controller (discovered)

1 KB

ZIP file updates for writable volumes

Size of the ZIP file + 30%

ASSIGNMENTS, APPSTACKS/PACKAGES, AND WRITABLE VOLUMES

 

AppStack/package

16 KB

AppStack/package assignment

6 KB

Application in the AppStack/package

2 KB

Writable volume

22 KB

VMDK or VHD file (including AppStacks/packages replicated across storage groups)

5 KB

AUDITING INFORMATION

 

Activity log events

0.5–1 KB per operation

System messages such as agent errors, communication failures

0.5–2 KB per message

DYNAMIC DATA

 

Administrator sessions

1 KB per session

Pending tasks and delayed jobs

1 KB per job

 

Summary and Additional Resources

This guide lists all the pertinent best practices for using VMware App Volumes with a Microsoft SQL Server database, including database sizing, database performance, and high availability. 

Additional Resources

For more information, you can explore the following resources:

Changelog

The following updates were made to this guide.

Date

Description of Changes

2021-01-15

Updated to include App Volumes 4 components.

2019‑04‑15

Initial publication.

Authors

The following team wrote this document:

  • Josh Spencer, Staff Technical Marketing Architect in End-User-Computing Technical Marketing, VMware
  • Denis Gundarev, formerly of VMware, co-authored the original version of this document
  • Stéphane Asselin, formerly of VMware, co-authored the original version of this document

To comment on this paper, contact VMware End-User-Computing Technical Marketing at euc_tech_content_feedback@vmware.com.

 

 

 

 

 

Filter Tags

Horizon App Volumes Horizon Document Deployment Considerations Intermediate Optimize