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, tat allows 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 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).