Risoluzione dei problemi e delle prestazioni con SqlPackage

  • Articolo

In alcuni scenari le operazioni SqlPackage richiedono più tempo del previsto o non vengono completate. Questo articolo descrive alcune tattiche consigliate di frequente per risolvere i problemi o migliorare le prestazioni di queste operazioni. Anche se è consigliabile leggere la pagina della documentazione specifica per ogni azione per comprendere i parametri e le proprietà disponibili, questo articolo funge da punto di partenza per imparare a conoscere in maggiore dettaglio le operazioni di SqlPackage.

Strategia complessiva

Come linea guida generale, è possibile ottenere prestazioni migliori tramite la versione .NET di SqlPackage anziché la versione di .NET Framework installata tramite il DacFramework.msi.

Se non è possibile installare lo strumento dotnet SqlPackage, che consente l'esecuzione di comandi SqlPackage dal prompt dei comandi in qualsiasi directory:

  1. Scaricare il file ZIP per SqlPackage in .NET 8 per il sistema operativo in uso (Windows, macOS o Linux).
  2. Decomprimere l'archivio come indicato nella pagina di download.
  3. Aprire un prompt dei comandi e passare (cd) alla directory SqlPackage.

È importante usare la versione più recente disponibile di SqlPackage perché vengono rilasciati regolarmente miglioramenti delle prestazioni e correzioni di bug.

Sostituire SqlPackage.exe per il servizio di importazione/esportazione

Se si è tentato di usare il servizio importazione/esportazione per importare o esportare il database, potrebbe essere utile usare SqlPackage.exe per eseguire la stessa operazione con un maggiore controllo su parametri e proprietà facoltativi.

Per l'importazione, un comando di esempio è:

./SqlPackage /Action:Import /sf:<source-bacpac-file-path> /tsn:<full-target-server-name> /tdn:<a new or empty database> /tu:<target-server-username> /tp:<target-server-password> /df:<log-file>

Per l'esportazione, un comando di esempio è:

./SqlPackage /Action:Export /tf:<target-bacpac-file-path> /ssn:<full-source-server-name> /sdn:<source-database-name> /su:<source-server-username> /sp:<source-server-password> /df:<log-file>

In alternativa a nome utente e password, l'autenticazione a più fattori può essere usata per l'autenticazione tramite l'autenticazione di Microsoft Entra (in precedenza Azure Active Directory) con l'autenticazione a più fattori. Sostituire i parametri nome utente e password per /ua:true e /tid:"yourdomain.onmicrosoft.com".

Problemi comuni

Errori di timeout

Per i problemi relativi ai timeout, è possibile usare le proprietà seguenti per ottimizzare la connessione tra SqlPackage e l'istanza di SQL:

  • /p:CommandTimeout=: specifica il timeout del comando in secondi quando viene eseguita una query. Valore predefinito: 60
  • /p:DatabaseLockTimeout=: specifica il timeout di blocco del database in secondi. È possibile usare -1 per attendere a tempo indeterminato, valore predefinito: 60
  • /p:LongRunningCommandTimeout=: specifica il timeout per i comandi a esecuzione prolungata in secondi. Il valore predefinito, 0, viene usato per attendere a tempo indeterminato.

Utilizzo delle risorse client

Per i comandi di esportazione ed estrazione, i dati della tabella vengono passati a una directory temporanea al buffer prima di essere scritti nel file bacpac/dacpac. Questo requisito di archiviazione può essere di grandi dimensioni ed è relativo alle dimensioni complete dei dati da esportare. Specificare una directory temporanea alternativa con la proprietà /p:TempDirectoryForTableData=<path>.

Il modello di schema viene compilato in memoria, quindi per schemi di database di grandi dimensioni il requisito di memoria nel computer client che esegue SqlPackage può essere significativo.

Consumo ridotto delle risorse nel server

Per impostazione predefinita, SqlPackage imposta il parallelismo massimo del server su 8. Se si nota un consumo ridotto delle risorse del server, l'aumento del valore del parametro MaxParallelism può migliorare le prestazioni.

Token di accesso

L'uso del parametro /AccessToken: o /at: abilita l'autenticazione basata su token per SqlPackage, ma il passaggio del token al comando può essere complicato. Se si analizza un oggetto token di accesso in PowerShell, passare in modo esplicito il valore stringa o eseguire il wrapping del riferimento nella proprietà del token in $(). Ad esempio:

$Account = Connect-AzAccount -ServicePrincipal -Tenant $Tenant -Credential $Credential
$AccessToken_Object = (Get-AzAccessToken -Account $Account -Resource "https://database.windows.net/")
$AccessToken = $AccessToken_Object.Token

SqlPackage /at:$AccessToken
# OR
SqlPackage /at:$($AccessToken_Object.Token)

Connessione

Se SqlPackage non riesce a connettersi, il server potrebbe non avere la crittografia abilitata o il certificato configurato potrebbe non essere emesso da un'autorità di certificazione attendibile, ad esempio un certificato autofirmato. È possibile modificare il comando SqlPackage per connettersi senza crittografia o per considerare attendibile il certificato del server. La procedura consigliata consiste nel garantire che sia possibile stabilire una connessione crittografata attendibile al server.

  • Connettersi senza crittografia: /SourceEncryptConnection:False o /TargetEncryptConnection:False
  • Certificato di attendibilità del server: /SourceTrustServerCertificate:True oppure /TargetTrustServerCertificate:True

È possibile visualizzare uno dei messaggi di avviso seguenti durante la connessione a un'istanza di SQL, a indicare che i parametri della riga di comando potrebbero richiedere modifiche per la connessione al server:

The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured.
The connection string provided contains encryption settings which may lead to connection failure if the server is not properly configured.

Altre informazioni sulle modifiche alla sicurezza delle connessioni in SqlPackage sono disponibili in Miglioramenti della sicurezza della connessione in SqlPackage 161.

Errore dell'azione di importazione 2714 per il vincolo

Quando si esegue un'azione di importazione, potrebbe essere visualizzato l'errore 2714 se esiste già un oggetto:

*** Error importing database:Could not import package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 2714, Level 16, State 5, Line 1 There is already an object named 'DF_Department_ModifiedDate_0FF0B724' in the database.
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [HumanResources].[Department]
    ADD CONSTRAINT [DF_Department_ModifiedDate_] DEFAULT ('') FOR [ModifiedDate];

Ecco le cause e le soluzioni per risolvere questo errore:

  1. Verificare che la destinazione in cui si sta eseguendo l'importazione sia un database vuoto.
  2. Se il database ha vincoli che usano l'attributo DEFAULT (in cui SQL Server assegna un nome casuale al vincolo) e un vincolo denominato in modo esplicito, è possibile creare due volte un vincolo con lo stesso nome. È consigliabile usare tutti i vincoli denominati in modo esplicito (non usando DEFAULT) o tutti i nomi definiti dal sistema (usando DEFAULT).
  3. Modificare manualmente il model.xml e rinominare il vincolo con il nome che presenta l'errore in un nome univoco. Questa opzione deve essere utilizzata solo se richiesto dal supporto tecnico Microsoft e rappresenta un rischio di danneggiamento .bacpac.

Eccezione di overflow dello stack

Gli script T-SQL di grandi dimensioni con molte istruzioni annidate sono spesso la causa di eccezioni intermittenti o persistenti di overflow dello stack. In questo caso, il messaggio di errore includerà il testo Stack overflow e un'analisi dello stack di:

Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.Visit(Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression)
Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.Accept(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.AcceptChildren(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.Accept(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.AcceptChildren(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)

Un parametro per SqlPackage è disponibile in tutti i comandi, /ThreadMaxStackSize:, che specifica le dimensioni massime dello stack per il thread che esegue il processo SqlPackage. Il valore predefinito è determinato dalla versione .NET che esegue SqlPackage. L'impostazione di un valore elevato può influire sulle prestazioni complessive di SqlPackage, ma l'aumento di questo valore potrebbe risolvere l'eccezione di overflow dello stack causata dalle istruzioni annidate. È consigliabile effettuare il refactoring del codice T-SQL per evitare eccezioni di overflow dello stack quando possibile, ma il parametro /ThreadMaxStackSize: può essere usato come soluzione alternativa.

Quando si usa il parametro /ThreadMaxStackSize:, è consigliabile ottimizzare le operazioni ripetute al valore più basso che risolve l'eccezione di overflow dello stack se viene rilevato un impatto sulle prestazioni. Il valore del parametro è in megabyte (MB), i valori di esempio per il test come soluzione alternativa includono 10 e 100.

Diagnostica

I log sono essenziali per la risoluzione dei problemi. Acquisire i log di diagnostica in un file con il parametro /DiagnosticsFile:<filename>.

È possibile registrare dati di traccia aggiuntivi correlati alle prestazioni impostando la variabile di ambiente DACFX_PERF_TRACE=true prima di eseguire SqlPackage. Per impostare questa variabile di ambiente in PowerShell, usare il comando seguente:

Set-Item -Path Env:DACFX_PERF_TRACE -Value true

Suggerimenti per l'azione di importazione

Per le importazioni che contengono tabelle di grandi dimensioni o tabelle con molti indici, l'uso di /p:RebuildIndexesOfflineForDataPhase=True o /p:DisableIndexesForDataPhase=False può migliorare le prestazioni. Queste proprietà modificano rispettivamente l'operazione di ricompilazione dell'indice in modo che venga eseguita offline o non venga eseguita affatto. Queste e altre proprietà sono disponibili per ottimizzare l'operazione di importazione di SqlPackage.

Suggerimenti per l'azione di esportazione

Una causa comune della riduzione delle prestazioni durante l'esportazione sono i riferimenti a oggetti non risolti, a causa dei quali SqlPackage tenta di risolvere l'oggetto più volte. Ad esempio, viene definita una vista che fa riferimento a una tabella e la tabella non esiste più nel database. Se nel log di esportazione compaiono riferimenti non risolti, valutare la possibilità di correggere lo schema del database per migliorare le prestazioni di esportazione.

Negli scenari in cui lo spazio su disco del sistema operativo è limitato e si esaurisce durante l'esportazione, l'uso di /p:TempDirectoryForTableData consente di memorizzare nel buffer i dati per l'esportazione in un disco alternativo. Lo spazio necessario per questa azione potrebbe essere grande ed è relativo alle dimensioni complete del database. Questa e altre proprietà sono disponibili per ottimizzare l'operazione di esportazione di SqlPackage.

Durante un processo di esportazione i dati della tabella vengono compressi nel file bacpac. L'uso di /p:CompressionOption impostato su Fast, SuperFast o NotCompressed potrebbe migliorare la velocità del processo di esportazione e al tempo stesso ridurre la compressione del file bacpac di output.

Per ottenere lo schema e i dati del database ignorando la convalida dello schema, eseguire l'azione Export con la proprietà /p:VerifyExtraction=False. Potrebbe essere generata un'esportazione non valida che non può essere importata.

Database SQL di Azure

I suggerimenti seguenti sono specifici dell'esecuzione dell'importazione o dell'esportazione nel database SQL di Azure da una macchina virtuale di Azure:

  • Usare un database di livello Business Critical o Premium per ottenere prestazioni ottimali.
  • Usare l'archiviazione SSD nella macchina virtuale.
  • Assicurarsi che ci sia spazio sufficiente per decomprimere il bacpac.
  • Eseguire SqlPackage da una macchina virtuale nella stessa area del database.
  • Abilitare la rete accelerata nella macchina virtuale.

Per altre informazioni sull'uso di uno script di PowerShell per raccogliere altre informazioni su un'operazione di importazione, vedere Analisi di fine progetto #211: Monitoraggio del processo di importazione SQLPackage.

Altre risorse

Il blog del supporto del database di Azure contiene molti articoli sulla risoluzione dei problemi e sull'ottimizzazione delle prestazioni per database SQL di Azure, inclusi diversi articoli su SqlPackage.

Alcuni degli articoli più rilevanti includono:

Contenuto correlato