Promotic
WikipediaLinkedInYoutubeTwitterFacebook

MS SQL Server - description, installation and management

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, 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 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 OS Windows 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) In OS Windows XP/Vista/7 it runs as a operation system service (it runs even when no user is logged in to the computer).
9) A designer can use it to develop and debug the application which then runs at a customer on the MS SQL Server or the MS SQL Server Express.
10) It is possible to set some PROMOTIC components to write its data into the MS SQL Server or MS SQL Server Express (e.g. alarms, trends).
 
Disadvantages and limitations of MS SQL Server 2012 Express:
1) The total 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 suppiled with the PROMOTIC system are referring to the anonymous instance. If the named instance is used (e.g. because there is one anonymous instance allready running on the computer and therefore it cannot be used) it is necessary to modify the configuration scripts accordingly (the procedure is described in 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 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 authorization provided by the MS SQL Server (otherwise the user authorization would be based only on OS Windows NT and the MS SQL Server configuration for the use of 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 for PROMOTIC apllicalions, it is necessary to complete the basic configuration (create the databases and users with preconfigured access rights). It can be done 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 if the server name, user name or password is different from the values contained in the file) followed by executing the command file MsSQLInit.bat in the C:\Pm\Tools\MsSQL folder.

During the configuration two new database 'pm_data' is created for user data accessible by the PmAdo, PmDatabase objects for storing trends by the PmTrend object and for storing alarms/events by the PmAlarmEvent 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 for reading 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.
2) Set the folder C:\Pm\Tools\MsSQL as the working folder by the command cd \pm\tools\mssql.
3) Run the batch file MsSQLInit.bat.
4) Check if error messages aren't dumped during the batch file run.
5) If the database is to be accessible on the network (it is local by default), 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 to 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_trend' database.

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

Text console for MS SQL Server administration

For the administration of the MS SQL Server (Express) it is possible to use the text console with the command line (OSQL.EXE or ISQL.EXE respectively). On starting the console, parameters for the 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" /?

Starting 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 enables the interactive work with the MS SQL Server. From the command line it is possible to make any SQL commands (Transact SQL). If the SQL command returns the output data, then these data are displayed in the command line. Each entered SQL command is executed only after entering the special command GO. The SQL command needn't be entered on one line but it can be split into more lines when the command is executed at a time by entering the command GO. Formerly entered lines are recorded and it is possible to browse them by the cursor keys <Up> or <Down>. In the command it is possible to move and edit by <Left> and <Right>.

The command line enables 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 the OS Windows and then to add the above mentioned entry parameters in the shortcut parameters. It results into the shortcut (icon) in the OS Windows 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.

© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice