vPro: Handling Large IntelAMT databases

Version 1

    Introduction

    Recently I received an inquiry from a customer about a 16GB IntelAMT database.  The customer had configured less than five thousand Intel AMT systems, and they expressed concerned that the database was so large.  There are customers with several tens of thousands of Intel AMT objects configured, yet their databases are less than half that size.  What guidelines can be provided to determine an appropriate database size?  In general, the core database is only a few megabytes in size, with each configured Intel AMT system adding on average less than 0.5MB to the database.  But if your IntelAMT database is far beyond this average (i.e. much greater than 0.5MB per configured system), you might be wondering what to do.

    The core answer to handling large or overgrown databases is to reduce the amount of event logging.  Events are logged based on actions, errors, and level of logging.  If you are experiencing a number of errors in configuring or maintaining with the Intel Setup and Configuration Service (SCS), the number of events in the IntelAMT log table will grow rapidly. 

    This article attempts to explain why the database grows so rapidly, how to minimize the growth, and how to deal with existing overgrown databases.  Readers of the article should have a basic understanding of Microsoft SQL server and the Intel Setup and Configuration Service (SCS).  The viewpoints and insights shared are based on experiences with customers and in lab environments.  The reader is encouraged to validate the recommendations in a test environment before making production changes.  Before pursuing recommendations or actions, consult with your database administrator to review options and best approach.  The information is provided “as-is”.

    The article is dividing into 7 core sections:

    ·        Introduction – this section

    ·        Check the current size of your database – What database files to monitor and size guidelines

    ·        What causes rapid database growth – Background insights to causes, settings, and events

    ·        Summary recommendations – Focus points to keep in mind, and might be all you need to know

    ·        Before you update Intel SCS – Difference between latest and supported versions

    ·        Shrinking an overgrown database – For the experts, a little insight on what database table

    ·        Additional thoughts and considerations – concluding thoughts and remarks

    Checking the current size of your database

    The IntelAMT database files are located at the designated database file location for the Microsoft SQLserver instance.  In a default installation, the IntelAMT.mdf and IntelAMT.ldf files will be located at c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

    Perhaps an easier way to see database size and properties is via Microsoft SQL Server Management Studio Express.  As shown in the following image, the IntelAMT database properties show a size of 9.19MB.

    http://www.intel.com/communities/pix/DB-size.gif

    Another method to check the database size is via a SQL query such as the following

     

    Use IntelAMT
    Exec sp_helpfile
    

     

    The following screenshot provides an example of the generated output.  Per the output shown, the database is approximately 9.4MB in size.  Note: there was a difference of time and activities which occurred between the two screenshots, thus a slight difference in the exact database size.

    http://www.intel.com/communities/pix/scripted-db-size.gif

     

     

    In the examples above, the database is fairly small.  This instance of the database is a new install, with only a single Intel AMT system configured.  The database few to 14.75MB with full verbose logging left on for 10 days, miscellaneous maintenance actions set to their highest frequency, and another system configured into the environment.

    One might ask – “How does the database grow to multiple gigabytes?”  Imagine several hundred of clients being configured each day with maintenance routines occurring daily and monthly. 

    What causes the database to grow so rapidly?

    About a year ago, a paper was posted on Intel SCS version 3.x sizing and capacity modeling.  That paper is available at http://communities.intel.com/docs/DOC-1636.  In addition, an explanation of the core Intel Setup and Configuration service was provided in the third article of a series about Deployment Models was posted at http://communities.intel.com/docs/DOC-1933.  Since then, Intel SCS version 5.x was released and subsequent versions are in the works. 

    The most common reason for a rapidly expanding database is due to event logging.  The frequency and per instance of events can cause a database to grow rapidly.  Thus the primary focuses are to ensure the configuration process is working correctly, that the logging of activities is appropriate, and that subsequent configuration maintenance activities are able to complete successful. 

    The logging level of activities can be changed through the console interacting with the Intel SCS service (i.e. AMTconfig windows service).  The default logging level is errors only.  Yet for testing or troubleshooting purposes, customers will often change to verbose logging levels.  This setting will cause about 0.11MB of data per system configured in the database, about 0.05MB per systems re-configured, and roughly 0.01MB for logging on a per-event basis.  (Note: Those numbers are stated in the sizing and capacity modeling paper mentioned above.  That study was done with Intel SCS 3.x and the real numbers may vary for different versions of Intel SCS).    The important item to remember is that several of these little data capturing events are happening at any given time, and that the retention of these events in the database may persist for 60 days which is the default retention value.

    Remember that the IntelAMT database stores both the configuration of the service, as well as the configuration events and settings of the individual Intel AMT systems.  The following screenshot shows the Intel SCS console for version 5.x of the software.  Similar settings will appear in Intel SCS version 3.x console or an embedded Intel SCS console such as the Altiris Out-of-Band Management implementation.

    http://www.intel.com/communities/pix/SCSconsole-logging.gif

     

    The above image shows the log level is set to Verbose with data retention for 60 days.  If the Intel AMT configuration process is working just fine, changing the Log Level back to “Errors Only” with a data retention of 5 days or less may be a better service configuration setting.  On a related note, if errors in the Intel AMT configuration process are already occurring, adding more systems into the database for configuration will only enlarge the number of error events being logged.  Thus, it may be best to first resolve outstanding configuration issues before exasperating event logging.

    Under the maintenance tab some events may not be required or can be set to a less frequent interval.  If your environment does not use Kerberos, the Intel AMT clock synchronization is not important.  If using AD integration with Kerberos, synchronizing the clock on a 1-7 day basis may be sufficient.  If the Intel AMT administrator password does not need to be changed monthly or at all, then unselect or adjust the setting.  If Microsoft Active Directory integration, TLS encryption, or Intel AMT configuration profile settings are not changing – then reapplying the Intel AMT configuration (i.e. re-provisioning or re-configuration) on a monthly or other frequent interval may not be important. 

    http://www.intel.com/communities/pix/maintenance-events.gif

     

     

    Database impact from maintenance events is relatively low, yet the volume and frequency of those events will impact the database growth.  The Intel SCS application will attempt to resolve the IP address of each system queued for maintenance.  If that IP address cannot be found, or the resolved IP address is incorrect due to DNS, an error event will occur and added to the log.  Unsuccessful events are then placed in a delayed queue which will make another attempt in the future.

    The timing of maintenance is based on the timing of configuration.  For example, if 100 systems were configured on the first of the month, a maintenance action configured for every month will occur on that batch of 100 systems per the recorded configuration instance. In some environments, systems are configured at a fairly random basis as units are introduced to the infrastructure.  If a customer is introducing 100 systems per week at a schedule of 20 systems per day separated by a 20-30 minutes, then the maintenance activities are spread out and will likely occur during business hours.  However, if a policy is set to initiate configuration on thousands of systems within a short timeframe, the Intel SCS application and IntelAMT database activity may be high at the original configuration and subsequent maintenance intervals.

    Based on the information above, a good scenario would be configuration and maintenance of systems when IP addresses are most likely to be resolved correctly via DNS.  The premise here is that the systems are connected to the corporate network, DNS-to-IP resolution is correct, and that configuration events were distributed across systems in intervals of less than a few hundred systems at any moment.  Keep in mind that the time of configuration or last maintenance event is the basis for the next scheduled maintenance event.

    In addition to Maintenance and Logging, the Service Performance Settings will determine how often the queue of pending tasks is polled along with the number of concurrent operations.  These settings are less important in regards to database growth, since the queued tasks will be performed eventually and will reflect logging activity.  However, if there appears to be backlog of configuration and maintenance requests, a temporary increase to these settings might help in clearing.  For the purposes of this paper, these settings will remain unchanged.

    Lastly, the number of Intel SCS engines (i.e. AMTconfig service) associated to a single database instance multiplies the actions that can be occurring at any given point.  This was briefly explained in the Deployment Models series referenced at the beginning of this article.  Most commonly, customers have only a single AMTconfig service running in the environment.  Related to overall system performance, in large environments it may be best to separate the database and application server, although commonly both are running on the same physical system.

    Summary Points to Keep IntelAMT Database Manageable

    In providing the insights shared above, the following are key points to keep in mind to avoid a bloated database.  If unsure why a particular point was made, please refer to the above material.  The summary points provide guidance across any environment, and individual points may be more or less applicable to a given environment.  In general, three core areas showed by focused on: Logging, Provisioning, and Maintenance.

    • Test small samples of each client model variation to be introduced into an environment.  Ensure the Intel AMT provisioning process completes successfully without persistent problems
    • Batch your Intel AMT provisioning requests into groups of less than 1,000.  This will spread the original configuration and subsequent maintenance loads
    • Monitor and avoid a backlog of failed requests.  Common failures include unable to resolve or contact an Intel AMT system by IP address.  Another common error is requesting a provisioning event when all requirements are not fulfilled (i.e. FQDN\UUID match, provision profile, etc)
    • Set logging to errors.  Short exceptions for testing or troubleshooting may require a higher logging level, yet keep to short periods of time.
    • Set event log retention to less than 5 days.  The security log can be per corporate policy (e.g. 60-days)
    • If using Microsoft Active Directory Integration for Kerberos authentication, set clock synchronization maintenance tasks to greater than 1 day, preferably 7 days. 
    • Default performance settings for thread count and queue size are usually sufficient.  If a backlog of requests appears to occur, temporarily increase to help clear the backlog if configuration and maintenance processes are running correctly.

     

     

     

    These are summary points and suggestions.  Additional considerations may apply to individual environments, such as separating the Intel SCS application and IntelAMT database unto separate systems.

     

    Before Updating to the Latest Version of Intel SCS

    After making the above changes, some customers may still experience overgrown databases.  This may occur more frequently on older versions of Intel SCS.  Situations have occurred with version 3 of the application where an especially large database persisted.  Updating to the latest version of Intel SCS will often include fixes and improvements to database maintenance, yet it may also the schema and per object data requirements. 

    Although available for download at http://software.intel.com/en-us/articles/download-the-latest-version-of-intel-amt-setup-and-configuration-service-scs/, the latest and greatest version of Intel SCS may not be the right answer for a particular environment.  If using a management application like Altiris, it is best to ensure what versions of Intel SCS are compatible with versions of the management application.  In the case of Altiris, there are two core versions of the management application (version 6 and 7).  If updated with the latest patches from Altiris, the approved Intel SCS versions are currently 3.2.1 and 5.1.50 respectively.    However, at the time of this article the most current download of Intel SCS is version 5.2.0.34B.  In this scenario, although a fix or improvement might exist in the Intel SCS application, it is often best to stay compliant with the approved and validated version supported by the management console vendor.

    An additional consideration in upgrading Intel SCS versions is that a schema update will often be applied to the IntelAMT database.  If using Intel SCS version 3 today, an upgrade to version 5 might show an increase in the IntelAMT object data at a scale of 2-3 times larger due to additional fields, options, and so forth.  However, the newer version of the application will have better performance and optimizations, including 2-3 times better application threading.  In addition, the newer Intel SCS version will allow configuration of the latest available options within newer Intel AMT systems.  The general recommendations is to upgrade to the latest, yet keep in mind the underlying reasons why such a decision might or might not apply to a particular environment.

    How to shrink an overgrown database

    In some cases, adjusting maintenance and logging levels may not be sufficient to decrease the size of an overgrown database.  Similarly, updating to the newest version of Intel SCS may not be an option for a particular environment.  Thus the focus becomes how to directly modify the database.  A word of caution before proceeding to this point: direct modifications to the database can be made, yet such actions are “at your own risk”. 

    The most common offending database table for overgrown IntelAMT databases is dbo.csti_log.  This table collects all of the reported log events.  In a small lab environment with logging set to verbose and only 2 systems configured, 7 days of log events generated over 7,500 entries.  Roughly 1,000 entries per day for just 2 IntelAMT systems plus regular service maintenance activities.  This number will vary based on service settings and environments, and is provided only as a reference for the following actions.

    By changing the log retention to 6 days, approximately 1,000 entries were cleared from the database in the lab using Intel SCS version 5.2.0.34B.  The removed log entries exceeded the 6 day limit.  If the database size did not decrease, it may be due to empty tables consuming space.   A few insights that might help in these situations.

    The DBCC SHRINKDATABASE command can be used to shrink the database file.  A similar process can be done automatically by changing the database property for “Auto Shrink”.  The screenshot below shows the properties of the IntelAMT database.  Auto shrink is set to a default value of “False”, and can be switched to “True”.

    http://www.intel.com/communities/pix/shrink-GUI.gif

     

    Following the above steps and reported database sizes, the actions taken thus far reduced my test database from 14.75MB down to 9.63MB. 

    If a more direct approach is desired, all log entries older than a defined time period can be removed.  This action may cause a temporary database usage spike since the table will be scanned for entries meeting the criteria. 

    To remove all log entries older than 2 days, use the following SQL query

                    Use IntelAMT
            go
            Delete dbo.csti_log
            Where datediff(dd,insert_time,getdate()) > 2
            Go
    

    On a test system, that action removed over 2700 rows from the dbo.csti_log table.  Basically, any entry older than 2 days.  Although the rows were removed, the database shrink process was still needed.

    To remove all entries from the dbo.csti_log table, thus truncating, use the following command:

    use IntelAMT
    truncate table [csti_log]
    

    Combined with shrinking the database, the above steps will help to reduce to overall database size.

    Additional thoughts and recommendations

    In completing this article, some customers have inquired about using the stored procedures available within IntelAMT.    This is not recommended since the stored procedures are undocumented and will change from one version to the next.  The best approach is to use the Intel SCS console or supporting management applications which interface with the database, thus allowing service settings to be changed.  If a situation occurs where Intel SCS cannot be upgraded to the latest version available, the recommended points have been completed, yet the database continues to be especially large – then take a look at the dbo.csti_log table within IntelAMT.  Truncating or directly deleting older entries might help in rare situations.