ALTER DATABASE (Transact-SQL)

Panoramica: SQL Server

In SQL Server, questa istruzione consente di modificare un database oppure i file e i filegroup associati al database. ALTER DATABASE aggiunge o rimuove file e file group da un database, modifica gli attributi di un database o i suoi file e file group, modifica la collazione del database e imposta le opzioni del database. Non è possibile modificare gli snapshot del database. Per la modifica delle opzioni di database associate alla replica, usare sp_replicationdboption.

A causa della lunghezza, la sintassi di ALTER DATABASE è separata in più articoli.

Sintassi

-- SQL Server Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<file_and_filegroup_options>::=
  <add_or_modify_files>::=
  <filespec>::=
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::=

<option_spec>::=
{
  | <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <external_access_option>
  | <FILESTREAM_options>
  | <HADR_options>
  | <parameterization_option>
  | <query_store_options>
  | <recovery_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <termination>
  | <temporal_history_retention>
  | <data_retention_policy>
  | <compatibility_level>
      { 170 | 160 | 150 | 140 | 130 | 120 | 110 | 100 }
}

Argomenti

database_name

Nome del database da modificare.

CORRENTE
Si applica a: SQL Server 2012 (11.x) e versioni successive.

Specifica che il database corrente in uso deve essere modificato.

MODIFICA NOME = new_database_name

Rinomina il database con il nome specificato come new_database_name.

COLLATE collation_name

Specifica le regole di confronto per il database. In collation_name è possibile usare nomi di regole di confronto di Windows o SQL. Se omesso, al database vengono assegnate le regole di confronto dell'istanza di SQL Server.

Quando si creano database con regole di confronto diverse da quelle predefinite, i dati nel database rispettano sempre le regole di confronto specificate. Per SQL Server, quando si crea un database indipendente, le informazioni del catalogo interno vengono mantenute usando le regole di confronto predefinite di SQL Server, ovvero Latin1_General_100_CI_AS_WS_KS_SC.

Per maggiori informazioni sui nomi di collation Windows e SQL, vedi COLLATE.

< > delayed_durability_option ::=

Si applica a: SQL Server 2014 (12.x) e versioni successive.

Per maggiori informazioni, consulta ALTER DATABASE SET le opzioni e Controlla la Durabilità delle Transazioni.

< >file_and_filegroup_options::=

Per altre informazioni, vedere ALTER DATABASE Opzioni file e filegroup.

Osservazioni:

Per rimuovere un database, usa DROP DATABASE.

Per ridurre le dimensioni di un database, usare DBCC SHRINKDATABASE.

L'istruzione ALTER DATABASE deve essere eseguita in modalità commit automatico (modalità di gestione delle transazioni predefinita) e non è consentita in una transazione esplicita o implicita.

Lo stato di un file di database, ad esempio online o offline, viene mantenuto indipendentemente dallo stato del database. Per altre informazioni, vedere Stati del file. Lo stato dei file all'interno di un filegroup determina la disponibilità dell'intero filegroup. Un filegroup è disponibile se tutti i file in esso inclusi sono online. Se un filegroup è offline, qualsiasi tentativo di accesso al filegroup da un'istruzione SQL ha esito negativo e viene generato un errore. Per la compilazione di piani delle query per istruzioni SELECT, Query Optimizer evita gli indici non cluster e le viste indicizzate presenti in filegroup offline. Ciò consente la corretta esecuzione di tali istruzioni. Se tuttavia il filegroup offline contiene l'indice cluster o heap della tabella di destinazione, l'istruzione SELECT avrà esito negativo, Inoltre, qualsiasi INSERTistruzione , UPDATEo DELETE che modifica una tabella con qualsiasi indice in un filegroup offline ha esito negativo.

Quando un database si trova nello stato RESTORE, la maggior parte ALTER DATABASE delle istruzioni ha esito negativo. Un'alternativa consiste nell'impostare le opzioni di mirroring del database. Un database potrebbe trovarsi nello stato RESTORE durante un'operazione di ripristino attiva o quando un'operazione di ripristino di un database o di un file di log non riesce a causa di un file di backup danneggiato.

La cache dei piani per l'istanza di SQL Server viene cancellata quando si imposta una delle opzioni seguenti.

• COLLATE
• MODIFICA FILE GROUP DEFAULT
• MODIFICA FILEGROUP SOLA_LETTURA
• MODIFICA GRUPPO_DI_FILE LETTURA_SCRITTURA
• MODIFY_NAME
• OFF-LINE
• IN LINEA
• VERIFICA_PAGINA
• SOLO_LETTURA
• LETTURA_SCRITTURA

La cancellazione della cache dei piani comporta la ricompilazione di tutti i piani di esecuzione successivi e può causare un peggioramento improvviso e temporaneo delle prestazioni di esecuzione delle query. Per ogni archivio cache cancellato nella cache dei piani, il log degli errori di SQL Server contiene il messaggio informativo seguente: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Questo messaggio viene registrato ogni cinque minuti per tutta la durata dello scaricamento della cache.

La cache dei piani viene inoltre scaricata negli scenari seguenti:

• L'opzione AUTO_CLOSE di un database è impostata su ON. Se il database non viene utilizzato da alcuna connessione utente, neanche come riferimento, tramite l'attività in background viene effettuato il tentativo di chiusura e di arresto automatici del database.
• Vengono eseguite diverse query su un database contenente opzioni predefinite. Successivamente, il database viene eliminato.
• Viene eliminato uno snapshot del database per un database di origine.
• Viene ricompilato correttamente il log delle transazioni per un database.
• Viene ripristinato un backup del database.
• Viene scollegato un database.

Modificare le regole di confronto del database

Prima di applicare regole di confronto diverse a un database, verificare che siano soddisfatte le condizioni seguenti:

• È l'unico attualmente in uso nel database.
• Nessun oggetto associato a schema dipende dalle regole di confronto del database.

Se gli oggetti seguenti, che dipendono dalle regole di confronto del database, esistono nel database, l'istruzione ALTER DATABASE database_name COLLATE ha esito negativo. SQL Server restituisce un messaggio di errore per ogni oggetto che blocca l'azione ALTER :

• Funzioni definite dall'utente e viste create con SCHEMABINDING
• Colonne calcolate
• Vincoli CHECK
• Funzioni con valori di tabella che restituiscono tabelle contenenti colonne di tipo carattere con regole di confronto ereditate dalle regole di confronto predefinite del database

Le informazioni sulle dipendenze per le entità non associate a schemi vengono aggiornate automaticamente quando vengono modificate le regole di confronto del database.

La modifica delle regole di confronto del database non crea duplicati tra i nomi di sistema per gli oggetti di database. Se i nomi duplicati derivano dalle regole di confronto modificate, gli spazi dei nomi seguenti possono causare l'errore di una modifica delle regole di confronto del database:

• Nomi di oggetti, come stored procedure, tabelle, trigger o viste
• Nomi schema
• Entità, come gruppi, ruoli o utenti
• Nomi di tipi di dati scalari, come i tipi di dati di sistema e definiti dall'utente
• Nomi di cataloghi full-text
• Nomi di colonne o parametri in un oggetto
• Nomi di indici in una tabella

I nomi duplicati risultanti dalle nuove regole di confronto causano l'esito negativo dell'azione di modifica e SQL Server restituisce un messaggio di errore che specifica lo spazio dei nomi in cui è stato trovato il duplicato.

Visualizzare le informazioni sul database

Per restituire informazioni su database, file e filegroup, è possibile usare viste del catalogo, funzioni di sistema e stored procedure di sistema.

Autorizzazioni

È richiesta l'autorizzazione ALTER per il database.

Esempi

R. Modificare il nome di un database

Nell'esempio seguente il nome del database AdventureWorks2025 viene modificato in Northwind.

USE master;
GO
ALTER DATABASE AdventureWorks2022
Modify Name = Northwind ;
GO

B. Modificare le regole di confronto di un database

L'esempio seguente crea un database denominato testdb con le SQL_Latin1_General_CP1_CI_AS regole di confronto e quindi modifica le regole di confronto del testdb database in COLLATE French_CI_AI.

Si applica a: SQL Server 2008 (10.0.x) e versioni successive.

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Contenuto correlato

• CREATE DATABASE
• DATABASEPROPERTYEX (Transact-SQL)
• DROP DATABASE (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• EVENTDATA (Transact-SQL)
• sys.sp_configure (Transact-SQL)
• sys.sp_spaceused (Transact-SQL)
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• sys.data_spaces (Transact-SQL)
• sys.filegroups (Transact-SQL)
• sys.master_files (Transact-SQL)
• Database di sistema

Panoramica: database SQL

Nel database SQL di Azure usare questa istruzione per modificare un database. Usare questa istruzione per modificare il nome di un database, modificare l'obiettivo di servizio e l'edizione del database, creare un join con o rimuovere il database da un pool elastico, impostare le opzioni di database, aggiungere o rimuovere il database come database secondario in una relazione di replica geografica e impostare il livello di compatibilità del database.

A causa della lunghezza, la sintassi di ALTER DATABASE è separata in più articoli.

ALTER DATABASE
L'articolo corrente fornisce la sintassi e le informazioni correlate per modificare il nome e altre impostazioni di un database.

ALTER DATABASE SET Opzioni
Fornisce la sintassi e le informazioni correlate per la modifica degli attributi di un database utilizzando le SET opzioni di ALTER DATABASE.

ALTER DATABASE Livello di compatibilità
Fornisce la sintassi e le informazioni correlate per le SET opzioni di ALTER DATABASE che sono relative ai livelli di compatibilità del database.

Sintassi

-- Azure SQL Database Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | MODIFY ( <edition_options> [, ... n] ) [WITH MANUAL_CUTOVER]
  | MODIFY BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
  | SET { <option_spec> [ ,... n ] WITH <termination>}
  | ADD SECONDARY ON SERVER <partner_server_name>
    [WITH ( <add-secondary-option>::=[, ... n] ) ]
  | PERFORM_CUTOVER
  | REMOVE SECONDARY ON SERVER <partner_server_name>
  | FAILOVER
  | FORCE_FAILOVER_ALLOW_DATA_LOSS
}
[;]

<edition_options> ::=
{

  MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 ... 1024 ... 4096 GB }
  | EDITION = { 'Basic' | 'Standard' | 'Premium' | 'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale'}
  | SERVICE_OBJECTIVE =
       { <service-objective>
       | { ELASTIC_POOL (name = <elastic_pool_name>) }
       }
}

<add-secondary-option> ::=
   {
      ALLOW_CONNECTIONS = { ALL | NO }
     | BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
     | SERVICE_OBJECTIVE =
       { <service-objective>
       | { ELASTIC_POOL ( name = <elastic_pool_name>) }
       | DATABASE_NAME = <target_database_name>
       | SECONDARY_TYPE = { GEO | NAMED }
       }
   }

<service-objective> ::={ 'Basic' |'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12'
      | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15'
      | 'BC_DC_n'
      | 'BC_Gen5_n' 
      | 'BC_M_n' 
      | 'GP_DC_n'
      | 'GP_Gen5_n' 
      | 'GP_S_Gen5_n' 
      | 'HS_DC_n'
      | 'HS_Gen5_n'
      | 'HS_S_Gen5_n'
      | 'HS_MOPRMS_n' 
      | 'HS_PRMS_n' 
      | { ELASTIC_POOL(name = <elastic_pool_name>) }
      }

<option_spec> ::=
{
    <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
  | <compatibility_level>
    { 170 | 160 | 150 | 140 | 130 | 120 | 110 | 100 }

}

Argomenti

database_name

Nome del database da modificare.

CORRENTE
Specifica che il database corrente in uso deve essere modificato.

MODIFICA NOME = new_database_name

Rinomina il database con il nome specificato come new_database_name. Nell'esempio seguente il nome di un database db1 viene modificato in db2:

ALTER DATABASE db1
    MODIFY Name = db2 ;

MODIFY (EDITION = ['Basic' | 'Standard' | 'Premium' |' GeneralPurpose' | 'BusinessCritical' | 'Hyperscale'])

Modifica il livello di servizio del database.

Nell'esempio seguente l'edizione viene modificata in Premium:

ALTER DATABASE current
    MODIFY (EDITION = 'Premium');

MODIFY BACKUP_STORAGE_REDUNDANCY = ['LOCALE' | 'ZONE' | 'GEO']

Modifica la ridondanza di archiviazione dei backup per il ripristino temporizzato e dei backup con conservazione a lungo termine (se configurati) del database. Le modifiche vengono applicate a tutti i backup futuri eseguiti. I backup esistenti continuano a usare l'impostazione precedente.

Per applicare la residenza dei dati quando si crea un database usando T-SQL, usare LOCAL o ZONE come input per il parametro BACKUP_STORAGE_REDUNDANCY.

MODIFICA (MAXSIZE = [100 MB | 500 MB | 1 | 1024...4096] GB)

Specifica le dimensioni massime del database. Le dimensioni massime devono essere conformi al set valido di valori per la proprietà EDITION del database. La modifica delle dimensioni massime del database può causare la modifica dell'edizione del database.

Modello DTU

¹ P11 e P15 consentono a MAXSIZE fino a 4 TB con 1.024 GB di dimensioni predefinite. P11 e P15 possono usare fino a 4 TB di spazio di archiviazione incluso senza addebiti aggiuntivi. Nel livello Premium, MAXSIZE maggiore di 1 TB è attualmente disponibile nelle seguenti aree: Stati Uniti orientali 2, Stati Uniti occidentali, US Gov Virginia, Europa occidentale, Germania centrale, Asia sud-orientale, Giappone orientale, Australia orientale, Canada centrale e Canada orientale. Per altre informazioni sulle limitazioni delle risorse per il modello DTU, vedere Limiti delle risorse DTU.

Il valore MAXSIZE per il modello DTU, se specificato, deve essere un valore valido presente nella tabella precedente per il livello di servizio.

Per limiti come le dimensioni massime dei dati e tempdb le dimensioni nel modello di acquisto vCore, vedere gli articoli relativi ai limiti delle risorse per i database singoli o i limiti delle risorse per i pool elastici.

Se non viene impostato alcun valore MAXSIZE quando si usa il modello vCore, il valore predefinito è 32 GB. Per altre informazioni sulle limitazioni delle risorse per il modello vCore, vedere Limiti delle risorse vCore.

Le seguenti regole vengono applicate agli argomenti MAXSIZE ed EDITION:

• Se l'opzione EDITION è specificata ma MAXSIZE non è specificata, viene utilizzato il valore predefinito per l'edizione. Ad esempio, l'opzione EDITION è impostata su Standard e MAXSIZE non viene specificata, quindi MAXSIZE viene impostata automaticamente su 250 MB.
• Se non vengono specificati né MAXSIZE né EDITION, EDITION viene impostato su Utilizzo generico e MAXSIZE viene impostato su 32 GB.

MODIFY (SERVICE_OBJECTIVE = <obiettivo> del servizio)

Specifica le dimensioni di calcolo e l'obiettivo di servizio.

SERVICE_OBJECTIVE

Specifica le dimensioni di calcolo( note anche come obiettivo del livello di servizio o SLO).

• Per il modello di acquisto DTU: S0, S1S2, S3, , S4S6S7S9S12P1P2P4P6, . P11P15 Per trovare il numero di DTU assegnato a ogni dimensione di calcolo, vedere i limiti delle risorse per i database singoli o i limiti delle risorse per i pool elastici DTU.
• Per il modello di acquisto vCore scegliere il livello e specificare il numero di vCore da un elenco predefinito di valori, dove il numero di vCore è n. Vedere i limiti delle risorse per i database singoli vCore o i limiti delle risorse per i pool elastici vCore.
  • Ad esempio:
  • GP_Gen5_8 per utilizzo generico, calcolo con provisioning, serie Standard (Gen5), 8 vCore.
  • GP_S_Gen5_8 per utilizzo generico, calcolo serverless, serie Standard (Gen5), 8 vCore.
  • HS_Gen5_8 per Hyperscale, calcolo con provisioning, serie Standard (Gen5), 8 vCore.
  • HS_S_Gen5_8 per Hyperscale, calcolo serverless, Serie Standard (Gen5), 8 vCore.

Ad esempio, l'esempio seguente modifica l'obiettivo di servizio di un database di livello Premium nel modello di acquisto DTU in P6:

ALTER DATABASE <database_name>
    MODIFY (SERVICE_OBJECTIVE = 'P6');

Ad esempio, l'esempio seguente modifica l'obiettivo di servizio di un database di calcolo con provisioning nel modello di acquisto vCore in GP_Gen5_8:

ALTER DATABASE <database_name>
    MODIFY (SERVICE_OBJECTIVE = 'GP_Gen5_8');

Database_Name

Solo per database SQL di Azure Hyperscale. Il nome del database che verrà creato. Usato solo dalle repliche denominate hyperscale del database SQL di Azure, quando SECONDARY_TYPE = NAMED. Per altre informazioni, vedere Repliche secondarie Hyperscale.

SECONDARY_TYPE

Solo per database SQL di Azure Hyperscale. GEO specifica una replica geografica, NAMED specifica una replica denominata. Il valore predefinito è GEO. Per altre informazioni, vedere Repliche secondarie Hyperscale.

Per le descrizioni degli obiettivi di servizio e altre informazioni sulle dimensioni, le edizioni e le combinazioni degli obiettivi di servizio, vedere Confrontare modelli di acquisto basati su vCore e DTU di database SQL di Azure, limiti delle risorse DTU e limiti delle risorse vCore. Il supporto per gli obiettivi di servizio PRS è stato rimosso.

Quando SERVICE_OBJECTIVE non viene specificato, il database secondario viene creato allo stesso livello di servizio del database primario. Se si specifica SERVICE_OBJECTIVE, il database secondario viene creato al livello specificato. Il valore SERVICE_OBJECTIVE specificato deve rientrare nella stessa edizione dell'origine. Ad esempio, non è possibile specificare S0 se l'edizione è Premium.

MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (nome = <elastic_pool_name>)

Per aggiungere un database esistente a un pool elastico, impostare SERVICE_OBJECTIVE del database su ELASTIC_POOL e specificare il nome del pool elastico. È anche possibile usare questa opzione per modificare il database in un pool elastico all'interno dello stesso server. Per maggiori informazioni, si veda I pool elastici consentono di gestire e dimensionare più database nel database SQL di Azure. Per rimuovere un database da un elastic pool, si usa ALTER DATABASE impostare il SERVICE_OBJECTIVE su una singola dimensione di calcolo del database (obiettivo del servizio).

AGGIUNGI UN SECONDARIO AL SERVER <partner_server_name>

Crea un database secondario con replica geografica usando lo stesso nome in un server partner e trasformando il database locale in un database primario con replica geografica, quindi avvia la replica asincrona dei dati dal database primario nel nuovo database secondario. Se esiste già un database con lo stesso nome nel database secondario, il comando non riesce. Il comando viene eseguito nel database master sul server che ospita il database locale, il quale diventa il database primario.

CON ALLOW_CONNECTIONS { TUTTI | NO }

Quando non viene specificato ALLOW_CONNECTIONS, viene impostato su ALL per impostazione predefinita. Se è impostata su ALL, si tratta di un database di sola lettura che consente a tutti gli account di accesso con le autorizzazioni appropriate di connettersi.

ELASTIC_POOL (nome = <elastic_pool_name>)

Quando non viene specificato ELASTIC_POOL, il database secondario non viene creato in un pool elastico. Se si specifica ELASTIC_POOL, il database secondario viene creato nel pool specificato.

RIMUOVERE IL SECONDARIO SUL SERVER <partner_server_name>

Rimuove il database secondario con replica geografica nel server specificato. Il comando viene eseguito nel database master sul server che ospita il database primario.

failover

Alza di livello il database secondario nella relazione con replica geografica in cui il comando viene eseguito perché il database diventi primario e abbassa di livello il database corrente perché diventi il nuovo database secondario. Durante questo processo, la modalità di replica geografica passa temporaneamente dalla modalità asincrona alla modalità sincrona. Durante il processo di failover:

1. Il database primario non accetta più nuove transazioni.
2. Tutte le transazioni in sospeso vengono scaricate nel database secondario.
3. Il database secondario diventa il database primario e la replica geografica asincrona viene avviata con il database primario precedente e il nuovo database secondario.

Questa sequenza impedisce la perdita di dati. I due database non sono disponibili generalmente per un periodo di tempo compreso tra 0 e 25 secondi, il tempo necessario per lo scambio dei ruoli. L'operazione totale non richiederà più di un minuto circa. Se il database primario non è disponibile quando viene eseguito questo comando, il comando ha esito negativo e viene visualizzato un messaggio di errore che indica che il database primario non è disponibile. Se il processo di failover non viene completato e viene bloccato, è possibile usare il comando force failover e accettare la perdita di dati e quindi, se è necessario recuperare i dati persi, chiamare devops (CSS) per recuperare i dati persi.

FORCE_FAILOVER_ALLOW_DATA_LOSS

Alza di livello il database secondario nella relazione con replica geografica in cui il comando viene eseguito perché il database diventi primario e abbassa di livello il database corrente perché diventi il nuovo database secondario. Usare questo comando solo quando il database primario corrente non è più disponibile. È progettato solo per il ripristino di emergenza, quando il ripristino della disponibilità è critico e alcune perdite di dati sono accettabili.

Durante un failover forzato:

1. Il database secondario specificato diventa immediatamente il database primario e inizia ad accettare nuove transazioni.
2. Quando il database primario originale si riconnette al nuovo database primario, viene eseguito un backup incrementale nel database primario originale e il database primario originale diventa un nuovo database secondario.
3. Per ripristinare i dati da questo backup incrementale nel database primario precedente, è necessario usare la metodologia DevOps/CSS.
4. Se sono presenti altri database secondari, questi vengono riconfigurati automaticamente in modo da diventare secondari del nuovo database primario. Questo processo è asincrono e potrebbe verificarsi un ritardo fino al completamento di questo processo. Quando la riconfigurazione è completa, i database secondari continuano a essere database secondari del database primario precedente.

MANUAL_CUTOVER

Avviare la conversione del database nel livello di servizio Hyperscale con l'opzione per avviare manualmente il cutover quando si è pronti. Applicabile solo quando il livello di servizio viene convertito in Hyperscale. Per avviare il cutover, usare PERFORM_CUTOVER. Per altre informazioni, vedere Convertire un database esistente in Hyperscale.

Monitorare lo stato di avanzamento della conversione in Hyperscale con sys.dm_operation_status.

PERFORM_CUTOVER

Avvia il cutover quando la conversione del database nel livello Hyperscale è in stato WaitingForCutover. Applicabile solo quando il livello di servizio viene convertito in Hyperscale avviato con l'argomento MANUAL_CUTOVER.

Monitorare lo stato di avanzamento della conversione in Hyperscale con sys.dm_operation_status. Per altre informazioni, vedere Convertire un database esistente in Hyperscale.

Osservazioni:

Per rimuovere un database, usa DROP DATABASE. Per ridurre le dimensioni di un database, usare DBCC SHRINKDATABASE.

L'istruzione ALTER DATABASE deve essere eseguita in modalità commit automatico (modalità di gestione delle transazioni predefinita) e non è consentita in una transazione esplicita o implicita.

La cancellazione della cache dei piani comporta la ricompilazione di tutti i piani di esecuzione successivi e può causare un peggioramento improvviso e temporaneo delle prestazioni di esecuzione delle query. Per ogni archivio cache cancellato nella cache dei piani, il log degli errori di SQL Server contiene il messaggio informativo seguente: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Questo messaggio viene registrato ogni cinque minuti per tutta la durata dello scaricamento della cache.

La cache delle procedure viene scaricata anche nello scenario seguente: vengono eseguite diverse query su un database contenente opzioni predefinite. Successivamente, il database viene eliminato.

Visualizzare le informazioni sul database

Per restituire informazioni su database, file e filegroup, è possibile usare viste del catalogo, funzioni di sistema e stored procedure di sistema.

Autorizzazioni

Per modificare un database, un account di accesso deve essere l'account di accesso amministratore del server (creato al momento del provisioning del server logico database SQL di Azure), l'amministratore di Microsoft Entra del server, un membro del ruolo del database dbmanager in master, un membro del ruolo del database db_owner nel database corrente o dbo del database. Microsoft Entra ID (in precedenza Azure Active Directory).

Per scalare i database tramite T-SQL, ALTER DATABASE sono necessari i permessi. Per dimensionare i database tramite il portale di Azure, PowerShell, l'interfaccia della riga di comando di Azure o l'API REST, sono necessarie le autorizzazioni di Controllo degli accessi in base al ruolo di Azure, in particolare i ruoli Controllo degli accessi in base al ruolo di Azure Collaboratore, Collaboratore Database SQL o Collaboratore SQL Server. Per altre informazioni, vedere Ruoli predefiniti di Azure.

Esempi

R. Controllare le opzioni di edizione e cambiarle

Imposta un'edizione e una dimensione massima per il database db1:

SELECT Edition = DATABASEPROPERTYEX('db1', 'EDITION'),
        ServiceObjective = DATABASEPROPERTYEX('db1', 'ServiceObjective'),
        MaxSizeInBytes =  DATABASEPROPERTYEX('db1', 'MaxSizeInBytes');

ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');

B. Spostare un database in un pool elastico diverso

Sposta un database esistente in un pool denominato pool1:

ALTER DATABASE db1
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = pool1 ) ) ;

C. Aggiungere un database secondario con replica geografica

Crea un database db1 secondario leggibile nel server secondaryserver di db1 nel server locale.

ALTER DATABASE db1
ADD SECONDARY ON SERVER secondaryserver
WITH ( ALLOW_CONNECTIONS = ALL );

D. Rimuovere un database secondario con replica geografica

Rimuove il database db1 secondario nel server secondaryserver.

ALTER DATABASE db1
REMOVE SECONDARY ON SERVER testsecondaryserver;

E. Eseguire il failover di un database secondario con replica geografica

Alza di livello un database db1 secondario nel server secondaryserver per diventare il nuovo database primario quando viene eseguito nel server secondaryserver.

ALTER DATABASE db1 FAILOVER;

F. Forzare il failover su un database secondario con replica geografica con perdita di dati

Forza un database db1 secondario nel server secondaryserver a diventare il nuovo database primario quando viene eseguito nel server secondaryserver, nel caso in cui il server primario non sia disponibile. Questa opzione può comportare la perdita di dati.

ALTER DATABASE db1 FORCE_FAILOVER_ALLOW_DATA_LOSS;

G. Aggiornare un singolo database al livello di servizio S0 (edizione Standard, livello di prestazioni 0)

Aggiorna un database singolo all'edizione Standard (livello di servizio) con dimensioni di calcolo (obiettivo di servizio) pari a S0 e una dimensione massima di 250 GB.

ALTER DATABASE [db1] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S0');

H. Aggiornare la ridondanza dell'archivio di backup di un database

Aggiorna la ridondanza dell'archivio di backup di un database impostando la ridondanza della zona. Tutti i backup futuri di questo database usano la nuova impostazione. Sono inclusi i backup per il ripristino temporizzato e i backup con conservazione a lungo termine (se configurati).

ALTER DATABASE db1 MODIFY BACKUP_STORAGE_REDUNDANCY = 'ZONE';

Io. Convertire un database in un livello di servizio Hyperscale con cutover manuale

Per convertire un database esistente nel database SQL di Azure in Hyperscale con Transact-SQL, connettersi al database master nel server SQL logico .

È necessario specificare sia l'edizione che l'obiettivo di servizio nell'istruzione ALTER DATABASE.

Per impostazione predefinita, il database eseguirà un cutover al database Hyperscale per completare la conversione non appena è disponibile il database Hyperscale. L'argomento MANUAL_CUTOVER avvia invece la conversione che terminerà con un cutover avviato manualmente, al momento della scelta. Questa opzione è più utile per il tempo necessario per ridurre al minimo le interruzioni aziendali.

Questa istruzione di esempio converte un database denominato mySampleDatabase nel livello di servizio Hyperscale con l'obiettivo di servizio HS_Gen5_2. Sostituire il nome del database con il valore appropriato prima di eseguire l'istruzione .

ALTER DATABASE [mySampleDatabase]
   MODIFY (EDITION = 'Hyperscale', SERVICE_OBJECTIVE = 'HS_Gen5_2')
   WITH MANUAL_CUTOVER;

Per monitorare le operazioni per un database Hyperscale, connettersi al database master ed eseguire query sys.dm_operation_status?view=azuresqldb-current&preserve-view=true) per esaminare le operazioni nel server logico.

SELECT *
FROM sys.dm_operation_status
WHERE major_resource_id = 'mySampleDatabase'
ORDER BY start_time DESC;
GO

Quando si è pronti, il phase_code verrà WaitingForCutover. Usare l'argomento PERFORM_CUTOVER per avviare il cutover:

ALTER DATABASE [mySampleDatabase] PERFORM_CUTOVER;

Contenuto correlato

• CREATE DATABASE
• DATABASEPROPERTYEX (Transact-SQL)
• DROP DATABASE (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• EVENTDATA (Transact-SQL)
• sys.sp_spaceused (Transact-SQL)
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• sys.filegroups (Transact-SQL)
• sys.master_files (Transact-SQL)
• Database di sistema
• Indicazioni sul ripristino di emergenza - Database SQL di Azure
• Elenco di controllo per la disponibilità elevata e il ripristino di emergenza - Database SQL di Azure
• Limiti delle risorse DTU
• Limiti delle risorse per i singoli database che utilizzano il modello di acquisto vCore
• Limiti delle risorse per i pool elastici secondo il modello di acquisto vCore

Panoramica: Istanza gestita di SQL di Azure

In Istanza gestita di SQL di Azure usare questa istruzione per impostare le opzioni di database.

A causa della lunghezza, la sintassi di ALTER DATABASE è separata in più articoli.

Sintassi

-- Azure SQL Managed Instance syntax  
ALTER DATABASE { database_name | CURRENT }  
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>  
  | SET <option_spec> [ ,...n ]  
}  
[;]

<file_and_filegroup_options>::=  
  <add_or_modify_files>::=  
  <filespec>::=
  <add_or_modify_filegroups>::=  
  <filegroup_updatability_option>::=  

<option_spec> ::=
{
    <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>  
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <temporal_history_retention>
  | <compatibility_level>
      { 170 | 160 | 150 | 140 | 130 | 120 | 110 | 100 }

}  

Argomenti

database_name

Nome del database da modificare.

CORRENTE
Specifica che il database corrente in uso deve essere modificato.

Osservazioni:

• Per rimuovere un database, usa DROP DATABASE.

• Per ridurre le dimensioni di un database, usare DBCC SHRINKDATABASE.

• L'istruzione ALTER DATABASE deve essere eseguita in modalità commit automatico (modalità di gestione delle transazioni predefinita) e non è consentita in una transazione esplicita o implicita.

• La cache dei piani per il Istanza gestita di SQL di Azure viene cancellata impostando una delle opzioni seguenti.

  • COLLATE

  • MODIFICA FILE GROUP DEFAULT

  • MODIFICA FILEGROUP SOLA_LETTURA

  • MODIFICA GRUPPO_DI_FILE LETTURA_SCRITTURA

  • MODIFY_NAME

    La cancellazione della cache dei piani comporta la ricompilazione di tutti i piani di esecuzione successivi e può causare un peggioramento improvviso e temporaneo delle prestazioni di esecuzione delle query. Per ogni archivio cache cancellato nella cache dei piani, il log degli errori di SQL Server contiene il messaggio informativo seguente: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. Questo messaggio viene registrato ogni cinque minuti per tutta la durata dello scaricamento della cache. La cache dei piani viene scaricata anche quando vengono eseguite diverse query in un database con le opzioni predefinite. Successivamente, il database viene eliminato.

• Alcune istruzioni ALTER DATABASE richiedono l'esecuzione di un blocco esclusivo in un database. Ecco perché potrebbero verificarsi errori quando un altro processo attivo mantiene un blocco nel database. Un errore segnalato in un caso simile è Msg 5061, Level 16, State 1, Line 38 con il messaggio ALTER DATABASE failed because a lock could not be placed on database '<database name>'. Try again later. Si tratta in genere di un errore temporaneo e per risolverlo, una volta rilasciati tutti i blocchi del database, riprovare a eseguire l'istruzione ALTER DATABASE non riuscita. La vista di sistema sys.dm_tran_locks contiene informazioni sui blocchi attivi. Per verificare se sono presenti blocchi condivisi o esclusivi in un database, usare la query seguente.

  SELECT
      resource_type, resource_database_id, request_mode, request_type, request_status, request_session_id 
  FROM 
      sys.dm_tran_locks
  WHERE
      resource_database_id = DB_ID('testdb');
  

Visualizzare le informazioni sul database

Per restituire informazioni su database, file e filegroup, è possibile usare viste del catalogo, funzioni di sistema e stored procedure di sistema.

Autorizzazioni

Solo l'account di accesso dell'entità a livello di server (creato dal processo di provisioning) o i membri del ruolo del database dbcreator possono modificare un database.

Esempi

Negli esempi seguenti viene illustrato come impostare l'ottimizzazione automatica e come aggiungere un file a un database in Istanza gestita di SQL di Azure.

ALTER DATABASE WideWorldImporters
  SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);

ALTER DATABASE WideWorldImporters
  ADD FILE (NAME = 'data_17');

Contenuto correlato

• CREATE DATABASE
• DATABASEPROPERTYEX (Transact-SQL)
• DROP DATABASE (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
• EVENTDATA (Transact-SQL)
• sys.sp_configure (Transact-SQL)
• sys.sp_spaceused (Transact-SQL)
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• sys.data_spaces (Transact-SQL)
• sys.filegroups (Transact-SQL)
• sys.master_files (Transact-SQL)
• Database di sistema

Panoramica: Azure Synapse Analytics

In Azure Synapse modifica ALTER DATABASE alcune opzioni di configurazione di un pool SQL dedicato.

A causa della lunghezza, la sintassi di ALTER DATABASE è separata in più articoli.

ALTER DATABASE SET Options fornisce la sintassi e le informazioni correlate per modificare gli attributi di un database utilizzando le SET opzioni di ALTER DATABASE.

Sintassi

ALTER DATABASE { database_name | CURRENT }
{
  MODIFY NAME = new_database_name
| MODIFY ( <edition_option> [, ... n] )
| SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<edition_option> ::=
      MAXSIZE = {
            250 | 500 | 750 | 1024 | 5120 | 10240 | 20480
          | 30720 | 40960 | 51200 | 61440 | 71680 | 81920
          | 92160 | 102400 | 153600 | 204800 | 245760
      } GB
      | SERVICE_OBJECTIVE = {
            'DW100' | 'DW200' | 'DW300' | 'DW400' | 'DW500'
          | 'DW600' | 'DW1000' | 'DW1200' | 'DW1500' | 'DW2000'
          | 'DW3000' | 'DW6000' | 'DW500c' | 'DW1000c' | 'DW1500c'
          | 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c' | 'DW6000c'
          | 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
      }

ALTER DATABASE { database_name | Current } 
{ 
    COLLATE collation_name 
  | SET { <optionspec> [ ,...n ] } 
} 
[;] 

<optionspec> ::= 
{ 
    <auto_option> 
  | <sql_option> 
}  

<auto_option> ::= 
{ 
    AUTO_CREATE_STATISTICS { OFF | ON [ ( INCREMENTAL = { ON | OFF } ) ] } 
} 

<sql_option> ::= 
{ 
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 170 | 160 | 150 | 140 | 130 | 120 | 110 | 100 } 
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
} 

Argomenti

database_name

Specifica il nome del database da modificare.

MODIFICA NOME = new_database_name

Rinomina il database con il nome specificato come new_database_name.

L'opzione 'MODIFY NAME' presenta alcune limitazioni di supporto in Azure Synapse:

• Non è supportata con i pool serverless di Azure Synapse
• Non supportato con pool SQL dedicati creati nell'area di lavoro di Azure Synapse
• Supportato con pool SQL dedicati (in precedenza SQL Data Warehouse) creati tramite il portale di Azure, inclusi quelli con un'area di lavoro connessa

DIMENSIONE MASSIMA

Il valore predefinito è 245.760 GB (240 TB).

Si applica a: ottimizzato per il calcolo di prima generazione

Dimensioni massime consentite per il database. Il database non può aumentare oltre MAXSIZE.

Si applica a: ottimizzato per il calcolo di seconda generazione

Dimensioni massime consentite per i dati rowstore nel database. I dati archiviati in tabelle rowstore, un deltastore di un indice columnstore o un indice non cluster in un indice columnstore cluster non possono aumentare oltre MAXSIZE. I dati compressi in formato columnstore non hanno un limite di dimensioni e non sono vincolati da MAXSIZE.

SERVICE_OBJECTIVE

Specifica le dimensioni di calcolo (obiettivo di servizio). Per altre informazioni sugli obiettivi di servizio per Azure Synapse, vedere Unità Data Warehouse (DWU).

Autorizzazioni

Richiede le autorizzazioni seguenti:

• Accesso principale di livello server (creato dal processo di provisioning) oppure
• Membro del ruolo del database dbmanager.

Il proprietario del database non può modificare il database a meno che il proprietario non sia membro del dbmanager ruolo.

Osservazioni:

Il database corrente deve essere un database diverso da quello che si sta modificando, pertanto è necessario eseguire ALTER durante la connessione al master database.

COMPATIBILITY_LEVEL in Analisi SQL è impostato su 130 per impostazione predefinita e non può essere modificato. Per altre informazioni, vedere ALTER DATABASE Livello di compatibilità.

Limiti

Per eseguire ALTER DATABASE, il database deve essere online e non può trovarsi in uno stato sospeso.

L'istruzione ALTER DATABASE deve essere eseguita in modalità autocommit, ovvero la modalità di gestione delle transazioni predefinita. Questa opzione è specificata nelle impostazioni di connessione.

L'istruzione ALTER DATABASE non può far parte di una transazione definita dall'utente.

Non è possibile modificare le regole di confronto del database.

Esempi

Prima di eseguire questi esempi, assicurarsi che il database che si sta modificando non sia il database corrente. Il database corrente deve essere un database diverso da quello che si sta modificando, pertanto è necessario eseguire ALTER durante la connessione al master database.

R. Modificare il nome del database

ALTER DATABASE AdventureWorks2022
MODIFY NAME = Northwind;

B. Modificare la dimensione massima del database

ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB );

C. Modificare le dimensioni di calcolo (obiettivo di servizio)

ALTER DATABASE dw1 MODIFY ( SERVICE_OBJECTIVE= 'DW1200' );

D. Modificare le dimensioni massime e le dimensioni di calcolo (obiettivo di servizio)

ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB, SERVICE_OBJECTIVE= 'DW1200' );

Contenuto correlato

• CREATE DATABASE
• Elementi del linguaggio T-SQL per il pool SQL dedicato in Azure Synapse Analytics

Panoramica: Microsoft Fabric

In Microsoft Fabric Warehouse in Microsoft Fabric questa istruzione modifica un magazzino.

A causa della lunghezza, la sintassi di ALTER DATABASE è separata in più articoli.

Osservazioni:

Attualmente, gli usi per ALTER DATABASE in un magazzino Fabric sono documentati in ALTER DATABASE ... SET. Vedere ALTER DATABASE SET le opzioni.

Contenuto correlato

• Cos’è il data warehousing in Microsoft Fabric
• Area funzionale T-SQL nel Fabric Data Warehouse