Eseguire la migrazione di un database di Access a SQL Server

Si applica a

Access per Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Tutti abbiamo dei limiti e un database di Access non fa eccezione. Ad esempio, un database di Access ha un limite di dimensioni di 2 GB e non può supportare più di 255 utenti simultanei. Quindi, quando è il momento di portare il database di Access al livello successivo, è possibile eseguire la migrazione a SQL Server. SQL Server, sia locale che nel cloud di Azure, supporta quantità di dati superiori, più utenti simultanei e ha una capacità maggiore rispetto al motore di database JET/ACE. Questa guida consente di iniziare senza problemi il percorso verso SQL Server, consente di preservare le soluzioni front-end di Access create e, si spera, motiva a usare Access per le soluzioni di database future. Usare Microsoft SQL Server Migration Assistant (SSMA) per eseguire correttamente la migrazione, seguire questi passaggi.

Fasi della migrazione di database a SQL Server

Prima di iniziare

Le sezioni seguenti forniscono informazioni di base e altre informazioni utili per iniziare.

Informazioni sui database divisi

Tutti gli oggetti di database di Access possono essere in un file di database oppure possono essere archiviati in due file di database, un database front-end e un database back-end. Questa operazione è denominata suddivisione del database ed è progettata per facilitare la condivisione in un ambiente di rete. Il file di database back-end deve contenere solo tabelle e relazioni. Il file front-end deve contenere solo tutti gli altri oggetti, inclusi maschere, report, query, macro, moduli VBA e tabelle collegate al database back-end. La migrazione di un database di Access è simile a un database diviso in quanto SQL Server funge da nuovo back-end per i dati che ora si trovano in un server.

Di conseguenza, è comunque possibile gestire il database di Access front-end con tabelle collegate alle tabelle di SQL Server. In effetti, è possibile sfruttare i vantaggi offerti dallo sviluppo rapido di applicazioni offerto da un database di Access, insieme alla scalabilità di SQL Server.

Vantaggi di SQL Server

Hai ancora bisogno di un po 'di convincimento per migrare a SQL Server? Ecco alcuni vantaggi aggiuntivi a cui pensare:

Più utenti simultanei SQL Server può gestire molti più utenti simultanei rispetto ad Access e riduce al minimo i requisiti di memoria quando vengono aggiunti più utenti.
Maggiore disponibilità Con SQL Server è possibile eseguire il backup dinamico, incrementale o completo, del database mentre è in uso. Di conseguenza, non è necessario imporre agli utenti di disconnettersi dal database per eseguire il backup dei dati.
Alte prestazioni e scalabilità Il database di SQL Server offre in genere prestazioni migliori rispetto a un database di Access, soprattutto nel caso di database di grandi dimensioni e di terabyte. Inoltre, SQL Server elabora le query in modo molto più rapido ed efficiente elaborando le query in parallelo, usando più thread nativi all'interno di un singolo processo per gestire le richieste utente.
Sicurezza migliorata Utilizzando una connessione attendibile, SQL Server si integra con Sicurezza del sistema Windows per fornire un singolo accesso integrato alla rete e al database, impiegando il meglio di entrambi i sistemi di sicurezza. Ciò rende molto più semplice l'amministrazione di schemi di sicurezza complessi. SQL Server è lo spazio di archiviazione ideale per informazioni riservate, ad esempio codici fiscali, dati di carte di credito e indirizzi riservati.
Recuperabilità immediata In caso di arresto anomalo del sistema operativo o interruzione dell'alimentazione, SQL Server può ripristinare automaticamente il database a uno stato coerente in pochi minuti e senza alcun intervento da parte dell'amministratore del database.
Utilizzo di VPN Access e reti private virtuali (VPN) non vanno d'accordo. Ma con SQL Server, gli utenti remoti possono usare ancora il database front-end di Access su un desktop e il back-end di SQL Server situato dietro il firewall VPN.
Azure SQL Server Oltre ai vantaggi di SQL Server, offre scalabilità dinamica senza tempi di inattività, ottimizzazione intelligente, scalabilità e disponibilità globali, eliminazione dei costi hardware e amministrazione ridotta.

Scegliere l'opzione migliore per il server Azure SQL

Se si esegue la migrazione ad Azure SQL Server, è possibile scegliere tra tre opzioni, ognuna con vantaggi diversi:

Database singolo/pool elastici Questa opzione ha un proprio set di risorse gestite tramite un server di database SQL. Un singolo database è simile a un database indipendente in SQL Server. È anche possibile aggiungere un pool elastico, ovvero una raccolta di database con un set condiviso di risorse gestite tramite il server di database SQL. Le funzionalità di SQL Server più comunemente usate sono disponibili con backup, patch e ripristino predefiniti. Tuttavia, non esiste un orario di manutenzione esatto garantito e la migrazione da SQL Server potrebbe essere difficile.
Istanza gestita Questa opzione è una raccolta di database di sistema e di utenti con un set condiviso di risorse. Un'istanza gestita è come un'istanza del database di SQL Server altamente compatibile con SQL Server locale. Un'istanza gestita include backup, applicazione di patch e ripristino predefiniti ed è facile eseguire la migrazione da SQL Server. Tuttavia, esiste un numero limitato di funzionalità di SQL Server che non sono disponibili e non è garantito un tempo di manutenzione esatto.
Macchina virtuale di Azure: questa opzione consente di eseguire SQL Server all'interno di una macchina virtuale nel cloud di Azure. Hai il controllo completo sul motore di SQL Server e un percorso di migrazione semplice. Ma è necessario gestire i backup, le patch e il ripristino.

Per altre informazioni, vedere Scelta del percorso di migrazione del database ad Azure e Che cos'è Azure SQL?.

Primi passi

Esistono alcuni problemi che è possibile risolvere in anticipo che possono semplificare il processo di migrazione prima di eseguire SSMA:

Aggiungere indici di tabella e chiavi primarie Verificare che ogni tabella di Access disponga di un indice e di una chiave primaria. SQL Server richiede che tutte le tabelle abbiano almeno un indice e una tabella collegata deve avere una chiave primaria se la tabella può essere aggiornata.
Controllare le relazioni tra chiave primaria ed esterna Verificare che queste relazioni siano basate su campi con tipi di dati e dimensioni coerenti. SQL Server non supporta le colonne unite con tipi e dimensioni di dati diversi nei vincoli di chiave esterna.
Rimuovere la colonna Allegato SSMA non esegue la migrazione delle tabelle che contengono la colonna Allegato.

Prima di eseguire SSMA, eseguire i primi passaggi seguenti.

Chiudere il database di Access.
Assicurarsi che anche gli utenti correnti connessi al database chiudano il database.
Se il database è in formato .mdb file, Rimuovi sicurezza a livello di utente.
Eseguire il backup del database. Per altre informazioni, vedere Proteggere i dati con i processi di backup e ripristino.

Suggerimento Provare a installare sul desktop Microsoft SQL Server Express Edition, che supporta fino a 10 GB ed è un modo semplice e gratuito per eseguire e controllare la migrazione. Quando ci si connette, usare LocalDB come istanza del database.

Suggerimento Se possibile, usare una versione autonoma di Access.

Eseguire SSMA

Microsoft fornisce Microsoft SQL Server Migration Assistant (SSMA) per semplificare la migrazione. SSMA esegue principalmente la migrazione di tabelle e query di selezione senza parametri. Maschere, report, macro e moduli VBA non vengono convertiti. Esplora metadati di SQL Server visualizza gli oggetti di database di Access e gli oggetti di SQL Server, consentendo di esaminare il contenuto corrente di entrambi i database. Queste due connessioni vengono salvate nel file di migrazione nel caso in cui si decida di trasferire altri oggetti in futuro.

Avvertenza Il processo di migrazione può richiedere del tempo, a seconda delle dimensioni degli oggetti di database e della quantità di dati da trasferire.

Per eseguire la migrazione di un database usando SSMA, scaricare e installare il software facendo doppio clic sul file MSI scaricato. Assicurarsi di installare la versione a 32 o 64 bit appropriata per il computer.
Dopo aver installato SSMA, aprirlo sul desktop, preferibilmente dal computer con il file di database di Access.
È anche possibile aprirlo in una cartella condivisa in un computer che ha accesso al database di Access dalla rete.
Seguire le istruzioni iniziali in SSMA per fornire informazioni di base come il percorso di SQL Server, il database di Access e gli oggetti da migrare, le informazioni sulla connessione e se si vogliono creare tabelle collegate.
Se si esegue la migrazione a SQL Server 2016 o versione successiva e si vuole aggiornare una tabella collegata, aggiungere una colonna rowversion selezionando Strumenti> revisioneImpostazioni> progettoGenerale.
Il campo rowversion consente di evitare conflitti di record. Access usa questo campo rowversion in una tabella collegata di SQL Server per determinare la data dell'ultimo aggiornamento del record. Inoltre, se si aggiunge il campo rowversion a una query, Access lo usa per riselezionare la riga dopo un'operazione di aggiornamento. In questo modo si migliora l'efficienza evitando errori di conflitto di scrittura e scenari di eliminazione dei record che possono verificarsi quando Access rileva risultati diversi dall'invio originale, ad esempio con tipi di dati con numeri a virgola mobile e trigger che modificano le colonne. Evitare tuttavia di usare il campo rowversion in maschere, report o codice VBA. Per altre informazioni, vedere rowversion.
Avvertenza Evitare di confondere rowversion con i timestamp. Anche se la parola chiave timestamp è un sinonimo di rowversion in SQL Server, non è possibile usare rowversion come metodo per timestamp di una voce di dati.
Per impostare tipi di dati precisi, selezionare Mappatura> tipo impostazioniprogetto> Strumentirevisione. Se ad esempio si memorizza solo testo in lingua inglese, è possibile usare il tipo di dati varchar anziché nvarchar .

Convertire oggetti

SSMA converte gli oggetti di Access in oggetti di SQL Server, ma non copia subito gli oggetti. SSMA fornisce un elenco degli oggetti seguenti da migrare in modo da poter decidere se spostarli nel database di SQL Server:

Tabelle e colonne
Selezionare le query senza parametri.
Chiavi primarie ed esterne
Indici e valori predefiniti
Vincoli di controllo (consenti la proprietà della colonna di lunghezza zero, regola di convalida della colonna, convalida della tabella)

Come procedura consigliata, usare il report di valutazione SSMA, che mostra i risultati della conversione, inclusi errori, avvisi, messaggi informativi, stime del tempo per l'esecuzione della migrazione e singoli passaggi di correzione degli errori da eseguire prima di spostare effettivamente gli oggetti.

La conversione di oggetti di database acquisisce le definizioni degli oggetti dai metadati di Access e le converte in una sintassi Transact-SQL (T-SQL) equivalente e quindi carica queste informazioni nel progetto. È quindi possibile visualizzare gli oggetti di SQL Server o SQL Azure e le relative proprietà utilizzando Esplora metadati di SQL Server o SQL Azure.

Per convertire, caricare ed eseguire la migrazione di oggetti a SQL Server, seguire questa guida.

Suggerimento Dopo aver completato la migrazione del database di Access, salvare il file di progetto per un uso successivo, in modo da poter eseguire nuovamente la migrazione dei dati per il test o la migrazione finale.

Collegare tabelle

È consigliabile installare la versione più recente dei driver OLE DB e ODBC di SQL Server invece di usare i driver nativi di SQL Server forniti con Windows. Non solo i driver più recenti sono più veloci, ma supportano nuove funzionalità in Azure SQL che i driver precedenti non offrono. È possibile installare i driver in ogni computer in cui viene usato il database convertito. Per altre informazioni, vedere Microsoft OLE DB Driver 18 per SQL Server e Microsoft ODBC Driver 17 per SQL Server.

Dopo aver eseguito la migrazione delle tabelle di Access, è possibile creare un collegamento alle tabelle in SQL Server che ora ospita i dati. Il collegamento direttamente da Access consente anche di visualizzare i dati in modo più semplice rispetto ai più complessi strumenti di gestione di SQL Server. È possibile eseguire query e modificare i dati collegati a seconda delle autorizzazioni configurate dall'amministratore del database di SQL Server.

Avvertenza Se si crea un DSN ODBC quando si esegue il collegamento al database di SQL Server durante il processo di collegamento, creare lo stesso DSN in tutti i computer che usano la nuova applicazione oppure, a livello di codice, usare la stringa di connessione archiviata nel file DSN.

Per altre informazioni, vedere Collegare o importare dati da un database del server Azure SQL e Importare o creare un collegamento ai dati in un database di SQL Server.

Suggerimento Non dimenticare di usare Gestione tabelle collegate in Access per aggiornare e ricollegare facilmente le tabelle. Per altre informazioni, vedere Gestire le tabelle collegate.

Testare e rivedere

Le sezioni seguenti descrivono i problemi comuni che possono verificarsi durante la migrazione e come gestirli.

Query

Vengono convertite solo le query selezionate; Altre query non lo sono, incluse le query di selezione che accettano parametri. Alcune query potrebbero non essere convertite completamente e SSMA segnala errori durante il processo di conversione. È possibile modificare manualmente gli oggetti che non vengono convertiti usando la sintassi T-SQL. Gli errori di sintassi possono anche richiedere la conversione manuale di funzioni e tipi di dati specifici di Access in quelli di SQL Server. Per ulteriori informazioni, vedere Confronto tra SQL per Access e T-SQL per SQL Server.

Tipi di dati

Access e SQL Server hanno tipi di dati simili, ma occorre tenere presenti i seguenti potenziali problemi.

Numero grande Il tipo di dati Numero grande archivia un valore numerico non monetario ed è compatibile con il tipo di dati bigint SQL. È possibile usare questo tipo di dati per calcolare in modo efficiente numeri di grandi dimensioni, ma richiede il formato di file di database con estensione accdb di Access 16 (16.0.7812 o versione successiva) e offre prestazioni migliori con la versione a 64 bit di Access. Per ulteriori informazioni, vedere Utilizzo del tipo di dati Numero grande e Scegliere tra la versione a 64 bit o a 32 bit di Office.

Sì/No Per impostazione predefinita, una colonna Sì/No di Access viene convertita in un campo di bit di SQL Server. Per evitare il blocco dei record, assicurarsi che il campo di bit sia impostato in modo da impedire i valori NULL. IN SSMA è possibile selezionare la colonna di bit per impostare la proprietà Allow Nulls su NO. In TSQL usare le istruzioni CREATE TABLE o ALTER TABLE .

Data e ora Esistono diverse considerazioni relative a data e ora:

Se il livello di compatibilità del database è 130 (SQL Server 2016) o superiore e una tabella collegata contiene una o più colonne DateTime o DateTime2, la tabella potrebbe restituire il messaggio #deleted nei risultati. Per altre informazioni, vedere Accedere alle tabelle collegate per SQL-Server restituisce un database #deleted.
Usare il tipo di dati Data/ora di Access per eseguire il mapping al tipo di dati datetime. Usare il tipo di dati Data/ora estesa di Access per eseguire il mapping al tipo di dati datetime2 , che include un intervallo di data e ora più grande. Per ulteriori informazioni, vedere Utilizzo del tipo di dati Data/ora estesa.
Quando si eseguono query per date in SQL Server, prendere in considerazione sia l'ora che la data. Ad esempio:
- DataOrdinato tra 01/01/19 e 31/01/19 potrebbe non includere tutti gli ordini.
- DataOrdinato tra 01/01/19 00:00:00 AM e 31/01/19 23:59:59 include tutti gli ordini.

Allegato Il tipo di dati Allegato consente di archiviare un file in un database di Access. In SQL Server sono disponibili diverse opzioni. È possibile estrarre i file dal database di Access e quindi considerare la possibilità di archiviare i collegamenti ai file nel database di SQL Server. In alternativa, è possibile utilizzare le funzioni FILESTREAM, FileTable o Archivio BLOB remoti (RBS) per mantenere gli allegati archiviati nel database di SQL Server.

Collegamento ipertestuale Le tabelle di Access contengono colonne di collegamenti ipertestuali non supportate da SQL Server. Per impostazione predefinita, queste colonne verranno convertite in colonne nvarchar(max) in SQL Server, ma è possibile personalizzare il mapping per scegliere un tipo di dati più piccolo. Nella soluzione di Access è comunque possibile utilizzare il comportamento del collegamento ipertestuale in maschere e report, se si imposta la proprietà Collegamento ipertestuale per il controllo su true.

Campo multivalore Il campo multivalore di Access viene convertito in SQL Server come campo ntext contenente il set di valori delimitati. Poiché SQL Server non supporta un tipo di dati multivalore che dà corpo a una relazione molti-a-molti, potrebbero essere necessarie operazioni aggiuntive di progettazione e conversione.

Per altre informazioni sulla mappatura dei tipi di dati di Access e SQL Server, vedere Confrontare i tipi di dati.

Avvertenza I campi multivalore non vengono convertiti.

Per altre informazioni, vedere Tipi di data e ora, Tipi stringa e binari e Tipi numerici.

Visual Basic

Anche se VBA non è supportato da SQL Server, tenere presenti i possibili problemi seguenti:

Funzioni VBA nelle query Le query di Access supportano le funzioni VBA sui dati in una colonna di query. Tuttavia, le query di Access che usano funzioni VBA non possono essere eseguite in SQL Server, quindi tutti i dati richiesti vengono passati a Microsoft Access per l'elaborazione. Nella maggior parte dei casi, queste query devono essere convertite in query pass-through.

Funzioni definite dall'utente nelle query Le query di Microsoft Access supportano l'uso delle funzioni definite nei moduli VBA per elaborare i dati che vengono loro passati. Le query possono essere query autonome, istruzioni SQL in origini record di maschere/report, origini dati di caselle combinate e caselle di riepilogo in maschere, report e campi di tabelle ed espressioni di regole predefinite o di convalida. SQL Server non può eseguire queste funzioni definite dall'utente. Potrebbe essere necessario riprogettare manualmente queste funzioni e convertirle in stored procedure in SQL Server.

Ottimizza le prestazioni

Il modo di gran lunga più importante per ottimizzare le prestazioni con la nuova versione back-end di SQL Server consiste nel decidere quando usare query locali o remote. Quando si esegue la migrazione dei dati a SQL Server, si passa anche da un file server a un modello di database di elaborazione client-server. Segui queste linee guida generali:

Eseguire piccole query di sola lettura sul client per un accesso più rapido.
Eseguire lunghe query di lettura/scrittura sul server per sfruttare la maggiore potenza di elaborazione.
Riduci al minimo il traffico di rete con filtri e aggregazione per trasferire solo i dati che ti servono.

Ottimizzare le prestazioni nel modello di database client-server Per altre informazioni, vedere Creare una query pass-through.

Di seguito sono riportate linee guida aggiuntive consigliate.

Inserire la logica nel server L'applicazione può anche usare viste, funzioni definite dall'utente, stored procedure, campi calcolati e trigger per centralizzare e condividere la logica dell'applicazione, le regole e i criteri aziendali, le query complesse, la convalida dei dati e il codice di integrità referenziale sul server, anziché sul client. Chiediti, questa query o attività può essere eseguita sul server meglio e più velocemente? Infine, testare ogni query per garantire prestazioni ottimali.

Usare le visualizzazioni in maschere e report In Access eseguire le operazioni seguenti:

Per i moduli, usare una visualizzazione SQL per un modulo di sola lettura e una visualizzazione indicizzata SQL per un modulo di lettura/scrittura come origine record.
Per i report, usare una visualizzazione SQL come origine record. È tuttavia possibile creare una visualizzazione separata per ogni report, in modo da poter aggiornare più facilmente un report specifico senza influire sugli altri report.

Ridurre al minimo il caricamento di dati in una maschera o in un report Non visualizzare i dati finché l'utente non lo richiede. Ad esempio, lasciare vuota la proprietà recordsource, impostare gli utenti che selezionano un filtro nella maschera e quindi inserire il filtro nella proprietà recordsource. In alternativa, usare la clausola where di DoCmd.OpenForm e DoCmd.OpenReport per visualizzare i record esatti necessari all'utente. È consigliabile disattivare la navigazione tra i registri.

Prestare attenzione alle query eterogenee Evitare di eseguire una query che combina una tabella di Access locale e una tabella collegata di SQL Server, talvolta definita query ibrida. Questo tipo di query richiede comunque Access per scaricare tutti i dati di SQL Server nel computer locale e quindi eseguire la query, ma non in SQL Server.

Quando usare le tabelle locali È consigliabile usare tabelle locali per i dati che cambiano raramente, ad esempio l'elenco degli stati o delle province di un paese o di un'area geografica. Le tabelle statiche vengono spesso usate per i filtri e possono offrire prestazioni migliori nel front-end di Access.

Per altre informazioni, vedere Ottimizzazione guidata motore di database, Usare l'analizzatore prestazioni per ottimizzare un database di Access e Ottimizzazione di Microsoft Office Access applicazioni collegate a SQL Server.