MS SQL Express installation and usage with PHMI projects

Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
MS SQL Express installation
and usage with PHMI
projects
Introduction
This note describes the use of the Microsoft SQL Express 2008 database server in
combination with Premium HMI projects running on Win31/64 systems.
 Download
 Installation
 Configuration
Version
1
Description
First emission
Date
07/08/2015
Disclaimer
The information given in the documentation could change without notice and doesn't represent any
obligation for ASEM S.p.A.. ASEM S.p.A. is not responsible for technical mistakes or other omissions and
declines every responsibility resulting from its use.
ASEM S.p.A. will not be responsible for any loss of profits or damages, direct or not, of any kind
(included loss or damages of data), deriving from the use of this documentation.
Pagina 1 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
Table of Contents
1
2
3
4
Introduction ............................................................................................................................................... 3
Download ................................................................................................................................................... 3
Installation.................................................................................................................................................. 3
Configuration in Premium HMI Studio ....................................................................................................... 7
4.1
ODBC plug-in settings ........................................................................................................................ 7
4.2
Creating the database, the tables and the ODBC data sources ........................................................ 9
4.2.1 Creating the database, the tables and the ODBC links by means of Premium HMI Studio .......... 9
4.2.2 Creating the database and the tables by means of the SQL Express Management Studio ........ 11
Pagina 2 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
1 Introduction
SQL Server 2008 Express is a free edition of SQL Server that is ideal for learning and building desktop and
server applications of small and medium-sized compatible with the ODBC connector Premium HMI.
Database newer versions than 2008 edition, are not supported by Premium HMI.
2 Download
You can get the installation package from the following link.
http://www.microsoft.com/en-us/download/details.aspx?id=30438
When making the selection of the components to download, please get the one, which is compatible with
the system where you are going to install it according to the following table.
SQLEXPR_x64_ENU.exe
64bit systems
SQLEXPR_x86_ENU.exe
32bit systems
SQLEXPR32_x86_ENU.exe
includes the support for both 32/64bit systems
3 Installation
Make sure you have logged in to the system with a user account having administrative rights
and then run the setup.
In the initial screen, select “New installation …” as shown in the following figure.
Pagina 3 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
Proceed then with the installation.
In the “Feature selection” screen leave all the option as default as shown in the following figure.
The next screen allows you to configure the instance. You can leave all the default settings as shown in the
following figure.
Pagina 4 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
In the “Server configuration” screen, you can setup the service account. If not strictly required we suggest
leaving all the default settings.
In the “Database engine configuration”, you can define the authentication mode and the data storage
folder by opening the “Data directories” tab.
Pagina 5 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
The paths shown in this screen will be used to store the data. When keeping the default settings the data
are archived in a sub-folder of the database server installation directory.
Each database is made of two files, one for the data and one for the transaction log.
Click “Next” to finally start the installation.
At the end of the process, you will be notified about the operation result as shown in the following figure.
Pagina 6 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
4 Configuration in Premium HMI Studio
To configure a Premium HMI project to use the database server just installed you need to:
-
Configure the default ODBC plug-in
Create the tables for the several items you’ll need to archive:
o Alarms historical list
o Recipes and dataloggers
o Audit trail
4.1 ODBC plug-in settings
The ODBC plug-in settings are made in the “Default ODBC connection PlugIn” property available into the
“General” category of the project properties.
Pagina 7 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
Click on the right side of the column as select “SQLServerExp 2008”.
Click on “ODBC settings” to setup the connection parameters.
The server name can be found using the “SQL Server Configuration Manager” tool that has been installed
automatically together with the database server.
From the Start menu, start the tool and check the server name as shown in the following figure.
Pagina 8 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
The string “(local)” before the name says the server is local to the system on which Premium HMI Runtime
will be executed.
In the “Database” field, you need to specify the name of the database. If left empty Premium will use the
project name
4.2 Creating the database, the tables and the ODBC data sources
To have Premium HMI Runtime storing data into the database you will need to create the database, the
tables for each set of items you need to store and the ODBC data sources links used by the applications (in
this case PHMI Runtime) to connect to the database server.
Please remember that in order to enable the use of the relational database through the ODBC connector
you simply need to disable the IMDB archive.
The next figure shows the case of the system historical log.
The database, the tables and the ODBC links can be created directly from Premium HMI Studio or manually
using the “SQL Express Management Studio” tool.
4.2.1 Creating the database, the tables and the ODBC links by means of Premium HMI Studio
Make sure you have logged in to the system with a user account having administrative rights
and then run the setup.
You need to install the Premium HMI Studio on the PC where also the Premium HMI Runtime will be
executed and where the database server is installed.
Per each different historical data type, you need to archive through the database, it is then required you
create the corresponding table using the “Create DB table” command in the “ODBC manager” properties
category. The next figure shows the case of the historical system log.
Pagina 9 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
The first time this command is executed, Premium HMI Studio creates the database, the tables and the
ODBC links automatically.
To verify the table has been properly created it is enough to check into the Output window in Studio as
shown in the following figure.
Pagina 10 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
4.2.2 Creating the database and the tables by means of the SQL Express Management Studio
Make sure you have logged in to the system with a user account having administrative rights
and then run the setup.
You need to install the “Microsoft® SQL Server® 2008 Management Studio Express” tool available free from
the following link:
http://www.microsoft.com/en-us/download/details.aspx?id=7593
Launch the setup and select the option as per the following figure.
In the "Installation Type" screen, select "Perform a new installation ..." as shown in the following figure.
Pagina 11 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
In the "Feature selection" screen, mark the checkbox "Management tools ..." as shown in the following
figure.
Complete now the component installation.
From the "Start" menu launch the "SQL Server Management Studio" and connect to the server.
Pagina 12 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
The name of the server is specified in the "Server name" field, which is pre-compiled automatically when
the tool starts; if there is only one server instance it is also already the correct name.
Once the connection is done, click with the right mouse button over the ”Databases” icon and select “New
database”.
In the "New database" screen specify the name of the database, and confirm.
In the example below it was chosen the name "my_db", different from the project name. In this case, you
should take care to specify this custom name in the "Database" connection parameters of the ODBC Plugin
properties in the Premium HMI project.
Pagina 13 di 14
Nota Tecnica
MS SQL Express installation and usage with PHMI projects
TN0025
Premium HMI Runtime when executing the project will automatically create the tables and the ODBC data
sources.
Using the “ODBC data source administration”, you can check the newly created links.
Pagina 14 di 14