i4connected Knowledgebase 5.6

Configuring the SQL Database

Abstract

The following article follows the steps required to properly set up the SQL database for using it with the i4connected portal or server.

The following article follows the steps required to properly set up the SQL database for using it with the i4connected portal and/or server.

The configuration of the database consists in restoring the SQL Database and Analysis Services database backups. Once the databases are restored, the connection must be restored between them. Other access and security settings will be made in order to ensure a proper functionality.

The SQL Database backup (.bak) and the Analysis Services Database backup (.abf) files are required in order to proceed with this guide. Because there is no default place where the Database backups are located, both Database and Analysis Services backup files should be requested from the development team.

  1. For a new setup, take the i4_Empty_V55.bak file and restore it to the database server with a speaking name, as follows:

    1. Open the SQL Server Management Studio and connect to the Database Engine server.

      Clipboard01.jpg
    2. Right-click on the Databases to open the contextual menu. Select Restore Database....

      Clipboard01.jpg
    3. In the Restore Database dialog, select Device and browse for the backup (.bak) file.

      Clipboard01.jpg
    4. In the Select backup devices dialog, make sure that the Backup media type is set to File and click Add to browse for the backup file.

      Clipboard01.jpg
    5. The Locate Backup File dialog will point you to the exact location of the backup file (copied earlier to the backup folder of the SQL Server). Select the backup file and press OK.

      Confirm the Select backup devices dialog, as well. If the dialog doesn't point directly to the backup file location, use the left-side tree structure to locate the backup file on your hard drive.

      Capture976.jpg
    6. Back in the Restore Database window, another option is needed, if the backup to be restored will overwrite an existing Database. Hence, select the Options page from the left-side menu and enable the Overwrite the existing database (WITH REPLACE) option.

      Clipboard01.jpg
    7. Click OK to start the restore process. A dialog will confirm the success of the restore operation.

  2. Next, open the i4connected Migrations folder and identify the WEBfactory.DWH.Data.Migrations.exe.config file. Open the file with an editor tool and configure the connection string to point to the local SQL.

  3. Run the migration tool to set up the initial Database, as follows:

    1. Open the Command Prompt application with Administrative rights;

    2. Run the -b webfactory -u flags for WEBfactory brand and unattended migration.

Using the addInitData.sql script to setup a basic i4connected database
Abstract

Check out this article and learn how to setup a basic i4connected database having the minimum amount of data required to run the application.

Using the addInitData.sql script adds an initial set of data to a new i4connected database, created with the Migration tool (available in any i4connected built archive).

Tip

The addInitData.sql script should be requested from the WEBfactory Support team.

This script introduces the minimum amount of data required to run both the i4connected Server and the WebApp.

After running this script, a basic i4connected database will be available, with no other entities beyond a SuperAdministrator user and the Setup page with all the necessary tiles, allowing one to start the Server and log into the WebApplication. Upon login the user can start customizing the installation as desired.

Important

This script is designed for i4connected version 5.6 (and later). This script WILL NOT WORK on earlier i4connected versions!

  1. Create a fresh empty database, as follows:

    1. Deploy the i4connected version you need to configure, using any desired procedure.

      Important

      It is important to also deploy the Migration tool from the i4connected drop archive.

    2. Edit the connection string of the Migration tool in WEBfactory.DWH.Data.Migrations.exe.config, by setting the value of the Initial Catalog to the name of the new database you want to create.

      Warning

      If you use the name of an existing database, it means you won't create a new one!

    3. Using command line and the i4connected Migration tool, run the standard migration command against a new database name.

      For example: WEBfactory.DWH.Data.Migrations.exe -b webfactory -u.

      The Migration tool will see that the database does not exist and create it from scratch.

      Tip

      As more than 200 migrations will be applied, the operation will last a while!

    4. Once completed, the new database is created empty.

      Warning

      If you try to start the Server or log in the Webapp, immediately after this steps, you will encounter critical errors, since no data is defined.

  2. Edit and run the addInitData.sql script, as follows:

    1. Open the addInitData.sql script with a text editor and locate the @DBNAME variable at the top of the script.

    2. Replace the value of the @DBNAME variable with the name of your new database (the empty one created at step 1). Make sure to respect the existing quoting in the script's @DBNAME variable.

    3. Using your preferred method (SQL Management Studio, Azure Data Studio, command line, etc), run the addInitData.sql script against the SQL DB Server running your empty database.

    4. Check the script output to see all the performed steps.

  3. Configure the connection strings in your new i4connected deployment, using the new DB name as Initial Catalog.

  4. Run the Server and open the WebApp. You should be able to log in using the below listed credentials to start customizing the Webapp.

    Tip

    Your default credentials:

    • Username: admin

    • Password: WEBfactory2020!

  5. After logging into the i4connected WebApp, identify the Import Export Translations tile and click it to open the Import panel.

    Import_translations_panel.jpg

    Upload the translations file via the Browse button and select the desired Import type, choosing from the three available options:

    • Append - the translations will be appended to the current data.

    • Overwrite with merge - the current data will be overwritten and merged with the imported translations,

    • Overwrite with replace - the current data will be overwritten and replaced with the imported translations.

    To proceed with the translations import, click the Save button.

    Tip

    The Translations file should be requested from the WEBfactory Support team.