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).
 |
Refer to the ThingWorx System Requirements document for supported versions of MSSQL.
|
|
|
Optimizations to write performance were made in ThingWorx 8.4.0. As a result, some APIs may have reduced performance in MSSQL. Reference the Version 8.4.0 Release Notes for more information.
|
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:
• 2014 (Supported with versions earlier than ThingWorx 8.4) :https://msdn.microsoft.com/en-us/library/ms144275(v=sql.120).aspx
• 2016 (Supported with ThingWorx 8.4 and 8.5): https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2016
• 2017 (Supported with ThingWorx 8.5):https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-2017
MSSQL Server Requirements
Hardware and Software Requirements
The minimum requirements for MS SQL Server Express Edition are located at:
• 2014 (Supported with versions earlier than ThingWorx 8.4) —https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2014
• 2016 (Supported with ThingWorx 8.4)—https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2016
• 2017 (Supported with ThingWorx 8.5): https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2017
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/previous-versions/sql/sql-server-2008-r2/ms378988(v=sql.105) for the 2014 version of MS SQL Server or https://docs.microsoft.com/en-us/sql/database-engine/install-windows/installation-for-sql-server?view=sql-server-2017 for the 2017 version of MS SQL Server.
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
• Amazon EC2 (SQL Server in Amazon EC2)-
Amazon Web Services offers you the flexibility to run Microsoft SQL Server for as much or as little time as you need. You can select from a number of versions and editions, as well as choose between running it on Amazon Elastic Compute Cloud (Amazon EC2) or Amazon Relational Database Service (Amazon RDS).
Using SQL Server on Amazon EC2 gives you complete control over every setting, similar to when it’s installed on premise.
Amazon EC2 Environment Specifics
Use the following facilities in Amazon EC2 for production environments:
◦ Dedicated hardware
◦ Cross Availability Zones network latency can be six times higher than intra-zone network latency. Therefore, all the machines in the “Zone A – Data Center” should reside in a single Availability Zone.
◦ Different Availability Zones and/or a Region for the “Zone B- Data Center” for disaster recovery. Each Region is a separate geographic area and has multiple, isolated locations known as Availability Zones. For more information, reference http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html.
◦ Amazon EC2 VPC. Deploying machines into a Virtual Private Network to get complete control over the virtual networking environment, including a selection of your own IP addresses range, creation of subnets, and configuration of route tables and network gateways. For more information, reference http://aws.amazon.com/vpc/.
▪ A placement group is a logical grouping of Amazon EC2 instances within a single Availability Zone. Using placement groups enables applications to participate in a low-latency, 10 Gbps network. For more information, reference https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/placement-groups.html.
▪ Instances launched into a common cluster placement group are placed into a logical cluster that provides high-bandwidth, low-latency networking between all instances in the cluster. C4, C3, I2, CR1, G2, and HS1 instances support cluster networking, but M3 instances do not. Reference. https://aws.amazon.com/ec2/instance-types/.
▪ Multiple Elastic Network Interfaces (ENI) with multiple IP addresses in the same VPC as the ThingWorx Platform server. Reference https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-eni.html.
• 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.
• S3 Simple Storage Service for backups 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.
|
A guide to deploy MS SQL Server on AWS: https://aws.amazon.com/windows/products/sql/
Configuring a SQL Server AlwaysOn Availability Group
Refer to the following for more information: http://docs.aws.amazon.com/quickstart/latest/sql/part3.html
• Amazon EC2 (Microsoft SQL Server on Amazon RDS)-
• 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:
◦ 2014 (Supported with versions earlier than ThingWorx 8.4) —https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2014
◦ 2016 (Supported with ThingWorx 8.4)—https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2016
◦ 2017 (Supported with ThingWorx 8.5): https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server?view=sql-server-2017
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: