Documentation PI Nexus+ Documentation

PI Nexus+ / Installation Guide

Prepare SQL Server

This chapter decides who creates the PI Nexus+ database and makes sure that person has the SQL Server rights for it.

Overview

This chapter decides who creates the PI Nexus+ database and makes sure that person has the SQL Server rights for it.

Decide on the SQL Server instance and the database name. The recommended name is PINexus. The database is created by one of two paths:

PathUse it whenWho needs server-level SQL rights
Database Setup onlyThe administrator who installs PI Nexus+ may create databases and loginsThat administrator, once
SQL administrator prepares, then Database SetupA DBA team manages SQL Server and the PI administrator has no server-level rightsThe SQL administrator, once

In both paths the PI Nexus+ service account ends up as db_owner of the PI Nexus+ database, and nothing more. Later upgrades apply schema changes as the service account, so they need no SQL administrator.

Database Setup only

The account that runs Database Setup after the installer must be able to connect to SQL Server, create a database, create a Windows login, create a database user and grant database roles. sysadmin, or dbcreator together with securityadmin, is enough. Usually this is the administrator who runs the installer. Continue with Run the Installer.

SQL administrator prepares the database

The SQL administrator runs Prepare-Database.sql once, in SQL Server Management Studio, from any computer that reaches the instance. The script is in C:\Program Files\Software Athlete\PI Nexus+\Admin after installation; ask Software Athlete support for a copy if the DBA runs it before PI Nexus+ is installed.

  1. Open Prepare-Database.sql and set the values at the top: @DatabaseName, @ServiceAccount, and optionally @SecondServiceAccount (a split deployment with two accounts) and @SetupAdministrator (the account or group that will run Database Setup).
  2. Run the whole script.
  3. Check the messages. The script creates the database, turns on snapshot isolation, creates the logins and database users, and adds them to db_owner. It skips every step that is already done.
  4. If the script stops with an error, correct it and run the script again.

After that, db_owner on the PI Nexus+ database is enough for the PI administrator to run Database Setup.

Note: On an existing database that does not use snapshot isolation yet, the script ends open transactions in that database. Run it with PI Nexus+ stopped.

Optional: server memory figures

For server-wide memory figures on Admin > Support > Scale, grant the service account VIEW SERVER PERFORMANCE STATE (SQL Server 2022 and later; VIEW SERVER STATE on earlier versions). Without it, PI Nexus+ works and says that these figures are unavailable.

Reference: SQL Server Rights and Sizing