Using Azure SQL Database as the Persistence Provider
Azure SQL Deployment Options
Azure SQL Database offers several deployment options:
• As single database with its own set of resources managed via a logical server.
You can deploy a single database to a logical server. A logical server containing single and pooled databases offers most of database-scoped features of SQL Server. This option is optimized for modern application development of new cloud-born applications.
• As a part of a collection of databases known as a managed instance that contains system and user databases and sharing a set of resources.
You can deploy to Azure SQL Database Managed Instances. With Azure SQL Database Managed Instance, Azure SQL Database offers shared resources for databases and additional instance-scoped features. Azure SQL Database Managed Instance supports database migration from on-premises with minimal to no database change. This option provides all of the PaaS benefits of Azure SQL Database but adds capabilities that were previously only available in SQL VMs. This includes a native virtual network (VNet) and near 100% compatibility with on-premises SQL Server.
• As a pooled database in an elastic pool with a shared set of resources managed via a logical server.
Additional information on installing and configuring ThingWorx with AzureSQL is located on the PTC Community site. Videos are also included to assist you with installation.
Azure SQL Purchasing Model
Depending on the deployment model of Azure SQL Database, you can select the purchasing model that fits your needs:
1. (Recommended): vCore-based purchasing model that enables you to choose the exact amount of storage capacity and compute that you need for your workload.
2. DTU-based purchasing model where you can choose bundled compute & storage packages balanced for common workloads.
For more information, refer to https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers
Which Purchasing Model Should I Use for my Production Server?
Refer to the SQL Database FAQ, which discusses these questions in depth - https://docs.microsoft.com/en-us/azure/sql-database/sql-database-faq
Azure SQL Database Setup Options
In Azure SQL, certain resources like resource group, SQL Server logical server etc. need to be setup before a ThingWorx database and schema can be created. These resources can be preexisting or newly created. The following steps explain how to create (or use existing resources) using Microsoft’s Azure Portal and explain how to create ThingWorx database and schema using ThingWorx provided scripts and Microsoft Azure CLI. There are three types of Azure SQL Database deployment options:
• Single
Database Setup Prerequisites
• Set up Azure CLI in your environment from where you plan to run the ThingWorx database/schema setup scripts. For environment-specific instructions, refer to https://docs.microsoft.com/en-us/cli/azure/install-azure-cli?view=azure-cli-latest
• For a Windows environment, Powershell is required. Run Powershell as Administrator and execute the following.
 |
This is a one-time process after downloading the build:
|
Set-ExecutionPolicy RemoteSigned
Unblock-File *.ps1 (after navigating to the install directory)
Unblock-File *.ps1 (after navigating to the install directory)
• For a Windows environment, SQL Server Management Studio (SSMS) must be downloaded. See https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017 to download the latest version.
Installation
The following sections describe how to setup each deployment in Azure and how to connect it to ThingWorx.
• Single