2. Configure Database

Note: Please note: If your infrastructure was automatically provisioned using the ARM templates, you can skip this step in the install.

This step entails creating the necessary databases that will be used by the AI Fabric installer. Two options are available, creating multiple databases or creating one database with multiple schemas.

Important:

• This step creates a database and a login/user. To complete this step you need credentials that allow for these actions. In SQL server, this is the dbcreator role and the ddladmin role respectively.
• The same AI Fabric databases (the databases created by this step) cannot be used in multiple AI Fabric instances.

Configure

• Open Powershell in Administrator role.
• Download the script here, for example using Powershell:

  wget https://raw.githubusercontent.com/UiPath/ai-customer-scripts/master/database/createDatabases.ps1 -outfile "createDatabases.ps1"

• You can familiarize yourself with the script options by entering:

  get-help .\)\)createDatabases.ps1 -full

• The default parameters will work for most scenarios:

  <h1>If SQL Server can be accessed through Windows Authentication then:
  ./createDatabases.ps1 -sqlinstance "DESKTOP-LOUPTI1\)\)SQLEXPRESS" -windowsAuthentication "Y"</h1>

The parameter sqlinstance is just the SQL Server name. This is exactly what you enter in the Server name field when you are using SQL Server Management Studio. For example:

By default we are using multiple databases. If you want to use one database with multiple schemas you can add flag singleDatabase as Y as below:

<h1>If SQL Server can be accessed through Windows Authentication then:
./createDatabases.ps1 -sqlinstance "DESKTOP-LOUPTI1\)\)SQLEXPRESS" -windowsAuthentication "Y" -singleDatabase "Y"</h1>

• The script will output login and passwords to manage the created database(s).

  Please keep this login and password handy as they will be necessary when filling out the configuration screen in step 4. Run the AI Fabric Application Installer. Also in same step you will need to say if you used multiple or single database(s), make sure to select right one or preflight checks will fail.

  Important:

  • Make sure that SQL Server Authentication mode is enabled.
  • Make sure the linux machine provisioned in step 1 has been added to the approved IP list of SQL Server.
  • Make sure that in firewall rules allow for inbound connections to port 1433 (the default port for SQL server)

Manual configuration

Multiple databases

The Databases and Roles can also be created using the following manual script. Modify the password field for the new login aifadmin but do not add single or double-quotes for the password:

--Create Databases:
create database ai_deployer;
create database ai_trainer;
create database ai_helper;
create database ai_pkgmanager;
create database ai_appmanager;
USE [master]
GO
CREATE LOGIN [aifadmin] WITH PASSWORD=N'Admin@123!'  -- (Change the Password as per your requirement )
CREATE USER [aifadmin] FOR LOGIN [aifadmin]
GO
-- create the user on the target database i.e ai_deployer for the login
USE [ai_deployer]
GO
CREATE USER [aifadmin] FOR LOGIN [aifadmin]
GO
-- add the user to the desired role
USE [ai_deployer]
GO
ALTER ROLE [db_owner] ADD MEMBER [aifadmin]
GO
-- create the user on the target database i.e ai_helper for the login
USE [ai_helper]
GO
CREATE USER [aifadmin] FOR LOGIN [aifadmin]
GO
-- add the user to the desired role
USE [ai_helper]
GO
ALTER ROLE [db_owner] ADD MEMBER [aifadmin]
GO
-- create the user on the target database i.e ai_trainer for the login
USE [ai_trainer]
GO
CREATE USER [aifadmin] FOR LOGIN [aifadmin]
GO
-- add the user to the desired role
USE [ai_trainer]
GO
ALTER ROLE [db_owner] ADD MEMBER [aifadmin]
GO
-- create the user on the target database i.e ai_pkgmanager for the login
USE [ai_pkgmanager]
GO
CREATE USER [aifadmin] FOR LOGIN [aifadmin]
GO
-- add the user to the desired role
USE [ai_pkgmanager]
GO
ALTER ROLE [db_owner] ADD MEMBER [aifadmin]
GO
-- create the user on the target database i.e ai_appmanager for the login
USE [ai_appmanager]
GO
CREATE USER [aifadmin] FOR LOGIN [aifadmin]
GO
-- add the user to the desired role
USE [ai_appmanager]
GO
ALTER ROLE [db_owner] ADD MEMBER [aifadmin]
GO

Single database

The Database and Roles can also be created using the following manual script. Modify the password field but keep the same for all users and do not add single or double-quotes. You can also change username but you need to keep same schem having _schemaName at the end as below:

//Create Database
create database aifabric
//Create 5 logins
USE [master]
GO
CREATE LOGIN [aifadmin_helper] WITH PASSWORD=N'Admin@123!'  -- (Change the Password as per your requirement )
CREATE LOGIN [aifadmin_pkgmanager] WITH PASSWORD=N'Admin@123!'
CREATE LOGIN [aifadmin_deployer] WITH PASSWORD=N'Admin@123!'
CREATE LOGIN [aifadmin_trainer] WITH PASSWORD=N'Admin@123!'
CREATE LOGIN [aifadmin_appmanager] WITH PASSWORD=N'Admin@123!'
GO
//Map login to user and associate default schema with the user and grant db_ddladmin role to the user 
USE [aifabric]
GO
CREATE USER [aifadmin_helper]  FOR LOGIN  [aifadmin_helper] WITH DEFAULT_SCHEMA = ai_helper
GO
CREATE SCHEMA ai_helper AUTHORIZATION [aifadmin_helper]
GO
EXEC sp_addrolemember 'db_ddladmin', 'aifadmin_helper';
GO
USE [aifabric]
GO
CREATE USER [aifadmin_pkgmanager] FOR LOGIN  [aifadmin_pkgmanager] WITH DEFAULT_SCHEMA = ai_pkgmanager
GO
CREATE SCHEMA ai_pkgmanager AUTHORIZATION [aifadmin_pkgmanager]
GO
EXEC sp_addrolemember 'db_ddladmin', 'aifadmin_pkgmanager';
GO
USE [aifabric]
GO
CREATE USER [aifadmin_deployer]   FOR LOGIN  [aifadmin_deployer] WITH DEFAULT_SCHEMA = ai_deployer
GO
CREATE SCHEMA ai_deployer AUTHORIZATION [aifadmin_deployer]
GO
EXEC sp_addrolemember 'db_ddladmin', 'aifadmin_deployer';
GO
USE [aifabric]
GO
CREATE USER [aifadmin_trainer]    FOR LOGIN  [aifadmin_trainer] WITH DEFAULT_SCHEMA = ai_trainer
GO
CREATE SCHEMA ai_trainer AUTHORIZATION [aifadmin_trainer]
GO
EXEC sp_addrolemember 'db_ddladmin', 'aifadmin_trainer';
GO
USE [aifabric]
GO
CREATE USER [aifadmin_appmanager] FOR LOGIN  [aifadmin_appmanager] WITH DEFAULT_SCHEMA = ai_appmanager
GO
CREATE SCHEMA ai_appmanager AUTHORIZATION [aifadmin_appmanager]
GO
EXEC sp_addrolemember 'db_ddladmin', 'aifadmin_appmanager';
GO