Set-AzSqlDatabase

Module:

Az.Sql

Sets properties for a database, or moves an existing database into an elastic pool.

Syntax

Set-AzSqlDatabase
   [-DatabaseName] <String>
   [-MaxSizeBytes <Int64>]
   [-Edition <String>]
   [-RequestedServiceObjectiveName <String>]
   [-ElasticPoolName <String>]
   [-ReadScale <DatabaseReadScale>]
   [-Tags <Hashtable>]
   [-ZoneRedundant]
   [-AsJob]
   [-LicenseType <String>]
   [-ComputeModel <String>]
   [-AutoPauseDelayInMinutes <Int32>]
   [-MinimumCapacity <Double>]
   [-HighAvailabilityReplicaCount <Int32>]
   [-BackupStorageRedundancy <String>]
   [-SecondaryType <String>]
   [-MaintenanceConfigurationId <String>]
   [-AssignIdentity]
   [-EncryptionProtector <String>]
   [-UserAssignedIdentityId <String[]>]
   [-KeyList <String[]>]
   [-KeysToRemove <String[]>]
   [-FederatedClientId <Guid>]
   [-PreferredEnclaveType <String>]
   [-EncryptionProtectorAutoRotation]
   [-UseFreeLimit]
   [-FreeLimitExhaustionBehavior <String>]
   [-ManualCutover]
   [-PerformCutover]
   [-ServerName] <String>
   [-ResourceGroupName] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Set-AzSqlDatabase
   [-DatabaseName] <String>
   [-MaxSizeBytes <Int64>]
   [-Edition <String>]
   [-ReadScale <DatabaseReadScale>]
   [-Tags <Hashtable>]
   [-ZoneRedundant]
   [-AsJob]
   [-VCore <Int32>]
   [-ComputeGeneration <String>]
   [-LicenseType <String>]
   [-ComputeModel <String>]
   [-AutoPauseDelayInMinutes <Int32>]
   [-MinimumCapacity <Double>]
   [-HighAvailabilityReplicaCount <Int32>]
   [-BackupStorageRedundancy <String>]
   [-SecondaryType <String>]
   [-MaintenanceConfigurationId <String>]
   [-AssignIdentity]
   [-EncryptionProtector <String>]
   [-UserAssignedIdentityId <String[]>]
   [-KeyList <String[]>]
   [-KeysToRemove <String[]>]
   [-FederatedClientId <Guid>]
   [-PreferredEnclaveType <String>]
   [-EncryptionProtectorAutoRotation]
   [-UseFreeLimit]
   [-FreeLimitExhaustionBehavior <String>]
   [-ManualCutover]
   [-PerformCutover]
   [-ServerName] <String>
   [-ResourceGroupName] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Set-AzSqlDatabase
   [-DatabaseName] <String>
   -NewName <String>
   [-AsJob]
   [-BackupStorageRedundancy <String>]
   [-SecondaryType <String>]
   [-MaintenanceConfigurationId <String>]
   [-AssignIdentity]
   [-EncryptionProtector <String>]
   [-UserAssignedIdentityId <String[]>]
   [-KeyList <String[]>]
   [-KeysToRemove <String[]>]
   [-FederatedClientId <Guid>]
   [-PreferredEnclaveType <String>]
   [-EncryptionProtectorAutoRotation]
   [-UseFreeLimit]
   [-FreeLimitExhaustionBehavior <String>]
   [-ManualCutover]
   [-PerformCutover]
   [-ServerName] <String>
   [-ResourceGroupName] <String>
   [-DefaultProfile <IAzureContextContainer>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The Set-AzSqlDatabase cmdlet sets properties for a database in Azure SQL Database. This cmdlet can modify the service tier (Edition), performance level (RequestedServiceObjectiveName), and storage max size (MaxSizeBytes) for the database. In addition, you can specify the ElasticPoolName parameter to move a database into an elastic pool. If a database is already in an elastic pool, you can use the RequestedServiceObjectiveName parameter to move the database out of an elastic pool and into a performance level for single databases.

Examples

Example 1: Update a database to a Standard S0 database

Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Standard" -RequestedServiceObjectiveName "S0"

ResourceGroupName             : ResourceGroup01
ServerName                    : Server01
DatabaseName                  : Database01
Location                      : Central US
DatabaseId                    : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition                       : Standard
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 268435456000
Status                        : Online
CreationDate                  : 7/3/2015 7:33:37 AM
CurrentServiceObjectiveId     : 455330e1-00cd-488b-b5fa-177c226f28b7
CurrentServiceObjectiveName   : S0
RequestedServiceObjectiveId   : 455330e1-00cd-488b-b5fa-177c226f28b7
RequestedServiceObjectiveName :
ElasticPoolName               :
EarliestRestoreDate           :
Tags                          :

This command updates a database named Database01 to a Standard S0 database on a server named Server01.

Example 2: Add a database to an elastic pool

Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -ElasticPoolName "ElasticPool01"

ResourceGroupName             : ResourceGroup01
ServerName                    : Server01
DatabaseName                  : Database01
Location                      : Central US
DatabaseId                    : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition                       : Standard
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 268435456000
Status                        : Online
CreationDate                  : 7/3/2015 7:33:37 AM
CurrentServiceObjectiveId     : d1737d22-a8ea-4de7-9bd0-33395d2a7419
CurrentServiceObjectiveName   : ElasticPool
RequestedServiceObjectiveId   : d1737d22-a8ea-4de7-9bd0-33395d2a7419
RequestedServiceObjectiveName :
ElasticPoolName               : elasticpool01
EarliestRestoreDate           :
Tags                          :

This command adds a database named Database01 to the elastic pool named ElasticPool01 hosted on the server named Server01.

Example 3: Modify the storage max size of a database

Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -MaxSizeBytes 1099511627776

ResourceGroupName             : ResourceGroup01
ServerName                    : Server01
DatabaseName                  : Database01
Location                      : Central US
DatabaseId                    : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition                       : Standard
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 1099511627776
Status                        : Online
CreationDate                  : 8/24/2017 9:00:37 AM
CurrentServiceObjectiveId     : 789681b8-ca10-4eb0-bdf2-e0b050601b40
CurrentServiceObjectiveName   : S3
RequestedServiceObjectiveId   : 789681b8-ca10-4eb0-bdf2-e0b050601b40
RequestedServiceObjectiveName :
ElasticPoolName               :
EarliestRestoreDate           :
Tags                          :

This command updates a database named Database01 to set its max size to 1 TB.

Example 4: Update a existing General Purpose database to Hyperscale service tier

Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Hyperscale" -RequestedServiceObjectiveName "HS_Gen5_2"

ResourceGroupName             : ResourceGroup01
ServerName                    : Server01
DatabaseName                  : Database01
Location                      : Central US
DatabaseId                    : 56246136-839f-4171-80af-4c28142463b1
Edition                       : Hyperscale
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : -1
Status                        : Online
CreationDate                  : 12/6/2020 5:34:16 PM
CurrentServiceObjectiveId     : 00000000-0000-0000-0000-000000000000
CurrentServiceObjectiveName   : HS_Gen5_2
RequestedServiceObjectiveName : HS_Gen5_2
RequestedServiceObjectiveId   :
ElasticPoolName               :
EarliestRestoreDate           : 12/6/2020 5:34:16 PM
Tags                          : {}
ResourceId                    : /subscriptions/xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/ResourceGroup01/providers/Microsoft.Sql/servers/Server01/databases/Database01
CreateMode                    :
ReadScale                     : Enabled
ZoneRedundant                 :
Capacity                      : 2
Family                        : Gen5
SkuName                       : HS_Gen5
LicenseType                   : LicenseIncluded
AutoPauseDelayInMinutes       :
MinimumCapacity               :
ReadReplicaCount              : 1
BackupStorageRedundancy       : Geo

This command updates a database named Database01 from General Purpose to Hyperscale service tier.

Example 5: Update the preferred enclave type of a database to VBS

Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -PreferredEnclaveType "VBS"

ResourceGroupName             : ResourceGroup01
ServerName                    : Server01
DatabaseName                  : Database01
Location                      : Central US
DatabaseId                    : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition                       : Standard
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 1099511627776
Status                        : Online
CreationDate                  : 8/24/2017 9:00:37 AM
CurrentServiceObjectiveId     : 789681b8-ca10-4eb0-bdf2-e0b050601b40
CurrentServiceObjectiveName   : S3
RequestedServiceObjectiveId   : 789681b8-ca10-4eb0-bdf2-e0b050601b40
PreferredEnclaveType          : VBS
RequestedServiceObjectiveName :
ElasticPoolName               :
EarliestRestoreDate           :
Tags                          :

This command updates a database to configure VBS enclave on it

Parameters

-AsJob

Run cmdlet in the background

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-AssignIdentity

Generate and assign a Microsoft Entra identity for this database for use with key management services like Azure KeyVault.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-AutoPauseDelayInMinutes

The auto pause delay in minutes for database (serverless only), -1 to opt out

Type:	Int32
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-BackupStorageRedundancy

The Backup storage redundancy used to store backups for the SQL Database. Options are: Local, Zone, Geo and GeoZone. To know the options supported by each edition of the database, see Get-AzSqlCapability.

Type:	String
Accepted values:	Local, Zone, Geo, GeoZone
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ComputeGeneration

The compute generation to assign.

Type:	String
Aliases:	Family
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ComputeModel

Computed model of Azure Sql database. Serverless or Provisioned

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type:	SwitchParameter
Aliases:	cf
Position:	Named
Default value:	False
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-DatabaseName

Specifies the name of the database.

Type:	String
Aliases:	Name
Position:	2
Default value:	None
Required:	True
Accept pipeline input:	True
Accept wildcard characters:	False

-DefaultProfile

The credentials, account, tenant, and subscription used for communication with azure

Type:	IAzureContextContainer
Aliases:	AzContext, AzureRmContext, AzureCredential
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-Edition

Specifies the edition for the database. The acceptable values for this parameter are:

• None
• Basic
• Standard
• Premium
• DataWarehouse
• Free
• Stretch
• GeneralPurpose
• Hyperscale
• BusinessCritical

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ElasticPoolName

Specifies name of the elastic pool in which to move the database.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-EncryptionProtector

The encryption protector key for SQL Database.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-EncryptionProtectorAutoRotation

The AKV Key Auto Rotation status

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	True
Accept wildcard characters:	False

-FederatedClientId

The federated client id for the SQL Database. It is used for cross tenant CMK scenario.

Type:	Nullable<T>[Guid]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-FreeLimitExhaustionBehavior

Exhaustion behavior of free limit database.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-HighAvailabilityReplicaCount

The number of readonly secondary replicas associated with the database. For Hyperscale edition only.

Type:	Int32
Aliases:	ReadReplicaCount
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-KeyList

The list of AKV keys for the SQL Database.

Type:	String[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-KeysToRemove

The list of AKV keys to remove from the SQL Database.

Type:	String[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-LicenseType

The license type for the Azure Sql database. Possible values are:

• BasePrice - Azure Hybrid Benefit (AHB) discounted pricing for existing SQL Server license owners is applied. Database price will be discounted for existing SQL Server license owners.
• LicenseIncluded - Azure Hybrid Benefit (AHB) discount pricing for existing SQL Server license owners is not applied. Database price will include a new SQL Server license costs.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-MaintenanceConfigurationId

The Maintenance configuration id for the SQL Database.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ManualCutover

Use Manual Cutover for migrating to Hyperscale.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-MaxSizeBytes

The maximum size of the Azure SQL Database in bytes.

Type:	Int64
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-MinimumCapacity

The Minimal capacity that database will always have allocated, if not paused. For serverless Azure Sql databases only.

Type:	Double
Aliases:	MinVCore, MinCapacity
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-NewName

The new name to rename the database to.

Type:	String
Position:	Named
Default value:	None
Required:	True
Accept pipeline input:	False
Accept wildcard characters:	False

-PerformCutover

Trigger Cutover for migrating to Hyperscale.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-PreferredEnclaveType

The preferred enclave type for the Azure Sql database. Possible values are Default and VBS.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ReadScale

If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica. This property is only settable for Premium and Business Critical databases.

Type:	DatabaseReadScale
Accepted values:	Disabled, Enabled
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-RequestedServiceObjectiveName

Specifies the name of the service objective to assign to the database. For information about service objectives, see Azure SQL Database Service Tiers and Performance Levels in the Microsoft Developer Network Library.

Type:	String
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ResourceGroupName

Specifies the name of resource group to which the server is assigned.

Type:	String
Position:	0
Default value:	None
Required:	True
Accept pipeline input:	True
Accept wildcard characters:	False

-SecondaryType

The secondary type of the database if it is a secondary. Valid values are Geo and Named.

Type:	String
Accepted values:	Named, Geo
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ServerName

Specifies the name of the server that hosts the database.

Type:	String
Position:	1
Default value:	None
Required:	True
Accept pipeline input:	True
Accept wildcard characters:	False

-Tags

Key-value pairs in the form of a hash table. For example: @{key0="value0";key1=$null;key2="value2"}

Type:	Hashtable
Aliases:	Tag
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-UseFreeLimit

Use free limit on this database.

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-UserAssignedIdentityId

The list of user assigned identity for the SQL Database.

Type:	String[]
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-VCore

The Vcore number for the Azure Sql database

Type:	Int32
Aliases:	Capacity, MaxVCore, MaxCapacity
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type:	SwitchParameter
Aliases:	wi
Position:	Named
Default value:	False
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

-ZoneRedundant

The zone redundancy to associate with the Azure Sql Database

Type:	SwitchParameter
Position:	Named
Default value:	None
Required:	False
Accept pipeline input:	False
Accept wildcard characters:	False

Inputs

String

Outputs

AzureSqlDatabaseModel

Related Links

• Get-AzSqlDatabase
• New-AzSqlDatabase
• Remove-AzSqlDatabase
• Resume-AzSqlDatabase
• Suspend-AzSqlDatabase
• SQL Database Documentation