Frends Agents will have always have SQLite installed with them. It is used to store, for example, configuration data. In addition to that Frends Agents may use an external database from SQL database.
In general, the Agent Store databases are pretty light, but shared state usage, schedules and file Triggers use the database. So, excessive usage of those increases the load to the database.
That database, or the Agent Store, is used to provide HA capabilities and to store log messages before they are sent to a Core on Legacy Agents. You may also use an SQL cluster for an Agent Store so that the database won't become a point of failure.
Overall picture of what gets stored and where in whether of you have an legacy or cross-platform agent and whether you have an external database or not is
Cross platform without a configured database
-stores all data in SQLiteCross platform with a configured database
-stores data needed for triggers and shared state task in a real database that all agents in the agent group connect to
-stores rest of the information in SQLite files (processes, environment variables etc) that are agent specificLegacy agent without configured database
-stores most of the data in SQL LocalDB
-stores logs that have not been sent in SQLite
-stores trigger initialization errors in SQLiteLegacy agent with configured database
-stores most of the data in SQL server that all agents in the agent group connect to
-stores logs that have not been sent in SQLite
-stores trigger initialization errors in SQLite
Prerequisites
Agent store needs Azure SQL Database or SQL Server 2016 or newer.
Cross Platform Agent Store initializer
In Frends 5.5. and earlier, the DatabaseInitializer.exe was available only from the Kubernetes installation package. However, it can be used to setup an Agent Store for any Agent types. Frends 5.6 will include it for typical Cross-platform installers as well.
The DatabaseInitializer.exe needs access in the database to:
Create a database
Create a login
Create stored procedures
Alter tables
Create backup jobs
Run backup jobs
It is usually best to grant DB Admin for it. If the whole Agent Group has those rights the initializer will work out of the box. Otherwise, you need to give a sufficient SQL connection string for it.
Usually running the DatabaseInitializer.exe is a straightforward process, but you can alter its behavior with different options on the command line.
Usually, you need to permit DatabaseInitializer.exe in the connection string given via --connectionstring parameter. If the account that you use to run the exe has e.g. Admin rights you can use Integrated Security=True in the connection string.
You usually also need to tell the user name that the agent uses via parameter --sqlUserName. If that user does not use Integrated Security please also give a password with --sqlUserPassword. This is the same user that is given in Agnet Group setting.
You can run .\Frends.DatabaseInitializer.exe -? to see help for other parameters
Frends.DatabaseInitializer 2.0.0.0
Copyright c 2022. All rights reserved.
ERROR(S):
Option '?' is unknown.
Required option 'c, connectionstring' is missing.
USAGE:
Deploy local agent database for a user:
Frends.DatabaseInitializer --connectionstring "Data Source=(local);Initial Catalog=FRENDS;Integrated Security=True"
--migrationNamespace Frends.DatabaseInitializer.AgentStore --sqlUserName HFWS460\FrendsUser
Upgrade an existing Configuration store database to Azure:
Frends.DatabaseInitializer --assemblyFile .\Frends.DatabaseMigrations.dll --connectionstring
"Server=tcp:[serverName].database.windows.net;Database=myDataBase;User
ID=[LoginForDb]@[serverName];Password=myPassword;Trusted_Connection=False;Encrypt=True;" --doNotCreate
--migrationNamespace Frends.DatabaseMigrations.ConfigurationStore
-c, --connectionstring Required. Connection string to the FRENDS database, e.g. 'Data
Source=(local);Initial Catalog=FRENDS;Integrated Security=True'
-l, --logConnectionstring When migrating configuration store to 5.4, some data is migrated from the
logStore to the configuration store. In that case this parameter is needed.
-u, --sqlUserName Username of the SQL user, e.g. 'DOMAIN\FrendsUser'
-p, --sqlUserPassword Password of the SQL user when using SQL logins
-m, --giveUserTableManagementRights (Default: false) Should the SQL user be allowed to manage the tables, used
e.g. for reorganizing indexes
-a, --assemblyFile The assembly with the fluent migrations to run. If not given, the internal
agent database migrations will be run.
-n, --migrationNamespace (Default: Frends.DatabaseInitializer.AgentStore) The namespace from which to
run the migrations. If not given, the Agent store migrations will be run.
--scriptMigrationNamespace The namespace from which to run embedded Entity framework SQL migration
scripts. If not given, no SQL script migrations will be run.
--doNotCreate (Default: false) Switch for disabling the creation of the database and user
--doNotMigrate (Default: false) Switch for disabling the database migration
--doNotEnableSnapshotIsolation (Default: false) Switch for disabling the default enabling of snapshot
isolation (it is e.g. not supported for Azure SQL)
-t, --timeout (Default: 240) The command timeout (in seconds) used by the SQL commands,
default value 240
--collation (Default: Latin1_General_CI_AS) The collation of the database to create,
default value Latin1_General_CI_AS
--showSql (Default: false) Outputs the executed SQL
--doNotCreateBackupJobs (Default: false) If flag is included backup jobs for the sql server will not
be created
--doNotSetSimpleBackupRecoveryModel (Default: false) If flag is included recovery mode will not be changed to
simple
--backupRetentionMonths (Default: 2) Define retention months for backup data, default is 2 months
--help Display this help screen.
--version Display version information.
Legacy Agent Store initializer
A Legacy Agent installer for an Agent store is built into an Agent installer.
By default, being in the User group for the Agent user account should be enough, the installer tries to add it to the Performance Log Users which is needed for the Performance Counters to work.
You can give the following options on the command line to alter the Agent Store's options.
DONOTCREATEDB = Do not create DB, pass --doNotCreate switch to database initializer. It won't try to create the database and user (required for Azure or stricter DBs where the customer creates the DB and user beforehand). Migrations are run.
DONOTMIGRATEDB = Do not Migrate DB, pass --donotmigrate switch to db initializer. It won't run any migrations
BACKUPRETENTIONMONTHS = How many months should the backup retention to be set for the backup jobs, pass the --backupRetentionMonths switch to db initializer.
DONOTCREATEBACKUPJOBS = Do not create Backup jobs, pass the --donotcreatebackupjobs switch to the db initializer. Skips creating backup jobs. This is only done for new databases created by the initializer.
NODBINIT = Do not run the database initializer, skips running the db initializer altogether.
Due to technical limitations, a Legacy Agent cannot work without an external database and if it is not provided the Agent installer will install a LocalDB on the same machine. That LocalDB is restricted to be used only with one Agent. Thus, you cannot install multiple Legacy Agents on the same machine without an external database. Cross-Platform Agents don't have this limitation.