Community Tip - When posting, your subject should be specific and summarize your question. Here are some additional tips on asking a great question. X
Hi everyone,
In case you’re looking for more reasons to appreciate the power of Azure, today we’re answering 10 frequently asked questions around how and why to use Azure SQL with ThingWorx.
You likely already know that we support multiple persistence providers, like Azure SQL, InfluxDB, H2, MSSQL and PostgreSQL, for you to store and persist your ThingWorx data. Here’s an up-close-and-personal look into why we recommend Azure SQL.
1. What is Azure SQL?
Azure SQL is a relational database hosted in the Azure cloud and is a fully managed Platform as a Service (PaaS) Database Engine. Azure SQL Database engine is based on the Enterprise Edition of SQL Server. The Azure platform fully manages every Azure SQL Database with a high percentage of data availability and guarantees no data loss. Azure SQL Database comes with built-in high availability, disaster recovery, and upgrade for the database. Refer to Microsoft's Azure SQL Database - Platform as a Service documentation for more information on Azure SQL Database and its features.
2. What are the top 3 reasons to use Azure SQL with ThingWorx?
Ease of Use and Management: Azure SQL greatly reduces the need to manage database resources for ThingWorx. It helps to reduce your total cost of ownership for managing database resources for ThingWorx by managing virtual machines, operating system, database software, upgrades, high availability, and backups for you, so you can focus on building your IoT solution. It provides unmatched scale and high availability for compute and storage without sacrificing performance. With Azure SQL, you can scale your application on demand with up to 99.95% availability.
Hybrid Deployments: ThingWorx supports multiple persistence providers to store IoT data for different use cases. Please refer to the ThingWorx Model and Data Best Practices Guide to learn more. If you’re already using Microsoft SQL Server with ThingWorx on premise, then you can use Azure SQL for your cloud deployments of ThingWorx-based IIoT solutions in hybrid scenarios. This allows you to reduce development time—develop once and deploy anywhere through a common programming surface area across Azure SQL (on cloud) and SQL Server (on premise). You can leverage ThingWorx federation to run ThingWorx in different deployment topologies.
If needed, you can also accelerate your on-premise SQL Server migrations without changing the application code by leveraging Managed Instance. Use the Azure Hybrid Benefit Savings Calculator to calculate your TCO. Enjoy additional deployment flexibility with Single Database for SQL applications created in the cloud or Elastic Pool for multi-tenant applications.
Security and Compliance: Azure SQL Database meets the most stringent compliance standards with built-in auditing and information protection technology. With its availability in different regions, its best suited for Government cloud and sovereign cloud. Please see this link to check for the latest update on Azure product availability by region. You can also get multi-layered security provided by Microsoft across physical datacenters, infrastructure, and operations and will always have the latest SQL Server capabilities in the cloud, with no patching or upgrading.
It also offers protection to your databases from malicious acts with fine-grained access controls, Always Encrypted technology, and advanced threat protection capabilities.
3. How do I configure ThingWorx for Azure SQL?
From ThingWorx Foundation platform version 8.4 release onwards, ThingWorx provides you an option to choose Azure SQL as a persistence provider to store your value stream, stream, and data table data. This Help Center provides all the details and steps to help you set up Azure SQL with ThingWorx.
You can run ThingWorx with Azure SQL either by downloading the ThingWorx Azure SQL .WAR file or by running it as containerized ThingWorx Docker images by downloading ThingWorx Dockerfiles. For reference, see the below image to help you download ThingWorx 8.4 artifacts.
Here’s a video demonstrating how to install ThingWorx.
Here’s a second video that walks you through configuring ThingWorx with Azure SQL.
4. Which versions of Azure SQL does ThingWorx support?
Consult the latest system requirements guide here to learn which versions of Azure SQL ThingWorx supports.
5. What database deployment options do I have?
In Azure, you can have your SQL Server workloads running in a hosted infrastructure (IaaS) or running as a hosted service (PaaS). Within PaaS, you have multiple deployment options and service tiers within each deployment option, such as Single Database, Elastic Pool sets, and managed Instance. ThingWorx supports all the deployment options to setup Azure SQL as a persistence provider. You can refer to this link on Azure SQL Database versus SQL Server to help you choose an option that works best for your business needs.
6. Why would I want to use an PaaS database?
Service tools and built-in features enable a more streamlined and automated means of controlling and operating your database. The need for constant manual control and tweaking of information, recovery tools, compliance and updates is now configured and built into Azure SQL for a more hands-off approach to your storage database. Here is a table to inform you on how Azure SQL PaaS helps.
7. Which features are new to Azure SQL 2019?
Azure SQL now offers Always Encrypted data transfer through TLS and auto-failover for managed instance deployment to enable transparent and coordinated failover of multiple databases. Azure SQL also offers a data migration assistant, which detects compatibility issues that can impact functionality when upgrading your database. For more information on features and functionality, see Microsoft SQL documentation or Azure SQL’s latest release notes.
8. Is there any guidance available to help me migrate to Azure SQL?
Yes! Microsoft’s Database Migration Service enables seamless migration to Managed Instance with downtime measured in minutes. The process is highly automated and risk-free while streamlining the transition of SQL Server and on-Microsoft database systems such as Oracle to Azure SQL Database. You can learn more about upgrading to Azure SQL here.
9. What purchasing models are available to me?
vCore based (recommended) - For customers that prioritize flexibility and control, this model offers scaling of compute, storage, and I/O resources independently to optimize price based on need. The customer chooses the hardware and service tier based on high-availability design, storage type, fault-isolation methods, and I/O ranges.
DTU based - Three distinct available tiers are differentiated based directly upon compute, memory, and I/O resources. This model bundles the measures together for customers who want pre-configured or simplified resource options. You can refer to more pricing and purchase options here.
10. What should I do if I need technical support?
If you select Azure SQL as your persistence provider, then all support requests related to configuring Azure SQL can be logged through PTC Technical Support at https://support.ptc.com or by calling 1-800-477-6435.
You may also want to use the PTC Community to learn and collaborate with the growing PTC developer community. For all other requests related to database management, troubleshooting, monitoring, and administration, we encourage you to reach out to Microsoft directly.
Let me know what you think in the comments below.
Stay connected,
Kaya
Hello, we started with On-premises installation of Thingworx with PostgreSQL Database. Now it seems that migration from PostgreSQL to Azure SQL is not possible ? So how can we migrate to a cloud deployment of Thingworx in Azure?
Hi @TomDecock,
If you're using PostgreSQL, then I'd like to let you know that we are planning to support Azure Database for PostgreSQL early next year 2022 as ThingWorx Foundation supported persistence provider. Let us know if that helps with your move to ThingWorx on Azure?