This chapter is for handling a new database. Additional steps are necessary when upgrading from an older version of TM. See → How to Upgrade

Create Database

To prepare the database, go to SQL Server machine and follow the steps:

  1. Install and launch Database Updater.

  2. Fill the minimum necessary fields and click Create button.

    Example of SQL Database Creation

  3. When the creation is completed click Check DB button to get current version of newly created database.

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

    • If Updates are necessary, chances are that you are coming from an existing Database. Make sure to read the Deploy Upgrade > "Update Database" 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.

    Update Database Version Check
  5. Close the Database Updater.
  6. Create Server Login on the SQL Instance where LUCS DB was created for the AD Security Group "LucsSystem"
  7. Add the "Luware-System" User Mapping for this Group on the LUCS Database

Service Broker

Service Broker has to be enabled on each SQL Server 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 Database on each instance of SQL Server that hosts an availability replica for the availability group.

To check if Service Broker is enabled on Database use following script (please, replace 'YourDBName' target with the Database name):

SELECT is_broker_enabled
FROM sys.databases
WHERE [name] = 'YourDBName'
SQL

→ 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:

  1. Go to the machine where SQL Server is installed.
  2. Open Microsoft SQL Server Management Studio.
  3. Create a new SQL server login:
    1. Expand the Security folder in the folder tree.
    2. Launch context menu of the Logins folder and choose New Login…

    3. In the opened Login – new window handle according to your case:

      Case A

      When DB connection is performed using Integrated Security and PS service and SQL server are running on different machines:

      • Fill the Login Name field and put the $ sign at the end of computer name
      • Choose Windows authentication
      • Confirm the creation

      Creation for new SQL Login using Windows Authentication
      Case B

      When DB connection is performed using Integrated Security and PS service and SQL server are running on one machine:

      • Fill the Login Name field with NT AUTHORITY\NETWORK SERVICE value
      • Choose Windows authentication
      • Confirm the creation

      Creation for new SQL Login using NT Authority
      Case C

      When DB connection is performed using SQL server login and password:

      • Fill the Login name
      • Choose SQL Server authentication
      • Fill Password and Confirm Password fields
      • Confirm the creation

      Creation for new SQL Login using direct Server Login
  4. Create a new database user for login:
    1. In the folder tree expand the Databases folder, expand the folder of your database, expand Security
    2. Launch context menu of the Users folder and choose New User…
  5. In the opened Database User – New window in the General tab:
    1. Choose the created user type option in the User type
    2. Fill the User name
    3. Add the created user to the Login name

    4. Fill the Default schema.

      New database user creation

  6. Give the necessary rights to the created user via the "User Mapping":

    1. In the Database User – New window choose the Membership

    2. Choose the Luware System database role.

      Giving necessary rights to the created user

Setting Install_DATE

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:

  1. Execute a special stored procedure:[mgt].[usp_Reporting_SysSetup_SetInstallDate with the parameter @NewINstallDate.

    Example

    EXEC [mgt].[usp_Reporting_SysSetup_SetInstallDate] @NewInstallDate = ‘20160101’
    CODE


    → Result: 
    After the successful execution, the stored procedure gives the output like below:

    New Install date is set to ‘2016-01-01’

402 Sessions added
    CODE

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

  2. 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.
CODE