Migrar uma base de dados do Access para o SQL Server

Aplica-se A

Access para Microsoft 365 Access 2024 Access 2021 Access 2019 Access 2016

Obtenha ajuda com o Copilot

Todos temos limites e uma base de dados do Access não é exceção. Por exemplo, uma base de dados do Access tem um limite de tamanho de 2 GB e não pode suportar mais de 255 utilizadores em simultâneo. Por isso, quando chegar a altura de tornar a sua base de dados do Access mais robusta, pode migrar para o SQL Server. SQL Server (quer seja no local ou na Azure nuvem) suporta grandes quantidades de dados, mais utilizadores simultâneos e tem uma capacidade superior à do motor de base de dados JET/ACE. Este guia dá-lhe um início sem problemas para o seu percurso de SQL Server, ajuda a preservar as soluções front-end do Access que criou e, espera-se, motiva-o a utilizar o Access para futuras soluções de base de dados. Utilize o Assistente de Migração do Microsoft SQL Server (SSMA) para migrar com êxito, siga estas etapas.

As etapas da migração de banco de dados para SQL Server

Antes de começar

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

Acerca de bases de dados divididas

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

Como resultado, continua a poder manter a base de dados front-end do Access com tabelas ligadas às tabelas SQL Server. De forma eficaz, pode tirar partido das vantagens do rápido desenvolvimento de aplicações proporcionado por uma base de dados do Access, juntamente com a escalabilidade do SQL Server.

SQL Server benefícios

Ainda precisa de convencer a migrar para o SQL Server? Eis alguns benefícios adicionais que deve considerar:

Mais utilizadores simultâneos SQL Server podem processar muito mais utilizadores em simultâneo do que o Access e minimiza os requisitos de memória quando são adicionados mais utilizadores.
Maior disponibilidade Com SQL Server, você pode fazer backup dinamicamente, incremental ou completo, do banco de dados enquanto ele está em uso. 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.
Alto desempenho e escalabilidade Normalmente, a base de dados SQL Server tem um melhor desempenho do que uma base de dados do Access, especialmente se se tratar de uma base de dados de grandes terabytes. Além disso, o SQL Server processa consultas de forma muito mais rápida e eficiente processando consultas em paralelo, usando vários threads nativos em um único processo para lidar com solicitações do usuário.
Segurança melhorada Usando uma conexão confiável, SQL Server integra com a segurança do sistema Windows para fornecer um único acesso integrado à rede e ao banco de dados, empregando o melhor dos dois sistemas de segurança. Isto facilita muito a administração de esquemas de segurança complexos. SQL Server é o armazenamento ideal para informações confidenciais, como números de 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 se houver uma falha de energia, SQL Server pode recuperar automaticamente a base de dados para um estado consistente numa questão de minutos sem intervenção do administrador da base de dados.
Uso de VPN O Access e as Redes Privadas Virtuais (VPN) não se dão bem. No entanto, com o SQL Server, os utilizadores remotos continuam a poder utilizar a base de dados de front-end do Access num computador e o SQL Server back-end localizado atrás da firewall de VPN.
SQL do Azure Server Além dos benefícios do 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 benefícios diferentes:

Banco de dados único/pools elásticos Esta opção tem o seu próprio conjunto de recursos geridos através de um servidor Base de Dados SQL. Uma única base de dados é como uma base de dados contida no SQL Server. Você também pode adicionar um pool elástico, que é uma coleção de bancos de dados com um conjunto compartilhado de recursos gerenciados por meio do servidor Base de Dados SQL. Os recursos de SQL Server usados com mais freqüência estão disponíveis com backups, patches e recuperação integrados. No entanto, não existe um tempo de manutenção exato garantido e a migração a partir do SQL Server pode ser difícil.
Instância gerida Esta opção é uma coleção de bases de dados do sistema e do utilizador com um conjunto de recursos partilhado. Uma instância gerida é como uma instância da base de dados SQL Server que é altamente compatível com SQL Server no local. Uma instância gerenciada tem backups, aplicação de patches e recuperação integrados e é fácil de migrar do SQL Server. No entanto, há um pequeno número de recursos de SQL Server que não estão disponíveis e sem tempo de manutenção exato garantido.
Azure Máquina Virtual Esta opção permite executar SQL Server dentro de uma máquina virtual na nuvem Azure. Tem controlo total sobre o motor SQL Server e um caminho de migração fácil. Mas você precisa gerenciar seus backups, patches e recuperação.

Para obter mais informações, consulte Escolhendo seu caminho de migração de banco de dados para Azure e O que é SQL do Azure?.

Primeiros passos

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

Adicionar índices de tabela e chaves primárias Certifique-se de que cada tabela do Access tem um índice e uma chave primária. SQL Server exige que todas as tabelas tenham, pelo menos, um índice e uma tabela ligada para ter uma chave primária se for possível atualizar a tabela.
Verificar relações entre chaves primárias/externas 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 tipos e tamanhos de dados diferentes em restrições de chaves externas.
Remover a coluna Anexo O SSMA não migra tabelas que contenham a coluna Anexo.

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

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 no formato de ficheiro .mdb, remova a segurança de nível de utilizador.
Faça uma cópia de segurança da sua base de dados. Para obter mais informações, consulte Proteger seus dados com processos de backup e restauração.

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 mais fácil de executar e verificar a sua migração. Quando você se conectar, use LocalDB como a instância do banco de dados.

Sugestão Se possível, utilize uma versão autónoma do Access.

Executar SSMA

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

Observação O processo de migração pode demorar algum tempo, consoante o tamanho dos objetos da sua base de dados e a quantidade de dados que tem de ser transferida.

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-la num computador que tenha acesso à base de dados do Access a partir da rede numa pasta partilhada.
Siga as instruções iniciais no SSMA para fornecer informações básicas, como a localização do SQL Server, a base de dados do Access e os objetos a 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 Rever Ferramentas>Definições>de Projeto Gerais.
O campo rowversion ajuda a evitar conflitos de registos. O Access utiliza este campo rowversion numa tabela ligada SQL Server para determinar quando é que o registo foi atualizado pela última vez. Além disso, se adicionar o campo rowversion a uma consulta, o Access utiliza-o para voltar a selecionar 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 pode ocorrer com os tipos de dados 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, consulte rowversion.
Observação Evite confundir rowversion com carimbos de data/hora. Embora a palavra-chave "carimbo de data/hora" seja sinónimo de "rowversion" no SQL Server, não pode utilizar "rowversion" como um carimbo de data/hora de uma entrada de dados.
Para definir tipos de dados precisos, selecione Revisar Ferramentas>Configurações do Projeto>Mapeamento de tipos. Por exemplo, se armazenar apenas texto em inglês, pode utilizar o tipo de dados varchar em vez de nvarchar .

Converter objetos

O SSMA converte objetos do Access em objetos SQL Server, mas não copia os objetos imediatamente. 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
Restrições de verificação (permitir propriedade de coluna de comprimento zero, regra de validação de coluna, validação de tabela)

Como prática recomendada, utilize o relatório de avaliação SSMA, que apresenta os resultados de conversões, incluindo erros, avisos, mensagens informativas, estimativas de tempo para efetuar a migração e passos de correção de erros individuais a serem executados antes de mover realmente os objetos.

Converter objetos de banco de dados pega as definições de objeto dos metadados do Access, converte-os em sintaxe Transact-SQL (T-SQL) equivalente e, em seguida, carrega essas informações no projeto. Em seguida, pode ver os objetos SQL Server ou SQL Azure e as respetivas propriedades ao utilizar o SQL Server ou SQL Azure Explorador de Metadados.

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

Sugestão Assim que tiver migrado a sua base de dados do Access com êxito, guarde o ficheiro de projeto para utilizar mais tarde, para que possa migrar os seus dados novamente para migração de teste ou final.

Ligar tabelas

Pondere instalar a versão mais recente dos controladores OLE DB e ODBC do SQL Server em vez de utilizar os controladores de SQL Server nativos fornecidos com o Windows. Não só os drivers mais recentes são mais rápidos, mas eles suportam novos recursos em SQL do Azure que os drivers anteriores não fazem. 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 agora aloja os seus dados. Ligar diretamente a partir do Access também lhe proporciona 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.

Observação 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 utilizem a nova aplicação ou utilize programaticamente as cadeia de ligação armazenadas no ficheiro DSN.

Para obter mais informações, consulte Vincular ou importar dados de um banco de dados do SQL do Azure Server e Importar ou vincular a dados em um banco 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 de forma conveniente. Para obter mais informações, consulte Gerenciar tabelas vinculadas.

Testar e rever

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

Consultas

Apenas as Consultas Selecionar são convertidas; as outras consultas não o são, incluindo as Consultas Selecionar que utilizam parâmetros. Algumas consultas podem não ser convertidas completamente 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 em funções 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 o SQL Server têm tipos de dados semelhantes, mas esteja ciente dos 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 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 ter um melhor desempenho com a versão de 64 bits do Access. Para 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, a 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, você pode selecionar a coluna de bits para definir a propriedade Allow Nulls como NO. 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, consulte Tabela vinculada do Access para SQL-Server banco de dados retorna #deleted.
Utilize o tipo de dados Data/Hora do Access para mapear para o tipo de dados data/hora. Utilize o tipo de dados Data e Hora Prolongada do Access para mapear para o tipo de dados datetime2 que tem um intervalo de datas e horas maior. Para obter mais informações, consulte Usando o tipo de dados Data/Hora Estendida.
Ao consultar datas no SQL Server, tenha em conta a hora, bem como a data. Por exemplo:
- DataEncomendado entre 1/1/19 e 31/1/19 pode não incluir todas as encomendas.
- DataEncomendado entre 1/1/19 00:00:00 AM E 31/1/19 23:59:59 PM inclui todos os pedidos.

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, ponderar armazenar ligações para os ficheiros na sua base de dados SQL Server. Como alternativa, você pode usar FILESTREAM, FileTables ou Remote BLOB Store (RBS) para manter os anexos armazenados no banco de dados SQL Server.

Hiperligação As tabelas do Access têm colunas de hiperligação que o SQL Server não suporta. 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 de hiperligação em formulários e relatórios se definir a propriedade Hiperligação do controlo para 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 como mapear os tipos de dados do Access e do SQL Server, consulte Comparar tipos de dados.

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

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

Visual Basic

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

Funções do 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 solicitados são transmitidos para o Microsoft Access para serem processados. Na maioria dos casos, estas consultas deverão 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 em módulos VBA para processar os dados que lhes são transmitidos. 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 listagem em formulários, relatórios e campos de tabela, e expressões de regras predefinidas ou de validação. SQL Server não pode executar essas funções definidas pelo usuário. Pode ser necessário reestruturar manualmente essas funções e convertê-las em procedimentos armazenados no SQL Server.

Otimizar o desempenho

De longe, a maneira mais importante de otimizar o desempenho com sua nova SQL Server de back-end é decidir quando usar consultas locais ou remotas. Ao migrar os seus dados para o SQL Server, também está a passar de um servidor de ficheiros para um modelo de computação de base de dados cliente-servidor. Siga estas diretrizes gerais:

Execute pequenas consultas só de leitura no cliente para um acesso mais rápido.
Execute consultas longas de leitura/escrita no servidor para tirar partido das maiores capacidades de processamento.
Minimize o tráfego de rede com filtros e agregação para transferir apenas os dados 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 orientações adicionais recomendadas.

Colocar lógica no servidor Seu aplicativo também pode usar exibições, funções definidas pelo usuário, procedimentos armazenados, campos calculados e gatilhos para centralizar e compartilhar lógica do aplicativo, regras e políticas de negócios, consultas complexas, validação de dados e código de integridade referencial no servidor, em vez de no cliente. Pergunte a si mesmo: essa 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 origem de registos. No entanto, crie uma vista separada para cada relatório, de modo a poder atualizar mais facilmente um relatório específico, sem afetar os outros relatórios.

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

Tenha cuidado com consultas heterogéneas Evite executar uma consulta que combina uma tabela do Access local com SQL Server tabela ligada, por vezes designada consulta híbrida. Este tipo de consulta continua a exigir 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 o uso de tabelas locais para dados que raramente mudam, como a lista de estados ou províncias de um 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, Usar o Analisador de Desempenho para otimizar um banco de dados do Access e Otimizar aplicativos do Microsoft Office Access vinculados ao SQL Server.