Database Servers#

This guide will walk you through the steps to successfully plan and install a database server to host Critical Manufacturing MES databases.

Planning for disaster recovery and high-availability#

Critical Manufacturing recommends adopting the Always On for Availability Groups disaster recovery and high-availability solution for database systems hosting Critical Manufacturing databases. If you are not familiar with the process, the Always On Guide contains technical information on the subject. This guide is intended to serve as a summary and does not cover the technical details included in the above-mentioned guide.

An individual cluster for online, for ODS and for DWH to maximize the availability should be created, though the solution will also work if you decide to create a single cluster. In both cases, it is necessary to create an availability group for each of the product databases. This process is detailed below.

Database Server pre-requisites#

The following software requirements must be met in all database nodes:

Table: Database Server pre-requisites

SQL Server Licensing#

This section contains licensing information regarding SQL Server 2019 and SQL Server 2022.

SQL Server 2019 and SQL Server 2022 Standard editions are licensed in Core-Based and Server + CAL modes, whereas SQL Server 2019 and SQL Server 2022 Enterprise editions are licensed only in Core-Based mode.

When running Critical Manufacturing on SQL Server Standard edition, the following limitations apply:

• Database instance maximum hardware consists of 128 GB or the lesser of 4 CPUs or 24 Cores (SQL Server 2019 and SQL Server 2022).
• Mobile reports and KPIs are not available.
• Power BI Report Server is not available.

For more information about SQL Server licensing, see the URLs below:

• SQL Server 2019 ⧉
• SQL Server 2022 ⧉

Always On for Availability Groups pre-requisites#

If you have decided to implement an high-available SQL Server solution using Always On for Availability Groups, these additional requirements and preparation steps must me met:

Table: Always On Availability Groups pre-requisites

Preparing Windows Server Failover Cluster#

To prepare the WSFC cluster, follow the instructions on this guide ⧉. Because we use Always On for Availability Groups, we do not require any kind of storage resource in the cluster. You can also choose the quorum solution that best fits your environment.

Microsoft Distributed Transaction Coordinator (MS-DTC) Configuration#

This section describes how to install and configure Microsoft Distributed Coordinator (MS-DTC). MS-DTC is a component that is used to managed distributed transactions and the two-phase commit protocol. Microsoft Distributed Coordinator (MS-DTC) is installed as part of the operating system installation but it needs to be configured in order for Critical Manufacturing to work correctly.

The instructions for configuring Microsoft Distributed Coordinator (MS-DTC) according to Critical Manufacturing requirements are the same for Windows Server 2008 and for Windows Server 2012 and can be found in the URL: http://technet.microsoft.com/en-us/library/cc731495.aspx ⧉. For convenience, the configuration procedure is described in this section.

• Start the Component Services management console by typing comexp.msc in the start command and pressing OK.

• Drill-down by clicking on Component Services > Computer > My Computer > Distributed Transaction Coordinator > Local PC.

• Right-click on the Local PC under Distributed Transaction Coordinator and then select Properties.

• Click on the Security tab and then make sure that the following options are checked as shown in the picture below:

  • Network DTC Access
  • Allow Remote Clients
  • Allow Remote Administration
  • Allow Inbound
  • Allow Outbound
  • Enable XA Transactions
  • Enable SNA LU 6.2 Transactions

SQL Server Installation#

To implement Always On for Availability Groups, first you need to perform a single node installation of SQL Server on each of the nodes of the cluster. This procedure can also be used to setup a development environment where a high-availability solution is not required.

Table: SQL Server pre-requisites

The next picture shows the Feature Selection screen for SQL Server. It represents the features required by Critical Manufacturing.

Next set the name of the instance to install. Critical Manufacturing recommends deploying three instances of SQL Server (Online, ODS and DWH) ideally on different physical machines. In that sense this procedure should be repeated for each of the instances.

Next set the service account to run the engine service and the agent service. This account should be the domain account, which requires rights to access the file system.

It is necessary to check that the collation is exactly Latin1_General_CI_AS. The deployment software will verify this setting and will fail if it differs.

If the values do not match choose the "Customize..." action and configure it as shown here:

Now configure the database engine. Set the authentication mode to "Mixed mode" and set the sa account password to a value of your choice.

Now configure the data directories. Critical Manufacturing recommends separating the data files and at this moment you should have already prepared the disk drives on all nodes.

The same recommendation is applicable to the TempDB database.

And also to the Analysis Services database files.

SQL Server Reporting Services#

SQL Server Reporting Services server installation is performed outside the main SQL Server installation. This guide from Microsoft provides assistance to install this feature: https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-reporting-services?view=sql-server-ver15 ⧉.

Configuring Reporting Services for Critical Manufacturing#

To configure reporting services for Critical Manufacturing in Always On on Availability Groups you need to create the database in its own availability group. Please refer to this ⧉ Microsoft article for instructions on how to configure reporting server on that scenario.

The following changes should be performed on ReportServer.Config.

Reporting Services Authentication#

<AuthenticationTypes>
    <RSWindowsNTLM/>
    <RSWindowsBasic/>
</AuthenticationTypes>

Reporting Services Concurrent Connections#

By default Microsoft Reporting Services have the maximum number of requests per user set to 20. Given the nature of our system we recommend this limit to be increased to maximum number of users seeing reports simultaneously. If in doubt, increase it to at least 500.

<Add Key="MaxActiveReqForOneUser" Value="500" />

Creating Availability Groups in SQL Server#

Pre-conditions:

• All database servers have WSFC installed;

• All SQL Servers have the Always On High Availability for Availability Groups enabled.

The most universal way (in place since the initial versions of SQL Server that support Always On High Availability) to create an Availability Group is as follows.

Create a temporary database for each planned Availability Group. If you want to create an Availability Group for Online, ODS and DWH, you need to create 3 temporary databases.

Start by creating a temp, tempODS and tempDWH:

Before creating the Availability Group, you need to perform a full backup of each database:

Then, go to the Always On High Availability folder and right click on it to start the New Availability Group Wizard:

After this you must select the name of the Availability Group. In this case the system will be called CMF and three availability groups will be created (Online, ODS and DWH). According to the guidelines, the names will be AGCMF, AGCMFODS and AGCMFDWH:

Now select the database that will be associated to this Availability Group:

Next set the replicas that you need and set how you want the availability groups to work:

Change the default settings of the original replica and then add a another replica:

It is also possible to create the listeners for each Availability Group. Follow the guidelines and give the listener the same name as the Availability Group. Hence, the listener will be named AGCMF:

The port and IP Address must be selected according to the available values. Do not forget that the listener will be the public address of the Availability Group. Therefore, this will be the value that you will use when preparing the database server in the MES setup:

Use Automatic seeding as your data synchronization preference because experience has proved it to be the most stable, and it is recommended that all the SQL Server machines directory structure be the same.

The validation step should have an all green result:

Finally, select Next and wait for the creation of the Availability Group. If no problem is found, the successful result will be clear:

If you now go to the Availability Groups folder, you will see the new Availability Groups:

After creating the Availability Group, you can delete the temporary database. First remove the database from the Availability Group:

And then delete the database from all the nodes:

For more information about SQL Server Always On for Availability Groups please refer to SQL Server AlwaysOn Availability Groups ⧉

Microsoft Advanced Firewall Configuration#

The following inbound ports must be allowed in the database servers for proper operation:

Table: Advanced Firewall Configuration

SQL Server Installation Advanced Topics#

In this section, we present some guidelines and considerations for optimizing SQL Server at the installation phase.

• Breaking Up SQL Server Databases into Multiple Files
• Database Filegroups and Data Files
• TempDB Database Configuration
• Storage and RAID Levels
• Recommendations for SQL Server Installation Options

Installing to an Azure managed instance#

Critical Manufacturing can use an Azure SQL Managed Instance as the database. This will require having the files located in the actual instance, together with the proper configuration in the installation process.