1. Setup SQL server and create database

1.1. Downloading

1.1.1. We recommend you download and install the latest express edition of Microsoft SQL server 2012 (free) ;
you may use previous editions like 2008R2 too (for RK7 versions before 7.5.4.1);
with management studio advisable
(64bit: https://www.microsoft.com/betaexperience/pd/SQLEXPDBMT64/enus/ ; 32bit: https://www.microsoft.com/betaexperience/pd/SQLEXPDBMT32/enus/).
1.1.2. Mean that SQL 2008R2 and 2012 requires:
1) PowerShell 1.0
2) .Net framework 3.5 sp1
3) Windows installer 4.5
Usually windows XP has none of them. All this software is available at Microsoft web site for free.

1.1.3. If you have huge restaurant chain, it is recommended to buy full version or use one that customer already had bought.

1.2. Installation


1.2.1. Start .exe installer for MS SQL server

1.2.2. You have to install both SQL server (database engine) and Management studio. Other functions are not requered by UCS software.

1.2.3. Give your new server unique network name

1.2.4. Set 'Database engine' and 'Browser' services to start automatically

1.2.5. Go to second tab and set your main language national collation.
Use only Case-insensitive collations.
1.2.6. Use mixed authentication, set 'sa' user password

1.3. Database creation

1.3.1. Start Management Studio and specify server name (correct format is "computername\servername", this name to be inputed to RK7 settings "Data Source=" string) and authentication login and password (that you set during server installation).

1.3.2. After login select Databases in object explorer and create New database (right click).

1.3.3. Specify database name in "General" tab  (this name to be inputed to RK7 settings "Initial Catalog=" string) and owner (user).
 


1.3.4. On "options" tab specify collation for your language (CI AI types should be used).

1.3.5. For execution click "OK" button.

2. Setup connection between RK7 and SQL

2.1. Setup external database configuration

2.1.1. External database configurations

2.1.1.1. Service -> Data export -> External database configurations: copy and paste preset configuration "Microsoft SQL Server 2008".

2.1.1.2. In the "other" section all parameters leave by default (you have to change them only in case of error or known necessity).

2.1.1.3. Set higher "SQL Timeout" (sec) parameter value if you have got big data or slow connection(s).

2.1.2. Connection string

2.1.2.1.Set connection string in Basic section (separating character in this string is semicolon; no additional gaps).
2.1.2.2. Change parameter "Initial Catalog=" value to your database name created in 1.3.3.
2.1.2.3. Change paramrter "Data Source=" value to your SQL server name used in 1.3.1 (obligatory format is: [computer name]\[SQL server name]).
2.1.2.4. All the other parameters leave by default.
2.1.2.5. For MSSQLSE2012 native client connection string will be the following: Provider=SQLNCLI11.1;Persist Security Info=True;Initial Catalog=RK7DM;Data Source=UCSDENISMOROZOV\SQLEXPRESSDM .

2.1.3. Remote SQL server

2.1.3.1. If you want to use SQL server installed on another PC (or on the same PC but with IP address link in connection string) you have to start SQL Server Browser service.

2.1.3.2. In this case you also have to enable IP protocol for that SQL server.

2.1.3.3. On the opposite side (RK7 server PCs) you should have corresponding data provider installed (by default - SQL Native Client). Mind compatible version of data provider.
2.1.3.4. For manager station PCs where users generate reports with SQL source you have to install data provider also (except 2.4.1. case). Mind 2.3.2. settings for Roles in all cases.
2.1.3.5. For x64 versions enable protocols in both x32 and x64 components (if exist).

2.2. Setup Report servers to use SQL database

2.2.1.1. Options -> OLAP reports -> Reporting servers: set in properties of each report server (by default only one: References server) in section "External Data Base link" all the parameters.

2.2.1.2. In "External Database Settings" parameter choose External database configuration from 2.1.1.
2.2.1.3. In "External Database Username" enter database username (owner) from 1.3.3.
2.2.1.4. In "External Database Password" enter password for corresponding account from 2.2.1.2.
2.2.1.5. Save settings. If an error appeared (licensing related), get proper license.

2.2.2. Recalculating cubes via SQL

2.2.2.1. In  Options -> OLAP reports -> Reporting servers -> [your report server]: in properties "Basic" section change parameter "Cubes source type" to "SQL database" - so that all cubes data will be taken from SQL database (not UDB).
2.2.2.2. You are able to change "UDB database mode" parameter to "Lite" if you have huge storage database (see 2.2.3.).
2.2.2.3. Set "Data wait time interval" parameter value to about 30 minutes (sometimes it happens to be set to zero).

2.2.3. UDB database mode

2.2.3.1. There are 4 check.udb modes in RK7: full, light and intermediate, and ultra light. Check.udb is report server accumulative database.

2.2.3.2. "Large (full copy)" mode is a default one for versions before 7.5.0.0. Use it for regular restaurants (not multirestaurant corporations). This is the only possible variant if no valid external database connection exists. Do not change "Full copy" to something if you have got no valid external database connection!

2.2.3.2.1. "Ultralight" is the default for versions 7.5.0.0+

2.2.3.3. Light UDB mode means that no order data except totals is saved to check.udb. This setting doesn't affect SQL DB data (full information is saved to and available from external DB independently of "UDB database mode" parameter value).

2.2.3.4. Set "UDB database mode" parameter to "Ultra Lite" if you have huge storage database (check.udb), but it's not recommended for regular restaurants. In most cases you'd better use "large (full copy)" until it becomes too large.

2.2.3.5. If you decided to change "UDB database mode" parameter to "Ultra Lite" or "Lite" from higher, set up SQL connection and execute a manual export before parameter change (never execute check.udb manual export after Light or Medium UDB database mode has been set because this will erase most accumulative data). Check.udb file size will reduce automatically.

2.2.3.6. If you changed "UDB database mode" to "Lite", set both this Report server and each cube Source type to "SQL DB" obligatory.

2.2.3.7. Accumulative DB mode does not affect reference DB. Reference DB is always saved on each report server as "full".

2.3. Setup some other references to use SQL

2.3.1. Cube source

2.3.1.1. Define what cubes will be recalculated via SQL database (parameter in Options -> OLAP reports -> Cubes and reports -> [your cube group] -> [your cube] of the manager station).

2.3.2.1. Objects are created, if you set username and password in properties of role: Personal -> Employees -> Properties of the role -> Complementary -> SQL Configuration.

2.3.2.2. You are able to override these settings with rk7man.ini [REFEDIT] section parameter SQLServerLink = "Provider=SQLNCLI11.1;Initial Catalog=RK7;Data Source=PCNAME\SQLEXPRESS".

2.4. Setup RK7 manager station to use SQL-query-based reports

2.4.1. You have to add "ExecSQLRepOnServ = 1" to [REFEDIT] section of rk7man.ini in order to set all data come to manager from report server, to avoid rk7man-sqlsrv connection (since 7.4.21.111).

2.4.2. If you set direct rk7man-sqlsrv connection (ExecSQLRepOnServ = 0), make sure manager station is on the same network and able to connect to SQL server. Mind 2.3.1.

3. Export data to external database

3.1. Edit rk7srv.ini (or repsserv.ini): Set UseSQL=0.
3.2. Restart reference (or report) server to apply settings. Make a simple checking, if this connection is set correctly.
3.3. Service -> Data export -> Export to other DB:

3.3.1. Choose your external database configuration (that you've created in p.1) in settings dialog window [Database connection settings] section ;
3.3.2. Enter user name and password (for access to database) also.

3.3.3. Press Test to test connection. If everything is set correctly, button OK will become active. Otherwise an error appears.
3.3.4. Put necessary ticks (one or several, but never all), by default you have to export only 3 of them: References data, Checks data, Changes log data:

3.3.4.1. References data – export rk7.udb to SQL database;
3.3.4.2. Checks data – export check.udb (accumulative database) to SQL database (mind 3..6.);
3.3.4.3. Changes log data – export rk7log.udb to SQL database;
3.3.4.4. Table descriptions - system table;

3.3.4.5. Create RKeeper 7 objects – create role with password on SQL server (with rights to view) and assign that to report server (if 3.3.4.6., optional);
3.3.4.6. Configure RKeeper 7 parameters – set parameters (username, password) into Options -> Parameters -> Installation -> Reference server -> SQL Server Configuration
(see below).
3.3.4.7. Use Table Objects – special method of export (works slowly, so used only in special cases), do not put tick.
3.3.4.8. Structure only - create empty tables.
3.4. Switching on SQL (main parameter): Set UseSQL=1 in rk7srv.ini (example below). You should restart refserver to apply changes.

[REFSERVER]
Server=myrefserver
UDBFILE=..\..\base\rk7.udb
MainLang=.\RefSrv.ENG
AltLang=
WorkModules=..\..\base\workmods
UseSQL=1

3.5. If you want to change any external database configurations, you should previously stop all the servers that use external databases and start them with UseSQL=0 parameter, change settings, stop again, change back UseSQL=1. So, all the external database settings have to be made during UseSQL=0 server session.
3.6. Never repeat 3.3. "Checks data" manual export if you set Light or Medium UDB database mode (because this will erase most accumulative data).

4. Data exchange during server uptime

4.1. When report server (including reference server) is working it's exporting all reference data changes to corresponding external DB tables according to its settings.
4.2. Accumulative data is also being exported even if no license exists, but data gathering should be enabled for that (see below).

5. Additional checking


5.1. To make sure, that everything is ok and all changes are sent to SQL db, make a simple test: open Menu, make some simple changes (for example, change dish code or name), after that save changes and switch to SQL server. Press F5 (Refresh) or restart SQL Server. In your database open table MENUITEMS (it contains all information about dishes) and make sure, that your changes were successfully sent to SQL database.