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

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.  

Basic Installation

If implementing the basic installation:

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

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

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

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

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

Custom Installation

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

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 

2. Restart the SQL Server

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

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

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

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

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

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

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)

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.”

 

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

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