Migrar um banco de dados do Access para o SQL Server

Antes de começar

As seções a seguir fornecem informações em segundo plano e outras informações para ajudá-lo a começar.

Sobre bancos de dados divididos

Todos os objetos de banco de dados do Access podem estar em um arquivo de banco de dados ou podem ser armazenados em dois arquivos de banco de dados: um banco de dados front-end e um banco de dados de back-end. Isso é chamado de divisão do banco de dados e é projetado para facilitar o compartilhamento em um ambiente de rede. O arquivo de banco de dados de back-end deve conter apenas tabelas e relações. O arquivo front-end deve conter apenas todos os outros objetos, incluindo formulários, relatórios, consultas, macros, módulos VBA e tabelas vinculadas ao banco de dados back-end. Quando você migra um banco de dados access, ele é semelhante a um banco de dados dividido nesse SQL Server está agindo como um novo back-end para os dados que agora estão localizados em um servidor.

Como resultado, você ainda pode manter o banco de dados de acesso front-end com tabelas vinculadas às tabelas SQL Server. Efetivamente, você pode derivar os benefícios do rápido desenvolvimento de aplicativos que um banco de dados access fornece, juntamente com a escalabilidade de SQL Server.

SQL Server benefícios

Ainda precisa de algum convencimento para migrar para SQL Server? Aqui estão alguns benefícios adicionais para pensar:

Usuários mais simultâneos SQL Server pode lidar com muitos usuários mais simultâneos do que o Access e minimiza os requisitos de memória quando mais usuários são adicionados.
Maior disponibilidade Com SQL Server, você pode fazer backup dinâmico, incremental ou completo, do banco de dados enquanto ele estiver 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 O banco de dados SQL Server geralmente tem um desempenho melhor do que um banco de dados access, especialmente com um banco de dados grande e do tamanho de terabyte. Além disso, 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 de usuário.
Segurança aprimorada Usando uma conexão confiável, SQL Server se integra à 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. Isso torna muito mais fácil administrar esquemas de segurança complexos. SQL Server é o armazenamento ideal para informações confidenciais, como números da Previdência Social, dados de card de crédito e endereços confidenciais.
Recuperabilidade imediata Se o sistema operacional falhar ou a energia acabar, SQL Server poderá recuperar automaticamente o banco de dados para um estado consistente em questão de minutos e sem intervenção de administrador de banco de dados.
Uso de VPN O Acesso e a VPN (Redes Virtuais Privadas) não se dão bem. Mas com SQL Server, os usuários remotos ainda podem usar o banco de dados front-end access em uma área de trabalho e o SQL Server back-end localizado atrás do firewall VPN.
SQL do Azure 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.

Escolha a melhor opção SQL do Azure Servidor

Se você estiver migrando para SQL do Azure Server, há três opções para escolher, cada uma com benefícios diferentes:

Banco de dados único/pools elásticos Essa opção tem seu próprio conjunto de recursos gerenciados por meio de um servidor Banco de Dados SQL. Um único banco de dados é como um banco de dados contido 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 mais usados estão disponíveis com backups internos, patching e recuperação. Mas não há tempo de manutenção exato garantido e a migração de SQL Server pode ser difícil.
Instância gerenciada Essa opção é uma coleção de bancos de dados do sistema e do usuário com um conjunto compartilhado de recursos. Uma instância gerenciada é como uma instância do banco de dados SQL Server altamente compatibilidade com SQL Server local. Uma instância gerenciada tem backups internos, patching, recuperação e é fácil migrar de SQL Server. No entanto, há um pequeno número de recursos SQL Server que não estão disponíveis e nenhum tempo de manutenção exato garantido.
Máquina Virtual Azure Essa opção permite que você execute SQL Server dentro de uma máquina virtual na nuvem do Azure. Você tem controle total sobre o mecanismo 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, confira Escolhendo seu caminho de migração de banco de dados para o Azure e O que é SQL do Azure?.

Primeiras etapas

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

Adicionar índices de tabela e chaves primárias Verifique se cada tabela access tem um índice e uma chave primária. SQL Server requer que todas as tabelas tenham pelo menos um índice e exige que uma tabela vinculada tenha uma chave primária se a tabela puder ser atualizada.
Verificar relações de chave primária/estrangeira Verifique se essas relações são baseadas em campos com tipos e tamanhos de dados consistentes. SQL Server não dá suporte a colunas unidas com diferentes tipos de dados e tamanhos em restrições de chave estrangeira.
Remover a coluna Anexo O SSMA não migra tabelas que contêm a coluna Anexo.

Antes de executar o SSMA, siga as primeiras etapas a seguir.

Feche o banco de dados access.
Verifique se os usuários atuais conectados ao banco de dados também fecham o banco de dados.
Se o banco de dados estiver em .mdb formato de arquivo, remova a segurança no nível do usuário.
Faça backup do banco de dados. Para obter mais informações, consulte Proteger seus dados com processos de backup e restauração.

Dica Considere instalar Microsoft SQL Server Express edição em sua área de trabalho que dá suporte a até 10 GB e é uma maneira gratuita e mais fácil de executar e marcar sua migração. Ao se conectar, use LocalDB como a instância do banco de dados.

Dica Se possível, use uma versão autônoma do Access.

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. Forms, relatórios, macros e módulos VBA não são convertidos. O Explorer de metadados SQL Server exibe objetos e objetos de SQL Server de banco de dados do Access, permitindo que você examine o conteúdo atual de ambos os bancos de dados. Essas duas conexões são salvas em seu arquivo de migração caso você decida transferir objetos adicionais no futuro.

Observação O processo de migração pode levar algum tempo dependendo do tamanho dos objetos de banco de dados e da quantidade de dados que devem ser transferidos.

Para migrar um banco de dados usando o SSMA, primeiro baixe e instale o software clicando duas vezes no arquivo MSI baixado. Instale a versão apropriada de 32 ou 64 bits para seu computador.
Depois de instalar o SSMA, abra-o na área de trabalho, preferencialmente do computador com o arquivo de banco de dados Access.

Você também pode abri-lo em um computador que tenha acesso ao banco de dados access da rede em uma pasta compartilhada.
Siga as instruções inicio no SSMA para fornecer informações básicas, como o local SQL Server, o banco de dados access e objetos para migrar, informações de conexão e se você deseja criar tabelas vinculadas.
Se você estiver migrando para SQL Server 2016 ou posterior e quiser atualizar uma tabela vinculada, adicione uma coluna de rowversion selecionando Revisão Ferramentas > Configurações do Projeto > Geral.

O campo rowversion ajuda a evitar conflitos de registro. O Access usa esse campo de rowversion em um SQL Server tabela vinculada para determinar quando o registro foi atualizado pela última vez. Além disso, se você adicionar o campo rowversion a uma consulta, o Access o usará para selecionar novamente a linha após uma operação de atualização. Isso melhora a eficiência ajudando a evitar erros de conflito de gravação e cenários de exclusão de registro que podem acontecer quando o Access detecta resultados diferentes do envio original, como pode ocorrer com tipos de dados de número de ponto flutuante e gatilhos que modificam colunas. No entanto, evite usar o campo rowversion em formulários, relatórios ou código VBA. Para obter mais informações, consulte rowversion.

Observação Evite a inversão de linha confusa com carimbos de data/hora. Embora o carimbo de data/hora palavra-chave seja um sinônimo de inversão de linha em SQL Server, você não pode usar a rowversion como uma maneira 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 SQL Server ou SQL Azure e as respetivas propriedades com SQL Server ou SQL Azure metadados Explorer.

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

Dica 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.

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 utilizam a nova aplicação ou utilize programaticamente o cadeia de conexão armazenado 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.

Dica 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 saber mais, confira Comparar o SQL do Access com o SQL Server TSQL.

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 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 o mapeamento do Access e SQL Server tipos de dados, veja Comparar tipos de dados.

Observação 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 banco 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.

Usar exibições em formulários e relatórios No Access, faça o seguinte:

Para formulários, use uma exibição SQL para um formulário somente leitura e uma exibição indexada de SQL para um formulário de leitura/gravação como a fonte de registro.
Para relatórios, use uma exibição SQL como a 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 peça. Por exemplo, mantenha a propriedade recordsource em branco, faça com que os usuários selecionem um filtro em seu formulário e, em seguida, preencha a propriedade de fonte de registro com seu filtro. Ou use a cláusula em que a cláusula DoCmd.OpenForm e DoCmd.OpenReport exibe os registros exatos necessários pelo usuário. Considere desativar a navegação de registro.

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

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

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