Promotic

MS SQL Server - description, installation and management

MS SQL Server Express

If necessary for smaller database applications, it is possible to use the freeware versions of MS SQL Server Express, together with the PROMOTIC system.
It is possible (according to the Microsoft licence politics) to distribute these products free of charge together with the PROMOTIC system and use them accordingly as a database engine for all suitable PROMOTIC applications.
The product is available for download at Microsoft Web pages, http://download.microsoft.com. Download the latest version (e.g. MS SQL Server 2012 Express with Management Tools).
It is important to know that these free products are fully compatible with other charged versions of the MS SQL Server. The admin text console OSQL.EXE (or ISQL.EXE) with the command line is included.
 
MS SQL Server 2012 Express is the successor of the older versions, fixing some of their's weakneses (especially fixing the performance limitation when executing parallel queries, expanding the database size from 4GB (for MS SQL Server 2005 Express), up to 10GB (for MS SQL Server 2012 Express, MS SQL Server 2008 R2 Express), adding the MS SQL Server Management Studio Express client tools, also downloadable at http://download.microsoft.com.

MS SQL Server and MS SQL Server Express features

Advantages of using MS SQL Server or MS SQL Server Express in the PROMOTIC system:
1) No charge for MS SQL Server Express using.
2) Easy upgrade in case of the transition to the charged version of the MS SQL Server. Thanks to the consistent core, data and programs are stored in the MS SQL Server Express fully compatible with charged versions of the MS SQL Server.
3) High efficient, stable and technology mature product.
4) Easy installation with the support fors most Windows OS versions.
5) Easy backup, limitation by access rights, transaction processing, replies, etc.
6) Network access without the necessity of file sharing.
7) Using the internal language Transact SQL to program various data controls, calculations, filters, directly into the MS SQL Server.
8) It runs as Windows service (it runs even if no user is logged-in to the computer).
9) The designer can use it to develop and debug the application which then runs at a customer on the MS SQL Server or the MS SQL Server Express.
10) It is possible to set some PROMOTIC components to write its data into the MS SQL Server or MS SQL Server Express (e.g. alarms, trends).


Disadvantages and limitations of MS SQL Server 2012 Express:
1) The size of one database can be 10 GB maximum (for MS SQL Server 2012 Express, MS SQL Server 2008 R2 Express) and 4GB (for MS SQL Server 2005 Express)
2) Maximum usable RAM memory is 1GB.
3) The MS SQL Server Agent service is not available.
4) Only one CPU and 4 CPU cores can be used. (it runs also on multi CPU systems, but the MS SQL Server Express cannot use the potential of multiple CPUs)

MS SQL Server or MS SQL Server Express installation

MS SQL Server and MS SQL Server Express are installed via the correstponding installators and can be installed as anonymous or named MS SQL Server instance.
As for the PROMOTIC system it is better to use the anonymous instance because all following configuration scripts supplied with the PROMOTIC system are referring to the anonymous instance.
If the named instance is used (e.g. because there is one anonymous instance already exists on the computer and therefore it cannot be used) it is necessary to modify the configuration scripts accordingly (the procedure is described in the MSSqlInit.bat file, see more Configuration of MS SQL Server).
As for the MS SQL Server Express installation, it is also done via the installator program, the client tools MS SQL Server Management Studio Express are installed from independent install package, for running MS SQL Server Express it is necessary to to install Microsoft NET.Frameworks 2.0 or higher. During the installation of MS SQL Server or MS SQL Server Express it is necessary to enable the user authentication provided by the MS SQL Server (otherwise the user authorization would be based only on Windows OS NT and the MS SQL Server configuration for the use of the PROMOTIC system would be more complicated).

MS SQL Server or MS SQL Server Express configuration

In order to enable the installed MS SQL Server or MS SQL Server Express to be used from the PROMOTIC application, it is necessary to complete the basic configuration (create the databases and users with preconfigured access rights). It can be done by using the installed MS SQL Server Management Studio Express client tools or by predefined command files.
Using the command file is based on editation (it is edited only if the server name, user name and password is different from the values contained in the file) followed by executing the command file MsSQLInit.bat in the \Promotic\Tools\MsSQL folder.
During the configuration two new database 'pm_data' is created for user data accessible by the PmaAdo, PmaDatabase objects for storing trends by the PmaTrendGroup object, for storing alarms by the PmaAlarmGroup object and for storing events by the PmaEventGroup object. Additional 3 user accounts are created, where the 'pm_admin' has admin rights, the 'pm_writer' has the rights for read and write and is able to create, delete and modify the table structure and the 'pm_reader' has the access right to tables read only. Also the ODBC source 'pm_data' is registered for access over the ODBC into the 'pm_data' database.

Walkthrough of the configuration itself:
1) Open the window of the command line.
2) Set the folder C:\Promotic\Tools\MsSQL as the working folder by the command cd \Promotic\Tools\MsSQL.
3) Launch the batch MsSQLInit.bat file.
4) Check if error messages aren't dumped during the batch file run.
5) If the database is to be accessible on the network (default: local), then it is necessary to use the MS SQL Server Configuration Manager tool and set in the MS SQL Server Network Configuration/Protocols for SQLEXPRESS(MSSQLSERVER)/ protocol TCP/IP to Enabled. Switching this setting demands SQL server restart.


Description of actions performed during the configuration:
1) Batch file runs the SQL configuration script MsSQLInit.sql by the utility OSQL.
2) New 'pm_data' database is created for the user data of the application.
3) New entry accounts 'pm_admin', 'pm_writer' and 'pm_reader' are created, its initial 'pm_data' database is set and initial passwords are set.
4) Access to the 'pm_data' database is enabled for the accounts.
5) System role 'sysadmin' is assigned for the 'pm_admin' account in the 'pm_data' database.
6) Database roles 'db_ddladmin', 'db_datareader' and 'db_datawriter' are assigned for the 'pm_writer' account in the 'pm_data' database.
7) Database role 'db_datareader' is assigned for the 'pm_reader' account in the 'pm_data' database.
8) ODBC data source 'pm_data' is registered for the access over the ODBC into the 'pm_trend' database.

Access from the PROMOTIC application to MS SQL Server

The 'pm_data' database: is designed for the user data in the PROMOTIC system (PmaAdo and PmaDatabase objects) and also for PROMOTIC system components data (trends, alarms).

The PmaAdo object: the application can access directly for example using ADO Provider 'SQLOLEDB', or via ODBC by using the ADO Provider 'MSDASQL' (registered data ODBC source 'pm_data' is not necessary). In this object, it is necessary to set the following configurators:
1) 'Database connection parameters', for example "provider=SQLOLEDB;server=.\SQLEXPRESS;database=pm_data;uid=pm_writer;pwd=pmwriter;".


The PmaDatabase object: the application can work with data in this database via ODBC interface (registered ODBC data source 'pm_data' is necessary). In this object, it is necessary to set the following configurators:
1) 'Technology' to Access through ODBC.
2) 'Database type' to 'ODBC; Data source'.
3) 'Database (file, folder, data source)' to the name of registered ODBC source, for example to 'pm_data'.
4) 'Table (file)' to the name of the corresponding table (if the SELECT SQL statement option would not be used when opening the table).
5) 'User' to the MS SQL Serveru user name, for example to 'pm_writer'.
6) 'Password' to the password of corresponding MS SQL Serveru user for authentication purposes.
7) 'Database connection parameters' for example "provider=SQLOLEDB;server=.\SQLEXPRESS;database=pm_data;uid=pm_writer;pwd=pmwriter;".


The PmaTrendGroup object: the trend component can read/write its data to the database. In this object, it is necessary to set the following configurators:
2) and in window Database MS SQL server backups (obsolete) or Database MS SQL server cyclic it is necessary to configure the items that refer to the corresponding database for writing data.
For example:
"server=.\SQLEXPRESS;database=pm_data;uid=pm_writer;pwd=pmwriter;"
(server instance name .\SQLEXPRESS may differ).


The PmaAlarmGroup object: the alarms component can read/write its data to the database. In this object, it is necessary to set the following configurators:
1) "Storage type" configurator to MS SQL database.
2) and in the "MS SQL database" window it is necessary to configure the items that refer to the corresponding database for writing data.
For example:
"server=.\SQLEXPRESS;database=pm_data;uid=pm_writer;pwd=pmwriter;"
(server instance name .\SQLEXPRESS may differ).

Text console for MS SQL Server administration

For the administration of the MS SQL Server (Express) the text console can be used with the command line (OSQL.EXE or ISQL.EXE respectively). On starting the console, parameters for connection to the appropriate MS SQL Server can be entered. The list of possible parameters is displayed by the console after entering the parameter '/?':
  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" /?
 
Starting the console for the default installed MS SQL Server and logging in the 'pm_admin' user:
  "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S(local) -Upm_admin -Padmin
The console allows the interactive work with the MS SQL Server. From the command line it is possible to make any SQL statement (Transact SQL). If the SQL statement returns the output data, then these data are displayed from the command line. Each entered SQL statement is executed only after entering the special statement GO. The SQL statement needn't be entered on one line but it can be split into more lines when the statement is executed at a time by entering the statement GO. Formerly entered lines are recorded and it is possible to browse them by the keys "up key" or "down key". In the command it is possible to move and edit by the keys "left key" and "right key".
The command line allows the full administration of the MS SQL Server, from another computer respectively. For example the configuration of the MS SQL Server, creating and deleting databases, creating and restoring backups, creating, modification and deleting tables, adding, changing and deleting data in individual tables, viewing and searching data, adding and deleting users, changing entry passwords, setting access rights to individual objects in the database, etc.
 
Tip: It is possible to create a shortcut to the OSQL.EXE (ISQL.EXE) program in Windows OS and then to add the above mentioned entry parameters in the shortcut parameters. It results into the shortcut (icon) in Windows OS after its activation the console for the appropriate MS SQL Server with the logged in user is opened.
 
Tip: For deleting the console content it is possible to use the command: !!CLS.
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r.o.