Database Server Prerequisites

Meeting database prerequisites is essential to a successful Workspace ONE UEM installation. Learn more about all requirements for SQL Server hardware and software, TCP/IP, SQL Server Always On, and how to ensure that they are met.

SQL Server Hardware Requirements

The exact specifications needed for your SQL server depend on the size and needs of your deployment. Gather this information before proceeding so you size your servers correctly. Read the Workspace ONE UEM Recommended Architecture Guide, available at docs.vmware.com, for hardware sizing information and other technical details that ensure the smooth operation of your Workspace ONE UEM database.

Note: To ensure the best installation experience, do not run the database installer from the database server.

SQL Server Software Requirements

SQL Server 2014 Enterprise, SQL Server 2016, SQL Server 2017, or SQL Server 2019 with Client Tools (SQL Management Studio, SQL Server Agent, latest service packs). Ensure that the SQL Servers are 64-bit (OS and SQL Server).
Workspace ONE UEM does not support Express, Workgroup, or Web editions of the SQL Server. These editions do not support all the features used in the Workspace ONE UEM application. Workspace ONE UEM supports only the Standard and Enterprise Editions.
.NET 4.8 is required to run the database installer and is installed through the .NET Framework web downloader. If you do not want to install .NET on to your database server, then run the database installer from another Workspace ONE UEM server or a jump server where .NET can be installed.
Ensure that the SQL Server Agent Windows service is set to Automatic or Automatic (Delayed) as the Start type for the service. If set to Manual, then the service has to be manually started before database installation.
To create, back up, and restore a database, you must have the access and knowledge required.

When the database installer runs, it automatically updates your SQL server with the latest versions of:

ODBC Driver 13 for SQL Server 64-bit
Command Line Utilities 13 for SQL Server 64-bit

TCP/IP Enabled

Workspace ONE UEM can use TCP/IP to connect to the database. Deactivating Named Pipes forces TCP/IP communication, which can improve performance. Workspace ONE UEM works with active and deactivated named pipes. In the SQL Server Configuration Manager, navigate to SQL Server Network Configuration and select Protocols for MSSQLSERVER.

SQL Server Always On

The SQL server's Always On capability combines failover clustering with database mirroring and log shipping. Always On allows for multiple read copies of your database and a single copy for read-write operations.

For more information about Always On functionality, see https://msdn.microsoft.com/en-us/library/ff877884.aspx.

If you have the bandwidth to support the traffic generated by Workspace ONE UEM, then the Workspace ONE UEM database supports Always On. The following Always On functionality has been tested for support:

Database in an Availability Group
Availability Group failover
Secondary Replica promotion to Primary
Synchronous Replication

To integrate the SQL server's Always On, set up the following prerequisites:

Create a database listener to integrate with the Workspace ONE UEM Application and Database installations.
For more information on creating a database listener, see https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server.
If the SQL accounts used for Workspace ONE UEM have minimal permissions, you might need to script the SQL account creation on the secondary nodes.
You will need to query the system table on the primary node to get the hexadecimalSID for the login. Use the following query:
```
USE [master]SELECT * FROM SYS.SYSLOGINS WHERE NAME LIKE ‘%LOGINNAME%’
```
Once you get the SID, the script below can be used to create the login on secondary nodes.
```
USE [master]GOCREATE LOGIN [SqlLogin] WITH PASSWORD=N'[Password]’, SID=[HexadecimalSID], DEFAULT_DATABASE=[myDatabase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=[setting], CHECK_POLICY=[setting]GO
```
If the Always On Availability Group uses different network subnets, you must configure your Availability Group Listener settings before you can deploy Workspace ONE UEM. Run the following commands using PowerShell on each database server in your cluster before you run the database installer:
```
>Get-ClusterResource <AG Listener Resource Name> | Set-ClusterParameter -Name HostRecordTTL -Value 60
```
```
>Get-ClusterResource <AG Listener Resource Name> | Set-ClusterParameter -Name RegisterAllProvidersIP -Value 0
```
For more information about HostRecordTTL values, including how to retrieve the AG Listener Resource Name, see https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/.
Your database administrators decide the value for the HostRecordTTL. Low values result in a faster reconnection after a fail-over. For example, with a value of 60, the listener's DNS record updates take up to 60 seconds to match the IP address of the Primary (Active) SQL Node after an SQL fail-over.

Workspace ONE UEM Database Performance Recommendations

Workspace ONE UEM provides a database of performance recommendations based on scalability tests performed by the Workspace ONE UEM team.


Recommendation	Description
TempDB Configuration	The number of TempDB files must match the number of CPU cores when the core is less than or equal to 8 cores. Beyond 8 cores, the number of files must be the closest multiple of 4 that is less than or equal to the number of cores (example, 10 cores need 8 tempDBs, 12 cores need 12 TempDBs, 13 cores need 12 TempDBs, 16 cores need 16 TempDBs.) File size, growth rate, and the location must be the same for all TempDB files.
Memory Allocation	80% of the server memory should be allocated to SQL. The remaining 20% must be freed up to run the OS.
Cost Threshold for Parallelism and Maximum Degree of Parallelism	Cost Threshold for Parallelism is the cost needed for a query to be qualified to use more than a single CPU thread. Maximum Degree of Parallelism is the maximum number of threads that can be used per query. The following are recommended values for these parameters: Cost Threshold of Parallelism: 50 Max Degree of Parallelism: 2 and reduce to 1 if there is high server utilization.
Trace Flag	The following trace flags must be set to 1 at Global. 1117 (https://msdn.microsoft.com/en-us/library/ms188396.aspx) - Not applicable to SQL 2016 and greater. 1118 (https://msdn.microsoft.com/en-us/library/ms188396.aspx) - Not applicable to SQL 2016 and greater. 1236 (https://support.microsoft.com/en-us/kb/2926217) - Not applicable to SQL 2016 and greater. 8048 (https://blogs.msdn.microsoft.com/psssql/2015/03/02/running-sql-server-on-machines-with-more-than-8-cpus-per-numa-node-may-need-trace-flag-8048/) - Not applicable to SQL 2016 and greater.
Trace Flag - SQL Server 2016	See https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceontrace- flags-transact-sql/view=sql-server-2017
Hyperthreading	To ensure best performance, hyperthreading must be deactivated on the database if the database is running on a physical server. If it is on a VM, then having hyperthreading enabled on the ESX host doesn't have any performance impact, but hyperthreading must be deactivated on the Windows host level.
Optimize for Ad hoc Workloads	Enable Optimize for Ad hoc Workloads under SQL server properties. This is recommended to free memory from the server. Refer to the following article for more information: https://msdn.microsoft.com/en-us/library/cc645587(v=sql.120).aspx.
Lock Escalation	Deactivate Lock Escalation for the “interrogator.scheduler” table by running the “alter table interrogator.scheduler set (lock_escalation = {Disable})” command. This is recommended as the scheduler table has very high rate of updates/inserts. There is a high contention on this table with the use of FCM, and deactivating lock escalation helps improve performance. However, the drawback is that more memory is consumed. Refer to the following article for more information: https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx.
Autogrowth	For Production and Temp DBs, set Autogrowth to 128 MB and max size to Unlimited.

For device deployments of more than 150,000 devices, ensure that the Database is partitioned. You can run the installer from an elevated command prompt with the following flag: Name_Of_Database_installer.exe /V"AWINSTALLPARTITIONEDDATABASE=1".

For example: AirWatch_DB_9.1_GA_Setup.exe /V"AWINSTALLPARTITIONEDDATABASE=1".

Important:

This command requires SQL Enterprise. If you are running this command on a Workspace ONE UEM Database, you must run the installer with the flag for each upgrade from then on. If you do not, an error displays.