MSSQL Database Setup for ThingWorx: Windows
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 bat scripts.
 |
If you are not using Windows, go to the section, MSSQL Database Setup for ThingWorx: Linux.
|
Creating the twadmin Login
|
|
Before executing the following steps, verify that the sections Installing SQL Server Management Studio (Database Admin Client) on Windows Machine are performed.
|
You can use the GUI or command line to create the login.
|
|
The GUI steps are below. If you are using the Query Editor in SSMS, enter the following Transact-SQL command:
CREATE LOGIN twadmin WITH PASSWORD = '<password>';
|
1. In SQL Server Management Studio (SSMS), open Object Explorer and expand the folder of the server instance in which to create the new login.
2. Right-click the Security folder, point to New, and click Login.
3. On the General page, enter a name for the new login in the Login name box.
4. Select SQL Server Authentication.
5. Enter a password for the login.
6. Click OK.
Configuring and Executing the Database Setup Script
1. Obtain the database setup script (thingworxMssqlDBSetup.bat) from the ThingWorx software download.
2. Open the command prompt and change the directory to the script folder.
3. Enter the below command:
thingworxMssqlDBSetup.bat -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>
4. Upon execution, it will ask for password of database-admin-user. Enter the password and click enter.
5. Set the collation. To set the collation using SQL Server Management Studio, select Database > Properties > Options and select Latin1_General_100_CS_AS_SC from the drop down menu.
6. Set the following properties to ON:
◦ READ_COMMITTED_SNAPSHOT
◦ ALLOW_SNAPSHOT_ISOLATION
Once the script is executed, a database will be created with a user inside the database and a login associated with the user. This user will be assigned the db_owner role on created database. A schema will also 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 that has appropriate rights.
|
-a sa
|
||
|
-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 that will be created inside the database.
|
-u twadmin
|
||
|
-s
|
schema-name
|
twschema
|
Name of schema created inside thingworx database.
|
-s twschema
|
||
|
-r
|
password
|
Password@123
|
Password of database-admin-user
|
"Password@123"
|
Configuring and Executing the Model/Data Provider Schema Script
1. In the command prompt, execute the bat file (thingworxMssqlSchemaSetup.bat) with the appropriate parameters (listed in the table below):
thingworxMssqlSchemaSetup.bat -h <server> -i <server-instance> -p <port> -l <login-name> -r <password> -d <thingworx-database-name> -o <option (all,model,data,property,modelwithproperty)>
Upon execution of the bat file, it will ask for password of login (manually created above) for each script. Enter the password and click enter each time.
|
|
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.
|
Upon execution of these scripts, all the tables, indexes, procedures, etc. required for setting up the ThingWorx platform will be created inside the thingworx database with default schema twschema
|
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
|
||
|
-d
|
thingworx-database-name
|
thingworx
|
Name of database
|
-d thingworx
|
||
|
-o
|
option
|
all
|
To execute all scripts / model / data / property / model with property will execute.
|
-o all
|
||
|
-r
|
password
|
Password@123
|
Password for the login-name
|
"Password@123"
|
Configuring and Executing the Database Cleanup Script
The database cleanup bat script (thingworxMssqlDBCleanup.bat) is provided for convenience and for development/testing purposes. Performing the steps in this section is only required if the entire database object and data needs to be wiped out so that you can start from scratch.
1. In the command prompt, execute the bat file with the appropriate parameters (listed in the table below):
thingworxMssqlDBCleanup.bat -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
|
||
|
-d
|
thingworx-database-name
|
thingworx
|
Name of database
|
-d thingworx
|
||
|
-r
|
password
|
Password@123
|
Password of database-admin-user
|
"Password@123"
|