My postings are my own and don’t necessarily represent VMware’s positions, strategies or opinions.
- 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.
MAX database connections are set to 150 per Application Pool and Service. Currently in production on each DS server there are :
|4 Application Pools||8 Services|
- Green - AirWatch official recommendations
- Yellow - Additional Microsoft recommendations for SQL DB for large SCCM installations
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
Eighty percent of the server memory should be allocated to SQL. The remaining 20% must be freed up to run the OS.
In Properties of server in Memory tab set restrictions for memory usage of SQL Server:
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:
The following trace flags must be set to 1 at Global.
Set flags 1117, 1118, 1236, 8048 = 1
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.
Disable Lock Escalation for “interrogator.scheduler” table by running the
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
Best practive by Microsoft - to set an SQL Alert on event "log size greater than 18Gb" and a Job to execute Shrink Log File