Manual installation of the VTS SQL database

This guide explains how to manually install the VTS database on a Microsoft^® SQL Server using a script. The required Microsoft^® SQL Server version can be found in the system requirements for the VTS version to be installed.

These instructions apply if you are performing a customized installation of VTS for the first time and prefer not to use the provided Microsoft^® SQL Server Express version.

If you are updating a VTS installation that uses a manually installed SQL Server, you must also update the SQL Server or the corresponding VTS databases manually using scripts. You can find the instructions here Manual update of the VTS SQL database.

Prerequisites

The SQL server is installed (the version corresponds to the system requirements of the VTS version to be installed).
You have the SQL Server login data with sufficient rights to create further logins.
You have permission to execute scripts with the user sa.
You have the necessary SQL scripts for creating the database. These can be found in the VTS installation files (setup) in the Scripts folder.

Preparing the SQL Server

Before the database can be set up using the scripts, a login “wtsnx” must be created in the designated database instance. This is mandatory for the installation!

You will receive the login password from technical support. Please contact our Support for this.

Creating the login “wtsnx” with Microsoft SQL Server Management Studio:
1. Connect to the appropriate SQL Server instance.
2. Navigate to Security --> Logins and add a login with the following details:
  - Login name: wtsnx
  - Login type: SQL Server authentication
  - Password: Contact our Support
  - Enforce password policy: Deactivate this option

Creating the databases for installing the VTS

In this step, all databases required for installing the VTS are set up one after the other using the corresponding scripts.

The following databases must be created:

WTS database: contains person and test data
DTC database: contains information for the browser-based user interface.
WTSKatalog database: contains metadata (only for VTS version 8.28 or older)

The scripts are located in the installation files under Scripts\First Installation.

v8.X.X stands for the corresponding VTS version. For example, if you are installing VTS 8.29, the scripts are labeled v8.29.0.

1. Creating the WTS database

Execute the following scripts one after the other in the exact order specified and observe the notes below on executing the scripts!
1. wts_1_create_database_v8.X.X.sql
2. wts_2_create_tables_v8.X.X.sql
3. wts_3_insert_data_v8.X.X.sql
4. wts_4_optimize_database_v8.X.X.sql

Notes on executing the scripts

Make sure that the database is created with the collation Latin1_General_CI_AI.
Make sure to execute the scripts with sa user (or a user with equivalent rights).
By default, a database with the name wts_deploy is created.
- Do not use a hyphen (‘-’) if you use a different name for the database.
- If a different name is to be used when creating the database, the four SQL scripts must be adjusted accordingly.
Scripts 1, 2, and 4 can generally be executed from Microsoft SQL Server Management Studio.
Execute script 3 via the “sqlcmd” command line. The command for this is:
sqlcmd -S <NameorIPofSQLServer>\<InstanceName> -U sa -P <password> -d <DatabaseName> -i <path to script & scriptname.sql>
- Correct installation can only be guaranteed with the user “sa”!

Example of the installation command for script 3

SQL

sqlcmd  -S localhost\wtsnxexpress 
        -U sa 
        -P 1234
        -d wts
        -i C:\temp\wts_3_insert_data_v8.15.1.sql

2. Creating the DTC database

Execute the following scripts one after the other in the exact order specified and observe the notes below on executing the scripts!

dtc_1_create_database_v8.X.X.sql
dtc_2_create_tables_v8.X.X.sql

Notes on executing the scripts

Make sure that the database is created with the collation SQL_Latin1_General_CP1_CI_AS.

3. Creating the VTS catalog database

This step is only necessary if you are installing with VTS version 8.28 or earlier. This database is no longer necessary as of VTS version 8.29.

In Microsoft^® SQL Server Management Studio:
1. Right-click on Databases and select Append.
2. Click on the Add button and select the file WTSKatalog.mdf.
3. Important: If necessary, enter the name WTSKatalog in the Append as column field.
4. Caution: Change the file path in the Current File Path column to the .mdf and .ldf files in the extracted folder.
Then run the productdb_1_update_schema_v8.X.X.sql script on the WTSKatalogdatabase.

Notes on executing the scripts

Make sure that the database is created with the collation SQL_Latin1_General_CP1_CI_AS.
Make sure that the WTSKatalog database is linked to the wtsnx user.