Migrar uma base de dados do Access para o SQL Server

Antes de começar

As secções seguintes fornecem informações de fundo e outras informações para o ajudar a começar.

Acerca de bases de dados divididas

Todos os objetos de base de dados do Access podem estar num ficheiro de base de dados ou podem ser armazenados em dois ficheiros de base de dados: uma base de dados front-end e uma base de dados de back-end. Isto chama-se dividir a base de dados e foi concebido para facilitar a partilha num ambiente de rede. O ficheiro de base de dados de back-end só tem de conter tabelas e relações. O ficheiro de front-end só tem de conter todos os outros objetos, incluindo formulários, relatórios, consultas, macros, módulos VBA e tabelas ligadas à base de dados de back-end. Quando migra uma base de dados do Access, esta é semelhante a uma base de dados dividida nesse SQL Server está a funcionar como um novo back-end para os dados que estão agora localizados num servidor.

Como resultado, ainda pode manter a base de dados do Access de front-end com tabelas ligadas às tabelas SQL Server. De forma eficaz, pode obter os benefícios do desenvolvimento rápido de aplicações que uma base de dados do Access proporciona, juntamente com a escalabilidade de SQL Server.

SQL Server benefícios

Ainda precisa de ser convincente para migrar para SQL Server? Eis alguns benefícios adicionais em que pensar:

Mais utilizadores simultâneos SQL Server pode processar muitos mais utilizadores simultâneos do que o Access e minimiza os requisitos de memória quando são adicionados mais utilizadores.
Maior disponibilidade Com SQL Server, pode fazer uma cópia de segurança dinâmica, incremental ou concluída, da base de dados enquanto estiver a ser utilizada. Consequentemente, não tem de forçar os utilizadores a saírem da base de dados para criar uma cópia de segurança dos dados.
Elevado desempenho e escalabilidade Normalmente, a base de dados SQL Server tem um melhor desempenho do que uma base de dados do Access, especialmente com uma base de dados de tamanho terabyte grande. Além disso, SQL Server processa consultas de forma muito mais rápida e eficiente ao processar consultas em paralelo, utilizando vários threads nativos num único processo para processar pedidos de utilizador.
Segurança melhorada Através de uma ligação fidedigna, SQL Server integra-se com a segurança do sistema Windows para fornecer um único acesso integrado à rede e à base de dados, empregando o melhor de ambos os sistemas de segurança. Isto torna muito mais fácil administrar esquemas de segurança complexos. SQL Server é o armazenamento ideal para informações confidenciais, como números da Segurança Social, dados de cartão de crédito e endereços confidenciais.
Capacidade de recuperação imediata Se o sistema operativo falhar ou a energia se desligar, SQL Server pode recuperar automaticamente a base de dados para um estado consistente numa questão de minutos e sem intervenção do administrador da base de dados.
Utilização da VPN O Access e as Redes Privadas Virtuais (VPN) não se dão bem. No entanto, com SQL Server, os utilizadores remotos ainda podem utilizar a base de dados de front-end do Access num ambiente de trabalho e o back-end SQL Server localizado atrás da firewall da VPN.
Microsoft SQL Azure Server Além dos benefícios da SQL Server, oferece escalabilidade dinâmica sem tempo de inatividade, otimização inteligente, escalabilidade e disponibilidade globais, eliminação de custos de hardware e administração reduzida.

Escolher a melhor opção SQL do Azure Server

Se estiver a migrar para o SQL do Azure Server, existem três opções à escolha, cada uma com diferentes benefícios:

Base de dados individual/conjuntos elásticos Esta opção tem o seu próprio conjunto de recursos geridos através de um servidor Base de Dados SQL. Uma base de dados individual é como uma base de dados contida no SQL Server. Também pode adicionar um conjunto elástico, que é uma coleção de bases de dados com um conjunto partilhado de recursos geridos através do servidor Base de Dados SQL. As funcionalidades de SQL Server mais utilizadas estão disponíveis com cópias de segurança incorporadas, aplicação de patches e recuperação. No entanto, não há tempo de manutenção e migração exatos garantidos do SQL Server poderá ser difícil.
Instância gerida Esta opção é uma coleção de bases de dados de sistema e de utilizador com um conjunto partilhado de recursos. Uma instância gerida é como uma instância da base de dados SQL Server altamente compatibilidade com SQL Server no local. Uma instância gerida tem cópias de segurança incorporadas, aplicação de patches, recuperação e é fácil de migrar a partir de SQL Server. No entanto, existe um pequeno número de funcionalidades SQL Server que não estão disponíveis e nenhum tempo de manutenção exato garantido.
Máquina Virtual do Azure Esta opção permite-lhe executar SQL Server dentro de uma máquina virtual na cloud do Azure. Tem controlo total sobre o motor de SQL Server e um caminho de migração fácil. No entanto, tem de gerir as suas cópias de segurança, patches e recuperação.

Para obter mais informações, veja Escolher o caminho de migração da base de dados para o Azure e O que é SQL do Azure?.

Primeiros passos

Existem alguns problemas que pode resolver antecipadamente que podem ajudar a simplificar o processo de migração antes de executar o SSMA:

Adicionar índices de tabelas e chaves primárias Certifique-se de que cada tabela do Access tem um índice e uma chave primária. SQL Server requer que todas as tabelas tenham pelo menos um índice e requer que uma tabela ligada tenha uma chave primária se a tabela puder ser atualizada.
Verificar relações de chave primária/externa Certifique-se de que estas relações se baseiam em campos com tipos e tamanhos de dados consistentes. SQL Server não suporta colunas associadas com diferentes tipos de dados e tamanhos em restrições de chave externa.
Remover a coluna Anexo O SSMA não migra tabelas que contenham a coluna Anexo.

Antes de executar o SSMA, siga os primeiros passos seguintes.

Feche a base de dados do Access.
Certifique-se de que os utilizadores atuais ligados à base de dados também fecham a base de dados.
Se a base de dados estiver .mdb formato de ficheiro, remova a segurança ao nível do utilizador.
Faça uma cópia de segurança da base de dados. Para obter mais informações, veja Proteger os seus dados com processos de cópia de segurança e restauro.

Sugestão Considere instalar Microsoft SQL Server Express edição no seu ambiente de trabalho, que suporta até 10 GB e é uma forma gratuita e fácil de executar e verificar a migração. Quando se ligar, utilize LocalDB como a instância da base de dados.

Sugestão Se possível, utilize uma versão autónoma do Access. Se só conseguir utilizar Microsoft 365, utilize o motor de base de dados do Access 2010 para migrar a base de dados do Access ao utilizar o SSMA. Para obter mais informações, consulte Microsoft Access Database Engine 2010 Redistributable.

Executar o SSMA

A Microsoft fornece Assistente de Migração do Microsoft SQL Server (SSMA) para facilitar a migração. O SSMA migra principalmente tabelas e seleciona consultas sem parâmetros. Os módulos de formulários, relatórios, macros e VBA não são convertidos. O SQL Server Metadata Explorer apresenta os objetos da base de dados do Access e SQL Server objetos que lhe permitem rever o conteúdo atual de ambas as bases de dados. Estas duas ligações são guardadas no ficheiro de migração caso decida transferir objetos adicionais no futuro.

Nota O processo de migração pode demorar algum tempo, dependendo do tamanho dos objetos da base de dados e da quantidade de dados que têm de ser transferidos.

Para migrar uma base de dados com o SSMA, transfira e instale primeiro o software ao fazer duplo clique no ficheiro MSI transferido. Certifique-se de que instala a versão de 32 ou 64 bits adequada para o seu computador.
Depois de instalar o SSMA, abra-o no seu ambiente de trabalho, de preferência a partir do computador com o ficheiro de base de dados do Access.

Também pode abri-lo num computador que tenha acesso à base de dados do Access a partir da rede numa pasta partilhada.
Siga as instruções ininterruptas no SSMA para fornecer informações básicas, como a localização SQL Server, a base de dados e os objetos do Access para migrar, informações de ligação e se pretende criar tabelas ligadas.
Se estiver a migrar para o SQL Server 2016 ou posterior e quiser atualizar uma tabela ligada, adicione uma coluna rowversion ao selecionar Ferramentas de Revisão > Definições do Projeto > Geral.

O campo rowversion ajuda a evitar conflitos de registos. O Access utiliza este campo de rowversion numa SQL Server tabela ligada para determinar quando o registo foi atualizado pela última vez. Além disso, se adicionar o campo rowversion a uma consulta, o Access utiliza-o para selecionar novamente a linha após uma operação de atualização. Isto melhora a eficiência ao ajudar a evitar erros de conflito de escrita e cenários de eliminação de registos que podem ocorrer quando o Access deteta resultados diferentes da submissão original, como podem ocorrer com tipos de dados de número de vírgula flutuante e acionadores que modificam colunas. No entanto, evite utilizar o campo rowversion em formulários, relatórios ou código VBA. Para obter mais informações, veja rowversion.

Nota Evite confundir a rowversion com carimbos de data/hora. Embora o carimbo de data/hora da palavra-chave seja um sinónimo de rowversion no SQL Server, não pode utilizar a rowversion como forma de carimbo de data/hora de uma entrada de dados.
Para definir tipos de dados precisos, selecione Ferramentas de Revisão > Definições do Projeto > Mapeamento de Tipos. Por exemplo, se armazenar apenas texto em inglês, pode utilizar o varchar em vez do tipo de dados nvarchar .

Converter objetos

O SSMA converte objetos do Access em objetos SQL Server, mas não copia os objetos de imediato. O SSMA fornece uma lista dos seguintes objetos a migrar para que possa decidir se pretende movê-los para SQL Server base de dados:

Tabelas e Colunas
Selecione Consultas sem parâmetros.
Chaves primárias e externas
Índices e Valores predefinidos
Verificar restrições (permitir propriedade de coluna de comprimento zero, regra de validação de colunas, validação de tabela)

Como melhor prática, utilize o relatório de avaliação do SSMA, que mostra os resultados da conversão, incluindo erros, avisos, mensagens informativas, estimativas de tempo para realizar a migração e passos de correção de erros individuais a executar antes de mover os objetos.

A conversão de objetos de base de dados utiliza as definições de objetos dos metadados do Access, converte-as em sintaxe transact-SQL (T-SQL) equivalente e, em seguida, carrega estas informações para o projeto. Em seguida, pode ver os objetos de SQL Server ou SQL Azure e as respetivas propriedades com SQL Server ou SQL Azure o Explorador de Metadados.

Para converter, carregar e migrar objetos para SQL Server, siga este guia.

Sugestão Depois de migrar com êxito a base de dados do Access, guarde o ficheiro de projeto para utilização posterior, para que possa migrar novamente os seus dados para testes ou migração final.

Ligar tabelas

Considere instalar a versão mais recente do SQL Server controladores OLE DB e ODBC em vez de utilizar os controladores SQL Server nativos que são enviados com o Windows. Não só os controladores mais recentes são mais rápidos, como suportam novas funcionalidades no SQL do Azure que os controladores anteriores não suportam. Pode instalar os controladores em cada computador onde a base de dados convertida é utilizada. Para obter mais informações, consulte Microsoft OLE DB Driver 18 para SQL Server e Microsoft ODBC Driver 17 para SQL Server.

Depois de migrar as tabelas do Access, pode ligar às tabelas no SQL Server que aloja agora os seus dados. Ligar diretamente a partir do Access também lhe fornece uma forma mais simples de ver os seus dados em vez de utilizar as ferramentas de gestão de SQL Server mais complexas. Pode consultar e editar dados ligados consoante as permissões configuradas pelo administrador da base de dados SQL Server.

Nota Se criar um DSN ODBC quando ligar à base de dados SQL Server durante o processo de ligação, crie o mesmo DSN em todos os computadores que utilizam a nova aplicação ou utilize programaticamente o cadeia de ligação armazenados no ficheiro DSN.

Para obter mais informações, consulte Ligar ou importar dados de uma Base de Dados do SQL do Azure Server e Importar ou ligar a dados numa base de dados SQL Server.

Sugestão Não se esqueça de utilizar o Gestor de Tabelas Ligadas no Access para atualizar e voltar a ligar tabelas convenientemente. Para obter mais informações, veja Gerir tabelas ligadas.

Testar e rever

As secções seguintes descrevem problemas comuns que pode encontrar durante a migração e como lidar com os mesmos.

Consultas

Apenas selecionar consultas são convertidas; outras consultas não são, incluindo Selecionar Consultas que assumem parâmetros. Algumas consultas podem não ser completamente convertidas e o SSMA comunica erros de consulta durante o processo de conversão. Pode editar manualmente objetos que não são convertidos com a sintaxe T-SQL. Os erros de sintaxe também podem exigir a conversão manual de funções e tipos de dados específicos do Access para SQL Server. Para obter mais informações, consulte Comparação do SQL do Access com o TSQL do SQL Server.

Tipos de dados

O Access e SQL Server têm tipos de dados semelhantes, mas tenha em atenção os seguintes potenciais problemas.

Número Grande O tipo de dados Número Grande armazena um valor numérico não monetário e é compatível com o tipo de dados bigint do SQL. Pode utilizar este tipo de dados para calcular números grandes de forma eficiente, mas é necessário utilizar o formato de ficheiro de base de dados .accdb do Access 16 (16.0.7812 ou posterior) e tem um melhor desempenho com a versão de 64 bits do Access. Para obter mais informações, consulte Utilizar o tipo de dados Número Grande e Escolher entre a versão de 64 bits ou de 32 bits do Office.

Sim/Não Por predefinição, uma coluna Sim/Não do Access é convertida num campo de SQL Server bits. Para evitar o bloqueio de registos, certifique-se de que o campo de bits está definido para não permitir valores NULL. NO SSMA, pode selecionar a coluna de bits para definir a propriedade Permitir Nulos como NÃO. No TSQL, utilize as instruções CREATE TABLE ou ALTER TABLE .

Data e Hora Existem várias considerações de data e hora:

Se o nível de compatibilidade da base de dados for 130 (SQL Server 2016) ou superior e uma tabela ligada contiver uma ou mais colunas datetime ou datetime2, a tabela poderá devolver a mensagem #deleted nos resultados. Para obter mais informações, veja Access linked table to SQL-Server database returns #deleted (O Access ligou a tabela a SQL-Server base de dados devolve #deleted).
Utilize o tipo de dados Data/Hora do Access para mapear para o tipo de dados datetime. Utilize o tipo de dados Data/Hora de Acesso Prolongada para mapear para o tipo de dados datetime2 que tem um intervalo de data e hora maior. Para obter mais informações, veja Utilizar o tipo de dados Data/Hora Prolongada.
Ao consultar datas em SQL Server, tenha em conta a hora, bem como a data. Por exemplo:
- DateOrdered Entre 1/1/19 e 31/1/19 não pode incluir todas as encomendas.
- DateOrdered Entre 1/1/19 00:00:00 E 31/1/19 23:59:59 inclui todas as encomendas.

Anexo O tipo de dados Anexo armazena um ficheiro na base de dados do Access. No SQL Server, tem várias opções a considerar. Pode extrair os ficheiros da base de dados do Access e, em seguida, considerar armazenar ligações para os ficheiros na base de dados SQL Server. Em alternativa, pode utilizar FILESTREAM, FileTables ou Arquivo de BLOBs Remotos (RBS) para manter os anexos armazenados na base de dados SQL Server.

Hiperligação As tabelas do Access têm colunas de hiperligação que SQL Server não suportam. Por predefinição, estas colunas serão convertidas em colunas nvarchar(max) em SQL Server, mas pode personalizar o mapeamento para escolher um tipo de dados mais pequeno. Na sua solução do Access, ainda pode utilizar o comportamento da hiperligação em formulários e relatórios se definir a propriedade Hiperligação para o controlo como verdadeiro.

Campo de valores múltiplos O campo de valores múltiplos do Access é convertido em SQL Server como um campo ntext que contém o conjunto delimitado de valores. Como o SQL Server não suporta um tipo de dados de valores múltiplos que serve como modelo de uma relação muitos para muitos, poderão ser necessárias estruturas e conversões adicionais.

Para obter mais informações sobre o mapeamento do Access e SQL Server tipos de dados, veja Comparar tipos de dados.

Nota Os campos de valores múltiplos não são convertidos.

Para obter mais informações, veja Tipos de data e hora, Tipos de cadeia e binário e Tipos numéricos.

Visual Basic

Embora o VBA não seja suportado pelo SQL Server, tenha em atenção os seguintes problemas possíveis:

Funções VBA em Consultas As consultas do Access suportam funções VBA em dados numa coluna de consulta. No entanto, as consultas do Access que utilizam funções VBA não podem ser executadas no SQL Server, pelo que todos os dados pedidos são transmitidos ao Microsoft Access para processamento. Na maioria dos casos, estas consultas devem ser convertidas em consultas pass-through.

Funções Definidas pelo Utilizador em Consultas As consultas do Microsoft Access suportam a utilização de funções definidas nos módulos VBA para processar dados transmitidos aos mesmos. As consultas podem ser consultas autónomas, instruções SQL em origens de registos de formulários/relatórios, origens de dados de caixas de combinação e caixas de lista em formulários, relatórios e campos de tabela e expressões de regras predefinidas ou de validação. SQL Server não é possível executar estas funções definidas pelo utilizador. Poderá ter de reestruturar manualmente estas funções e convertê-las em procedimentos armazenados no SQL Server.

Otimizar o desempenho

De longe, a forma mais importante de otimizar o desempenho com os seus novos SQL Server de back-end é decidir quando utilizar consultas locais ou remotas. Quando migra os seus dados para SQL Server, também está a mover de um servidor de ficheiros para um modelo de base de dados cliente-servidor de computação. Siga estas diretrizes gerais:

Execute pequenas consultas só de leitura no cliente para obter o acesso mais rápido.
Execute consultas longas de leitura/escrita no servidor para tirar partido do maior poder de processamento.
Minimize o tráfego de rede com filtros e agregação para transferir apenas os dados de que precisa.

Otimizar o desempenho no modelo de base de dados do servidor cliente

Para obter mais informações, consulte Criar uma consulta pass-through.

Seguem-se diretrizes recomendadas adicionais.

Colocar lógica no servidor A sua aplicação também pode utilizar vistas, funções definidas pelo utilizador, procedimentos armazenados, campos calculados e acionadores para centralizar e partilhar lógica de aplicação, regras e políticas de negócio, consultas complexas, validação de dados e código de integridade referencial no servidor, em vez de no cliente. Pergunte a si mesmo: esta consulta ou tarefa pode ser executada no servidor melhor e mais rapidamente? Por fim, teste cada consulta para garantir um desempenho ideal.

Utilizar vistas em formulários e relatórios No Access, faça o seguinte:

Para formulários, utilize uma vista SQL para um formulário só de leitura e uma vista indexada SQL para um formulário de leitura/escrita como origem de registos.
Para relatórios, utilize uma vista SQL como a origem de registos. No entanto, crie uma vista separada para cada relatório, para que possa atualizar mais facilmente um relatório específico, sem afetar outros relatórios.

Minimizar o carregamento de dados num formulário ou relatório Não apresente dados até que o utilizador os peça. Por exemplo, mantenha a propriedade origemdosregistos em branco, faça com que os utilizadores selecionem um filtro no formulário e, em seguida, preencha a propriedade origemdosregistos com o filtro. Em alternativa, utilize a cláusula where de DoCmd.OpenForm e DoCmd.OpenReport para apresentar os registos exatos necessários para o utilizador. Considere desativar a navegação de registos.

Tenha cuidado com consultas heterogéneas Evite executar uma consulta que combine uma tabela local do Access e SQL Server tabela ligada, por vezes denominada consulta híbrida. Este tipo de consulta ainda requer que o Access transfira todos os dados SQL Server para o computador local e, em seguida, execute a consulta, não executa a consulta no SQL Server.

Quando utilizar tabelas locais Considere utilizar tabelas locais para dados que raramente mudam, como a lista de estados ou províncias num país ou região. As tabelas estáticas são frequentemente utilizadas para filtragem e podem ter um melhor desempenho no front-end do Access.

Para obter mais informações, consulte Database Engine Tuning Advisor, Utilizar o Analisador de Desempenho para otimizar uma base de dados do Access e Otimizar Aplicações do Microsoft Office Access Ligadas a SQL Server.