Using Microsoft SQL Server as the Persistence Provider
Overview
SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). SQL Server should be managed using normal database management procedures defined by a DBA.
 |
Refer to the ThingWorx System Requirements for supported versions of MSSQL.
|
The following topics are included in this section:
SQL Server Editions Comparison
There are several SQL Server editions that you can choose from to best fit your data solution: Enterprise, Standard, or Express. The table below compares the three choices for editions of the MS SQL Server.
Generally, the SQL Standard Edition is suitable for production environments, as it supports most of the features required.
If your production environment requires High Availability features such as AlwaysOn and other features such as In-Memory OLTP, Table and index partitioning, SQL Enterprise Edition is recommended.
|
Feature
|
Enterprise Edition
|
Standard Edition
|
Express Edition
|
|---|---|---|---|
|
Maximum relational Database size
|
524 PB
|
524 PB
|
10 GB
|
|
Maximum memory utilized (per instance of SQL Server Database Engine)
|
Operating system maximum
|
128 GB
|
1 GB
|
|
AlwaysOn Availability Groups
|
Yes
|
No
|
No
|
|
Backup compression
|
Yes
|
Yes
|
No
|
|
Database Mirroring
|
Yes
|
Yes
|
Witness only
|
|
Log Shipping
|
Yes
|
Yes
|
No
|
|
Multi-instance support
|
50
|
50
|
50
|
|
Encrypted Backup
|
Yes
|
Yes
|
No
|
|
Table and index partitioning
|
Yes
|
No
|
No
|
|
Parallel query processing on partitioned tables and indices
|
Yes
|
No
|
No
|
For detailed descriptions about features supported by different editions of SQL Server, refer to the following:
MSSQL Server Requirements
Hardware and Software Requirements
The minimum requirements for MS SQL Server Express Edition are located at:
For SQL Server Sizing Resources for production, refer to the Capacity Planning section.
Operating System Requirements
Microsoft Windows is the only supported operating system for MSSQL Server. Linux may be supported in the future, although the choice of the operating system does not affect ThingWorx. For more information, follow the link in the list above to the Microsoft Hardware and Software Requirements page for the version of MS SQL Server that you are using.
ThingWorx Connectivity to MSSQL Server
ThingWorx connects to the MSSQL database using a SQL JDBC driver. For more information on JDBC connection properties, refer to the following https://docs.microsoft.com/en-us/sql/database-engine/install-windows/installation-for-sql-server?view=sql-server-2017.
These connection properties can be configured in the platform-settings.json file that is available in the ThingWorx software download. The platform_settings.json file contains the following connection properties:
• jdbcUrl: jdbc:sqlserver://localhost:1433;databaseName=thingworx;applicationName=Thingworx;"
Connection properties:
ServerName — The computer running SQL Server. – localhost
PortNumber — The port where SQL Server is listening. -1433
DatabaseName —The name of the database to connect to – ThingWorx
applicationName — The application name, Thingworx
Planning for MSSQL Installation
The MSSQL installation can be installed in the following environments:
• On Premise
The SQL Server Installation Wizard provides a single feature tree for installation of all SQL Server components so that you do not have to install them individually.
For more information, refer to https://msdn.microsoft.com/en-us/library/ms143219(v=sql.120).aspx
• Hardware Virtual Machine (HVM) uses a new network virtualization stack that provides higher I/O performance and lower CPU utilization compared to traditional implementations. In order to take advantage of Enhanced Networking, an HVM AMI should be launched in a VPC, and install the appropriate driver.
• SSD Ephemeral Storage (EBS volumes are not recommended). SSD-backed instance storage is optimized for very high random I/O performance.
• Amazon Simple Storage Service (Amazon S3) https://console.aws.amazon.com/s3/home?region=us-east-1#
|
|
If you are not logged in to your Amazon account, following this link results in a request for you to log in.
|
Configuring a SQL Server AlwaysOn Availability Group
Refer to the following for more information: http://docs.aws.amazon.com/quickstart/latest/sql/part3.html
• Microsoft Azure-
Microsoft Azure SQL Database is a managed cloud database for app developers that makes building and maintaining applications easier and more productive.
SQL Azure enables organizations to store relational data in the cloud and quickly scale the size of their databases up or down as business needs change. Data is hosted, managed, and provisioned in Microsoft data centers.
Organizations can build applications on-premises and move them to SQL Azure or build them on Windows Azure and keep the data in the cloud. SQL Azure supports SQL Server’s Transact-SQL (T-SQL) query language, offers built-in support for high availability and fault tolerance and allows for data to be synchronized between on-premises SQL Server and cloud databases.
Refer to the following for additional details:
Capacity Planning
To determine if MSSQL Server is the right solution for your data, refer to the sizing and planning sections from the following Microsoft documentation:
• Hardware and Software Requirements:
Understand SQL Server and IOPS
When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server.
Before installing SQL, we recommend that you benchmark the I/O subsystem by using the SQLIO disk subsystem benchmark tool.
For information about how to use the SQLIOSim utility and SQLIO for stress testing, refer to the TechNet video, Stress testing using SQLIOSIM and SQLIO.
Choose disk types
The disk types that you use in the system can affect reliability and performance.
Use solid state drives (SSD) for storage in SQL Server.
Choose RAID types
Although RAID is not a part of SQL Server, implementing RAID can directly affect the way SQL Server performs. RAID levels 0, 1, and 5 are typically used with SQL Server.
Compute Capacity Limits by Edition of SQL Server
To learn more about Compute Capacity Limits, refer to the following:
Maximum Capacity Specifications for SQL Server
To learn more about Maximum Capacity Limits, refer to the following:
Pre-deployment I/O Best Practices
For SQL Server best practices, refer to the following article:
MSSQL Limitations
In MSSQL, the CONCAT function requires at least two arguments and less than 254 arguments.
ThingWorx uses the CONCAT function to execute MSSQL query for instances like add or update data table entries. You will get the following error if there are more than 254 fields in a datashape which are passed in the CONCAT function.
com.microsoft.sqlserver.jdbc.SQLServerException: The concat function requires 2 to 254 arguments.
To learn more about the CONCAT Function, see CONCAT (Transact-SQL) - SQL Server.