automation-suite
2023.4
false
UiPath logo, featuring letters U and I in white
Automation Suite on Linux Installation Guide
Last updated Nov 21, 2024

Configuring Microsoft SQL Server

Databases

Note: Unless otherwise specified in the dedicated requirements sections, these requirements are applicable to all Automation Suite products.

Each product in Automation Suite requires its own SQL database with corresponding specifications.

By default, the installer creates all the databases for you during the installation process. You must provide the necessary permissions for the installer to do so on your SQL server.

Alternatively, you can create your own databases. Refer to the following table for the names you need to use.

If you choose to bring your own databases for a new Automation Suite installation, we strongly recommend setting up new databases rather than using existing ones. This precaution is necessary to prevent any conflicts with the operation of Automation Suite that might occur due to leftover metadata from old databases.

The following table lists out the database names and their corresponding format.

Product / service

Default database name

Connection string format

Shared Platform Capabilities

AutomationSuite_Platform

Orchestrator

AutomationSuite_Orchestrator

Action Center

AutomationSuite_Orchestrator

Automation Suite Robots

AutomationSuite_Orchestrator

Automation Hub

AutomationSuite_Automation_Hub

Data Service

AutomationSuite_DataService

Insights

AutomationSuite_Insights

Test Manager

AutomationSuite_Test_Manager

Task Mining

AutomationSuite_Task_Mining

AI Center

AutomationSuite_AICenter

Apps

AutomationSuite_Apps

1

ODBC

Document Understanding

AutomationSuite_DU_Datamanager

Process Mining

AutomationSuite_ProcessMining_Metadata

AutomationSuite_ProcessMining_Warehouse2
AutomationSuite_Airflow3
1 The password for the Apps database must not contain #{.
2AutomationSuite_ProcessMining_Warehouse uses both ADO.NET and PYODBC formats.
3 AutomationSuite_Airflow uses PYODBC connection string format. AutomationSuite_Airflow must have READ_COMMITTED_SNAPSHOT enabled when creating databases manually. The connection to the AutomationSuite_Airflow database can not begin with a { character.
Important:

If you manually set the connection strings in the configuration file, you can escape SQL, JDBC, or ODBC passwords as follows:

  • for SQL: add ' at the beginning and end of the password, and double any other '.
  • for JDBC/ODBC: add { at the beginning of the password and } at the end, and double any other }.
Note:

You could use the databases in an elastic pool for better cost-effectiveness.

SQL collation requirements

Automation Suite supports SQL collation set to SQL_Latin1_General_CP1_CI_AS at both the server and database level. We strongly recommend this particular setup for optimum performance and stability.

While you have the flexibility to use a collation of your choice, be aware that untested configurations can potentially lead to unexpected issues.

Important:

We do not recommend the use of Binary SQL or any collations that are case sensitive, as they cause known issues while installing Automation Suite.

Permissions

If you want the installer to create a database on the provided SQL server, make sure to grant at least the dbcreator role to the SQL account used to connect to the database.
If you want to create your own databases for all the products, you must grant the SQL account the db_owner and db_securityadmin roles for all Automation Suite databases.
If security restrictions do not allow the use of db_owner, then the SQL account should have the following roles and permissions on all databases (the only exception is Insights which still requires db_owner).
  • db_securityadmin
  • db_ddladmin
  • db_datawriter
  • db_datareader
  • EXECUTE permission on dbo schema
Note: We only use the db_securityadmin and db_ddladmin roles during installation or if the databases are reprovisioned, so you may revoke these permission afterwards.

SQL requirements for Full-Text Search

Document Understanding requires the SQL Server to have the Full-Text Search component of the SQL Server Database Engine, which is not always installed by default.

If you did not select Full-Text Search when you installed SQL Server, run SQL Server Setup again to add it before configuring Document Understanding.

For more info, check this guide on how to add/install features on SQL server. You can also check here all the components that can be added to the SQL Server, which includes also Full-Text Search.

SQL requirements for Insights

Insights requires SQL Server 2019 or 2022, including support for columnstore index and .json functions.

For Azure SQL, ensure the database is S3 service objective or above.

Make sure the compatibility level for Insights database is set to 130 or higher. In most cases, the default settings meet this requirement. For more info, refer to View or Change the Compatibility level of a Database - SQL Server.

The installation validates both conditions and alerts you if minimum requirements are not met.

SQL requirements for Data Service

Data Service requires SQL Server version 2016 or higher.

If you create your own database using SQL Server version 2019 or higher, make sure to grant the following Data Service permissions to your users: 

GRANT ALTER ANY SENSITIVITY CLASSIFICATION TO {userName};
GRANT VIEW ANY SENSITIVITY CLASSIFICATION TO {userName};GRANT ALTER ANY SENSITIVITY CLASSIFICATION TO {userName};
GRANT VIEW ANY SENSITIVITY CLASSIFICATION TO {userName};

SQL requirements for Process Mining

Process Mining on Automation Suite requires a separate Microsoft SQL Server for the AutomationSuite_ProcessMining_Warehouse for data storage for Process Mining process apps.
Important:
You must use the default server port 1433 for the AutomationSuite_ProcessMining_Warehouse databases. Non-standard SQL server ports are not supported.
Below is an overview of hardware requirements and recommendations for setting up a Microsoft SQL Server database machine for AutomationSuite_ProcessMining_Warehouse.
Attention:

When setting up Microsoft SQL Server make sure that the timezone of the SQL Server machine where the Airflow database and a dedicated Process Mining database are installed, is set to UTC.

To calculate the hardware requirements, you need to have an indication of:

  • the number of (million) events in your process.

  • the number of case and event fields in your output data.

    Note: In a development environment, for performance reasons, it is recommended to work on a small development dataset with a limited number of records.

You can use the Capacity calculator to determine the hardware requirements for setting up a dedicated Microsoft SQL Server machine for Process Mining. When you add Process Mining to the Product section, the minimum requirements for 1 Dedicated SQL Server are displayed.

The SQL user used in the connection strings must have the db_securityadmin database-level role both during and post-installation to enable per app security on the Process Mining data warehouse SQL Server. For details, see the official Microsoft documentation on Database-level roles.

SQL requirements for AI Center

Requirements for AI Center connected to an external Orchestrator

If you install an AI Center that connects to an external Orchestrator, you must ensure you create a new database named AutomationSuite_Platform for AI Events and AI Storage services.

Requirements for AI Center installed on a FIPS 140-2-enabled machine

To install AI Center on a FIPS 140-2-enabled machine, take the following steps:

  1. Before starting the Automation Suite installation, take the following steps:

    1. Enable FIPS 140-2 on the machine on which you plan to install Microsoft Server by following the Microsoft instructions.

    2. Install Microsoft SQL Server on the FIPS 140-2-enabled machine.

    3. Get the Microsoft SQL Server certificate by running the following command from the SQL Server or any server that can connect to the SQL server with the configured SQL host name: 

      nmap -sV -p <port> -vv --script ssl-cert domainnmap -sV -p <port> -vv --script ssl-cert domain
  2. During the Automation Suite installation, take the following steps:
    1. Append the following values to the AI Center sql_connection_string_template_jdbc connection string in the cluster_config.json file: encrypt=true;trustServerCertificate=false;fips=true;.

      Example:

      jdbc:sqlserver://sfdev1804627-c83f074b-sql.database.windows.net:1433;database=DB_NAME_PLACEHOLDER;user=testadmin;password=***;encrypt=true;trustServerCertificate=false;fips=true;Connection Timeout=30;hostNameInCertificate=sfdev1804627-c83f074b-sql.database.windows.net"jdbc:sqlserver://sfdev1804627-c83f074b-sql.database.windows.net:1433;database=DB_NAME_PLACEHOLDER;user=testadmin;password=***;encrypt=true;trustServerCertificate=false;fips=true;Connection Timeout=30;hostNameInCertificate=sfdev1804627-c83f074b-sql.database.windows.net"

      For details on database configuration, see Advanced installation experience and Updating the SQL database.

    2. Add the exported cert from step 1.c. to the trust store of the host machine. For details see Updating the CA Certificates.

SQL requirements for HA

For a high-availability configuration of your SQL Server, select an Always On availability group. For more info, refer to Overview of Always On availability groups.

SQL requirements for Orchestrator

Important: To increase performance, avoid deadlocks, and prevent dirty reads in SQL, make sure that the READ_COMMITTED_SNAPSHOT option is set to ON.
Use this query to check if READ_COMMITTED_SNAPSHOT is enabled or disabled: 
SELECT is_read_committed_snapshot_on FROM sys.databases
   WHERE name= 'UiPath'SELECT is_read_committed_snapshot_on FROM sys.databases
   WHERE name= 'UiPath'
If it is enabled, the returned value will be 1.
If it is disabled, the returned value will be 0. Use this query to enable it: 
ALTER DATABASE UiPath
SET READ_COMMITTED_SNAPSHOT ONALTER DATABASE UiPath
SET READ_COMMITTED_SNAPSHOT ON
If you use different databases for Test Automation and/or Update Server, READ_COMMITTED_SNAPSHOT must be enabled on those as well.

Was this page helpful?

Support and Services
Get The Help You Need
UiPath Academy
Learning RPA - Automation Courses
UiPath Forum
UiPath Community Forum
Uipath Logo White
Trust and Security
Terms of Use
Privacy Policy
Cookies Policy
© 2005-2024 UiPath. All rights reserved.