Blog

WinCC OA - How to Create a Microsoft SQL Server Install for NextGen Archiving

WinCC OA - How to Create a Microsoft SQL Server Install for NextGen Archiving

This two-part blog series is intended to be a step-by-step overview on how to set up and utilize a MS SQL Server and WinCC OA's NextGen Archive (NGA). Information for a general setup exists via the WinCC OA Documentation (see Further Reading/Links), but this walkthrough aims to be more detailed and explicit in the necessary steps. 

  1. How to Create a Microsoft SQL Server Install for NextGen Archiving
  2. How to Configure NextGen Archiving in WinCC OA to use a Microsoft SQL Server

Table of Contents:

  1. Notes/Prerequisites
  2. MS SQL Server and Database
    1. MS SQL Server and Installation
      1. Basic Installation
      2. Custom Installation
    2. MS SQL Server Configuration
      1. SQL Server Configuration Manager
      2. Microsoft SQL Server Management Studio
    3. Database Creation
  3. Further Reading/References

1. Notes/Prerequisites

Required programs:

  • Microsoft SQL Server (Installation instructions in part 2a)
  • Microsoft SQL Server Management Studio
  • Microsoft SQL Server Configuration Manager (installed alongside Microsoft SQL Server)

This demo was implemented using:

  • WinCC OA 3.18 P006
  • Microsoft SQL Server 2022 Express
    • NOTE: Other versions of MS SQL may work with NGA, but it has not yet been verified by DMC.
  • Microsoft SQL Server Management Studio 18
  • Windows 11

Assumptions:

  • Proper licensing for NGA is configured
  • The OS user has Windows administrator privileges

2. MS SQL Server and Database

2.1 MS SQL Server and Installation

Back to Table of Contents

  1. Begin the SQL server installation
    1. Download Microsoft SQL Server setup application from Microsoft's SQL Server Downloads page
    2. Run the application and proceed with the Basic or Custom installation. 

WinCC OA - SQL Server 2022 Express Edition

Basic Installation

If implementing the basic installation:

2. On the installation confirmation screen, select the Customize option

Screenshot 2

3. Configure the “Installation Type” Window properties

  1. Select the Add features to an existing instance of SQL Server <Year> option
  2. Select the server you wish to use for NGA connection

NOTE: If a specific server setup window is not specified, feel free to use the default options

Screenshot 3

4. Configure the “Azure Extension for SQL Server” Window properties

  1. Un-check the Azure extension for SQL Server option

Screenshot 4

5. Configure the “Feature Selection” Window properties

  1. Select the SQL Server Replication option
  2. NOTE: Installing this option is the primary purpose of customizing the server
  3. Click Next to begin installation

Screenshot 5

Custom Installation

If implementing the custom installation, follow the steps outlined in the WinCC OA MS SQL® Server Installation documentation.

2.2 MS SQL Server Configuration

Back to Table of Contents

Steps for configuring the SQL server will take place in both the SQL Server Configuration Manager and Microsoft SQL Server Management Studio programs (installed in 2.1 MS SQL Server Installation)

SQL Server Configuration Manager

Within the SQL Server Configuration Manager…

  1. Configure the Server’s TCP/IP Properties
    1. Navigate to “SQL Server Network Configuration/Protocols for <SERVER NAME>”
    2. Double click the TCP/IP item in the “Protocol Name” column to open the “TCP/IP Properties” window
    3. In the “Protocol” tab, toggle the Enabled option to Yes
    4. In the “IP Addresses” tab, specify 1433 for the IPAll/TCP Port option
    5. Click OK to apply and close the window

NOTE: A warning will appear indicating that changes will be applied only after the server is restarted 

Screenshot 6

Screenshot 7

Screenshot 8

Warning WinCC OA Any changes made will be saved

2. Restart the SQL Server

  1. Navigate to “SQL Server Services”
  2. Right click the “SQL Server (<SERVER NAME>)” row and click Restart

Screenshot 10

  1. Download Microsoft SQL Server setup application from Microsoft’s SQL Server Downloads page
  2. Run the application and proceed with the Basic or Custom installation

Microsoft SQL Server Management Studio

Within Microsoft SQL Server Management Studio

1. Connect to the SQL Server

  1. Click the “Connect Object Explorer” icon within the “Object Explorer”
  2. Specify the following parameters in the “Connect to Server” window and click Connect

Parameter

Value

Server type

Database Engine

Server name

<host>/<SERVER NAME>

Authentication

Windows Authentication

 

NOTE: If the “Custom” SQL Server installation was implemented, then connection can be made using the “SQL Server Authentication” method and the specified credentials

Screenshot 11

Screenshot 12

2. Enable dual authentication mode

  1. Right click the server name in the “Object Explorer” and select Properties
  2. Navigate to the “Security” page in the “Select a Page” menu
  3. Under the “Server Authentication” section, select SQL Server and Windows Authentication Mode option
  4. Click OK to apply changes
  5. NOTE: A warning will appear indicating that changes will be applied only after the server is restarted
  6. Restart the SQL Server via the SQL Server Configuration Manager using the steps outlined in Step 2 of the SQL Server Configuration Manager instructions

Screenshot 13

Screenshot 14

Screenshot 15

3. Configure the System Administrator credentials

  1. Under the “Object Explorer”, navigate to “<Database Name>/Security/Logins”
  2. Right click the sa (System Administrator) option and select the Properties option
  3. Under the “General” page of the Login Properties window, specify a password in the Password: and Confirm password: fields
    1. For this demonstration, I’ll use the password $martPeople3xpertSolutions
  4. Optional: Toggle the other general properties as needed or deemed necessary by your security need
  5. Click OK to apply changes

Screenshot 16

Screenshot 17

4. If a red “X” appears next to the sa user in the “Object Explorer”  , then enable sa (System Administrator) login

  1. Within the “Login Properties” window from step 3, navigate to the “Status” page
  2. Under the Settings/Login: option, select Enabled
  3. Click OK to apply changes
  4. NOTE: You may need to refresh the Object Explorer to see the red “X” disappear

Screenshot 18

Screenshot 19

5. Optional: Create a WinCC OA user account

NOTE: This step can be done now, or the user can be automatically created upon database generation (recommended)

  1. Under the “Object Explorer”, navigate to “<Database Name>/Security/Logins”
  2. Right click the “Logins” folder or any of its elements and select the New Login… option
  3. Within the “General” page
    1. Click the SQL Server authentication radio button
    2. Specify the user’s name in the Login name: field
      1. For this demonstration, I’ll use the username winccoa
    3. Specify the user’s password in the Password: and Confirm password: fields
      1. For this demonstration, I’ll use the password $martPeople3xpertSolutions
  4. Within the “Server Roles” page
    1. Check the public server role
    2. Click OK to apply changes

Screenshot 20

Screenshot 21

Screenshot 22

2.3 Database Creation

Back to Table of Contents

The database used for NGA will be auto generated using files that can be found in OA’s base project

1. Locate and copy the required database generation files

  1. Navigate to the “<winccoa>/data/NGA/MSSQLServer/sql folder” within Windows Explorer
    1. Where <winccoa> is the base WinCC OA project
    2. The default install location of the base project is typically “C:\Siemens\Automation\WinCC_OA\3.18”
    3. Therefore, the path I’m using is “C:\Siemens\Automation\WinCC_OA\3.18\data\NGA\MSSQLServer\sql”
  2. Copy the relevant files
    1. schema.sql
    2. db.windows.config
    3. create_database_windows.bat
  3. If your NGA project already exists, copy the relevant files into the same directory in your project
    1. You may need to manually create the relevant folders for “<project>/data/NGA/MSSQLServer/sql”
    2. It should be noted that the files can be copied anywhere, but it’s organizationally preferable to use the location specified above
    3. If you haven’t created a project yet, copy the files into a temporary location and move said files into your project upon project creation (outlined in the 2a. Project Setup instructions)

Screenshot 23

Screenshot 24

2. Modify the config file parameters

NOTE: The db.windows.config file will be used to specify the parameters needed to auto generate the appropriate database

  1. Open the db.windows.config file and with your favorite text-editing tool like Notepad or Notepad++
  2. Modify the parameters as needed.

Parameter

Modify?

Description/Notes

dbServer

YES

This should match the name of the server as specified in the MSSMS’s “Connect to Server” window and the Object Explorer.

Port

MAYBE

The default MS SQL server port is 1433. If you specified a different port during the MS SQL Server Configuration (step 1d), then use that value here

adminUsername

 

If using the “sa” user as the system administrator, this field should not change

adminPassword

YES

Modify this field using the System Administrator password specified during the MS SQL Server Configuration (step 3c)

winccoaLogin

MAYBE

If desired, modify the winccoa login/username. If already configured, then specify the name here. If not yet configured, then a user will be auto generated.

winccoaPassword

YES

If desired, modify the winccoa password. If already configured, then specify the password here. If not yet configured, then a user will be auto generated.

dbName

MAYBE

This will be the name of the to-be-generated database. You can use the default name or specify something a bit more descriptive.

     

sqlscriptpath

YES

This will be the file path of the schema.sql file duplicated in steps 1b and 1c. If the file was not duplicated, you may reference the corresponding file in the base OA project (i.e. <winccoa>/data/NGA/MSSQLServer/sql/schema.sql)

numberType

MAYBE

Can be left as default. Change if desired.

dbInitSize

MAYBE

Can be left as default. Change if desired.

dbFileGrowth

MAYBE

Can be left as default. Change if desired.

logInitSize

MAYBE

Can be left as default. Change if desired.

logFileGrowth

MAYBE

Can be left as default. Change if desired.

logMaxSize

MAYBE

Can be left as default. Change if desired.

     

dbPath

YES

This will be the physical location where the database is stored. The folder location specified must be created if it is not so already. You may opt to use the standard SQL database storage location or locate it in a more accessible location.

dbBackupPath

YES

This will be the physical location where the database backup is stored. The folder location specified must be created if it is not so already. You may opt to use the standard SQL database storage location or locate it in a more accessible location.

It should be noted that, according to the db.windows.config file comments, “The backup folder must be accessible for users under which the WinCC OA is running.”

 

Screenshot 25

3. Generate the SQL database

  1. Run the Windows Command Prompt as administrator
    1. In the Windows search bar, search “Cmd”
    2. Right click the Command Prompt application and select “Run as administrator”
  2. Navigate to the location of the create_database_windows.bat file (in the “<project>/data/NGA/MSSQLServer/sql” folder)
    1. Use cd <folder> in the command prompt to navigate to the appropriate directory
  3. Run the create_database_windows.bat file
    1. Run the create_database_windows prompt to generate the appropriate database

Screenshot 26

Screenshot 27

Screenshot 28

4. Verify that the appropriate database and users were created

  1. Within Microsoft SQL Server Management Studio, you should see:
    1. The newly created database
    2. The generated server-level winccoa user (if it did not already exist)
    3. The generated/mapped database-level winccoa user

Screenshot 29

3. Further Reading/References

Learn more about our Manufacturing Automation and Intelligence expertise and contact us for your next project. 

Comments

There are currently no comments, be the first to post one.

Post a comment

Name (required)

Email (required)

CAPTCHA image
Enter the code shown above: