Best practices
Learn more about the best practices in BigFix 10 Insights.
To minimize the impact of the data added and to optimize the ETL process duration, the
following general best practices are recommended:
- Reduce the amount of sites imported (that is, dialog provided per data set). If you do not plan to historically report on the data, do not import the data into insights. Treat the data load like a debt and be aware of the accumulated workload.
- You must maintain the data of primary sites historically, starting from when you added them. You can never delete the data. Hence, be prescriptive of which sites to import at the time of setting up Insights itself.
- Plan to run the ETL at a frequency that is useful to your business operations (less is more, daily is reasonable). Balance the performance and managing expectations as appropriate.
- Reduce the site subscriptions of checklist to machines that are intended to evaluate.
Insights SQL Configuration
| Configuration | Description \ reasoning |
|---|---|
| Verify SQL Server Collation matches all BigFix Enterprise datasources. | Collation setting for all databases in the system must match. The default collation for SQL Server databases is SQL_Latin1_General_CP1_CI_AS. Please refer to https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver15 for more information. |
| TempDB IO channel must be a dedicated channel and not shared. | BigFix Insights leverages TempDB heavily. Try to isolate TempDB from other workloads. |
| Configure memory limitations within SQL rightly. | Make sure that the SQL memory is capped to allow at least 8 GB for the OS. This is configured within the SQL Server properties. |
| Configure virus scanners to exclude the SQL file storage location including all data file sets | When you configure your antivirus software settings, make sure that you exclude the following files or directories(as applicable) from virus scanning. This improves the performance of the files and makes sure that the files are not locked when the SQL Server service must use them. Refer to instructions from your virus scanner on how to set this exclusion rule. Reference: https://support.microsoft.com/en-us/help/309422/choosing-antivirus-software-for-computers-that-run-sql-server |
| Do not use file indexing or file compression on supporting SQL data files. | Similar to the reasoning behind A/V exclusions apply the same to HIPS based applications or File indexing operations that potentially could lock the data files in use. |
| Initially establish the sizes of Tlog and MDF to 80% of size projection and set auto growth appropriately. | Leveraging the sizing information provided assures that the MDF and LDF data files are initially established prior to setup of the initial database. This will allow the system to minimize auto growth during the initial ETL. |
| Configure auto growth of SQL Database files to be substantial vs minimal. | The Supporting DB File can become quite sizeable within the SQL in support of the Insights DB. To minimize the time and resources the system is taking growing the supporting data files, assure the growth characteristics are changed from default value to be more than 2 GB autogrow, or by 10% every time. |
| Assure soft NUMA is in place | Per Microsoft SQL 2017 and 2019, soft NUMA configurations should be managed by default. Make sure that this setting has not been altered from the default. For more information, see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/soft-numa-sql-server?view=sql-server-ver15 |