6 Configuration of ToolsNet applications
6.5 ToolsNet Settings (server settings)
As soon as SQL Server is started, controller data will be retrieved from the .que files and stored in the database. Note that it may take time to retrieve all data if the database has been stopped for a long while.
Database maintenance
4 Database maintenance
In order to keep the ToolsNet database up and running with good performance and reliability there are a few maintenance steps that we recommend.
• First of all, take regularly backups of the database, both to secure the data in case of a hardware failure and to keep the transaction log from growing in memory.
• Secondly, to keep the transactions and searches fast and optimized it is also necessary to rebuild and reorganize the indexes in the database regularly.
• The third step is delete old results that no longer needs to be saved. This reduces the required disk space and improves report creation speed. After a delete maintenance it is also
recommended to reorganize the indexes.
The procedure for database maintenance is different on SQL Server 2005/2008 Express and SQL Server 2005/2008 Standard/Enterprise editions. The reason is that express versions do not include the SQL Agent Job Scheduling Service. Instead, a separate scheduled task must be set up in the Windows operating system.
4.1 Database maintenance on SQL Server Enterprise/Standard edition
After installation, maintenance jobs are set up in SQL Server. These jobs are by default disabled to avoid that production data is accidentally deleted. Delete maintenance is performed by a number of stored procedures with the prefix tnmt.
Job Purpose Default values
ToolsNet_DeleteMaintenance All results, graphs and events older than specified number of days to keep will be deleted. The deletion is done in six steps with the possibility to set different values for each step. The default values are the same for all steps
Step 1: Results – non safety critical Step 2: Results – safety critical Step 3: Graphs – non safety critical Step 4: Graphs – safety critical Step 5: Events
Step 6: MaintenanceLog
Days to keep = 100, Bulksize = 5000 (rows/transaction) Schedule is every day at 01:00
Job is disabled
ToolsNet_DeleteUnboundGr aphs
A job to delete all unbound graphs, i.e. traces without a result that have been sent to ToolsNet. This is usually a result of PM data drop commands.
Days to keep = 1 BulkSize = 5000
Schedule everyday at 23:00
Job is enabled.
ToolsNet_IndexRebuild Rebuilds the indexes physically on the hard drives.
Scheduled to run once a week.
Makes all transactions and searches faster.
No settings to change.
Job is enabled.
Scheduled every Sunday at
9836 4181 01 31 (86) Sundays, at 03:00
Metadata_DeleteMaintenance Deletes all Logs, MaintenanceProgressEvents and
MaintenanceLogs that are older than the number of "days to keep" (see each step).
Days to keep = 100, Bulksize = 5000 (rows/transaction) Job is disabled
Schedule is every day at 01:00 Metadata_IndexRebuild Rebuilds the indexes physically on the hard drives. Should be
scheduled to run once a week.
Makes all transactions and searches faster.
No settings to change.
Job is enabled.
Scheduled every Sunday at 03:00
Metadata_IndexReorganize Reorganizes the indexes. Should be scheduled to run once a day.
Makes all transactions and searches faster.
No settings to change.
Job is enabled.
Scheduled every day, but Sundays, at 03:00
4.1.1 Database backup
We recommend that the ToolsNet data collection database is backed up on regular intervals and that the backups are stored on a separate device, separated from the production environment in order to secure the data. Backups are to be performed with SQL Server or Oracle recommended procedures and not through file copying.
For SQL Server we recommend that backup routines are set up by using SQL Server Management
Console and scripting the backup to a SQL Server Agent Job that can be scheduled and monitored through SQL Server. Please refer to the SQL Server manuals for detailed instructions on how to do this.
4.1.2 Setting up Delete maintenance
Delete maintenance is set up in the SQL Server Agent job ToolsNet_Delete Maintenance.
Follow these steps to enable, set number of days to keep and schedule the job.
On the database server: click Start | All programs | Microsoft SQL Server 2005, and then click SQL Server Management Studio.
Database maintenance
Go to SQL Server Agent – Jobs. After installation, 6 jobs are set up.
Note! SQL Server agent must be installed and started.
Double-click the
ToolsNet_DeleteMaintenance job to configure settings. Settings are made in the Job Properties window.
9836 4181 01 33 (86) ToolsNet_DeleteMaintenance has a list of
defined steps.
Each delete operation will delete data in batches. Each batch deletes a max number of rows = @BulkSize. This will be repeated until all data older than
@DaysToKeep is deleted.
To set number of days to keep, and bulk size:
1. Select Steps.
2. For each Job step 1 to 6, click Edit. 3. In the General tab/command field, set
the correct value after
@DaysToKeep.
Note! @DaysToKeep = 0 will keep data from the current day.
4. Click OK.
To change the schedule, go to Job
properties and select Schedules. To set up the schedule:
1. Select the job in the Schedule list
2. Press Edit
3. Enter the desired schedule 4. If the job should run from now
on, check the Enabled check box.
5. Click OK.
Database maintenance
4.1.3 Index reorganize and Index rebuild
Index reorganize and Index rebuild are also set up as a job in SQL Server Agent. The reorganize job should be run once a day on a database with lots of transactions and the rebuild job should be run once a week.
The index maintenance is set up with this schema by default.
4.2 Database maintenance on SQL Server Express
Database maintenance on a SQL Server Express database needs to be set up through Scheduled Tasks in Windows operating system.
After installing ToolsNet 4000 folder, there are a set of bat-files for database maintenance in the bin folder that are to be scheduled manually.
4.2.1 Setting up a scheduled task in Windows
Follow these steps to add a Scheduled Task in Windows. It is the same procedure for all database maintenance jobs.
1. To open Scheduled Tasks on the database server, click Start | Settings | Control panel, and then double-click Scheduled Tasks. Double-click Add Scheduled Task to add a new Scheduled Task, and then click Next to start the wizard.
9836 4181 01 35 (86)
2. Browse for the batch file and select the file/program to run.
The following files will be found in the folder Utilites/Database maintenance for SQL Express on the installation media:
• RunDBMaintenance.bat
• IndexRebuild.bat
• Backup_AtlasCopco_ToolsNet_Database.bat
• Backup_AtlasCopco_ReportFramework_Meta data.bat
3. Set how often the task should be performed and the starting time.
Recommended:
Backup: Daily
Delete maintenance: Daily
Index rebuild: Weekly
4. Select the time and click Next. Recommended start time:
• 00:00/12 AM for backups
• 02:00/2 AM for truncate transaction log
• 01:00/1 AM for delete maintenance
• 02:00/ 2 AM for Index reorganize
Database maintenance
5. Enter username and password for the user that automatically executes the task and click Next.
Note! The user account used must be set up with a password in order for the scheduled task to work.
6. Then verify your entries and click Finish.
4.2.2 Database backup
To run backups when using SQL Server Express edition, please read the ReadMe.txt file in the
Utilities/Database maintenance for SQL Express folder on the ToolsNet 4000 installation CD. The SQL Server Express should be the default database instance, otherwise the scripts has to be edited.
1. Copy bat files to the
ToolsNet 4000\bindirectory (
C:\Program Files\Atlas Copco Tools AB\ToolsNet 4000\bin)
2. Modify path to where the backup file should be located in
Backup_AtlasCopco_ReportFramework_Metadata.bat
3. Modify path to where the backup file should be located in
Backup_AtlasCopco_ToolsNet_Database.bat
4. Create a Scheduled Task for
Backup_AtlasCopco_ReportFramework_Metadata.bat5. Create a Scheduled Task for
Backup_AtlasCopco_ToolsNet_Database.bat4.2.3 Delete maintenance on SQL Server 2005 Express
The database maintenance batch will delete old rows as specified in the batch. There are six different commandos to run in the batch file; two for deleting results (safety critical and non safety critical), two for graphs (safety critical and non safety critical), one for events and one for programs and units that no longer has any data connected to them.
These are the default values used for delete maintenance in the batch files:
• Events: @DaysToKeep = 100, @BulkSize = 5000
• Results: @DaysToKeep = 100, @BulkSize = 5000
• Graphs: @DaysToKeep = 100, @BulkSize = 5000
9836 4181 01 37 (86)
@BulkSize is the number of rows that are deleted for each transaction. This can be tuned for optimum performance by the database administrator.
4.2.4 Index rebuild and index reorganize
The bat file for reorganizing index doesn’t contain any settings, just schedule the jobs at an appropriate time. Follow the same steps as described for delete maintenance.
The bat file for delete maintenance also contains calls to Index rebuild after the delete maintenance is done.
4.3 Verifying database maintenance
When delete maintenance or index rebuild is run, all actions are logged in the database. This is regardless if have a SQL Server Enterprise/Standard Edition or SQL Server Express.
Database maintenance is logged into two tables in the database.
• MaintenanceLog
• MaintenanceResult
The information can be viewed in two separate logs in the ToolsNet 4000 web application.
Click Connections in the status bar, and then click DB Maintenance Log.
Accessing multiple databases
5 Accessing multiple databases
ToolsNet is able to present reports from different data sources in one single web interface.
This makes it possible to access:
• multiple production databases
• multiple archive databases.
• any other database in the customers’ environment, as long as a report database function and template is created for this database.
Each additional database is presented in a drop down list in the ToolsNet interface as soon as an additional database is specified. This enables the end user to select which database to use when running reports.
Database settings are configured in the file ToolsNetConnectionString.config. Edit the file in notepad or similar editor. Default file location and name is C:\Program Files\Atlas Copco Tools AB\ToolsNet 4000\bin\
ToolsNetConnectionString.config.
5.1.1 ToolsNet Archive database(s)
This instruction assumes that there is one default production database and one archive database called
AtlasCopco_ToolsNet_Archive
1. Find the xml element <ConnectionString key=”toolsnet”> in the file. This is the connection string to the production database.
2. Copy everyting from <ConnectionString key=”toolsnet”> to </ConnectionString>
and underneath so you get two identical entries in the file. The key tag value must be unique.
3. Change the key to a different name, ex. <ConnectionString key=”toolsnet_archive”>
4. Change the DisplayName to ToolsNet Archive. It is the display name that will be listed in the user interface for database selection.
5. Modify the ConnectionStringTemplate to point to the archive database
a. Edit Data Source=127.0.0.1, so that the IP address is correct for the archive database.
b. Modify Initial Catalog=AtlasCopco_ToolsNet_Archive , change
AtlasCopco_ToolsNet_Archive so that the database name is correct for the archive database.
6. Save the file.
7. Start or refresh the ToolsNet web GUI, it should now have a drop down list showing ToolsNet and ToolsNet Archive.
9836 4181 01 39 (86)
5.1.2 Databases other than ToolsNet
ToolsNet supports running reports on other databases than ToolsNet databases.
1. Copy a <ConnectionString> section like described for creating a connection to an archive database..
2. Change the DisplayName to show a name of the database. It is the display name that will be listed in the user interface for database selection.
3. Set the DataBaseType element to define what type of database and reports the connection string defines..
4. Modify the ConnectionStringTemplate to point to the database.
5. Set the Default user ID and Password.
6. Set the Report user ID and Password. The specified user must have execute permission to the report procedure(s) in the custom database.
7. Save the file.
8. Start or refresh the ToolsNet web GUI, it should now have a drop down list showing ToolsNet and
Custom Database
9. Each report template that should be used on this database must be edited so that ConnectionString type in the report template is the same as the Database Type value (default CustomDatabase).
Accessing multiple databases
5.1.3 Database connections for web applications -ToolsNetConfigurationString.config
Default path C:\program files\Atlas Copco Tools AB\bin
Description Tag Collection of database connections that can be reached
from the ToolsNet web GUI
<DataBaseConnectionStrings>
Unique key for each connection <ConnectionString key="set value here">
Show or hide the display name in the web gui. Value can be true (name is shown) or false (name is not shown)
<Visible>set value here</Visible>
Name to display in the web GUI user selection. <DisplayName>set value here</DisplayName>
Target type for the connection string.
Note that production database and archive database have the same type, because the database has the same objects and structure.
Each report (template) is tied to a database type, identified in the report template with ConnectionString type.
For Custom databases add any custom name that uniquely defines the type.
<DataBaseType>set the value here</DataBaseType>
Default user for report procedure execution. <DefaultUser name="DefaultUser">
Different users for the system <Users><User name="value">
Used values: Report user, ReportstructureUser, AdminUser Login credentials for each user.
Note! This file is stored on server side.
Viewing and Editing of these credentials require windows admin password to the server.
<ID>user name</ID>
<Password>password</Password>
9836 4181 01 41 (86)
6 Configuration of ToolsNet applications
Most of the aspects of the web applications are configured on the server in the web configuration files.
This chapter contains a description of the locations and settings that are available.
6.1 ToolsNet Web
This section describes the configuration file for ToolsNet Web. The configuration file can be modified by opening the file in a text editor. Only values highlighted in bold text in the example below must be changed. These values can only be changed by editing this configuration file and are not accessed through a settings dialog.
Default file location and name:
C:\Program Files\Atlas Copco Tools AB\ToolsNet 4000\ToolsNetWeb\web.config.
Description Tag
Physical path to the web application log file
IP address and port for data collection status reporting in status bar.
If the web server has a fixed IP address, change it here.
<appSettings>
<add
key="ToolsNet.DataCollectionServer.InformationService.IP"
value="127.0.0.1:89"/>
Physical path for the translation file containing translations for the texts that are shown in the web ToolsNet admin, if an error occurs during web startup, user gets a link option.
<appSettings>
<add key="ToolsNet.ErrorReport.EmailToAddress"
value="set value here" />
Default subject text in admin mail
<appSettings>
<add key="ToolsNet.ErrorReport.EmailSubject"
value="Error in ToolsNet" />
Configuration of ToolsNet applications
Description Tag
Default text in mail message
<appSettings>
<add key="ToolsNet.ErrorReport.EmailText" value="An error occured in ToolsNet, please check the server log files for details." />
Starting day of week (0=Sunday, 1= Monday)
<appSettings>
<add key="ToolsNet.StartingDayOfWeek" value="0" />
applicationSettings
Web address to the ToolsNet Web Service.
This is the interface to get information about the default or custom defined controller groupings.
If the web server has a fixed IP address, change it here.
Address to ToolsNet Web Service.
This is the interface to get information about the default or custom defined controller groupings.
If the web server has a fixed IP address, change it here.
Default file location and name:
C:\Program Files\Atlas Copco Tools AB\ToolsNet 4000\ToolsNetWebService\bin\framework.config
Description Tag
Physical path to reports base folder <SettingsType="xs:string"key="ReportBasePath"
name="Base path to report folder"value=”set value here”><Setting/>
Sub folder in reports base folder where generated reports are stored as xml files
<SettingsType="xs:string"
key="CreatedReportDir"name="Directory for created reports"value="set value
9836 4181 01 43 (86)
Description Tag here"><Setting/>
Generated reports are deleted after a specified number of hours. The value must be an integer (whole hours)
<SettingsType="xs:posint"
key="CreatedReportCleanUpInterval"
name="Interval in hours for deleting created reports "value="set value here"><Setting/>
Physical path to database connection string configuration file.
<ConnectionStringConfig path="set value here"/>
6.3 Report structure editor
Default file location and name:
C:\Program Files\Atlas Copco Tools AB\ToolsNet 4000\ToolsNetReportingStructureWeb\web.config
Description Tag Address to report structure interface. This address is
used to get the structure from a web service
(ToolsNetReportingStructureWebService). The web service connects to the database and returns structure information when a request is made.
<Setting
6.4 Report structure web service
Default file location and name:
C:\Program Files\Atlas Copco Tools AB\ToolsNet 4000\ToolsNetReportingStructureWebService\ Web.config
Description Tag Physical path to database connection string
configuration file. This is used by the web service to connect to the database and return the report structure to the report structure editor and ToolsNet web application.
<ConnectionProviderPathpath="set the value here"/>
6.5 ToolsNet Settings (server settings)
The ToolsNet settings page is a separate web interface where the server registry settings can be edited.
The page can be accessed from a browser on the server where the TN service is running. For security reasons it is not possible to run the web page from any other computer.
ToolsNet parameters settings can be reached in several ways:
• From Start – Programs – Atlas Copco Tools AB – ToolsNet 4000 – Configuration Tools – Data collection settings
Configuration of ToolsNet applications
• From the ToolsNet service manager – Data collection. Click the icon
• From a web browser on the server enter http://127.0.0.1:89
To view advanced settings, select the Show advanced options box and click Store settings. Basic mode will always be presented as the default option even if advanced option has been used previously. To save any changes made on ToolsNet Server Settings page, click Store.
Registry settings - general
Setting/Button Function Comment
Show advanced options Show/Hides advanced setting options. Check/Uncheck the box and press the button Store settings to show/hide advanced options.
License IP IP address for License server.
Lost tightening timer PF3000 Set up this value if PF has speed problems when communicating.
Default should be 0.
Use MAC address Prompts TNServer to use MAC address as identifier for PowerFocus units, instead of using the IP address of the unit.
Used for cases when NAT (Network address translation) devices are used in between PowerFocus unit and ToolsNet. Note that web reports will show MAC address instead of IP address if this option is used.
Database connectivity
Setting/Button Function Comment
Connected/Disconnected Shows current database connection status. Use F5 or refresh in the browser to ensure that the latest status is presented.
Queue path Enter path to the buffer files that store data during lost database connection.
SMTP host IP address of the mail server to which alert message is sent in case of database connection
SMTP host IP address of the mail server to which alert message is sent in case of database connection