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 quanto 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 di 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.

Vantaggi di SQL Server

Serve ancora un po' di convincenti per eseguire la migrazione a SQL Server? Ecco alcuni altri vantaggi a cui pensare:

Altri utenti simultanei SQL Server è in grado di gestire molti più utenti simultanei rispetto ad Access e riduce 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 di 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 può 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 di 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 ottimale per il server SQL di Azure

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

Singolo database/pool elastici Questa opzione ha un set di risorse gestito tramite un server di 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 di database SQL. Le funzionalità di SQL Server più usate sono disponibili con backup, patch e ripristino predefiniti. Tuttavia, 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 è come un'istanza del database di SQL Server altamente compatibile con SQL Server locale. Un'istanza gestita ha backup incorporati, patch, ripristino ed è facile eseguire la migrazione da SQL Server. Tuttavia, ci sono un numero limitato di caratteristiche di SQL Server che non sono disponibili e nessun tempo di manutenzione esatta garantito.
Macchina virtuale di Azure Questa opzione consente di eseguire SQL Server all'interno di una macchina virtuale nel cloud Azure. È possibile avere 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 Che cos'è sql di Azure?.

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 Valutare l'installazione di 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.

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 che consentono 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, informazioni di connessione e se si desidera 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 a 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 rowversion 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 di 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 nel database di SQL Server:

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 di SQL Server o SQL Azure e le relative proprietà usando SQL Server o Esplora metadati di SQL Azure.

Per convertire, caricare ed eseguire la migrazione di oggetti in 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à del codice SQL di Azure non disponibili nei 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 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 ospitano i dati. Il collegamento diretto da Access offre anche un modo più semplice per visualizzare i dati anziché usare gli strumenti di gestione di SQL Server più complessi. È possibile eseguire query e modificare i dati collegati in base alle autorizzazioni configurate dall'amministratore del database di 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 oppure a livello di programmazione usare la stringa di connessione archiviata nel file DSN.

Per altre informazioni, vedere Collegare o importare dati da un database del server SQL di Azure e Importare o collegare dati in un database di 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 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 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 bit di SQL Server. 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 versione successiva 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 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 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 Confronto dei 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 non può eseguire queste funzioni definite dall'utente. Potrebbe essere necessario riprogettare manualmente queste funzioni e convertirle in stored procedure in SQL Server.

Ottimizzare le prestazioni

Di gran lunga, il modo più importante per ottimizzare le prestazioni con il nuovo SQL Server back-end 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 una tabella collegata di SQL Server, detta anche query ibrida. Questo tipo di query richiede ancora Access per scaricare tutti i dati di SQL Server nel computer locale e quindi eseguire la query, la query non viene eseguita in SQL Server.This type of query still requires Access to download all SQL Server data to the local machine and then run the query, it does not run the query 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 Gestione ottimizzazione motore di database, Usare Analizzatore prestazioni per ottimizzare un database di Access e Ottimizzazione delle applicazioni di Microsoft Office Access collegate a SQL Server.