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 are supported with the following SQL Server versions:

  • 2014
  • 2016
  • 2017

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

Datastore Runtime

The information in this section refers to prerequisites of MS SQL Server when used with Datastore Runtime.

Database information

Datastore Runtime Database settings must be as follows:

  • READ_COMMITTED_SNAPSHOT: ON
  • ALLOW_SNAPSHOT_ISOLATION: ON

Filegroup information

The following is a list with the filegroups requested by Datastore Runtime component and their minimum size.

  • 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 information

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.

Other components

The information in this section refers to prerequisites of MS SQL Server when used with other components than Datastore Runtime.

Filegroups Data file

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

IP_INDEX_WORK

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

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 both the Admin and the Functional user.

Related Links