MS SQL Server

You can personalize your databases, filegroups, schemas, and login names during the component installation.

To install the product correctly, you must have previously created databases, filegroups, schemas, users, and logins as specified.

Each component must have its own Database(s) and login(s).

Supported versions and editions

Products in the AI Suite (with the exception of Composer) are supported with the following SQL Server versions:

  • 2012
  • 2014
  • 2016

Supported editions are as follows:

  • Enterprise
  • Business Intelligence
  • Standard

Character limitations for SQL server

In the names of filegroups, schemas, users, databases, or logins specified for SQL server, the following restrictions apply:

  • First character: A..Z a..z _
    unaccentuated letters, underscore
  • Other characters: A..Z a..z _ 0..9
    digits, unaccentuated letters, underscore
  • Names should not be more than 100 characters in length

Database information

If you use SQL Server version 2016, you must add an option in the database to activate compatibility level with SQL Server 2014:

alter database XXXXX set compatibility_level = 120

This option concerns all databases except Datastore Runtime databases.

Datastore Runtime Database settings must be as follows:

  • READ_COMMITTED_SNAPSHOT: ON
  • ALLOW_SNAPSHOT_ISOLATION: ON

Filegroups Data file

Below are the minimum disk space needed and the growth potential of the Data file for each filegroup.

InterPlay

IP_INDEX_WORK

  • At least one data file
  • 265 megabytes minimum disk space
  • 10% growth potential

IP_LOB_WORK

  • At least one data file
  • 265 megabytes minimum disk space
  • 10% growth potential

Datastore Runtime

  • META - Primary filegroup - 1 GB minimum size
  • MAIN_DATA - 1 GB minimum size
  • MAIN_INDEX - 1 GB minimum size
  • MAIN_LOB - 1 GB minimum size
  • CACHE_DATA - 1 GB minimum size
  • CACHE_INDEX - 1 GB minimum size
  • CACHE_LOB - 1 GB minimum size

Schema information

The following is a list with the schemas requested by Datastore Runtime component.

  • SCH_MAIN
  • SCH_CACHE
  • SCH_VIEW
  • SCH_EXTENSION

User rights

Database users must have the following roles:

  • db_datareader
  • db_datawriter
  • db_ddladmin
Note   The execute permission must be granted to the database user.
Note   Use dbo schema as default when creating the database users.

If the configuration for the two users (Admin and Functional) is used the roles must be distributed as follows:

Admin

  • db_datareader
  • db_datawriter
  • db_ddladmin

Functional

  • db_datareader
  • db_datawriter
  • Note: The execute permission must be granted to the Functional user.

The following is a list with the users requested by Database Runtime component, their permissions and default schema

USR_MAIN

  • Permissions on DSRUNTIME database: CONNECT, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  • Permissions on SCH_MAIN schema: ALTER AUTHORIZATION
  • Default schema: SCH_MAIN
  • System procedures:
    • xp_cmdshell:for executing the Build Copy Program (BCP) command by the Proxy User.
    • OLE Automation Procedures for managing files:
      • sp_OACreate
      • sp_OAMethod
      • sp_OADestroy
  • To use the xp_cmdshell and the OLE Automation Procedures, activitate the following commands:
    • sp_configure 'show advanced options', 1
    • reconfigure
    • sp_configure 'xp_cmdshell', 1
    • sp_configure 'Ole Automation Procedures', 1
    • reconfigure
    • sp_configure 'show advanced options', 0
    • reconfigure

For details, see Enable the partition lifecycle functionality

USR_CACHE

  • Permissions on DSRUNTIME database: CONNECT, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  • Permissions on SCH_CACHE schema: ALTER AUTHORIZATION
  • Default schema: SCH_CACHE

USR_VIEW

  • Permissions on DSRUNTIME database: CONNECT, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  • Permissions on SCH_VIEW schema: ALTER AUTHORIZATION
  • Default schema: SCH_VIEW

USR_EXTENSION

  • Permissions on DSRUNTIME database: CONNECT, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  • Permissions on SCH_EXTENSION schema: ALTER AUTHORIZATION
  • Default schema: SCH_EXTENSION

Logins

The following is a list with the logins requested by Datastore Runtime component and associated user:

  • DSMAIN associated with USR_MAIN user
  • DSCACHE associated with USR_CACHE user
  • DSVIEW associated with USR_VIEW user
  • DSEXTENSION associated with USR_EXTENSION user
Note   the SCH_EXTENSION schema, USR_EXTENSION user and DSEXTENSION login are optional, because they are only used if certain SQL extensions have to be put in place. They are not necessary at installation time, but required when SQL extensions are setting up.

Related Links