Known Issue: 3.3 New Database Creation
When creating a new clean-base V3.3 Database there is a confirmed issue that a KPI Index (IX_View_FactSession_Indexed_CL and IDX_KPI) files are not created in the Database. In LUCS Frontend the following error appears:
'IDX_KPI' on table 'Reporting.View_FactSession_indexed (specified in the FROM clause) does not exist.
To circumvent the problem:
- Using DB Updater, create the Database in the latest 3.2 Version first
- The execute "Reporting.usp_Dataloader" → This creates the necessary Index files
- Then update the database from 3.2.08.xxxx to 3.3.12.xxxx or newest DB equivalent
Existing 3.2.x installations seem to be unaffected (upgrade). This note remains until the issue is fixed.
This chapter is for handling a new database. Additional steps are necessary when upgrading from an older version of LUCS. See → How to Upgrade
To prepare the database, go to SQL Server machine and follow the steps:
- Install and launch LUCS Database Updater.
Fill the minimum necessary fields and click Create button.Example of SQL Database Creation
When the creation is completed click Check DB button to get current version of newly created database.
Compare the Current DB version with Update to version and Hotfixes and update the database to the latest version by clicking the Update button.
Some hints on SQL Database Updates
Update Database Version Check
- If Updates are necessary, chances are that you are coming from an existing Database. Make sure to read the Deploy Upgrade chapter first.
- When update buttons are greyed out if no Hotfix or Update is necessary. You can skip to the next step.
- When upgrading from a old DB Version you may update to any version.
- If you are interested in knowing which SQL commands are used for which particular version / hotfix, refer to the "01_Setups\Lucs.Database.Updater\db" folder where your setups are located.
- Close the LUCS Database Updater.
- Create Server Login on the SQL Instance where LUCS DB was created for the AD Security Group "LucsSystem"
- Add the "Luware-System" User Mapping for this Group on the LUCS Database
SSRS SQL Reports
This is an optional step which requires Reporting Services SSRS to be set up.
Service Broker has to be enabled on each SQL Server LUCS database of LUCS versions 2.8 and above.
- If SQL Server works in single instance mode, and the DB updater connects database with db_owner permissions, Service Broker will be enabled automatically during the update.
- If SQL Server works in Always On availability group, Service Broker has to be manually enabled on LUCS database on each instance of SQL Server that hosts an availability replica for the availability group.
To check if Service Broker is enabled on LUCS database use following script (please, replace 'YourDBName' target with LUCS database name):
SELECT is_broker_enabled FROM sys.databases WHERE [name] = 'YourDBName'
→ The execution result 1 means that service broker is enabled, 0 means disabled.
Grant DB Permissions on SQL Server Machine
To grant the permissions follow the steps:
- Go to the machine where SQL Server is installed.
- Open Microsoft SQL Server Management Studio.
- Create a new SQL server login:
- Expand the Security folder in the folder tree.
- Launch context menu of the Logins folder and choose New Login…
In the opened Login – new window handle according to your case:
When DB connection is performed using Integrated Security and PS service and SQL server are running on different machines:
Creation for new SQL Login using Windows Authentication
- Fill the Login Name field and put the $ sign at the end of computer name
- Choose Windows authentication
- Confirm the creation
Case B Case C
- Create a new database user for login:
- In the folder tree expand the Databases folder, expand the folder of your database, expand Security
- Launch context menu of the Users folder and choose New User…
- In the opened Database User – New window in the General tab:
- Choose the created user type option in the User type
- Fill the User name
- Add the created user to the Login name
Fill the Default schema.
Give the necessary rights to the created user via the "User Mapping":
- In the Database User – New window choose the Membership
Choose the Luware System database role.
Set Install Date for Reporting
INSTALL_DATE is a value in [Reporting].[_SysSetup] table. Sessions, which started after or on this date, are included into the reporting. However, old report data may be left out by this rule and thus not appear in the reporting anymore.
To change this date:
Execute a special stored procedure:[mgt].[usp_Reporting_SysSetup_SetInstallDate with the parameter @NewINstallDate.
EXEC [mgt].[usp_Reporting_SysSetup_SetInstallDate] @NewInstallDate = ‘20160101’CODE
After the successful execution, the stored procedure gives the output like below:
New Install date is set to ‘2016-01-01’ 402 Sessions addedCODE
→ This means that install date was shifted back to the past to 2016-01-01 and 402 sessions will be added into reporting after integration is completed.
- To ensure that any changes on Install Date are propagated to reports, run "Reporting.ups_Dataloader" method once
→ SSRS Report data should now reflect the new install date as part of the metrics .
Abvoiding (future) Date-errors in your Reporting
We recommend setting up a Job for this Dataloader method (at an interval like 10-20 min) to ensure that reports always pushed to db with the newest respective date.
If you try to shift the install date forward and integration job has already loaded sessions for that date, setting install date will fail with message like below:
Can not set Install date ‘2016-01-02’, because all Sessions beginning from ‘2016-01-01’ have already been loaded into reporting and will be not reverted.