Eseguire la migrazione di un database di Access 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 trovarsi in un unico file di database oppure in due file di database: un database front-end e un database back-end. Questa operazione è detta divisione 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. Quando si esegue la migrazione di un database di Access, è simile a un database diviso in tale SQL Server funge da nuovo back-end per i dati che ora si trovano in un server.

Di conseguenza, è comunque possibile mantenere il database front-end di Access con tabelle collegate alle tabelle SQL Server. In modo efficace, è possibile trarre i vantaggi di uno sviluppo rapido di applicazioni fornito da un database di Access, insieme alla scalabilità di SQL Server.

SQL Server vantaggi

Hai ancora bisogno di alcuni convincenti per eseguire la migrazione a SQL Server? Ecco alcuni altri vantaggi a cui pensare:

Altri utenti simultanei SQL Server possibile gestire molti più utenti simultanei rispetto ad Access e ridurre al minimo i requisiti di memoria quando vengono aggiunti più utenti.
Disponibilità maggiore Con SQL Server, è possibile eseguire in modo dinamico il backup, 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.
Prestazioni elevate e scalabilità Il database SQL Server in genere funziona meglio di un database di Access, in particolare con un database di grandi dimensioni di dimensioni 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 degli utenti.
Maggiore sicurezza Utilizzando una connessione attendibile, SQL Server si integra con la sicurezza del sistema Windows per fornire un unico accesso integrato alla rete e al database, impiegando il meglio di entrambi i sistemi di sicurezza. In questo modo è molto più facile amministrare schemi di sicurezza complessi. SQL Server è lo spazio di archiviazione ideale per informazioni riservate come i numeri di previdenza sociale, i dati della carta di credito e gli indirizzi riservati.
Ripristinabilità immediata Se il sistema operativo si arresta in modo anomalo o se l'alimentazione si spegne, SQL Server possibile ripristinare automaticamente il database a uno stato coerente in pochi minuti e senza alcun intervento da parte dell'amministratore del database.
Utilizzo della VPN Access e reti private virtuali (VPN) non sono d'aiuto. Con SQL Server, tuttavia, gli utenti remoti possono comunque usare il database front-end di Access su un desktop e il back-end SQL Server 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 server di Azure SQL migliore

Se si esegue la migrazione a Azure SQL Server, sono disponibili tre opzioni tra cui scegliere, ognuna con vantaggi diversi:

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

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

Primi passi

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

Aggiungere indici di tabella e chiavi primarie Verificare che ogni tabella di Access contenga un indice e una chiave primaria. SQL Server richiede che tutte le tabelle abbiano almeno un indice e che una tabella collegata abbia una chiave primaria se la tabella può essere aggiornata.
Controllare le relazioni chiave primaria/chiave esterna Verificare che queste relazioni siano basate su campi con dimensioni e tipi di dati coerenti. SQL Server non supporta colonne collegate con tipi di dati e dimensioni diverse 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 .mdb formato di file, rimuovere la sicurezza a livello utente.
Eseguire il backup del database. Per altre informazioni, vedere Proteggere i dati con i processi di backup e ripristino.

Suggerimento È consigliabile installare Microsoft SQL Server Express edition sul desktop 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. Se è possibile usare solo Microsoft 365, usare il motore di database di Access 2010 per eseguire la migrazione del database di Access quando si usa SSMA. Per altre informazioni, vedere Motore di database di Microsoft Access 2010 Redistributable.

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. Il SQL Server Esplora metadati visualizza gli oggetti di database di Access e SQL Server oggetti consentendo di esaminare il contenuto corrente di entrambi i database. Queste due connessioni vengono salvate nel file di migrazione se si decide di trasferire altri oggetti in futuro.

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

Per eseguire la migrazione di un database con SSMA, scaricare e installare prima di tutto 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 un computer che ha accesso al database di Access dalla rete in una cartella condivisa.
Seguire le istruzioni iniziali in SSMA per fornire informazioni di base come la posizione di SQL Server, il database e gli oggetti di Access di cui eseguire la migrazione, le informazioni di 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 revisione > Impostazioni di Project > Generale.

Il campo rowversion consente di evitare conflitti tra record. Access usa questo campo rowversion in una tabella collegata 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 selezionare nuovamente 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.

Nota Evitare di confondere rowversion con timestamp. Anche se il timestamp delle parole chiave è sinonimo di inversione di riga in SQL Server, non è possibile usare rowversion come modo per timestamp di un'immissione di dati.
Per impostare tipi di dati precisi, selezionare Strumenti di revisione > Impostazioni di Project > Mapping tipi. Ad esempio, se si archivia solo il testo in inglese, è possibile usare il tipo di dati varchar invece di nvarchar .

Convertire oggetti

SSMA converte gli oggetti di Access in oggetti SQL Server, ma non copia immediatamente gli oggetti. SSMA fornisce un elenco degli oggetti seguenti di cui eseguire la migrazione in modo da poter decidere se spostarli in SQL Server database:

Tabelle e colonne
Selezionare Query senza parametri.
Chiavi primarie ed esterne
Indici e valori predefiniti
Controllare i vincoli (proprietà consenti colonna di lunghezza zero, regola di convalida delle colonne, convalida 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, le converte nella sintassi Transact-SQL (T-SQL) equivalente e quindi carica queste informazioni nel progetto. È quindi possibile visualizzare gli oggetti SQL Server o SQL Azure e le relative proprietà usando SQL Server o SQL Azure Esplora metadati.

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

Suggerimento Dopo aver eseguito correttamente la migrazione del database di Access, salvare il file di progetto per usarlo in seguito, in modo da poter eseguire di nuovo 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 SQL Server nativi forniti con Windows. Non solo i driver più recenti sono più veloci, ma supportano le nuove funzionalità in Azure SQL che non lo sono i driver precedenti. È possibile installare i driver in ogni computer in cui viene usato il database convertito. Per altre informazioni, vedere Microsoft OLE DB Driver 18 for SQL Server e Microsoft ODBC Driver 17 for 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 diretto da Access offre anche un modo più semplice per visualizzare i dati anziché usare gli strumenti di gestione dei SQL Server più complessi. È possibile eseguire query e modificare i dati collegati in base alle autorizzazioni configurate dall'amministratore del database SQL Server.

Nota Se si crea un DSN ODBC quando si crea un collegamento al database di SQL Server durante il processo di collegamento, creare lo stesso DNS in tutti i computer che usano la nuova applicazione o a livello di programmazione usano i stringa di connessione archiviati nel file DSN.

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

SuggerimentoNon dimenticare di usare Gestione tabelle collegate in Access per aggiornare e ricollegare comodamente 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 di selezione; altre non lo sono, incluse le query di selezione che accettano parametri. Alcune query potrebbero non essere completamente convertite e SSMA segnala errori di query 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 SQL Server funzioni. 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 tenere presente i potenziali problemi seguenti.

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 elevati, ma è necessario usare il formato di file di database accdb di Access 16 (16.0.7812 o versione successiva) e prestazioni migliori con la versione a 64 bit di Access. Per altre informazioni, vedere Uso 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 SQL Server bit. Per evitare il blocco dei record, verificare che il campo di bit sia impostato per non consentire 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 Access linked table to SQL-Server database returns #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 con un intervallo di data e ora più ampio. Per altre informazioni, vedere Uso del tipo di dati Data/ora estesa.
Quando si ese cercano date in SQL Server, tenere conto sia dell'ora che della data. Ad esempio:
- DataOrdinato Tra l'1/1/19 e il 31/1/19 potrebbe non includere tutti gli ordini.
- DataOrdinato Tra l'1/1/19 00:00:00 E il 31/1/19 23:59:59 PM include tutti gli ordini.

Allegato Il tipo di dati Allegato archivia un file nel database di Access. In SQL Server sono disponibili diverse opzioni da considerare. È possibile estrarre i file dal database di Access e quindi è consigliabile archiviare i collegamenti ai file nel database di SQL Server. In alternativa, è possibile usare FILESTREAM, FileTable o RBS (Remote BLOB Store) per mantenere gli allegati archiviati nel database SQL Server.

Collegamento ipertestuale Le tabelle di Access contengono colonne di collegamenti ipertestuali non supportate 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 Access è comunque possibile usare il comportamento dei collegamenti ipertestuali in maschere e report se si imposta la proprietà Hyperlink 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 delimitato. 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 sul mapping dei tipi di dati di Access e SQL Server, vedere Confrontare i tipi di dati.

Nota I campi multivalore non vengono convertiti.

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

Visual Basic

Anche se VBA non è supportato da SQL Server, tenere presente 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 a essi 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 tabella ed espressioni di regole predefinite o di convalida. SQL Server impossibile eseguire queste funzioni definite dall'utente. Potrebbe essere necessario riprogettare manualmente queste funzioni e convertirle in stored procedure su SQL Server.

Ottimizzare le prestazioni

Di gran lunga, il modo più importante per ottimizzare le prestazioni con il nuovo back-end SQL Server consiste nel decidere quando usare le 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 client-server di elaborazione. Segui queste linee guida generali:

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

Ottimizzare le prestazioni nel modello di database server client

Per altre informazioni, vedere Creare una query pass-through.

Di seguito sono riportate altre linee guida consigliate.

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

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

Per le maschere, usare una visualizzazione SQL per una maschera di sola lettura e una visualizzazione indicizzata SQL per una maschera di lettura/scrittura come origine record.
Per i report, usare una visualizzazione SQL come origine record. Tuttavia, 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 dei dati in una maschera o un report Non visualizzare i dati finché l'utente non lo richiede. Ad esempio, mantenere vuota la proprietà originerecord, fare in modo che gli utenti selezionino un filtro nella maschera e quindi popolano la proprietà originerecord con il filtro. In alternativa, usare la clausola where di DoCmd.OpenForm e DoCmd.OpenReport per visualizzare i record esatti necessari all'utente. È consigliabile disattivare l'esplorazione dei record.

Prestare attenzione con query eterogenee Evitare di eseguire una query che combina una tabella di Access locale e SQL Server tabella collegata, detta anche query ibrida. Questo tipo di query richiede ancora access per scaricare tutti i dati SQL Server nel computer locale e quindi eseguire la query, la query non viene eseguita in SQL Server.

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

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