WS1 SQL Database

Articles in section

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 Pools 8 Services
AirWatch Local AW Tunnel Queue Monitor Service
AirWatch API AW Entity Change Queue Monitor
AirWatch DS AW Interrogator Queue Monitor
AirWatch SSP 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

VMware official recommendations

  • 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 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.

Additional Microsoft recommendations for SQL DB for large installations

  • 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.