Page History

...

By default, SQL Server works in the «Read Commited» mode, which blocks sections 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 sections can only cause problems if different users are recording data in the same place. Reading data is never blocked sectioned and never blocks sections no one.

To enable versioning mode, run the script:

...

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. 
   Image Added

2. As a result, the «Connection string» will

   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² 

   "

   
   

...

1. Info
   Data Source = a name or an IP address of the computer where SQL Server is installed. Using the server instance

...

1. name, like 192.168.190.150\SQLEXPRESS, is also possible.

2. Then, perform an upload to the SQL DB database, using the setting you have just created.
3. Open Service > Data export > Export to other DBreference, and select the setting created in p. 5. Specify "Username" and "Password".the password in the «ADOBasePass» field and the username in the «ADOBaseUser» field.
4. In the «Export parameters» sectionIn "Export parameters" block, leave default checkmarks if an upload is performed to a clean SQL DB is performed.
5. Press "Check"Click «Test». If the connection is successful, "ОК" the «ОК» button shall become active. Press it. Data export to SQL DB will start. SQL are being created at this moment.
6. If the upload is successful, "Data export settings" the «Data export settings» window will close. There will be no additional messages.
7. Then, setup set up the references ( or report ) server by licensing it and selecting the setting created in p. 5.
8. Open Settings > OLAP Reports > Report Serversreference, select a required server, and configure it as described below.
9. License the references /or report server as described in in the «Setup of references serverReferences Server» article
10. In Connection to external DBgroup:
11. .
12. In the «External Data Base Link» section, perform the following actions:
    1. In the «External Database Settings» field, select the
    In "Setup of connection to externals DB" field, select
    1. setting created in p. 5 .
    2. In
    "External DB username"
    1. the «External Database Username» field, specify the SQL DB username.
    2. In
    "External DB user password"
    1. the «External Database Username» field, specify the SQL DB user password.
13. Specify the same settings in Reference logsgroupin the «Reference logs» section:
    1. In "Setup of connection to externals DB" the «External Database Settings» field, select the setting created in p. 5 .
    2. In "External DB username" the «External Database Username» field, specify the SQL DB username.
    3. In "External DB user password" the «External Database Username» field, specify the SQL DB user password.
       Image Added
14. In "Base" groupthe «Basic» section perform the following actions:
    • 1. In
    "Cubes data source"
    • 1. the «Cubes Source Type» field, select
    "SQL DB"
    • 1. the «SQL database» parameter
      2. In
    "Request logging"
    • 1. the «Queries Logging Type» field, select
    appropriate
    • 1. a suitable parameter:
    llAll 
    • 1. 1. llAll — to log all
    requests
    • 1. 1. queries
    llErroneous 
    • 1. 1. llErroneous — to log erroneous
    requests
    • 1. 1. queries
         2. llNone —
    do
    • 1. 1. not to log
      2. In
    UDB
    • 1. the «UDB Database
    Mode
    • 1. Mode» field, select one of the modes: large, medium,
    small
    • 1. lite, ultra
    light
    • 1. lite. This mode is for cumulative Check.udb base.
    See
    • 1. Find the description below.
    • Large

...

1. • (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 an external database. Do not modify the

...

1. • «Full copy» parameter until the connection to an external database is configured.
   • Medium

...

1. • (partial copy). This mode means that, apart from order data, all amounts will be saved in check.udb.

...

1. • Lite (

...

1. • checks 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 an oversized database.

   • Ultra

...

1. • lite (

...

1. • global shifts only). check.udb will only contain information on common shifts and links thereto in the SQL base.
     

...

1. • 
     

     Note
     If

...

1. • you decide to modify

...

1. • the UDB

...

1. • database mode from

...

1. • Large to

...

1. • Lite when the connection to SQL is set (i. e., the restaurant has been working in this mode for quite a while already) and to perform manual re-export of cumulative data, Check.udb file size will decrease automatically. Hence, a significant part of the data will be deleted from this database, and only a part of the data will be

...

1. • exported to the SQL

...

1. • database. Therefore, never perform cumulative data export if

...

1. • Lite or Medium UDB database mode is used.

...

1. • A 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.

2. In Data Processing the «Data Gathering» section, select the restaurants (the restaurant is represented by a set of POS cash servers) who's whose data is supposed to be collected and presented in reports.

3. In the config file of the references

   server 

   server rk7srv.INI

   /

   or the report

   server 

   server repsserv.ini, specify the parameter
   

   Code Block
   UseSQL=1

...

1. For the reference server in the [RefServer]

...

1. section,

...

1. and in the [Config]

...

1. section for the report server.

2. Restart the references / or report server (mandatory).

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

Performance optimization

To save the disk space and somewhat somehow improve performance, it is recomended recommended to use "Simple" the «Simple» recovery modelImportant

...

...

Image Added

After detail here: http://msdn.microsoft.com/ru-ru/library/ms191253.aspx.Upon recovery model change, perfrom perform log file compression. Menu at Right-click on the DB being used: Tasks -> Shrink -> Files, and select the «Log» file type in the next window: "Log".

Image Added

Image Added

Possible

...

problems

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

Issue Problem 2:  Direct reports are not generated.
Solution: For each role having different viewing rights for different object reports (ef. gex., 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 a schematic in the figure below.

 Setup of references server  Setup of manager stationImage Added