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 em que o 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 do Access front-end com tabelas vinculadas às tabelas do 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 do SQL Server.

Benefícios do SQL Server

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

Usuários mais simultâneos O 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 o 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, 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 de usuário.
Segurança aprimorada Usando uma conexão confiável, o SQL Server integra-se à 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. O SQL Server é o armazenamento ideal para informações confidenciais, como números da Previdência Social, dados de cartão de crédito e endereços confidenciais.
Recuperabilidade imediata Se o sistema operacional falhar ou a energia acabar, o 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 o SQL Server, os usuários remotos ainda podem usar o banco de dados front-end access em uma área de trabalho e o back-end do SQL Server 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 do SQL Server do Azure

Se você estiver migrando para o SQL Server do Azure, 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 de 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 de Banco de Dados SQL. Os recursos mais usados do SQL Server 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 do 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 do SQL Server que é altamente compatibilidade com o SQL Server local. Uma instância gerenciada tem backups internos, patching, recuperação e é fácil migrar do SQL Server. No entanto, há um pequeno número de recursos do 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 o SQL Server dentro de uma máquina virtual na nuvem do Azure. Você tem controle total sobre o mecanismo do 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 é o 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. O SQL Server exige 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. O 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 a edição do Microsoft SQL Server Express em sua área de trabalho que dá suporte a até 10 GB e é uma maneira gratuita e fácil de executar e verificar 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 o Microsoft SQL Server Migration Assistant (SSMA) para facilitar a migração. O SSMA migra principalmente tabelas e seleciona consultas sem parâmetros. Formulários, relatórios, macros e módulos VBA não são convertidos. O SQL Server Metadata Explorer exibe seus objetos de banco de dados access e objetos SQL Server 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 do 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 o 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 uma tabela vinculada do SQL Server 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 da palavra-chave seja um sinônimo de inversão de linha no SQL Server, você não pode usar a opção rowversion como uma maneira 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 você armazenar apenas o texto em inglês, poderá usar o tipo de dados varchar em vez de nvarchar .

Converter objetos

O SSMA converte objetos access em objetos SQL Server, mas não copia os objetos imediatamente. O SSMA fornece uma lista dos seguintes objetos a serem migrados para que você possa decidir se deseja movê-los para o banco de dados SQL Server:

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

Como prática recomendada, use o relatório de avaliação do SSMA, que mostra os resultados da conversão, incluindo erros, avisos, mensagens informativas, estimativas de tempo para executar a migração e etapas de correção de erro individuais a serem executadas antes de mover os objetos.

A conversão de objetos de banco de dados tira as definições de objeto dos metadados do Access, converte-as em sintaxe T-SQL (Transact-SQL) equivalente e 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 Metadata Explorer.

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

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

Tabelas de link

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 são enviados com o Windows. Não só os drivers mais recentes são mais rápidos, mas dão suporte a novos recursos no SQL do Azure que os drivers anteriores não oferecem. Você pode instalar os drivers em cada computador em que o banco de dados convertido é usado. Para obter mais informações, consulte Microsoft OLE DB Driver 18 for SQL Server e Microsoft ODBC Driver 17 for SQL Server.

Depois de migrar as tabelas do Access, você pode vincular-se às tabelas no SQL Server que agora hospeda seus dados. A vinculação diretamente 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 configuradas pelo administrador do 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 Server do Azure e Importar ou vincular a dados em um banco de dados do SQL Server.

Dica Não se esqueça de usar o Gerenciador de Tabelas Vinculados no Access para atualizar e revincular tabelas convenientemente. 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 Selecionar Consultas são convertidos; outras consultas não são, incluindo Selecionar Consultas que usam parâmetros. Algumas consultas podem não ser completamente convertidas 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. Erros de sintaxe também podem exigir a conversão manual de funções e tipos de dados específicos do Access em 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 estejam cientes dos problemas potenciais a seguir.

Número Grande O tipo de dados Número Grande armazena um valor não monetário e numérico e é compatível com o tipo de dados bigint do SQL. Você pode usar esse tipo de dados para calcular com eficiência números grandes, mas ele requer o uso do formato de arquivo de banco de dados .accdb do Access 16 (16.0.7812 ou posterior) e tem um desempenho melhor 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 Access Yes/No é convertida em um campo de bits do SQL Server. Para evitar o bloqueio de registros, verifique se o campo bit está definido para não permitir valores NULL. NO SSMA, você pode selecionar a coluna de bits para definir a propriedade Permitir Nulls 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 Acessar tabela vinculada a SQL-Server banco de dados retorna #deleted.
Use o tipo de dados Data/Hora de Acesso para mapear para o tipo de dados datetime. Use o tipo de dados Data/Hora de Acesso Estendido 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 estendido data/hora.
Ao consultar as datas no SQL Server, leve em conta a hora e a data. Por exemplo:
- DateOrdered Entre 1/1/19 e 31/19 pode não incluir todos os pedidos.
- DateOrdered Between 1/1/19 00:00:00 e 31/11/19 11:59:59 PM inclui todos os pedidos.

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

Hiperlink As tabelas de acesso têm colunas de hiperlink que o SQL Server não dá suporte. 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. Em sua solução access, você ainda pode usar o comportamento do hiperlink em formulários e relatórios se definir a propriedade Hyperlink para o controle como true.

Campo multivalorizado O campo multivalued access é convertido no 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 como mapear tipos de dados do Access e do SQL Server, consulte Comparar tipos de dados.

Observação Campos multivalorizados não são convertidos.

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

Visual Basic

Embora o VBA não tenha suporte no SQL Server, observe os seguintes problemas possíveis:

Funções VBA em Consultas As consultas de acesso dão suporte a funções VBA em dados em uma coluna de consulta. Mas as consultas de acesso 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 para 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 lista em formulários, relatórios e campos de tabela e expressões padrão ou de regra 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 o novo SQL Server de back-end é decidir quando usar consultas locais ou remotas. Ao migrar seus dados para o SQL Server, você também está migrando de um servidor de arquivos para um modelo de banco de dados cliente-servidor de computação. Siga estas diretrizes gerais:

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

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

Para obter mais informações, consulte Criar uma consulta de passagem.

A seguir estão diretrizes adicionais e 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 de aplicativo, regras e políticas de negócios, consultas complexas, validação de dados e código de integridade referencial no servidor e não no cliente. Pergunte a si mesmo, essa consulta ou tarefa pode ser executada no servidor cada vez mais rápido? Por fim, teste cada consulta para garantir o 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 uma tabela vinculada do SQL Server, às vezes chamada de consulta híbrida. Esse tipo de consulta ainda requer o Access para baixar todos os dados do SQL Server para o computador local e, em seguida, executar 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 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 Ajuste do Mecanismo de Banco de Dados, Usar o Analisador de Desempenho para otimizar um banco de dados access e Otimizar aplicativos do Microsoft Office Access vinculados ao SQL Server.