Installing the OctoSAM Inventory database¶

SQL Server requirements¶

We recommend that you use Windows Integrated Authentication for all connections to the database.

The import service service account needs to be in the ‘bulkadmin’ Server Role to bulk insert new data. For best efficiency the service account also needs ALTER permissions on the table SoftwarePackageUsageDetails to be able to truncate the table. If truncating is not possible, the service will fall back to delete operations - this usually results in increased processing time and resources required.

Creating the database¶

Create a new database using SQL Server provided tools. Choose locations for the database files according to your company standards. Set the database collation to Latin1_General_CI_AS. Compatibility Level SQL Server 2017(140). If you use the OctoSAM License Module, we recommend setting the recovery model to Full

Find the newest create_database_*.sql script in Support/Database/Scripts to create the database schema, then run all provided migration scripts up to the desired version.

Enabling the Service Broker for OctoSAM Monitor¶

To use the immediate update feature in OctoSAM Monitor, OctoSAM 1.10.1 and later require the Service Broker to be active.

Use the following statement to enable the service broker. Note that this will terminate all open transactions.

      alter database mydatabase set enable_broker with rollback immediate;

Migrating the database from a previous version¶

You need to run the provided migration scripts in exact order to update to the current version. Some migrations may require additional steps and scripts. See the release notes and the comments in the header of each migration script.

Backup your database

Always backup your database before running any migration scripts.

Configure OctoSAM server components¶

All OctoSAM server components read the global appsettings.json configuration file in a folder pointed to by the OCTOSAM_CONFIGURATION_FOLDER environment variable.

The .net connection string named OctoSAM is used by all server components.

  "ConnectionStrings": {
    "OctoSAM": "data source=localhost;initial catalog=Octopus;integrated security=SSPI"
  }

Insert or update initial database values¶

In addition to the database schema, the application relies on initial data for lookup tables, RBAC privileges etc. Initialize or update this data with the command:

octoutil dbinit

Tip

The import service also initializes and/or updates this data at startup. Use octoutil dbinit if you do not want to immediately start the import service after installation or update of the database schema.

Rebuilding database indexes and statistics¶

The OctoSAM services can optionally rebuild database indexes during housekeeping.

Info

This feature requires additional database permissions. Use only when your organization does not already have policies for scheduled rebuilding of indexes and statistics in place. Always consult with your DBA before activating this feature.

SQL Server Express Edition¶

We do not recommend the use of SQL Server Express Edition for productive installations. Its limits are usually too constraining. Experience has shown that the Express Edition can handle around 250 to 300 Machines with default data retention settings. You might be able to squeeze some more machines in by aggressively cutting the data history retention time via the housekeeping settings at the risk of losing functionality.

SQL Server cluster installations¶

We do not recommend the installation of the OctoSAM database on a SQL Server cluster. The OctoSAM database does not have a criticality that justifies the much higher cost of a professionally administrated cluster environment. Most data processing in OctoSAM is batch-oriented and can just restart after database recovery. In most cases, data can easily be recovered through re-import of scan files and re-scan of cloud applications or Active Directory.

OctoSAM software does not provide mechanisms for the graceful handling of transient cluster failures.