FactoryTalk AssetCentre database management

A FactoryTalk AssetCentre system relies on a Microsoft SQL Server back end to store data for the entire system. All components of the system are stored within the SQL database, including archived files, diagnostic logs, and the actual configuration of the FactoryTalk AssetCentre system. The FactoryTalk AssetCentre server is the only component that directly accesses the SQL database. The FactoryTalk AssetCentre client and agent components only connect to the FactoryTalk AssetCentre server to process their requests.

The capabilities of FactoryTalk AssetCentre with the most impact on the growth and performance of the database are the FactoryTalk AssetCentre Archive and the FactoryTalk Diagnostic Logs (Events and Audits). Microsoft SQL Server transaction logs can also consume a large amount of disk space, but are not controlled within the FactoryTalk AssetCentre system.

In most FactoryTalk AssetCentre systems, Event and Audit logs will be the vast majority of logs collected. These logs are stored in the

log_EventLog

and

log_AuditEventLog

tables, respectively. Additionally, the

log_LogAttachments

table is used to track any log attachments. The amount of space consumed by an individual log message will depend on the length of the message, but for the purposes of estimation, is generally between 1KB and 10KB. Attachments are not compressed, meaning that a PDF compare report of 2MB will consume 2MB of disk space when stored in the database.

The Archive is used to store all files that are managed by the FactoryTalk AssetCentre system, primarily using the

arch_AssetImages

table. Each version of a file is stored in the database, meaning that the database size will grow by the size of each file multiplied by its file size.

The individual files comprising a binder asset are stored in the same way, but it should be noted that a single binder can contain hundreds of files. In this way, the database size can increase substantially if many new binder versions are being created. Fortunately, the FactoryTalk AssetCentre system will detect differences in the individual files within a binder and, if the file is not different from one binder version to the next, the same file version will be referenced in both binder versions.

A database transaction log is a built-in function of Microsoft SQL Server. It captures each modification made to the database, and can be used to roll back any undesired changes. The FactoryTalk AssetCentre system does not make use of this log.

Due to the nature of the FactoryTalk AssetCentre system, a large number of changes are made through normal use (for example, collecting Diagnostic Logs write a new log to the database, resulting in a change). This means that the database's transaction log can grow quickly.

FactoryTalk AssetCentre database performance

A large FactoryTalk AssetCentre database can see performance impacts due to the size of archived files (in terms of how long a file retrieval operation takes) and in the total number of entries in a given table. Poor performance can include slow client response time and slow file interaction time.

In larger FactoryTalk AssetCentre systems, particularly with large numbers of clients, the Event and Audit log tables can grow quickly. While individual diagnostic messages are not large disk space consumers, increasingly large table sizes can slow the system's query speed.

When viewing the Event and Audit tables within the FactoryTalk AssetCentre desktop client, the client is querying the log tables at a 10 second refresh rate. As a table's size grows, the query response time of the SQL database will grow slower, resulting in slower client update rates.

When the Log Cleanup utility is used to remove unnecessary log information, the selection of logs to remove is determined by the logged time. The logged time is derived by querying the table, meaning that the time to clean up the logs will grow as the table size grows.

Each archived file, and each version thereof, results in an individual entry in the

arch_AssetImages

table. This means performance degradation is possible with a large number of archived files, a large number of versions, or a combination of the two. Performance degradation is mostly due to the amount of time required to query the table as the number of rows increases.

When viewing archived assets using the Archive view within the FactoryTalk AssetCentre desktop client, the

arch_AssetImages

table is queried to construct the appropriate information. As the number of entries within the table grow, the time to return this query will grow larger, potentially resulting in delays in opening and refreshing the view.

Similarly, the time necessary to delete older versions will grow as the

arch_AssetImages

table grows. Binders are particularly susceptible to delays because each file contained within the binder must be individually queried. These delays will be present in the Archive Cleanup utility, as well as during check-in procedures if Database Limitations are created and the maximum number of versions for an asset have been reached (this would result in a cleanup operation of the oldest version before the new one can be created).

Database connection configuration

The connection of the FactoryTalk AssetCentre server to the AssetCentre SQL database is configured using the Data Source Configuration Wizard, installed with the FactoryTalk AssetCentre server. This utility can be used to make FactoryTalk AssetCentre aware of a mirrored SQL database, and also to adjust the connection timeout for slower networks or larger databases.

A SQL user is used by the FactoryTalk AssetCentre server to communicate with the FactoryTalk AssetCentre database. This user must be configured for SQL Server authentication, not Windows authentication. The FactoryTalk AssetCentre server installation will create this user if it does not already exist, or an existing user can be identified.

Only the FactoryTalk AssetCentre server component makes use of this user, as it is the only component connecting to the database. The Data Source Configuration Wizard can be used to change the username or password. The user requires db_dataexecute, db_datareader, db_datawriter permissions on the FactoryTalk AssetCentre database.

Database maintenance

There are multiple utilities included with the FactoryTalk AssetCentre product to enable database monitoring and maintenance. Additionally, routine Microsoft SQL database maintenance is recommended.

FactoryTalk AssetCentre allows users to configure database size alerts using the Database Limitations setting under

Tools > Options

. Database Limitations can be configured to monitor the size of the Event and Audit logs, as well as the overall size of the database. Additionally, it can be configured to keep a maximum number of versions of an asset in archive, where it will automatically clean up the oldest version when a new one is created.

The Event and Audit log sizes can be periodically reduced using the Log Cleanup Wizard found under the

Tools

menu. The Log Cleanup Wizard allows the user to specify a time period of logs to remove from the database. Optionally, these removed logs can be exported to an MDF file for storage.

The Log Cleanup Wizard can also be run from a command line, allowing the cleanups to be executed on a scheduled basis. For more information on running this utility from a command line, see Knowledgebase Document ID: QA26667 - Using the FactoryTalk AssetCentre Log Clean-up Wizard from a Command Line.

The Archive size can be periodically reduced using the Archive Cleanup Wizard found under the

Tools

menu. The Archive Cleanup Wizard allows the user to specify a time range or version number range, which will then remove all applicable versions from the FactoryTalk AssetCentre database. Multiple archived assets can be selected for cleanup simultaneously.

The Archive Cleanup Wizard can also be run from a command line, allowing the cleanups to be executed on a scheduled basis. For more information on running the utility from a command line, see Knowledgebase Document I: QA36382 - FactoryTalk AssetCentre Archive Cleanup Tool from the Command Prompt.

Using the Database Limitations option can be a more effective way to manage archive size, as opposed to scheduled cleanups. The Database Limitations option allows users to set a maximum number of versions globally, applicable to every archived asset, as well as individually on each archived asset.

Because of the complexity of the binder asset type, users should consider setting a smaller number of maximum versions on archived binders. This is not a software requirement, but can have a positive effect on database performance.

Every SQL Server database has a transaction log that is used to record all changes to the database. Through normal operation, the transaction log of a FactoryTalk AssetCentre database can collect a large number of entries, resulting in a large amount of consumed disk space.

Transaction logs can be truncated manually, which is not recommended by Microsoft, or as part of backup operations. Using the simple recovery model, when a checkpoint is created in the database, the log is automatically truncated. This checkpoint should be used in conjunction with backing up the database.

Each table in the database has at least one associated index. Because the information in a FactoryTalk AssetCentre database is constantly changing, many of these indexes will become fragmented with normal use. Scheduling the system to perform frequent reorganize or rebuilds is recommended, though the user should be aware of the resource impact. Index cleanup can occur without stopping the FactoryTalk AssetCentre server, but as the number of records in a table increase, the index cleanup time will also increase.

In general, key tables include

arch_AssetImages

,

log_EventLog

,

log_LogAttachment

, and

log_AuditEventLog

. The structure of the FactoryTalk AssetCentre system will dictate the usage (and potential fragmentation) of the other tables in the database. As such, routine cleanup of all tables is recommended.

The complete contents of the FactoryTalk AssetCentre system are stored in the FactoryTalk AssetCentre SQL database. Backing up the database ensures that, in the event of a disaster, the FactoryTalk AssetCentre system can be fully restored. It is recommended to schedule backups of the SQL database, and store the backups on a separate disk from the database.