CIMHT_006 How to Configure the Database Logger Proficy HMI/SCADA – CIMPLICITY Outline The Proficy HMI/SCADA – CIMPLICITY product has the ability to log point data to a Microsoft SQL Database. This data can be point values, alarms, or potentially events that have happened in the CIMPLICITY project. This document will explain the minimal configuration required to achieve Data logging on a CIMPLICITY Server.

Requirements Software

� CIMPLICITY Development Server.

� Microsoft SQL or Microsoft Desktop Engine (MSDE)

NOTE: You will require the username and password of a valid Microsoft SQL user. Typically, it is suggested to use a system administrator (or “sa”) account to minimize the permissions based issues that can occur. If you do not know your SQL username and password, please contact your IT Administrator.

How it works When a CIMPLICITY project is running it is maintaining a list of point values. These points can be based off of a Device such as a PLC, or calculated internally. These points can be logged to the database to keep a historical record of the data values that were obtained. The CIMPLICITY Database Logger Option inserts the data into the database. There are two processes that do the inserts to the SQL Server; the Point Datalogger (PTDL) process – which handles point value logging, and the Datalogger Process (DL) – which handles alarm and event logging.

CIMPLICITY Project SQL Database

Figure 1

2

Microsoft SQL Server Desktop Engine (MSDE) Configuration

Step 1a: Configuring the Microsoft SQL Server

1. On the SQL Server go to the command prompt and run the following: Svrnetcn.exe A dialog similar to the following should appear.

Figure 2

2. Select the appropriate instance from the instance dropdown that you intend to configure. The instances that CIMPLICITY installs on newer versions of the software are going to be the computer name followed by “PROFICY” as the instance name.

3. Enable the following two protocols:

� Named Pipes

� TCP/IP NOTE: Named Pipes is not going to work across a network router, so if you intend to log across a network router you will want to use the TCP/IP protocol.

4. In the Enabled Protocols list, highlight the TCP/IP protocol and click on the Properties Tab. A dialog should appear as follows:

3

Figure 3

Make sure that the port that is listed is written down. This is the TCP/IP Port that the SQL Server will be listening on. In addition, make sure that the “Hide Server” checkbox is unchecked. In the case of this dialog it is set to 4500.

5. Once you have made the changes click on the “OK” button and then stop/restart the SQL Server via the SQL manager. This can be done by running:

Sqlmangr.exe A dialog should appear as follows:

Figure 4

6. Click on the “Stop” button and wait for the status to update that the SQL Server is stopped. 7. Click on the “Start” button and wait for the status to update to indicate that the SQL Server is

running. The SQL Server should now be configured at this point in time.

Step 2a: Configure the ODBC Data Source

1. Open the Windows ODBC Data Source Administrator. This can be found under the Windows Control Panel-> Administrative Tools. The icon is named “Data Sources (ODBC)”. A dialog should appear as follows:

4

Figure 5

2. Click on the “System DSN” tab.

NOTE: CIMPLICITY only uses System Data Source Names (DSN); it is not possible to use a User or File DSN with the CIMPLICITY Database Logger capability.

3. A list of the configured System DSN’s will be displayed. Click on the “Add..” button and a dialog

should appear as follows to create a new Data Source:

Figure 6

5

4. From the driver list choose the “SQL Server” driver and click on the “Finish” button. The

following dialog should be displayed:

Figure 7

5. For the name put in “CIMPLICITY Datalogger DSN” and for the dropdown choose the SQL

Server you configured in Step 1. In this case it was as follows:

Figure 8

6

6. Click on the “Next” button. A dialog should appear as follows:

Figure 9

Select the authentication type as “With SQL Server Authentication using a login ID and password entered by the user”. Also select the checkbox to “Connect to SQL Server to obtain default settings for the additional configuration options” and then put in the user name and password being used. In the case of the above example it is using a user “sa”.

7. Click on the “Client Configuration” button and the following Dialog should appear.

Figure 10

7

Make sure that the Network Library selected is TCP/IP and that the port number is the same as was configured in the “svrnetcn.exe” utility. In this case it is port 4500. When this is done, click on the “OK” button.

8. On the “Create a new Data Link to the SQL Server” click on the “Next” button. This will cause

the DSN to attempt to connect to the SQL Server. If there is an error connecting it will throw an error dialog at this point. The following dialog will appear:

Figure 11

9. Make sure that the following are selected:

� Use ANSI quoted Identifiers

� Use ANSI nulls, paddings and warnings

NOTE: At this point in time the logger is set up to log to the default logical database, however, you can point this to any database you want. It would be a good idea to change it from logging to the “master” database as this is the master SQL Configuration database and contains system tables.

10. Click the “Next” button. The following dialog will appear:

8

Figure 12

11. Click the “Finish” button. A dialog will appear asking you to test the data source. Click the “Test

Data Source” button.

Figure 13

The response should be that the test was successful. If it is not, then go back and double-check the previous steps.

9

Microsoft SQL Server 2005 Configuration

Step 1b: Configuring the Microsoft SQL Server

1. On the SQL Server go to the command prompt and run the following: SQLServerManager.msc A dialog similar to the following should appear.

Figure 14

2. Select the appropriate instance from the instance from the right hand side of the screen that you intend to configure. The instances that CIMPLICITY installs on newer versions of the software are going to be called “CIMPLICITY” or SQL Server (CIMPLICITY) as the instance name.

3. Enable the following two protocols:

� Named Pipes

� TCP/IP

Figure 15

10

NOTE: Named Pipes is not going to work across a network router, so if you intend to log across a network router you will want to use the TCP/IP protocol 4. In the Protocol Name list, highlight the TCP/IP protocol and right click on Properties. A dialog

should appear as follows:

Figure 16

Make sure that the protocol is enabled and verify the default settings.

5. By default the IP Addresses tab is configured to use TCP Dynamic Ports.

Figure 17

11

Also make sure that the port that is listed is written down. This is the TCP/IP Port that the SQL Server will be listening on. In the case of this dialog it is set to 1048.

6. If you have made any changes click on the “OK” button and then stop/restart the SQL Server via the SQL manager. This can be done by selecting “restart” in SQL Server Configuration Manager.

Figure 18

Step 2b: Configure the ODBC Data Source

12. Open the Windows ODBC Data Source Administrator. This can be found under the Windows Control Panel-> Administrative Tools. The icon is named “Data Sources (ODBC)”. A dialog should appear as follows:

Figure 19

12

13. Click on the “System DSN” tab.

NOTE: CIMPLICITY only uses System Data Source Names (DSN); it is not possible to use a User or File DSN with the CIMPLICITY Database Logger capability.

14. A list of the configured System DSN’s will be displayed. Click on the “Add..” button and a dialog

should appear as follows to create a new Data Source:

Figure 20

15. From the driver list choose the “SQL Server” driver and click on the “Finish” button. The

following dialog should be displayed:

Figure 21

13

16. For the name put in “CIMPLICITY Logging - POINTS” and for the dropdown choose the SQL Server you configured in Step 1. In this case it was as follows:

Figure 22

17. Click on the “Next” button. A dialog should appear as follows:

Figure 23

Select the authentication type as “With SQL Server Authentication using a login ID and password entered by the user”. Also select the checkbox to “Connect to SQL Server to obtain default settings for the additional configuration options” and then put in the user name and password being used. In the case of the above example it is using a user “sa”.

14

18. Click on the “Client Configuration” button and the following Dialog should appear.

Figure 24 Make sure that the Network Library selected is TCP/IP and that the port number is set as “Dynamically determine port” the same as was configured in the “SQL Server Configuration Manager” utility. NOTE: A static port can be configure in both the ODBC connection and in the SQL Server Configuration Manager. In this case it would be port 1048. When this is done, click on the “OK” button.

19. On the “Create a new Data Link to the SQL Server” click on the “Next” button. This will cause

the DSN to attempt to connect to the SQL Server. If there is an error connecting it will throw an error dialog at this point. The following dialog will appear:

Figure 25

15

20. Make sure that the following are selected:

� Use ANSI quoted Identifiers

� Use ANSI nulls, paddings and warnings

NOTE: At this point in time the logger is set up to log to the default “CIMPLICITY” database, however, you can point this to any database you want. Ensure that the desired database instance to log to is set here.

21. Click the “Next” button. The following dialog will appear:

Figure 26

22. Click the “Finish” button. A dialog will appear asking you to test the data source. Click the “Test

Data Source” button.

Figure 27

16

The response should be that the test was successful. If it is not, then go back and double-check the previous steps.

Step 3: Configure the CIMPLICITY Datalogger

1. Open the project you want to log with in the CIMPLICITY Workbench. 2. Go to the Project Properties. Make sure that the “Database Logger” option is selected. Click OK

on the dialog once it is selected.

Figure 28

3. From the CIMPLICITY Workbench open the “Database Logger” utility. The following dialog will

appear:

Figure 29

4. Highlight the DATA_LOG table and right click. On the right click menu choose “Logging

Properties”. The following dialog should appear:

17

Figure 30

5. Click on the “Default Alarm Connection” tab. Choose the DSN that you had configured

previously in the Data Source Administrator (“CIMPLICITY Datalogger DSN”) from the dropdown and put in the username and password that you used (“sa”).

Figure 31

18

6. Click on the “Default Point Connection” tab. When you transition from tab to tab in this dialog it will attempt to authenticate the connection information. If you get an error dialog when moving between tabs then it means that the Data Source is invalid, or the username and/or password are invalid.

7. Configure the same information on the “Default Point Connection” tab.

Figure 32

8. Click OK to exit the dialog. 9. For each table in the Database Logger utility then highlight each table and right click. From

the popup choose “Properties”. A dialog should appear as follows:

Figure 33

19

If it does not open to the “Connection” tab then select it. For each table make sure that the checkbox “Use default Point” connection is selected. For the case of the ALARM_LOG, EM_LOG, and EVENT_LOG tables you can choose the “Default Alarm Connection”. When done click OK on the dialog.

10. Close the Database Logger utility. 11. In the CIMPLICITY Workbench, do a configuration update of the project. 12. Start the project. At this point it should be set up for logging. You are free to add a point to the

DATA_LOG table with logging.