Introduction

R_keeper 7 can only be connected to MS SQL version 2012 or higher.

Setup of connection to MS SQL 2012

SQL server installation

1. Install MS SQL Server 2012 or higher. Express edition may be used.
   During server installation, use combined authentication mode and set the user password «sa».
2. Run SQL Management Studio, and create a new base named, f.ex., RK7.
3. In SQL Server Configuration Manager, enable TCP/IP protocol. Restrart SQL Server.
   
   
   
4. Change the base mode to Row Versioning

By default, SQL Server works in the «Read Commited» mode, which blocks data during request. This may cause problems in multi-user environment. Starting from 2005 version, the «READ COMMITTED using row versioning» mode is supported, where the blocks can only cause problems if different users are recording data in the same place. Reading data is never blocked and never blocks no one.

To enable versioning mode, run the script:

ALTER DATABASE RK7 SET READ_COMMITTED_SNAPSHOT ON;

See more details in the articles on Row Versioning-based Isolation Levels or Row Versioning-Based Transaction Isolation.

Manager workstation setup

1. Go to Service > Data Export > External Database Configurations reference, and copy pre-set «Microsoft SQL Server» setting. Assign a unique name to it .
2. In Basic > Connection String, press the button at the end of line.
   
3. Click «Build...» and configure connection to the database created in par. 1.
   
   
   
4. Make sure that the «Provider» tab has the Microsoft OLE DB Provider for SQL Server provider selected. In the «Connection» tab, select the server from the list, enter the username and the password, select the d atabase, and click «Check connection». If connection is verified successfully, click «ОК». Then, click «ОК» again.

If there is no required server in the list, enter its name manually. It is usually the same as the name of the computer where SQL Server is installed, an IP address may also be used as the name. Then, you should fill in the «User name» and «Password» fields before selecting the base.
The server name is displayed during SQL Server Management Studio launch:!

1. The "Connection string" will now look as follows: "Provider=SQLOLEDB.1;Password=1;Persist Security Info=True;User ID=sa;Initial Catalog=RK7;Data Source=127.0.0.1² "

Note

Data Source = name or IP address of computer where SQL Server is installed. Using server instance is, like 192.168.190.150\SQLEXPRESS, is also possible.

1. Then, perform upload to SQL DB using the setting you just created.
2. Open Service > Data export > Export to other DBreference, select setting created in p. 5. Specify "Username" and "Password".
3. In "Export parameters" block, leave default checkmarks if upload to clean SQL DB is performed.
4. Press "Check". If connection is successful, "ОК" button shall become active. Press it. Data export to SQL DB will start. SQL are being created at this moment.
5. If upload is successful, "Data export settings" window will close. There will be no additional messages.
6. Then, setup references (or report) server by licensing it and selecting the setting created in p. 5.
7. Open Settings > OLAP Reports > Report Serversreference, select required server, and configure it as described below.
8. License the references/report server as described in Setup of references serverarticle
9. In Connection to external DBgroup:
10. In "Setup of connection to externals DB" field, select setting created in p. 5 .
11. In "External DB username" field, specify SQL DB username.
12. In "External DB user password" field, specify SQL DB user password.
13. Specify same settings in Reference logsgroup:
    1. In "Setup of connection to externals DB" field, select setting created in p. 5 .
    2. In "External DB username" field, specify SQL DB username.
    3. In "External DB user password" field, specify SQL DB user password.
14. In "Base" group:
15. In "Cubes data source" field, select "SQL DB" parameter
16. In "Request logging" field, select appropriate parameter:
    1. llAll log all requests
    2. llErroneous log erroneous requests
    3. llNone — do not log
17. In UDB Database Mode field, select one of the modes: large, medium, small, ultra light. This mode is for cumulative Check.udb base. See description below

• Large (full copy). This is a default mode. It has been used for simple (not chain) restaurants. It used to be the only possible option if there was no connection to external database. Do not modify the "Full copy" parameter until connection to external database is configured.
• Medium (partial copy). This mode means that, apart from order data, all amounts will be saved in check.udb.
• Small (bills only). The mode is used if you are sure there will be enough cumulative data (several restaurants). In most cases, this mode is recommended to avoid oversized database.
• Ultra-light (common shifts only). check.udb will only contain information on common shifts and links thereto in SQL base.

Note

If decision is made to modify "UDB databases" mode from large to small when connection to SQL is set (i. e., restaurant has been working in this mode for quite a while already) and perform manual re-export of cumulative data, Check.udb file size will decrease automatically. Hence, significant part of data will be deleted from this database, and part of data will be lost for export to SQL databse. Therefore, never perform cumulative data export if Small or Medium UDB database mode is used.
Selected UDB database mode does not affect reference info in any way. Reference info is always fully saved on each server and can be re-exported if needed.

1. In Data Processing section, select restaurants (restaurant is represented by set of POS servers) who's data is supposed to be collected and presented in reports.
2. In config file of references server rk7srv.INI/ report server repsserv.ini, specify parameter

UseSQL=1

Reference server has it in [RefServer] section, report server in [Config] section.

1. Restart references / report server (mandatory).

Note

If anything needs to be modified in some external DB settings, first stop all report servers that use such setting, and launch them with parameter UseSQL=0, then change settings, then stop the server again, and set value 1 again for UseSQL parameter (UseSQL=1). I. e., all changes in external DB settings shall be made while the server is running with parameter UseSQL=0.

If UDB database mode parameter is being modified from large to small or medium, and further download to SQL is performed, most of cumulative info will be deleted from check.udb base. This is critical if external cumulative DB is not used in SQL.

It is not possible to establish simultaneous connection to SQL Server DB for several RK7 roles using the same SQL DB user. RK7 will not save user info in settings of connection to external DB.

Performance optimization

To save disk space and somewhat improve performance, it is recomended to use "Simple" recovery model

Important

It is strongly recommended to familiarize yourself with different SQL recovery models, differences and specific features thereof. See more detail here: http://msdn.microsoft.com/ru-ru/library/ms191253.aspx.

Upon recovery model change, perfrom log file compression. Menu at DB being used: Tasks -> Shrink -> Files, select file type in the next window: "Log".

Possible issues

Issue 1: In some occasions, export to just created SQL Server 2008 database may not go smooth with selected Native Client provider in accordance with p. 3.1.3.
Solution: Select other Microsoft OLE DB Provider for SQL Server, re-created clean DB, and repeat export.

Issue 2: Direct reports are not generated
Solution: For each role having different viewing rights for different object reports (e. g., one role has viewing rights for one object, another role for several objects), create different users at SQL server for the role to generate direct reports, and configure roles with such users in SQL accordingly. See schematic in the figure below.

 Setup of references server  Setup of manager station