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.
10) It is possible to set some PROMOTIC components to write its data into the MS SQL Server (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 either 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
MsSQLInit.bat file 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.
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 into 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_data' 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 either directly for example using
ADO Provider 'SQLOLEDB', or via ODBC by using the
ADO Provider 'MSDASQL' (registered ODBC data source
'pm_data' is not necessary). In this object, it is necessary to set the following configurators:
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:
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.
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:
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
Database MS SQL Server cyclic.
2) and in the "
Database MS SQL Server cyclic" 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" /?
Launching 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.