Skip to content

Database

Microsoft SQL Server

Info

The guided steps below do not include the base installation of the Microsoft SQL Server, this should be installed and licensed as per your organisation's standards.

Confirm that the server's specifications meet the sizing requirements / recommendations (OS, CPU Cores, Physical memory, Disks including storage type, Network, etc ...)

Relevant Firewall TCP ports should be open in advance of configuration, these are defined in the Server to Server Communication section of this guide.

Pre-requisites

  1. Access the server with Administrator rights.

  2. Disable the Windows Defender Real-Time Protection feature.

  3. Disable the User Access Control feature.

  4. Install the Telnet Client feature.

  5. Validate that the SQL Server instance has been installed with the default name of MSSQLSERVER and not as a named instance.

  6. Ensure that the collation setting SQL_Latin1_General_CP1_CI_AS has been set during installation of the SQL Server product. You can validate this by accessing the SQL Management Studio logged in as the SQL-SA user, right-click on the server's name > Properties.

    Warning

    An incorrect collation setting cannot be amended via a script, this is not a supported approach. Correct collation can only be set at installation time.

  7. Set the Maximum server memory (in MB) for the SQL Server instance. A suggested best practice is no more than 50% of RAM when co-located on the Discover Reporting server; 75% or less when on a dedicated server.

  8. Restart the SQL Server instance.

  9. From the same properties menu as step 6 and 7, configure SQL Server for Mixed Authentication Mode.

  10. Restart the SQL Server instance.

  11. Start the SQL Server Configuration Manager and ensure TCP/IP is enabled for the MSSQLSERVER, if not enabled, right-click and enable.

  12. Ensure the SQL Server instance listens on the default port 1433.

  13. Restart the SQL Server instance if changes have been made.

  14. The folders listed below should be populated post the installation of the Reporting Server:
    SQLData :\SQLData\
    SQL Log :\Tlog\

    Info

    OS permissions for the folder location of the database should be updated to allow for creation of files and folders. Failure to set correct permissions will return an error message of Access Denied during the installation phase.

    Ensure file permissions on the directory give full access to Users.

Install the Databases

  1. Using the utility <Discover_Home_Folder>\Reporting\DiscoverDatabaseManager.exe create the Discover databases. Ensure the application is started with Administrator User rights (right-click Run-as Administrator) and connect using the SQL-SA account.

  2. Once connected go to Mode -> Database Setup -> All databases

  3. Ensure the action Install is selected then select OK

  4. The databases will then go then go through the install scripts automatically.

    During the database creation step, the files below are created on the SQL Server machine:

``` py title="DC_SYSTEM"
<SQL_TARGET_LOCATION>\SQLData\DC_SYSTEM_PRIMARY.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_SYSTEM_LOG.ldf
<SQL_TARGET_LOCATION>\SQLData\DC_SYSTEM_CONFIG.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_SYSTEM_INDEX.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_SYSTEM_APP_LOGS.mdf
```

``` py title="DC_REPORTS"
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_PRIMARY.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_LOG.ldf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_DATA.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_EVENTDATA.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_EVENTINDEX.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_AGGDATA.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_AGGINDEX.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_AGGDATALT.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_AGGINDEXLT.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_AGGKEY.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_AGGKEYINDEX.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_DIMINDEX.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_DIMVALUES.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_PERFDATA.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_PERFINDEX.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_PERFDATALT.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_PERFINDEXLT.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_REPORTS_CANDATA.mdf
```

``` py title="DC_STATISTICS"
<SQL_TARGET_LOCATION>\SQLData\DC_STATISTICS_PRIMARY.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_STATISTICS_LOG.ldf
<SQL_TARGET_LOCATION>\SQLData\DC_STATISTICS_DATA.mdf
<SQL_TARGET_LOCATION>\SQLData\DC_STATISTICS_INDEX.mdf
```