Install Sage X3 V12 using Amazon Relational Database Service (RDS) SQL Server as your Database

In this discussion, I am going to show you how you can install Sage X3 V12 using Amazon Relational Database Service (RDS) SQL Server as your Database. I am assuming that you are using a single server to install the rest of X3 components on and have already installed the following components on it:

ADX Admin Component
Apache 2.4
Application Server
Main Runtime Server
Print Server
Console
SQL Server Management Studio 18.x
As SSMS 18.x does not embed SQLCMD and BCP, you must install (64-bit) SQLCMD and BCP version 15 from Microsoft SQL Command Tools redistributable (go.microsoft.com/.../
Installing SQL Server Command Line Utilities requires installing the SQL Server ODBC package (go.microsoft.com/.../, so install this one first.
MongoDB
Elastic Search
Syracuse
You do not need to install the SQL Server Component in this scenario as the DB component will need to be manually configured in the Console. So let's dive right in:

1- The first thing to do is to create an Amazon RDS instance using SQL Server 2019 Standard/Enterprise Edition. I am going with the Standard edition in this course. I will use the latest version of SQL Server 2019 which is provided by the RDS service.

In the Templates section, choose Production if you are going to use this instance for a production environment as it will install the service on faster, more reliable servers, otherwise choose Dev/Test.

You must also give the instance an identifier which you can easily remember later.

Choose the master username to be "sa" instead of the default "admin" to have the best experience when configuring your X3 solution. Also make sure you use a strong password for the sa account.

Next, choose the right server size for your needs. I would recommend using the R class servers for production environments as they provide more RAM compared to general purpose M servers. Also make sure you choose Provisioned SSD for production environments and the right disk capacity based on your needs and set the IOPS to be 50 times that of your storage in GB. In this example, I am asking for 100 GB of disk space, therefore 5000 IOPS.

Next, choose your DB to be in a single Availability Zone as Sage X3 does not support Multi-AZ Databases.

Now choose which VPC your database should reside into and whether you would like to allow external access to it. I highly recommend not allowing external access to your DB for better security reasons. I am also going to go with the default security group, but feel free to go with your own security group as long as it allows inbound traffic using SQL server ports TCP 1433 and UDP 1434.

Now press the Create Database button and wait for the DB to be provisioned. This could sometimes take up to an hour, so go grab a coffee.

Once the RDS Database is ready, click on its identifier in your RDS dashboard to retrieve its Endpoint URL: Write this down as this is your SQL Server address.

Now it's time to jump to our application server and configure the solution:

Before we configure the solution, let's make sure we have access to our RDS database from here using SQL Server Management Studio. We will also need to create a database for X3 using this tool. Run Sql Server Management Studio application and type in the Endpoint address you copied earlier as the server address, "sa" as your username and your "sa" password that you chose during RDS setup:

Click Connect and you shall be connected. If you receive any errors you must troubleshoot the issue. Perhaps it is a network connectivity issue with your security group or subnet settings.

Once you are connected to DB service, run the following Query to create the new database. I am using x3db as my database name here.

CREATE DATABASE x3db
COLLATE Latin1_general_BIN
GO
alter database x3db set read_committed_snapshot on

Open the Console application and click the New Solution button:

Now Click New and type in the address of your server (I am using localhost as i have the console installed on the same server). You will also need a local user account created in this server which has the rights to Log on as a Service (shall we call this runtime user?). We will use this account throughout our setup of other components as well.

Keep clicking Next until you reach the Database Configuration wizard. Choose Manual Configuration here:

Next, fill up the fields here as below:

Name of data server: Your AWS RDS Endpoint URL
Type of Database: SQL Server
Type of Platform: Microsoft Windows x64
DBMS Version: MS SQL Server 2019
DBMS installation path: Optional if X3 Runtime >=R091. Mandatory if X3 Runtime < R091, database tools installation path, directory for OSQL.exe with \binn stripped
ODBC tools path: ODBC tools installation path, directory for SQLCMD.exe with \binn stripped
Path of the database files: D:\RDSDBDATA (Always use this address. This points to the D drive in your RDS server, so do not change this


Click Next to continue the wizard until it is finished.

In the solution configuration, use the following values:

Db login: sa
Instance sysadmin user password: your RDS "sa" password
Password for X3 folders Schema: Your RDS "sa" password
SQL Server Instance Name: MSSQLSERVER
database name: x3db or any other name you used when you created the database earlier
DBMS installation path: Optional if X3 Runtime >=R091. Mandatory if X3 Runtime < R091, database tools installation path, directory for OSQL.exe with \binn stripped
ODBC tools path: ODBC tools installation path, directory for SQLCMD.exe with \binn stripped
Data directory path: D:\RDSDBDATA\data
Create a directory called Scripts in a location where your runtime user has full access to. I'm creating a D:\DBScripts directory and providing its address here in the Scripts line
Log directory path: D:\RDSDBDATA\logs


In the Application server section, provide the root directory of your Apache server. In my case it is C:\Apache24
In the Main runtime section, provide the ODBC tools path as used in the database section.
ODBC tools path: ODBC tools installation path, directory for SQLCMD.exe with \binn stripped
User login for Sage X3 service: our runtime username, ex. x3runtime
User account password: The runtime user's password
Now Save the solution and click to configure the database and the application:

If prompted to enter a scripts directory, provide the local scripts directory again:

If you receive any errors during the application configuration, especially the Error in distant registry configuration, do as follows:

1- Right click on the Sage folder and grant full control permission for the runtime user

2- Add the runtime user to the Administrators group. You can remove it from this group after the configuration is finished.

3- Close the console and open it again by clicking on "Run as administrator"

CONGRATULATIONS! You now have a Sage X3 solution using Amazon RDS! Do the setup of Syracuse and the rest of the components as you would in a normal setup.

Good Luck!