Migrar um banco 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 é necessário obrigar os usuários a sair do banco de dados para fazer backup 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 card 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 Banco 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 Banco 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?.

Primeiras etapas

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 marcar 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. A SQL Server Metadados Explorer apresenta os objetos da 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 o carimbo de data/hora palavra-chave seja sinónimo de rowversion no SQL Server, não pode utilizar rowversion como um método de 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, você pode exibir os objetos SQL Server ou SQL Azure e suas propriedades usando SQL Server ou SQL Azure Explorer Metadados.

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

Dica Depois de migrar com êxito seu banco de dados do Access, salve o arquivo de projeto para uso posterior, para que você possa migrar seus dados novamente para teste ou migração final.

Vincular tabelas

Considere instalar a versão mais recente dos drivers OLE DB e ODBC do SQL Server em vez de usar os drivers nativos do SQL Server que acompanham o Windows. Os drivers mais recentes não são apenas mais rápidos, mas também dão suporte a novos recursos no SQL do Azure que os drivers anteriores não dão. Você pode instalar os drivers em cada computador onde o banco de dados convertido é usado. 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, você pode vinculá-las às tabelas no SQL Server, que agora hospeda seus dados. A vinculação direta do Access também fornece uma maneira mais simples de exibir seus dados, em vez de usar as ferramentas de gerenciamento mais complexas do SQL Server. Você pode consultar e editar dados vinculados dependendo das permissões definidas pelo administrador de banco de dados do SQL Server.

Observação Se você criar um DSN ODBC ao vincular ao banco de dados do SQL Server durante o processo de vinculação, crie o mesmo DSN em todos os computadores que usam o novo aplicativo ou use programaticamente a cadeia de conexão armazenada no arquivo 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.

Dica Não se esqueça de usar o Gerenciador de Tabelas Vinculadas no Access para atualizar e vincular novamente tabelas de forma prática. Para obter mais informações, consulte Gerenciar tabelas vinculadas.

Testar e revisar

As seções a seguir descrevem problemas comuns que você pode encontrar durante a migração e como lidar com eles.

Consultas

Somente Consultas Selecionadas são convertidas; outras consultas não são, incluindo Consultas Selecionadas que usam parâmetros. Algumas consultas podem não ser convertidas completamente, e o SSMA relata erros de consulta durante o processo de conversão. Você pode editar manualmente objetos que não são convertidos usando 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 tipos de dados do SQL Server. Para saber mais, confira Comparar o SQL do Access com o SQL Server TSQL.

Tipos de dados

O Access e o SQL Server têm tipos de dados semelhantes, mas esteja ciente dos possíveis problemas a seguir.

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. Você pode usar esse tipo de dados para calcular números grandes com eficiência, mas isso requer o uso do formato de arquivo de banco de dados .accdb do Access 16 (16.0.7812 ou posterior) e tem melhor desempenho com a versão de 64 bits do Access. Para obter mais informações, consulte Usando o tipo de dados Número Grande e Escolha entre a versão de 64 bits ou 32 bits do Office.

Sim/Não Por padrão, uma coluna Sim/Não do Access é convertida em um campo de bits do SQL Server. Para evitar o bloqueio de registro, verifique se 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 Permitir Valores Nulos como NO. No TSQL, use as instruções CREATE TABLE ou ALTER TABLE .

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

Se o nível de compatibilidade do banco de dados for 130 (SQL Server 2016) ou superior, e uma tabela vinculada contiver uma ou mais colunas datetime ou datetime2, a tabela poderá retornar a mensagem #deleted nos resultados. Para obter mais informações, consulte Tabela vinculada do Access para SQL-Server banco de dados retorna #deleted.
Use o tipo de dados Data/Hora do Access para mapear para o tipo de dados de data e hora. Use o tipo de dados Data/Hora Estendida do Access para mapear para o tipo de dados datetime2 , que tem um intervalo de data e hora maior. Para obter mais informações, consulte Usando o tipo de dados Data/Hora Estendida.
Ao consultar datas no SQL Server, leve em consideração a hora e a data. Por exemplo:
- DataSolicitada Entre 01/01/19 e 31/01/19 pode não incluir todos os pedidos.
- DateOrdered Entre 1/1/19 00:00:00 E 31/1/19 23:59:59 inclui todos os pedidos.

Anexo O tipo de dados Anexo armazena um arquivo no banco de dados do Access. No SQL Server, você tem várias opções a considerar. Você pode extrair os arquivos do banco de dados do Access e considerar armazenar links para os arquivos no banco de dados do SQL Server. Como alternativa, você pode usar FILESTREAM, FileTables ou Remote BLOB store (RBS) para manter os anexos armazenados no banco de dados do SQL Server.

Hiperlink As tabelas do Access têm colunas de hiperlink incompatíveis com o SQL Server. Por padrão, essas colunas serão convertidas em colunas nvarchar(max) no SQL Server, mas você pode personalizar o mapeamento para escolher um tipo de dados menor. Na solução do Access, você ainda poderá usar o comportamento de hiperlink em formulários e relatórios se definir a propriedade Hyperlink do controle como true.

Campo de múltiplos valores O campo de valores múltiplos do Access é convertido para o SQL Server como um campo ntext que contém o conjunto de valores delimitado. Como o SQL Server não oferece suporte a um tipo de dados de múltiplos valores que modela uma relação muitos para muitos, pode ser necessário um trabalho adicional de design e conversão.

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 Campos de valores múltiplos não são convertidos.

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

Visual Basic

Embora o VBA não seja suportado pelo SQL Server, observe os seguintes possíveis problemas:

Funções VBA em Consultas As consultas do Access oferecem suporte a funções VBA em dados em uma coluna de consulta. Porém, as consultas do Access que usam funções VBA não podem ser executadas no SQL Server, portanto, todos os dados solicitados são passados para o Microsoft Access para processamento. Na maioria dos casos, essas consultas devem ser convertidas em consultas de passagem.

Funções definidas pelo usuário em consultas As consultas do Microsoft Access dão suporte ao uso de funções definidas em módulos VBA para processar dados passados a eles. As consultas podem ser consultas autônomas, instruções SQL em fontes de registro de formulário/relatório, fontes de dados de caixas de combinação e caixas de listagem em formulários, relatórios e campos de tabela e expressões de regra padrão ou de validação. O SQL Server não pode executar essas funções definidas pelo usuário. Talvez seja necessário reprojetar 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 seu novo back-end do SQL Server é decidir quando usar consultas locais ou remotas. Ao migrar seus dados para o SQL Server, você também está movendo de um servidor de arquivos para um modelo de computação de banco de dados cliente-servidor. Siga estas diretrizes gerais:

Execute pequenas consultas somente leitura no cliente para acesso mais rápido.
Execute consultas longas de leitura/gravação no servidor para aproveitar o maior poder de processamento.
Minimize o tráfego de rede com filtros e agregação para transferir apenas os dados de que você precisa.

Otimizar o desempenho no modelo de banco de dados do servidor cliente Para obter mais informações, consulte Criar uma consulta passagem.

A seguir estão as diretrizes adicionais recomendadas.

Colocar lógica no servidor Seu aplicativo também pode usar visualizações, funções definidas pelo usuário, procedimentos armazenados, campos calculados e gatilhos para centralizar e compartilhar lógica de 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 rápido? Por fim, teste cada consulta para garantir o desempenho ideal.

Usar modos de exibição em formulários e relatórios No Access, faça o seguinte:

Para formulários, use um modo de exibição SQL para um formulário somente leitura e um modo de exibição indexado SQL para um formulário de leitura/gravação como a fonte de registro.
Para relatórios, use um modo de exibição SQL como fonte de registro. No entanto, crie uma exibição separada para cada relatório, para que você possa atualizar mais facilmente um relatório específico, sem afetar outros relatórios.

Minimizar o carregamento de dados em um formulário ou relatório Não exiba dados até que o usuário os solicite. Por exemplo, mantenha a propriedade recordsource em branco, faça com que os usuários selecionem um filtro no formulário e preencha a propriedade recordsource com o filtro. Ou use a cláusula where de DoCmd.OpenForm e DoCmd.OpenReport para exibir os registros exatos necessários para o usuário. Considere desativar a navegação de registros.

Tenha cuidado com consultas heterogêneas Evite executar uma consulta que combine uma tabela do Access local e uma tabela vinculada ao SQL Server, às vezes chamada de consulta híbrida. Esse tipo de consulta ainda exige que o Access baixe todos os dados do SQL Server para o computador local e execute a consulta, ele não executa a consulta no SQL Server.

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

Para obter mais informações, consulte Orientador de Otimização do Mecanismo de Banco de Dados, Usar o Performance Analyzer para otimizar um banco de dados do Access e Otimizando aplicativos do Microsoft Office Access vinculados ao SQL Server.