StuertzCloud - Creating Local SQL Database

How to create a SQL Database instance on the local network to enable factory machines to communicate with StuertzCloud

Difficulté

Hard Difficile

Durée

30 minute(s) minute(s)

Introduction

See StuertzCloud - Setup Overview for an overview of the setup.

This tutorial covers the setup of the Local SQL Express database instance and creating a "ProOpt" database structure on it.

• This database installed on the 'Location' local network.
• The 'Machines' write their production data to it
• The 'Gateway' software uploads this data to the StuertzCloud server

See Also

StuertzCloud - Installing Gateway

StuertzCloud - Setting up Stuga Machine to Write Production Data

StuertzCloud - Creating a Customer Location

...You must have SQL Server Management Studio (SSMS) installed to manage the databases

Étape 1 - Download and Install SQL Server Express 2022

👉 Download SQL Server Express 2022

• Run the Installer: Locate the downloaded file named

SQL2022-SSEI-Expr.exe

• and double-click it to launch the installer.
• Choose Installation Type: In the installer window, select the Basic installation option. This will install SQL Server Express with default settings.
• Accept License Terms: Read and accept the license terms to proceed with the installation.
• Installation Progress: The installer will download and install the necessary components. This process may take several minutes.
• Installation Complete: Once the installation is complete, you'll see a confirmation screen. You can note the instance name (usually SQLEXPRESS) for future reference.
• SA password

Étape 2 - Verify Installation

After installation, you can verify that SQL Server Express is running by:

• Opening SQL Server Management Studio (SSMS).
• Connecting to the server using the instance name

(e.g., .\SQLEXPRESS)

• Ensuring that the connection is successful and that you can see the server objects.

Étape 3 - Download the ProOpt sql schema

This is stored on the Software versions Monday Board

Étape 4 - Open SQL Server Management Studio (SSMS)

Launch SSMS and connect to your local server. If you used default settings, the server name will be:

.\SQLEXPRESS

Use “Windows Authentication”

Tick trust Server certificate

...We will set up SQL Authentication later

Étape 5 - Enable SQL Server Authentication Mode

• In Object Explorer, right-click your server and select Properties.
• Go to the Security page.
• Under Server authentication, select: SQL Server and Windows Authentication mode
• Click OK.

Étape 6 - Restart SQL Service

1. In SSMS, open Object Explorer.
2. Right-click the server name at the top
3. Click Restart and confirm.

Alternatively, you can use SQL Server Configuration Manager if SSMS doesn’t show the restart option:

• Open

SQL Server Configuration Manager

• Expand SQL Server Services
• Right-click SQL Server (SQLEXPRESS) and choose Restart

Étape 7 - Enable the "sa" Login and Set a Password

• In SSMS, expand:

Security > Logins

• Right-click on sa and choose Properties
• In the General page:
  • Set a strong password (and confirm it)
• Switch to the Status page:
  • Set Login to: Enabled
• Click OK to save changes

Étape 8 - Test the sa Login

confirm that SQL Authentication works:

1. Disconnect your current session in SSMS.
2. Click Connect again.
3. Set:
   • Server name: .\SQLEXPRESS
   • Authentication: SQL Server Authentication
   • Login: sa
   • Password: (use the one you just set)

Étape 9 - Create a ProOpt Database

1. Right click on Databases->New Database
2. Call it ProOpt

Étape 10 - Open the SQL Script

• Go to File > Open > File…
• Browse to and open the script file:

ProOpt-StuertzCloud-Reference-Database-20240131 (4).sql

Étape 11 - Run the script in the ProOpt database

1. Select the ProOpt database in the drop down query menu
2. Execute the script
3. Check the proOpt tables have been created

Étape 12 - Create a SQL Server login for StuertzCloud

Uncheck Enforce Password Policy

Password in text file

Set permissions all bar denydatareader and denydatawriter

User Mapping->Map to ProOpt

should then appear in ProOpt->Security->Users folder

Étape 13 - Set configuration compatibility with StuertzCloud gateway

Étape 14 - Get a new GUID

• Use an online GUID generator https://guidgenerator.com/
• Copy the GUID

Étape 15 - Add GUID to StuertzCloud_Metadata

• Right click -> Edit top200 rows
• Paste GUID in ID field

en none 0 Draft