MS SQL Server Compact
For the purposes of small applications and testing, it is possible to use the freeware version 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 OS Windows.
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 OS Windows versions.
5) Database configuration, user list and 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).
'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 installationMS SQL Server Compact
is installed by the correstponding installation program. The corresponding ADO Provider
) 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 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 PROMOTIC to MS SQL Server
The 'pm_data' database:
is designed for the user data in the PROMOTIC system (PmaAdo
objects) and also for PROMOTIC system components data (trends, alarms).
object: the application can access directly for example via 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 configuration items:
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 configuration items:
' to the name of the corresponding table (if the SELECT
SQL statement option would not be used when opening the table).
' to the MS SQL Serveru user name, for example to 'pm_writer'
' to the password of corresponding MS SQL Serveru user for authentication purposes.
object: the trend component can read/write its data to the database. In this object, it is necessary to set the following configuration items:
object: the alarms/events component can read/write its data to the database. In this object, it is necessary to set the following configuration items:
and in the "MS SQL database
" window it is necessary to configure the items that refer to the corresponding database for writing data.
(server instance name .\SQLEXPRESS may differ).