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 truncate 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 2016(130). 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

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 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.

Tip

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"
  }

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. 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.

SQL Server Cluster Installations

We do not recommend 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. 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 graceful handling of transient cluster failures.