MSSQL Database Installation and Configuration
The architecture diagram below shows the options for ThingWorx Platform and database setup:
Installing MSSQL Server on the Database Server
 | The steps in the following procedure use MSSQL 2019. Other versions may be supported. Refer to the ThingWorx System Requirements document for the version of ThingWorx you are using. |
1. Obtain the licensed MSSQL Server installation from your software vendor for production deployments. Free editions for development and evaluation purposes can be found at the following locations:
◦ MSSQL 2019 can be found at https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2019?view=sql-server-ver15&viewFallbackFrom=sql-server-2017
◦ MSSQL 2022 can be found at https://www.microsoft.com/en-us/sql-server/sql-server-downloads
2. Refer to the links below for MSSQL Server Installation:
Installing SQL Server Management Studio (Database Admin Client) on Windows Machine
| | SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. Installing SSMS is only required if the administrator does not have access to the database server machine or if you want to separate the client to connect to the database server. Skip this section if a separate client is not necessary. |
1. Select the Installation tab in the top-left corner of the SQL Server Installation Center.
2. Click New SQL Server stand-alone installation or add features to an existing installation.
3. Accept the license terms and click Next.
4. Select Management Tools – Basic and the sub item Management Tools - Complete.
| | The SQL Client Connectivity SDK will be installed by default, whether you checked it or not. |
5. Click Next.
6. The summary for all the installed features and status of installation for each one is shown. Click Close.
Connecting to the Client with the Database Server (Windows)
| | Skip this section if you have not installed SSMS. |
1. Open SSMS from the start menu.
2. Enter the following details:
◦ Server type: Database Engine
◦ Server name: FQDN or IP of the database server.
◦ Authentication: Any authentication mode (Windows authentication/SQL Server Authentication)
◦ Login: sa (in case of SQL Server Authentication)
◦ Password: Password of sa account
Configuring the MSSQL Server Port (Windows)
1. Open the Sql Server Configuration Manager.
2. Navigate to SQL Server Network Configuration > Protocols for <instance_name> > TCP/IP Properties > IP Addresses.
3. Set TCP Port to 1433 for all available IPs.
| | Port 1433 is the default. If a different port is used, use that for all available IPs. |
Installing the ODBC Driver for SQL Server (Linux)
| | This section is only required if the administrator does not have access to the database server machine or if there is a requirement to separate the client to connect to the database server. Skip this section if the ThingWorx database setup scripts need to be executed from a Linux machine. If the “Database Admin Console” is a Windows machine, as per the diagram above, then this section can be skipped. For more information, refer to the following for the installation of ODBC Driver for SQL Server on Linux:https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017. |
Connecting to the Database Server (Linux)
If you did not create a data source during the installation, you must create one. The SQL Server ODBC driver installation creates a sample data source named SQLSERVER_SAMPLE that you can use as a starting point.
1. As root, open /etc/odbc.ini in a text editor.
2. To locate the sample data source, search for [SQLSERVER_SAMPLE].
3. Change the following attribute values:
◦ Server: The host name (or IP address) of the machine where your SQL Server instance is running. To connect to a named instance, use the format machinename\instancename. To connect to a SQL Server Express instance, use the format machinename\SQLEXPRESS.
◦ Port: If the SQL Server instance is listening on the default port, leave this set to 1433. If your database administrator told you to specify a different port, replace 1433 with the new port number. Otherwise, delete 1433.
◦ Database: The name of database to connect.
◦ User: Your SQL Server login name. If you usually connect to SQL Server through your Windows account, use your Windows user name. If the SQL Server instance is running on a machine that is part of a Windows domain, use the format domain\username. Otherwise, type a valid SQL Server user name.
◦ Password: The password for the login name specified by User.
4. Use isql to test the new data source. In the example below, dsn_name is the name of your ODBC data source. If you created a data source during the SQL Server ODBC driver installation, specify that data source name. If you have just edited the sample SQL Server ODBC driver data source, type SQLSERVER_SAMPLE.
cd /usr/local/easysoft/unixODBC/bin
./isql -v dsn_name
./isql -v dsn_name
5. At the prompt, type a select statement or type help to display a list of tables. To exit, press RETURN in an empty prompt line.