DX UIM Database Administration and Maintenance questions
Article ID: 380956
Updated On:
Products
Issue/Introduction
We are planning to deploy MS SQL DB (Always ON), 2 node cluster for our HA solution across 2 DC's. Before proceeding our DBAs have raised below pointers for clarification.
Environment
- DX UIM 20.4/23.4 or higher
Cause
- Data administration / maintenance
- UIM sizing guidance
Resolution
1.Table partitioning feature is the limitation mentioned for MS SQL Server Standard edition. If it’s missing, what is the impact on the application/monitoring in terms of:
a. Is it significant impact on monitoring that can result in component failure.
>>>Yes, it has significant impact and can result in database slowness/poor query performance when queries are sometimes run against large tables. Altough in later editions the Standard Edition itself does support partitioning, but the data_engine only supports p[artitioning when using the Enterprise Edition.
b. The data maintenance jobs and the index maintenance jobs will not work at all? Or the duration will be longer? Or Performance impact?
>>>Index maintenance should be disabled. It is a limited outdated option which only defragments the tables indexes for RN, HN, VN, DN, BN tables and it can easily get bogged down when operating on large tables and that slows down the database response.
Partitioning is sufficient for optimal performance!
Please refer to the following article on this subject which explains what you need to know including a subset of tables that you can run a daily job on for defragmenting tables indexes:
UIM data_engine administration and maintenance
2. If there is custom configuration needed for Microsoft SQL Server Standard edition, will Broadcom support with its implementation?
>>>No, MS SQL Server Standard Edition is supported but it is not the best practice and we dont recommend using Standard Edition due to these limitations:
-
Table partitioning is not supported in the Standard Edition of Microsoft SQL Server. Therefore, the data maintenance jobs and the index maintenance jobs, which are scheduled from the data_engine probe UI, will not be effective because of the unsupported version.
-
During the scheduled data maintenance job, the data is deleted in batches based on the values set in the data_engine configuration file.
Ensure that enough space is available on the drive where the transaction log file is located to avoid space issues. Use separate drives/file systems if possible. -
During the table maintenance and index maintenance, you must use the offline option in the data_engine UI before scheduling the index maintenance. The online index maintenance is not supported in the Standard Edition of Microsoft SQL Server.
3. How many databases (not SQL instance) are created as part of DX UIM deployment, SQL DBA need to analyze availability group HA.
>>>One single UIM database is created. Multiple databases or data_engine's are not officially supported but if youre using our HA probe for quick and easy high availability the data_engine would be deployed on the HA Secondary/Failover node but not ket running until failover.
4. If we opt for the MS SQL Server Enterprise Edition how many minimum vCPUs should be considered based on number of devices to be monitored?
The whole purpose of questioning is to understand that will SE of MS SQL 2022 suffice our requirement or we deinitely need EE of MS SQL 2022.
>>>Yes, you need an Enterprise edition ALWAYS unless you have a very small environment with ony a few hubs and less than 50-100 robots and limited monitoring and data collection.
Please refer to the following techdoc page and search for "Database Server":
DX UIM Sizing Recommendations
DX UIM Sizing Calculator guidance
DX UIM Sizing calculator can be found here-> https://sizer.broadcom.com/UIMSizingTool/
Feedback
