ThingWorx Model Definition and Composer > Data Storage > Persistence Providers > Using Azure SQL Server as the Persistence Provider
  
Using Azure SQL Server as the Persistence Provider
Overview
ThingWorx provides the option to choose a persistence provider for your value stream, stream, and data table data. In ThingWorx 8.4, Azure SQL is another persistence provider option in ThingWorx.
Azure SQL Database is a relational database-as-a-service (DBaaS) hosted in 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 and guarantees no data loss and high percentage of data availability. Azure SQL Database comes with built in high availability, disaster recovery, and upgrade for the database.
Refer to https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas for more information on Azure SQL Database and its features.
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.
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:
Managed Instance
Single
Elastic Pool
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)
Installation
The following sections describe how to setup each deployment in Azure and how to connect it to ThingWorx.
Managed Instance
Single
Elastic Pool