Promotic

MS SQL Server Compact - description, Installation and management

MS SQL Server Compact

For the purposes of small applications and testing, it is possible to use the freeware versions of MS SQL Server Compact together with the PROMOTIC system. It is possible (according to the Microsoft licence politics) to distribute this product free of charge together with the PROMOTIC system and use it accordingly as a database engine if suitable for 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 Compact 4.0 SP1).
Caution! It is necessary to instal 64-bit version on 64-bit Windows OS.

MS SQL Server Compact features

The MS SQL Server Compact differs from other MS SQL Server versions by the fact that all data of a single database are stored in a single file (SDF), located usually in the application data folder. The MS SQL Server Compact itself is provided (installed) in the form of dynamic libraries (DLL) that are loaded and called within the application process framework, allowing the application to read/write data directly into this database by using the ADO technology. This represents a simplified solution making data transfer, backup and configuration easier (only a single file is needed) and the file access is mananged by the application itself. This solution is similar to Microsoft Access MDB access to files directly by the application.
Standard MS SQL Server is installed and runs as independent program (service) and manages its data centrally. Other applications need to communicate with it by the SQL language. From the sturdiness, performance and data integrity point of view, especially when data is shared between multiple computers/applications, this standard solution is better.

Advantages of using MS SQL Server or MS SQL Server Express in the PROMOTIC system:
1) The MS SQL Server Compact usage is free of charge.
2) Easy upgrade in case of the transition to the charged version of the MS SQL Server.
3) Efficient, stable and technologically advanced product.
4) Easy installation with the support for most Windows OS versions.
5) Database configuration, user list or access rights setup is not needed because the data belongs only to the single application and are not shared with others.
6) Easy backup, transaction processing, replication, etc.
7) The designer can use it to develop and debug the application and then run it on the MS SQL Server.
8) It is possible to set some PROMOTIC components to write its data into the MS SQL Server Compact (e.g. alarms, trends).
9) 'Database connection parameters' for example "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;Data Source=d:\pm_data.sdf;ssce:max database size=4091;Persist Security Info=False;".


Disadvantages and limitations of MS SQL Server Compact:
1) The size of one database can be 4 GB maximum
2) The overall performance is lower than the other versions of MS SQL Server (including MS SQL Server Express).
3) It runs in the address area of the application, increasing its memory demands and consuming its CPU runtime.
4) Not suitable for simultaneous access to data common for multiple applications.

MS SQL Server Compact installation

MS SQL Server Compact is installed by the correstponding installation program. The corresponding ADO Provider (e.g. Microsoft.SQLSERVER.CE.OLEDB.4.0) is also installed, allowing the application to use the ADO technology and SQL statements to access the application data.

MS SQL Server Compact configuration

In order to enable the installed MS SQL Server or MS SQL Server Express to be used from the PROMOTIC application, it is not necessary to complete configuration as it is needed fro standard MS SQL Server (creation of databases and users with preconfigured access rights). Access to database file (SDF) can be limited by password (defined in ADO ConnectionString). After successfull database connection (with or without password), the application gains full access to all data in the database.
Note! If the PROMOTIC component (trends, alarms) refers to a non-existing database (SDF file does not exist), then the database is created automatically. This is different for access to user data (PmaAdo, PmaDatabase), where the database must exist or must be created by script. This is a difference compared to other SQL servers, where the reference must always point to existing database, otherwise reading/writing PROMOTIC components data fails (trends, alarms).

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

History:
Pm8.03.02: For the purpose of small and testing database applications this freeware version can be used in objects PmaAdo, PmaAlarmGroup and PmaTrendGroup.
PROMOTIC 9.0.27 SCADA system documentation MICROSYS, spol. s r.o.

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