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 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, 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 MS 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 since 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).
Disadvantages and limitations of MS SQL Server Compact:
1) The total 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 commands 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 by PROMOTIC applications, 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 (PmAdo, PmDatabase), 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, or 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 (PmAdo and PmDatabase objects) and also for PROMOTIC system components data (trends, alarms).
The PmAdo 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:
1) 'Database connection parameters', for example "provider=SQLOLEDB;server=.\SQLEXPRESS;database=pm_data;uid=pm_writer;pwd=pmwriter;".
The PmDatabase object: the application can work with data in this database via ODBC interface (registered ODBC data source 'pm_data' is necessary). It is necessary to configure the following configuration items in this object:
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 command 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 authorization purposes.
1) 'Database connection parameters' for example "provider=SQLOLEDB;server=.\SQLEXPRESS;database=pm_data;uid=pm_writer;pwd=pmwriter;".
The PmTrend object: the trend component can read/write its data to the database. It is necessary to set the following configuration items in this object:
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 PmAlarmEvent object: the alarms/events component can read/write its data to the database. It is necessary to configure the following configuration items in this object:
1) 'Storage type' to MS SQL database.
2) and in window Parameters for type: MS SQL database 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).
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice