VMware App Volumes 2.x Database Best Practices

TECHNICAL WHITE PAPER – FEBRUARY 2018

VMWARE APP VOLUMES 2.X DATABASE BEST PRACTICES VMware App Volumes 2.x

VMWARE APP VOLUMES 2.X DATABASE BEST PRACTICES

Table of Contents Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Database Sizing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Sizing Example 1. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Sizing Example 2. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Sizing Example 3. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Database Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 High Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Configuring App Volumes Manager to Use a Highly Available Database . . . . . . . . . . . . . . . . . . . . . 11 Database Maintenance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Deploying App Volumes in Multi-Site Environments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Appendix A: Disk Space Requirements for the App Volumes Database . . . . . . . . . . . . . . . . . . . . . . . . 13 Appendix B: AppStacks at Scale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Additional Resources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 About the Authors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

T E C H N I C A L W H I T E PA P E R | 2


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 files are being managed properly, or whether high-availability strategies are being used effectively, 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 2.x database best practices together and organized them into the following sections: • Database sizing • Database performance • High availability

App Volumes Managers

SQL Database

vSphere Hosts

Datastore 1

Datastore 2

Storage Group 1

Datastore 3

Not Attachable

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 configuration information – These settings, which are usually configured during initial setup and rarely change over the lifetime of the deployment, include App Volumes machine manager configuration, Active Directory (AD) configuration, and storage group configuration. 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 files, 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, and writable volumes – Configuration information about AppStacks, AppStack assignments, applications in AppStacks, writable volumes, and .vmdk and .vhd files is stored in the App Volumes database until the underlying configuration is removed. • Auditing information – Activity logs and system messages are retained indefinitely or until cleared manually. The number of records created depends on how the environment is used and on how often the configuration 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 assigned per user. VMs are distributed across 20 ESXi hosts, which use the AppStacks mount local option. We can use the following calculations for database sizing: ~5 MB of static configuration 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 * 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 file * 1,000 = 5,000 KB • 5 KB per replicated AppStack .vmdk file * 21 = 105 KB ~33 MB for assignments, AppStacks, and writable volumes This totals ~47 MB for configuration. +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 files: • 2 GB for the primary ROWS data file • 10 MB for the transaction log file 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 assigned per user. VMs are distributed across 20 ESXi hosts, which use the AppStacks mount local option. We can use the following calculations for database sizing: ~5 MB of static configuration 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 * 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 file * 1,000 = 5,000 KB • 5 KB per replicated AppStack .vmdk file * 21 = 105 KB ~33 MB for assignments, AppStacks, and writable volumes This totals ~47 MB for configuration. +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 files: • 5.1 GB for the database file • 10 MB for the transaction log file 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 five AppStacks assigned per user. VMs are distributed across 100 ESXi hosts, which use the AppStacks mount local option. We can use the following calculations for database sizing: ~5 MB of static configuration 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 * 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 file * 5,000 = 25,000 KB • 5 KB per replicated AppStack .vmdk file * 101 = 505 KB ~ 166 MB for assignments, AppStacks, and writable volumes This totals ~217 MB for configuration. +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 files: • 30 GB for the database file • 10 MB for the transaction log file 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 different 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. • Place SQL Server on a dedicated VM that has adequate CPUs, RAM, and disk space to support the SQL instance. See the VMware knowledge base article Tips for configuring Microsoft SQL Server in a virtual machine (1002951). • With regard to transaction logs, VMware testing shows that when SQL Server is configured to autogrow 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 autogrow option is used only as a contingency for unexpected growth, or set the transaction log to a fixed size.

Figure 2: Transaction Log Set to a Fixed Maximum Size



In this case, you should set up an 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.

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 configuration 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, depending on the deployment size, consider making the following changes:

APP VOLUMES ITEM

FOR ≤2,000 USERS

FOR >2,000 TO