> ## Documentation Index
> Fetch the complete documentation index at: https://syteca.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Creating Databases Manually

> Pre-create the Syteca databases and a limited-privilege database user by hand, for MS SQL Server or PostgreSQL, before installing the Application Server.

<Warning>
  **NOT AVAILABLE IN SAAS.**
</Warning>

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](/docs/administration/deployment/database-management).

<Tabs>
  <Tab title="MS SQL Server">
    ### Create a SQL Server login

    <Steps>
      <Step title="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**.
      </Step>

      <Step title="Create the login">
        ```sql theme={"system"}
        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**).
      </Step>
    </Steps>

    ### Create the Activity database

    ```sql theme={"system"}
    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

    ```sql theme={"system"}
    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

    ```sql theme={"system"}
    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)

    ```sql theme={"system"}
    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](/docs/administration/deployment/install-application-server) for the full installer walkthrough — the databases you just created will be detected and reused.
  </Tab>

  <Tab title="PostgreSQL">
    ### Create a database user

    <Steps>
      <Step title="Open a query window">
        In pgAdmin, log in as `postgres`. In the **Browser** pane, expand **Databases**, right-click **postgres**, and select **Query Tool**.
      </Step>

      <Step title="Create the role">
        ```sql theme={"system"}
        CREATE ROLE username WITH
            LOGIN
            NOSUPERUSER
            NOCREATEDB
            NOCREATEROLE
            INHERIT
            NOREPLICATION
            CONNECTION LIMIT -1
            PASSWORD 'password';
        ```

        Replace `username` and `password`, then execute.
      </Step>
    </Steps>

    ### Create and configure the Activity database

    ```sql theme={"system"}
    CREATE DATABASE ekranactivitydb
        WITH
        OWNER = username
        ENCODING = 'UTF8'
        CONNECTION LIMIT = -1;
    ```

    Then, connected to `ekranactivitydb`:

    ```sql theme={"system"}
    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

    ```sql theme={"system"}
    CREATE DATABASE ekranubadatabase
        WITH
        OWNER = username
        ENCODING = 'UTF8'
        CONNECTION LIMIT = -1;
    ```

    Then, connected to `ekranubadatabase`:

    ```sql theme={"system"}
    GRANT ALL ON DATABASE ekranubadatabase TO username;
    ```

    ### Create and configure the Management database

    ```sql theme={"system"}
    CREATE DATABASE ekranmanagementdatabase
        WITH
        OWNER = username
        ENCODING = 'UTF8'
        CONNECTION LIMIT = -1;
    ```

    Then, connected to `ekranmanagementdatabase`:

    ```sql theme={"system"}
    GRANT ALL ON DATABASE ekranmanagementdatabase TO username;
    ```

    ### Create and configure the Archive database (optional)

    ```sql theme={"system"}
    CREATE DATABASE archivedb
        WITH
        OWNER = username
        ENCODING = 'UTF8'
        CONNECTION LIMIT = -1;
    ```

    Then, connected to `archivedb`:

    ```sql theme={"system"}
    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:

    ```sql theme={"system"}
    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`.

    <Note>
      **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:

      ```sql theme={"system"}
      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.
    </Note>

    ### 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](/docs/administration/deployment/install-application-server) for the full installer walkthrough — the databases you just created will be detected and reused.

    <Tip>
      For further performance tuning after installation, see [Configuring PostgreSQL for maximum performance](/docs/resources/troubleshooting/postgresql-performance-tuning).
    </Tip>
  </Tab>
</Tabs>

## Related

<CardGroup cols={2}>
  <Card title="Installing the Application Server" icon="server" href="/docs/administration/deployment/install-application-server">
    The full installer walkthrough these manually-created databases plug into.
  </Card>

  <Card title="PostgreSQL performance tuning" icon="gauge" href="/docs/resources/troubleshooting/postgresql-performance-tuning">
    Optimize PostgreSQL after installation.
  </Card>

  <Card title="Database parameters" icon="database" href="/docs/administration/database/database-parameters">
    Change database connection settings after installation.
  </Card>

  <Card title="Database server errors" icon="alert-triangle" href="/docs/resources/troubleshooting/database-server-errors">
    Common database connectivity issues and fixes.
  </Card>
</CardGroup>