Azure SQL Elastic Pool Installation
Database Setup for Elastic Pool
Prerequisites
Create the following resources. If you are using existing resources, skip and move to the next section.
3. Elastic Pool – Create an elastic pool for your ThingWorx database by running following ThingWorx custom script:
◦ Linux:
./thingworxAzureElasticpoolSetup.sh -n [Elastic Pool Name] -s [SQL server (logical server) name] -g [resource group name]
◦ Windows:
.\thingworxAzureElasticpoolSetup.ps1 -n [Elastic Pool Name] -s [SQL server (logical server) name] -g [resource group name]
Setting up ThingWorx Database and Schema for Elastic Pool
Using Azure CLI, execute the following scripts to create ThingWorx database and ThingWorx schema. These scripts are packaged with the ThingWorx release artifacts.
 |
To display various script parameters and their descriptions, run help on any script as follows:
• For *.sh files (run in terminal):
*.sh --help
• For *.ps1 files (run in powershell) -
Get-Help .\*.ps1
|
|
|
In the examples below, the following resource names are used:
• Resource group name – testresourcegroup
• SQL server (logical server) name – testing-sql-server
• SQL server (logical server) admin name
|
1. Run the following script to create a ThingWorx database in your elastic pool:
◦ Linux:
./thingworxAzureSingleInstanceDBSetup.sh -h [SQL server (logical server) name] -d thingworx -g [Resource group name] --elastic-pool [elastic pool name]
◦ Windows:
.\thingworxAzureSingleInstanceDBSetup.ps1 -h [SQL server (logical server) name] -d thingworx -g [Resource group name] -elastic-Pool [elastic pool name]
|
|
• When prompted about what subscription to use, either type in one of the names listed, or press ENTER to use the subscription that was last set
• To verify the newly-created ThingWorx database in your SQL server logical server, use the following command to list all databases:
az sql db list --resource-group [resource group name] --server [SQL server (logical server) name]
|
2. Run the following script to create the ThingWorx Schema (model, property and data) in the ThingWorx database created above:
◦ Linux:
./thingworxAzureSingleInstanceSchemaSetup.sh -d thingworx -l [SQL server (logical server) admin name] -u twadmin -h [SQL server resource to store the DB in]
◦ Windows:
.\thingworxAzureSingleInstanceSchemaSetup.ps1 -d thingworx -l [SQL server (logical server) admin name] -u twadmin -h [SQL server resource to store the DB in]
|
|
• -l is the SQL Server login user and -u is the ThingWorx database user that will be created and used for ThingWorx.
• When prompted for a password, enter the password for the SQL server (logical server) admin’s password
• To view the details of your ThingWorx database, use following Azure CLI command:
- az sql db show --resource-group [resource group name] --server [logical server name] --name Thingworx
|
Executing the Database Cleanup Script for Elastic Pool
1. Run the following script to drop ThingWorx database:
◦ Linux:
./thingworxAzureSingleInstanceCleanup.sh -n thingworx -g [resource group name] -h [SQL server (logical server) name]
◦ Windows:
.\thingworxAzureSingleInstanceCleanup.ps1 -n thingworx -g [resource group name] -h [SQL server (logical server) name]
2. Run the following script to drop elastic pool.
|
|
To drop elastic pool, it should not contain any databases.
|
◦ Linux:
./thingworxAzureElasticpoolCleanup.sh -n [elastic pool name] -g [resource group name] -h [SQL server (logical server) name]
◦ Windows:
.\thingworxAzureElasticpoolCleanup.ps1 -n [elastic pool name] -g [resource group name] -h [SQL server (logical server) name]
For next steps, go to Configuring ThingWorx with Azure SQL.