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
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.
|
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"
|
||
|
-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.
|
|
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"
|
||
|
-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>
|
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"
|
||
|
-d
|
thingworx-database-name
|
thingworx
|
Name of database
|
-d thingworx
|