ThingWorx Model Definition in Composer > Data Storage > Persistence Providers > MSSQL Database Setup for ThingWorx: Linux
MSSQL Database Setup for ThingWorx: Linux
In MSSQL Server, a login needs to be created before creating a user. The login is created outside the database. Then, a database will be created. Inside the database, a user is created and assigned the login created. Then, a schema is created inside the database. Finally, the owner of the schema will be the user created above. With respect to ThingWorx, the twadmin login will be created manually by SSMS. The remaining steps are performed by executing shell scripts via the command line.
Creating the twadmin login
* 
If you are not using Linux, go to the section, MSSQL Database Setup for ThingWorx: Windows.
1. Open the command prompt in the Linux client.
2. Use the following commands to create a login on the SQL Server:
sqlcmd –s <database-server-name> -u <sql-administrator-username> -p <password>
CREATE LOGIN twadmin WITH PASSWORD = '<password>'
GO
exit
Configuring and Executing the Database Setup Script
1. Obtain and copy the ThingWorx software download folder. This folder contains the scripts described in this section.
* 
Software downloads are available on the PTC eSupport page.
2. Open the command prompt and change the directory to the script folder.
3. Enter the following command:
thingworxMssqlDBSetup.sh -h <server> -i <server-instance> -p <port> -a <database-admin-user-name> -r <password> -l <login-name> -d <thingworx-database-name> -u <thingworx-user-name> -s <schema-name>
Upon execution, it will ask for password of database-admin-user. Enter the password and click enter. Once the script is executed, a database will be created that includes the user with an associated login. This user will be assigned the db_owner role on created database. A schema also will be created if provided on the command line. Authorization of this schema will be given to the created user.
Description of Parameters
Option
Parameter
Default
Description
Example
-h
server
localhost
FQDN or IP of database server
-h 10.0.0.221
-i
server-instance
<blank>
Instance name provided during database installation
-i SQLEXPRESS
-p
port
1433
Port of SQL Server
-p 1433
-a
database-admin-user-name
sa
Admin user name which is having appropriate rights.
-a sa
-r
password
Password@123
Password of database-admin-user
"Password@123"
* 
Passwords with special characters must be surrounded in quotes.
-l
login-name
twadmin
Name of login created above manually.
-l twadmin
-d
thingworx-database-name
thingworx
Name of database
-d thingworx
-u
thingworx-user-name
<same-as-login-name>
Name of user which will be created inside the database
-u twadmin
-s
schema-name
twschema
Name of schema created inside thingworx database.
-s twschema
Configuring and Executing the Model/Data Provider Schema Script
1. In the command prompt, execute the below shell file with the appropriate parameters:
thingworxMssqlSchemaSetup.sh -h <server> -i <server-instance> -p <port> -l <login-name> -r <password> -d <thingworx-database-name> -o <option (all,model,data,property,modelwithproperty)>
On execution of this shell file, it will ask for password of login (created above manually) for each script. Enter the password and click enter each time. On execution of these scripts, all the tables, indexes, and procedures required for setting up the ThingWorx Platform will be created inside the thingworx database with default schema named twschema.
* 
The following expected warning displays when executing the script: Warning! The maximum key length for a clustered index is 900 bytes. The index 'data_table_indexes_pkey' has maximum length of 902 bytes. For some combination of large values, the insert/update operation will fail.
Description of Parameters
Option
Parameter
Default
Description
Example
-h
server
localhost
FQDN or IP of database server
-h 10.0.0.221
-i
server-instance
<blank>
Instance name provided during database installation
-i SQLEXPRESS
-p
port
1433
Port of SQL Server
-p 1433
-l
login-name
twadmin
Name of login created above manually.
-l twadmin
-r
password
Password@123
Password for the login-name
"Password@123"
* 
Passwords with special characters must be surrounded in quotes.
-d
thingworx-database-name
thingworx
Name of database
-d thingworx
-o
option
all
To execute all scripts. Model, data, property, and model with properties will execute.
-o all
Configuring and Executing the Database Cleanup Script
The database cleanup shell script is provided for convenience and development/testing purposes. This is only required when the entire database object and data needs to wiped out and start fresh again.
1. In the command prompt, execute the below shell file with the appropriate parameters:
thingworxMssqlDBCleanup.sh -h <server> -i <server-instance> -p <port> -a <database-admin-user-name> -r <password> -d <thingworx-database-name>
Description of Parameters
Option
Parameter
Default
Description
Example
-h
server
localhost
FQDN or IP of database server
-h 10.0.0.221
-i
server-instance
<blank>
Instance name provided during database installation
-i SQLEXPRESS
-p
port
1433
Port of SQL Server
-p 1433
-a
database-admin-user-name
sa
Admin user name which is having appropriate rights.
-a sa
-r
password
Password@123
Password of database-admin-user
"Password@123"
* 
Passwords with special characters must be surrounded in quotes.
-d
thingworx-database-name
thingworx
Name of database
-d thingworx
Was this helpful?