Persistence

Persistence for PowerShell Universal.

PowerShell Universal stores job output and input, identities and app tokens within the database.

SQLite

When using SQLite, PowerShell Universal stores all data within a single file database local to the PowerShell Universal application. We recommend SQLite over LiteDB for new installations as it is more widely used and supported.

You can configure SQLite by updating the appsettings.json file.

 "Plugins": [
    "SQLite"
  ],
  "Data": {
    "ConnectionString": "Data Source=%ProgramData%\\UniversalAutomation\\psu.db",
  },

SQL

SQL support requires a license.

You can configure PowerShell Universal to store data within a Microsoft SQL Database. This allows you to scale out your database and PowerShell Universal instances. PowerShell Universal will automatically run jobs across the pool of agents. You can update the appsettings.json file for PowerShell Universal as follows to connect to a central SQL server.

  "Plugins": [
    "SQL"
  ],
  "Data": {
    "ConnectionString": "Server=(localdb)\\mssqllocaldb;Database=PSUv3;Integrated Security=true;",
  },

The PowerShell Universal instances will share a single job queue and only one instance will run a job either on the schedule, as a trigger, or manually. All data about jobs will be stored in the centralized database.

Azure SQL

We recommend using Azure Managed Identities when working with Azure SQL. An example connection string is shown below.

Server=tcp:psudb.database.windows.net,1433;Initial Catalog=psu;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity;

A walkthrough on how to use Managed Identity for Azure SQL can be found here.

Manual Schema Install\Update

In some environments, the PSU service may not have access to create schema in the database. In this scenario, you can manually install and update the schema by executing SQL files included with PowerShell Universal. Within the PowerShell Universal installation folder, you will find a SQL directory that includes two SQL files.

Run these files against your database prior to upgrading PowerShell Universal. You will need to stop PowerShell Universal while you are doing this. You can run them in any order. Both scripts are idempotent.

By default, PowerShell Universal will attempt to update the schema of the database. To completely disable this feature, you can set the RunMigrations setting to false in appsettings.json.

"Data": {
   "RunMigrations": false
}

Frequently Asked Questions

Database Account Privileges

What is the maximum permission for a database user account needed during installs or upgrades?

During the startup of the PowerShell Universal server, the database will be installed and upgraded, if required. If the database already exists, you will need db_owner in order to create and modify tables. If the database does not yet exist, you will need dbcreator .

What is the maximum permission needed once the database(s) has been created for ongoing daily activities?

PowerShell Universal performs limited database operations during daily activities. This consist of inserting, updating, deleting and viewing data in the tables created. You will only need the following roles: db_datawriter, db_datareader

Database & Operating System Install & Support

Which versions of SQL Server are supported? Is there a minimum version required?

PowerShell Universal supports Microsoft SQL Server 2016 and onwards.

Which database compatibility versions do you support?

PowerShell Universal supports require database compatbility version 130 of later.

Do any other SQL Server components need to be installed beside the Database Engine?

None

Which versions of Windows Server are supported?

Currently Supported versions by Microsoft - Windows Server 2016 and onwards. Currently, the extended end date for support for 2012 R2 from Microsoft is October 10, 2023, at which time we will drop support for the operating system.

Database Maintenance & Backup

How quickly does the data need to be recovered?

Data is primarily for reporting, aside from app tokens, so data doesn't need to be immediately recovered. Depending on the use of the app tokens, some systems may lose access to the server when the database is being recovered.

Database Design

How large will the database(s) be for the initial load?

The initial database does not include a large set of initial data and primarily will only take the space of the schema.

What is the estimated growth per month/year of each database?

The estimated growth depends on many factors that include the number of jobs run per month, amount of job log and pipeline output stored, and how aggressive data is groomed.

Database size rarely grows over 2 GB in size with the default settings. Extending groom settings to contain a longer history of jobs, running jobs more frequently and storing data like terminal history can increase the database size.

Does your application support use of SQL Server compression (Row or Page level)?

Yes. We use a standard database client and has no limitation on row and page level compression.

Database Usage

Is this a transactional (OLTP) or reporting (OLAP) database?

This is primarily a reporting database that includes information like job history, job output and app tokens.

What is the total number of user/device connections?

One connection pool per PSU server. 1024 connections maximum per server.

What is the maximum number of concurrent user/device connections?

One connection pool per PSU server. 1024 connections maximum per server.

PostgreSQL

You can enable PostgreSQL will the PostgreSQL plugin.

 "Plugins": [
    "PostgreSQL"
  ],
  "Data": {
    "ConnectionString": "Host=PGhostname; Database=PGdatabase; User Id=PGusername; Password=PGpassword!;Port=5432",
  },