Set-PimSqlTier
Set the PIM Manager SQL database tier and its 250 GB maximum size
Purpose
Set the PIM Manager SQL database to the right Standard tier with a 250 GB maximum size, and optionally pin its tier so updates never change it.
When to run it
When the end-of-install check, the environment report or support says the database is below the product tier (S3) or below the 250 GB maximum size (for example after "has reached its size quota"), when an update reports "database is pinned at <tier>, the new default is S3 - not changed", when you want the database on a specific Standard tier, or to pin its tier. Installs and updates apply the same rule by themselves; this script does it now.
Who may run it
| Kind | Role | Scope | Why |
|---|---|---|---|
| azureRbac | SQL DB Contributor | the PIM SQL server (or its resource group); Contributor or Owner work too | Reads the database and changes its tier, its maximum size and its tags. |
| other | browser sign-in to Azure (first-party public client 'Microsoft Azure PowerShell'), the Invardia Support-app session's REST shim (never the Az PowerShell cmdlet), or the Invardia Support app (-AdminAppId + -AdminSecret) | Azure Resource Manager in your tenant | No PowerShell module and no certificate. The Support app's secret is accepted only for Invardia support; a person signs in in the browser. |
What it changes
| Where | Action | What | Permission / role | Scope | Why | Undo |
|---|---|---|---|---|---|---|
| Azure Resource Manager | update | the SQL database -Database (default PimPlatform) on -Server: its tier (sku: Basic, S0, S1 or S2 -> S3, or the Standard tier -Tier; never Basic, never lowered by the plan, a pinned database is not changed without -Tier and -Pin) | the database only | PIM's database is S3 (Standard) by default, so the PIM Manager stays fast on a large tenant; Basic is too small for PIM's store. | Azure portal > the database > Compute + storage: set the previous tier back (a lower tier must fit the data). | |
| Azure Resource Manager | update | the SQL database's maximum size (properties.maxSizeBytes) to 250 GB when it is lower; never lowered | the database only | Raising a tier does not raise the maximum size: a database moved up from Basic keeps a 2 GB limit and stops accepting writes when full. 250 GB is included in the Standard price. | Azure portal > the database > Compute + storage > Data max size: set the previous size back (it must still hold the data). | |
| Azure Resource Manager | update | the SQL database's tag pim-sql-tier-pin = <tier> -- only with -Pin; the database's other tags are kept | the database only | PIM's updater then never changes the database's tier (it still keeps the 250 GB maximum size). | Azure portal > the database > Tags: delete the pim-sql-tier-pin tag. |
What it only reads
- the SQL database (tier, maximum size, status, elastic pool, tags), before and after the change
Parameters
| Parameter | Required | Default | What it does |
|---|---|---|---|
| -SubscriptionId | yes | The subscription that holds the PIM SQL server. | |
| -ResourceGroup | yes | The resource group of the PIM SQL server. | |
| -Server | yes | The SQL server name or its FQDN (sql-x or sql-x.database.windows.net). | |
| -Database | no | PimPlatform | The PIM database. |
| -Tier | no | the plan: below S3 -> S3, S3 and above kept, a pinned database kept | A Standard tier (S0, S1, S2, S3, S4, S6, S7, S9, S12) to set; never Basic. A tier below S3 needs -Pin to stay (the next update raises an unpinned database to S3). Changing the tier of a pinned database needs -Pin. |
| -Pin | no | off | Tag the database pim-sql-tier-pin = its tier, so updates never change its tier. |
| -TenantId | no | Your tenant id, for the browser sign-in or with -AdminAppId + -AdminSecret. Not needed in an Invardia Support-app session. | |
| -AdminAppId | no | Invardia support only: the Invardia Support app's application id (with -AdminSecret and -TenantId) instead of the browser sign-in. | |
| -AdminSecret | no | Invardia support only: the Invardia Support app's secret. | |
| -ReadBackMinutes | no | 20 | How long to wait for the change to be confirmed (a tier change runs online and can take minutes). |
| -PollSeconds | no | 20 | Seconds between the read-backs. |
Preview with -WhatIf
Signs in, reads the database, prints its tier, maximum size and pin, and one "What if:" line with the change it would make (tier, maximum size, pin tag). Nothing is changed.
Undo
Set the previous tier and maximum size back in the Azure portal (the database > Compute + storage), and delete the pim-sql-tier-pin tag if the script added it.
Network
It connects to: login.microsoftonline.com, management.azure.com, localhost (the browser returns the sign-in to a local port on this computer)
Download and run
Run these lines in PowerShell: the first downloads the script into the current folder, the second runs it.
Invoke-WebRequest https://invardia.com/support/pim/Set-PimSqlTier.ps1 -OutFile Set-PimSqlTier.ps1
.\Set-PimSqlTier.ps1 -SubscriptionId <SubscriptionId> -ResourceGroup <ResourceGroup> -Server <Server>To check the download first, compare its SHA-256 with the published one:
SHA-256 0d43d2757d5c66b56765780f9a95dbb58fe4e42227c9c8fbc0616a1c16f39812
Checksums: SHA256SUMS.txt · Version 3.0.70
Before you run it, compare the file's SHA-256 with its line in SHA256SUMS.txt.
Every parameter: Get-Help .\Set-PimSqlTier.ps1 -Full · Source: Set-PimSqlTier.ps1
Every script, identity and permission for your security review: PIM Manager