Creating Databases Manually

NOT AVAILABLE IN SAAS.

Syteca Application Server normally creates its own databases during installation. Use this page instead when your organization requires databases and their users to be created manually by a DBA ahead of time — for example, to keep the Application Server installer from needing elevated database-creation privileges. Syteca internally consists of three databases: an Activity database, a UBA (user behavior analytics) database, and a Management database. Optionally, a fourth Archive database can also be created for the Archive and Cleanup feature.

MS SQL Server
PostgreSQL

Open a query window

In SQL Server Management Studio, log in as sa. In Object Explorer, expand System Databases, right-click master, and select New Query.

Create the login

CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Replace username and password with your own values, then execute (F5).

Create the Activity database

CREATE DATABASE EkranActivityDB
GO
ALTER DATABASE EkranActivityDB SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE EkranActivityDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE EkranActivityDB SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE EkranActivityDB SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE EkranActivityDB SET MULTI_USER
GO
USE EkranActivityDB
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

Create the UBA database

CREATE DATABASE EkranUbaDatabase;
GO
USE EkranUbaDatabase
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

Create the Management database

CREATE DATABASE EKRANManagementDatabase;
GO
USE EKRANManagementDatabase
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

Create the Archive database (optional)

CREATE DATABASE archiveDB
GO
ALTER DATABASE archiveDB SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE archiveDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE archiveDB SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE archiveDB SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE archiveDB SET MULTI_USER
GO
USE archiveDB
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE TO db_executor
GO
CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [username]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [username]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [username]
GO
ALTER ROLE [db_executor] ADD MEMBER [username]
GO

Install the Application Server against these databases

Run the Application Server installer as normal. On the Database Type page, select MS SQL Server, then on MS SQL Server Database Configuration, enter the login created above and the correct Server instance name. See Installing the Application Server for the full installer walkthrough — the databases you just created will be detected and reused.

Create a database user

Open a query window

In pgAdmin, log in as postgres. In the Browser pane, expand Databases, right-click postgres, and select Query Tool.

Create the role

CREATE ROLE username WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'password';

Replace username and password, then execute.

Create and configure the Activity database

CREATE DATABASE ekranactivitydb
    WITH
    OWNER = username
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Then, connected to ekranactivitydb:

GRANT ALL ON DATABASE ekranactivitydb TO username;

CREATE SCHEMA dbo
    AUTHORIZATION username;

GRANT ALL ON SCHEMA dbo TO username;

ALTER DATABASE ekranactivitydb
    SET search_path TO dbo;

CREATE EXTENSION pgcrypto SCHEMA dbo;

Create and configure the UBA database

CREATE DATABASE ekranubadatabase
    WITH
    OWNER = username
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Then, connected to ekranubadatabase:

GRANT ALL ON DATABASE ekranubadatabase TO username;

Create and configure the Management database

CREATE DATABASE ekranmanagementdatabase
    WITH
    OWNER = username
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Then, connected to ekranmanagementdatabase:

GRANT ALL ON DATABASE ekranmanagementdatabase TO username;

Create and configure the Archive database (optional)

CREATE DATABASE archivedb
    WITH
    OWNER = username
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Then, connected to archivedb:

GRANT ALL ON DATABASE archivedb TO username;

CREATE SCHEMA dbo
    AUTHORIZATION username;

GRANT ALL ON SCHEMA dbo TO username;

ALTER DATABASE archivedb
    SET search_path TO dbo;

CREATE EXTENSION pgcrypto SCHEMA dbo;

Grant extra privileges (Syteca 7.23 and later only)

Connected to ekranactivitydb, run this before installing Syteca 7.23 or higher, to allow the user to access the Management database as a foreign server:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS mngdbserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host '<postgres_server_hostname_or_ip>', dbname 'ekranmanagementdatabase', port '5432', use_remote_estimate 'true');

GRANT USAGE ON FOREIGN SERVER mngdbserver TO username;

ALTER SERVER mngdbserver OWNER TO username;

Replace <postgres_server_hostname_or_ip> with the PostgreSQL server hosting ekranmanagementdatabase.

Updating an existing Syteca deployment to 7.23 or later (rather than installing fresh) uses a different script — run this instead, connected to ekranactivitydb, as a superuser, before running the update:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS mngdbserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host '<postgres_server_hostname_or_IP_address>', dbname '<Syteca_management_database_name>', port '5432', use_remote_estimate 'true');

GRANT USAGE ON FOREIGN SERVER mngdbserver TO <nosuperuser_name>;

ALTER SERVER mngdbserver OWNER TO <nosuperuser_name>;

Replace <nosuperuser_name> with the existing PostgreSQL user with NOSUPERUSER privileges. Without this, that user sees a blocking pop-up when the Application Server installer reaches version 7.23 or later.

Install the Application Server against these databases

Run the Application Server installer as normal. On the Database Type page, select PostgreSQL, then on PostgreSQL Server Database Configuration, enter the user created above and the correct Server instance name. See Installing the Application Server for the full installer walkthrough — the databases you just created will be detected and reused.

For further performance tuning after installation, see Configuring PostgreSQL for maximum performance.

Installing the Application Server

The full installer walkthrough these manually-created databases plug into.

PostgreSQL performance tuning

Optimize PostgreSQL after installation.

Database parameters

Change database connection settings after installation.

Database server errors

Common database connectivity issues and fixes.

Overview

Installation

Scaling & High Availability

Database

Users and Permissions

Clients

Access

Management Tool Settings

Client Parameters

Licensing

Multi-Tenant

Dashboards

Audit Log

Security

Creating Databases Manually

Create the Activity database

Create the UBA database

Create the Management database

Create the Archive database (optional)

Install the Application Server against these databases

Create a database user

Create and configure the Activity database

Create and configure the UBA database

Create and configure the Management database

Create and configure the Archive database (optional)

Grant extra privileges (Syteca 7.23 and later only)

Install the Application Server against these databases

Installing the Application Server

PostgreSQL performance tuning

Database parameters

Database server errors

​Create a SQL Server login

​Create the Activity database

​Create the UBA database

​Create the Management database

​Create the Archive database (optional)

​Install the Application Server against these databases

​Create a database user

​Create and configure the Activity database

​Create and configure the UBA database

​Create and configure the Management database

​Create and configure the Archive database (optional)

​Grant extra privileges (Syteca 7.23 and later only)

​Install the Application Server against these databases

​Related

Installing the Application Server

PostgreSQL performance tuning

Database parameters

Database server errors

Create a SQL Server login

Create the Activity database

Create the UBA database

Create the Management database

Create the Archive database (optional)

Install the Application Server against these databases

Create a database user

Create and configure the Activity database

Create and configure the UBA database

Create and configure the Management database

Create and configure the Archive database (optional)

Grant extra privileges (Syteca 7.23 and later only)

Install the Application Server against these databases

Related