Page tree
Skip to end of metadata
Go to start of metadata

Disclaimer

My postings are my own and don’t necessarily represent VMware’s positions, strategies or opinions.

SQL Requirements

  • AirWatch 9.1 and later versions are supported on SQL Server 2016, SQL Server 2014, and SQL Server 2012.
  • Both Enterprise and Standard Editions are supported, Express Edition is NOT supported because it does not offer all of the features that are used by AirWatch.
  • It is recommended that the AirWatch databases are operating on 64-bit editions of Windows and using the 64-bit installation of SQL Server.
SQL Server Collation: AirWatch supports SQL_Latin1_General_CP1_CI_AS as the server AND database collation ONLY.

SQL Connections

MAX database connections are set to 150 per Application Pool and Service. Currently in production on each DS server there are :

4 Application Pools8 Services
  • AirWatch Local
  • AirWatch API
  • AirWatch DS
  • AirWatch SSP
  • AW Tunnel Queue Monitor Service
  • AW Entity Change Queue Monitor
  • AW Interrogator Queue Monitor
  • AW Interrogator Server
  • AW Log Manager Queue Monitor
  • AW Master Queue Service
  • AW MEG Queue Service
  • AW Messaging Service
Total = 12 * 150 max connections = 1800 per DS Server.

SQL Recommendations

  • Green - AirWatch official recommendations
  • Yellow - Additional Microsoft recommendations for SQL DB for large SCCM installations

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 (e.g. 10 cores will need 8 tempDBs, 12 cores will need 12 tempDBs, 13 cores will need 12 tempDBs, 16 cores will need 16 tempDBs.) File size, growth rate, and the location need to be the same for all tempDB files.

Microsoft SCCM best practice is to create no more than 8 temp DB files


Memory Allocation

Eighty percent of the server memory should be allocated to SQL. The remaining 20% must be freed up to run the OS.

  • Test = 16Gb
  •  Production = 128GB 

In Properties of server in Memory tab set restrictions for memory usage of SQL Server:

  • min = 60% 
  • max = 80%  

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 in case of 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)

1118 (https://msdn.microsoft.com/en-us/library/ms188396.aspx)

1236 (https://support.microsoft.com/en-us/kb/2926217)

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/)

Set flags 1117, 1118, 1236, 8048 = 1 

Hyperthreading

If the database is running on a physical server, hyperthreading must be disabled on the database to ensure best performance. If it is on a VM, then having hypertherading enabled on the ESX host will not have any performance impact, but hyperthreading must be disabled on the Windows host level.

Optimize for Ad hoc Workloads

Enable Optimize for Ad hoc Workloads under SQL server properties. This is recommended in order 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

Disable Lock Escalation for “interrogator.scheduler” table by running the

alter table interrogator.scheduler set (lock_escalation = {Disable}) 

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 GCM, and disabling 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.

SQL Server Agent autostart

In SQL Server Configuration Manager properties of SQL Server Agent component put automatic start

Disable Auto shrink of DB and journals

According to Microsoft Best Practices for SCCM, Auto Shrink of DB and journals should be turned OFF

Journal size edit

 According to Microsoft Best Practices for SCCM, initial size of transaction log should be 16Gb, growth by 512Mb

 Recovery Model type

Choose recovery model type - Full or Simple

See page on SQL Recovery model choice and consequences.

Best practive by Microsoft - to set an SQL Alert on event "log size greater than 18Gb" and a Job to execute Shrink Log File 

Important Articles