Skip to main content

managed_instances

Creates, updates, deletes, gets or lists a managed_instances resource.

Overview​

Namemanaged_instances
TypeResource
Idazure.sql.managed_instances

Fields​

The following fields are returned by SELECT queries:

NameDatatypeDescription
idstringFully qualified resource ID for the resource. Ex - /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/{resourceProviderNamespace}/{resourceType}/{resourceName}.
namestringThe name of the resource.
administratorLoginstringAdministrator username for the managed instance. Can only be specified when the managed instance is being created (and is required for creation).
administratorLoginPasswordstringThe administrator login password (required for managed instance creation).
administratorsobjectThe Azure Active Directory administrator can be utilized during instance creation and for instance updates, except for the azureADOnlyAuthentication property. To update the azureADOnlyAuthentication property, individual API must be used.
authenticationMetadatastringThe managed instance's authentication metadata lookup mode. Known values are: "AzureAD", "Paired", and "Windows". (AzureAD, Paired, Windows)
collationstringCollation of the managed instance.
createTimestring (date-time)Specifies the point in time (ISO8601 format) of the Managed Instance creation.
currentBackupStorageRedundancystringThe storage account type used to store backups for this instance. The options are Local (LocallyRedundantStorage), Zone (ZoneRedundantStorage), Geo (GeoRedundantStorage) and GeoZone(GeoZoneRedundantStorage). Known values are: "Geo", "Local", "Zone", and "GeoZone". (Geo, Local, Zone, GeoZone)
databaseFormatstringSpecifies the internal format of instance databases specific to the SQL engine version. Known values are: "AlwaysUpToDate", "SQLServer2022", and "SQLServer2025". (AlwaysUpToDate, SQLServer2022, SQLServer2025)
dnsZonestringThe Dns Zone that the managed instance is in.
dnsZonePartnerstringThe resource id of another managed instance whose DNS zone this managed instance will share after creation.
externalGovernanceStatusstringStatus of external governance. Known values are: "Enabled" and "Disabled". (Enabled, Disabled)
fullyQualifiedDomainNamestringThe fully qualified domain name of the managed instance.
hybridSecondaryUsagestringHybrid secondary usage. Possible values are 'Active' (default value) and 'Passive' (customer uses the secondary as Passive DR). Known values are: "Active" and "Passive". (Active, Passive)
hybridSecondaryUsageDetectedstringHybrid secondary usage detected. Possible values are 'Active' (customer does not meet the requirements to use the secondary as Passive DR) and 'Passive' (customer meets the requirements to use the secondary as Passive DR). Known values are: "Active" and "Passive". (Active, Passive)
identityobjectThe Azure Active Directory identity of the managed instance.
instancePoolIdstringThe Id of the instance pool this managed server belongs to.
isGeneralPurposeV2booleanWhether or not this is a GPv2 variant of General Purpose edition.
keyIdstringA CMK URI of the key to use for encryption.
licenseTypestringThe license type. Possible values are 'LicenseIncluded' (regular price inclusive of a new SQL license) and 'BasePrice' (discounted AHB price for bringing your own SQL licenses). Known values are: "LicenseIncluded" and "BasePrice". (LicenseIncluded, BasePrice)
locationstringThe geo-location where the resource lives. Required.
maintenanceConfigurationIdstringSpecifies maintenance configuration id to apply to this managed instance.
managedInstanceCreateModestringSpecifies the mode of database creation. Default: Regular instance creation. Restore: Creates an instance by restoring a set of backups to specific point in time. RestorePointInTime and SourceManagedInstanceId must be specified. Known values are: "Default" and "PointInTimeRestore". (Default, PointInTimeRestore)
memorySizeInGBintegerMemory size in GB. Minimum value: 28. Maximum value: 870. Minimum and maximum value depend on the number of vCores and service tier. Read more about resource limits: https://aka.ms/mi-resource-limits-api _.
minimalTlsVersionstringMinimal TLS version. Allowed values: 'None', '1.0', '1.1', '1.2'.
pricingModelstringPricing model of Managed Instance. Known values are: "Regular" and "Freemium". (Regular, Freemium)
primaryUserAssignedIdentityIdstringThe resource id of a user assigned identity to be used by default.
privateEndpointConnectionsarrayList of private endpoint connections on a managed instance.
provisioningStatestringProvisioning state of managed instance. Known values are: "Created", "InProgress", "Succeeded", "Failed", and "Canceled". (Created, InProgress, Succeeded, Failed, Canceled)
proxyOverridestringConnection type used for connecting to the instance. Known values are: "Proxy", "Redirect", and "Default". (Proxy, Redirect, Default)
publicDataEndpointEnabledbooleanWhether or not the public data endpoint is enabled.
requestedBackupStorageRedundancystringThe storage account type to be used to store backups for this instance. The options are Local (LocallyRedundantStorage), Zone (ZoneRedundantStorage), Geo (GeoRedundantStorage) and GeoZone(GeoZoneRedundantStorage). Known values are: "Geo", "Local", "Zone", and "GeoZone". (Geo, Local, Zone, GeoZone)
requestedLogicalAvailabilityZonestringSpecifies the logical availability zone Managed Instance is pinned to. Known values are: "NoPreference", "1", "2", and "3". (NoPreference, 1, 2, 3)
restorePointInTimestring (date-time)Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database.
servicePrincipalobjectThe managed instance's service principal.
skuobjectManaged instance SKU. Allowed values for sku.name: GP_Gen5 (General Purpose, Standard-series); GP_G8IM (General Purpose, Premium-series); GP_G8IH (General Purpose, Premium-series memory optimized); BC_Gen5 (Business Critical, Standard-Series); BC_G8IM (Business Critical, Premium-series); BC_G8IH (Business Critical, Premium-series memory optimized).
sourceManagedInstanceIdstringThe resource identifier of the source managed instance associated with create operation of this instance.
statestringThe state of the managed instance.
storageIOpsintegerStorage IOps. Minimum value: 300. Maximum value: 80000. Increments of 1 IOps allowed only. Maximum value depends on the selected hardware family and number of vCores.
storageSizeInGBintegerStorage size in GB. Minimum value: 32. Maximum value: 32768. Increments of 32 GB allowed only. Maximum value depends on the selected hardware family and number of vCores.
storageThroughputMBpsintegerStorage throughput MBps parameter is not supported in the instance create/update operation.
subnetIdstringSubnet resource ID for the managed instance.
systemDataobjectAzure Resource Manager metadata containing createdBy and modifiedBy information.
tagsobjectResource tags.
timezoneIdstringId of the timezone. Allowed values are timezones supported by Windows. Windows keeps details on supported timezones, including the id, in registry under KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. You can get those registry values via SQL Server by querying SELECT name AS timezone_id FROM sys.time_zone_info. List of Ids can also be obtained by executing [System.TimeZoneInfo]::GetSystemTimeZones() in PowerShell. An example of valid timezone id is "Pacific Standard Time" or "W. Europe Standard Time".
typestringThe type of the resource. E.g. "Microsoft.Compute/virtualMachines" or "Microsoft.Storage/storageAccounts".
vCoresintegerThe number of vCores. Allowed values: 4, 6, 8, 10, 12, 16, 20, 24, 32, 40, 48, 56, 64, 80, 96, 128. Supported vCores depends on the selected hardware family and service tier.
virtualClusterIdstringVirtual cluster resource id for the Managed Instance.
zoneRedundantbooleanWhether or not the zone-redundancy is enabled.

Methods​

The following methods are available for this resource:

NameAccessible byRequired ParamsOptional ParamsDescription
getselectresource_group_name, managed_instance_name, subscription_id$expandGets a managed instance.
list_by_instance_poolselectresource_group_name, instance_pool_name, subscription_id$expandGets a list of all managed instances in an instance pool.
list_by_resource_groupselectresource_group_name, subscription_id$expandGets a list of managed instances in a resource group.
listselectsubscription_id$expandGets a list of all managed instances in the subscription.
create_or_updateinsertresource_group_name, managed_instance_name, subscription_id, locationCreates or updates a managed instance.
updateupdateresource_group_name, managed_instance_name, subscription_idUpdates a managed instance.
create_or_updatereplaceresource_group_name, managed_instance_name, subscription_id, locationCreates or updates a managed instance.
deletedeleteresource_group_name, managed_instance_name, subscription_idDeletes a managed instance.
list_by_managed_instanceexecresource_group_name, managed_instance_name, subscription_idnumberOfQueries, databases, startTime, endTime, interval, aggregationFunction, observationMetricGet top resource consuming queries of a managed instance.
list_outbound_network_dependencies_by_managed_instanceexecresource_group_name, managed_instance_name, subscription_idGets the collection of outbound network dependencies for the given managed instance.
failoverexecresource_group_name, managed_instance_name, subscription_idreplicaTypeFailovers a managed instance.
reevaluate_inaccessible_database_stateexecresource_group_name, managed_instance_name, subscription_idReevaluates the inaccessibility state of all managed databases.
refresh_statusexecresource_group_name, managed_instance_name, subscription_idRefresh external governance enablement status.
startexecresource_group_name, managed_instance_name, subscription_idStarts the managed instance.
stopexecresource_group_name, managed_instance_name, subscription_idStops the managed instance.
validate_azure_key_vault_encryption_keyexecresource_group_name, managed_instance_name, subscription_id, tdeKeyUriValidates customer managed key.

Parameters​

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

NameDatatypeDescription
instance_pool_namestringThe name of the instance pool to be retrieved. Required.
managed_instance_namestringThe name of the managed instance. Required.
resource_group_namestringThe name of the resource group. The name is case insensitive. Required.
subscription_idstring
$expandstringThe child resources to include in the response. Default value is None.
aggregationFunctionstringAggregation function to be used, default value is 'sum'. Known values are: "avg", "min", "max", "stdev", and "sum". Default value is None.
databasesstringComma separated list of databases to be included into search. All DB's are included if this parameter is not specified. Default value is None.
endTimestringEnd time for observed period. Default value is None.
intervalstringThe time step to be used to summarize the metric values. Default value is PT1H. Known values are: "PT1H" and "P1D". Default value is None.
numberOfQueriesintegerHow many 'top queries' to return. Default is 5. Default value is None.
observationMetricstringMetric to be used for ranking top queries. Default is 'cpu'. Known values are: "cpu", "io", "logIo", "duration", and "dtu". Default value is None.
replicaTypestringThe type of replica to be failed over. Known values are: "Primary" and "ReadableSecondary". Default value is None.
startTimestringStart time for observed period. Default value is None.

SELECT examples​

Gets a managed instance.

SELECT
id,
name,
administratorLogin,
administratorLoginPassword,
administrators,
authenticationMetadata,
collation,
createTime,
currentBackupStorageRedundancy,
databaseFormat,
dnsZone,
dnsZonePartner,
externalGovernanceStatus,
fullyQualifiedDomainName,
hybridSecondaryUsage,
hybridSecondaryUsageDetected,
identity,
instancePoolId,
isGeneralPurposeV2,
keyId,
licenseType,
location,
maintenanceConfigurationId,
managedInstanceCreateMode,
memorySizeInGB,
minimalTlsVersion,
pricingModel,
primaryUserAssignedIdentityId,
privateEndpointConnections,
provisioningState,
proxyOverride,
publicDataEndpointEnabled,
requestedBackupStorageRedundancy,
requestedLogicalAvailabilityZone,
restorePointInTime,
servicePrincipal,
sku,
sourceManagedInstanceId,
state,
storageIOps,
storageSizeInGB,
storageThroughputMBps,
subnetId,
systemData,
tags,
timezoneId,
type,
vCores,
virtualClusterId,
zoneRedundant
FROM azure.sql.managed_instances
WHERE resource_group_name = '{{ resource_group_name }}' -- required
AND managed_instance_name = '{{ managed_instance_name }}' -- required
AND subscription_id = '{{ subscription_id }}' -- required
AND $expand = '{{ $expand }}'
;

INSERT examples​

Creates or updates a managed instance.

INSERT INTO azure.sql.managed_instances (
tags,
location,
properties,
identity,
sku,
resource_group_name,
managed_instance_name,
subscription_id
)
SELECT 
'{{ tags }}',
'{{ location }}' /* required */,
'{{ properties }}',
'{{ identity }}',
'{{ sku }}',
'{{ resource_group_name }}',
'{{ managed_instance_name }}',
'{{ subscription_id }}'
RETURNING
id,
name,
identity,
location,
properties,
sku,
systemData,
tags,
type
;

UPDATE examples​

Updates a managed instance.

UPDATE azure.sql.managed_instances
SET 
sku = '{{ sku }}',
identity = '{{ identity }}',
properties = '{{ properties }}',
tags = '{{ tags }}'
WHERE 
resource_group_name = '{{ resource_group_name }}' --required
AND managed_instance_name = '{{ managed_instance_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
RETURNING
id,
name,
identity,
location,
properties,
sku,
systemData,
tags,
type;

REPLACE examples​

Creates or updates a managed instance.

REPLACE azure.sql.managed_instances
SET 
tags = '{{ tags }}',
location = '{{ location }}',
properties = '{{ properties }}',
identity = '{{ identity }}',
sku = '{{ sku }}'
WHERE 
resource_group_name = '{{ resource_group_name }}' --required
AND managed_instance_name = '{{ managed_instance_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
AND location = '{{ location }}' --required
RETURNING
id,
name,
identity,
location,
properties,
sku,
systemData,
tags,
type;

DELETE examples​

Deletes a managed instance.

DELETE FROM azure.sql.managed_instances
WHERE resource_group_name = '{{ resource_group_name }}' --required
AND managed_instance_name = '{{ managed_instance_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
;

Lifecycle Methods​

Get top resource consuming queries of a managed instance.

EXEC azure.sql.managed_instances.list_by_managed_instance 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required, 
@numberOfQueries='{{ numberOfQueries }}', 
@databases='{{ databases }}', 
@startTime='{{ startTime }}', 
@endTime='{{ endTime }}', 
@interval='{{ interval }}', 
@aggregationFunction='{{ aggregationFunction }}', 
@observationMetric='{{ observationMetric }}'
;