Migrar una base de datos de Access a SQL Server

Antes de empezar

Las siguientes secciones proporcionan información general y otra información para ayudarle a empezar.

Acerca de las bases de datos divididas

Todos los objetos de base de datos de Access pueden estar en un archivo de base de datos o pueden almacenarse en dos archivos de base de datos: una base de datos front-end y una base de datos back-end. Esto se denomina dividir la base de datos y se ha diseñado para facilitar el uso compartido en un entorno de red. El archivo de base de datos back-end solo debe contener tablas y relaciones. El archivo front-end solo debe contener todos los demás objetos, incluidos formularios, informes, consultas, macros, módulos vba y tablas vinculadas a la base de datos back-end. Al migrar una base de datos de Access, es similar a una base de datos dividida, ya que SQL Server actúa como un nuevo back-end para los datos que ahora se encuentran en un servidor.

Como resultado, todavía puede mantener la base de datos front-end de Access con tablas vinculadas a las tablas SQL Server. De forma eficaz, puede obtener las ventajas del rápido desarrollo de aplicaciones que proporciona una base de datos de Access, junto con la escalabilidad de SQL Server.

SQL Server ventajas

¿Sigue necesitando algo convincente para migrar a SQL Server? Estas son algunas ventajas adicionales que puede considerar:

Más usuarios simultáneos SQL Server puede controlar muchos más usuarios simultáneos que Access y minimiza los requisitos de memoria cuando se agregan más usuarios.
Mayor disponibilidad Con SQL Server, puede realizar una copia de seguridad dinámicamente, ya sea incremental o completa, de la base de datos mientras está en uso. Por consiguiente, no es necesario que los usuarios salgan de la base de datos para hacer una copia de seguridad de los datos.
Alto rendimiento y escalabilidad La base de datos de SQL Server suele tener un mejor rendimiento que una base de datos de Access, especialmente con una base de datos de tamaño terabyte grande. Además, SQL Server procesa las consultas de forma mucho más rápida y eficaz procesando consultas en paralelo, con varios subprocesos nativos dentro de un único proceso para administrar las solicitudes de usuario.
Seguridad mejorada Mediante una conexión de confianza, SQL Server se integra con la seguridad del sistema de Windows para proporcionar un único acceso integrado a la red y a la base de datos, empleando lo mejor de ambos sistemas de seguridad. Esto hace que sea mucho más fácil administrar complejos esquemas de seguridad. SQL Server es el almacenamiento ideal para información confidencial, como números del Seguro Social, datos de tarjetas de crédito y direcciones confidenciales.
Capacidad de recuperación inmediata Si el sistema operativo se bloquea o la energía se apaga, SQL Server puede recuperar automáticamente la base de datos a un estado coherente en cuestión de minutos y sin intervención del administrador de la base de datos.
Uso de VPN El acceso y las redes privadas virtuales (VPN) no se llevan bien. Sin embargo, con SQL Server, los usuarios remotos pueden seguir usando la base de datos front-end de Access en un escritorio y el SQL Server back-end situado detrás del firewall VPN.
Azure SQL Server Además de las ventajas de SQL Server, ofrece escalabilidad dinámica sin tiempo de inactividad, optimización inteligente, escalabilidad y disponibilidad globales, eliminación de costos de hardware y administración reducida.

Elegir la mejor opción Azure SQL Server

Si va a migrar a Azure SQL Server, hay tres opciones entre las que elegir, cada una con diferentes ventajas:

Grupos elásticos o bases de datos individuales Esta opción tiene su propio conjunto de recursos administrados a través de un servidor de SQL Database. Una única base de datos es como una base de datos contenida en SQL Server. También puede agregar un grupo elástico, que es una colección de bases de datos con un conjunto compartido de recursos administrados mediante el servidor de SQL Database. Las características de SQL Server más usadas están disponibles con copias de seguridad, revisiones y recuperación integradas. Sin embargo, no hay ningún tiempo de mantenimiento exacto garantizado y la migración desde SQL Server puede ser difícil.
Instancia administrada Esta opción es una colección de bases de datos de sistema y de usuario con un conjunto compartido de recursos. Una instancia administrada es como una instancia de la base de datos de SQL Server que es muy compatible con SQL Server local. Una instancia administrada tiene copias de seguridad integradas, revisiones, recuperación y es fácil migrar desde SQL Server. Sin embargo, hay un número reducido de características de SQL Server que no están disponibles y no hay ningún tiempo de mantenimiento exacto garantizado.
Máquina Virtual de Azure Esta opción le permite ejecutar SQL Server dentro de una máquina virtual en la nube de Azure. Tiene control total sobre el motor de SQL Server y una ruta de migración sencilla. Sin embargo, debes administrar las copias de seguridad, los parches y la recuperación.

Para obtener más información, vea Elegir la ruta de migración de base de datos a Azure y ¿Qué es Azure SQL?.

Primeros pasos

Hay algunos problemas que puede abordar por adelantado que pueden ayudar a simplificar el proceso de migración antes de ejecutar SSMA:

Agregar índices de tabla y claves principales Asegúrese de que cada tabla de Access tiene un índice y una clave principal. SQL Server requiere que todas las tablas tengan al menos un índice y que una tabla vinculada tenga una clave principal si la tabla se puede actualizar.
Comprobar las relaciones de clave principal y clave externa Asegúrese de que estas relaciones se basan en campos con tamaños y tipos de datos coherentes. SQL Server no admite columnas combinadas con distintos tipos de datos y tamaños en restricciones de clave externa.
Quitar la columna Datos adjuntos SSMA no migra tablas que contienen la columna Datos adjuntos.

Antes de ejecutar SSMA, siga estos primeros pasos.

Cierre la base de datos de Access.
Asegúrese de que los usuarios actuales conectados a la base de datos también cierren la base de datos.
Si la base de datos está en .mdb formato de archivo, quite la seguridad por usuarios.
Haga una copia de seguridad de la base de datos. Para obtener más información, consulte Proteger los datos con procesos de copia de seguridad y restauración.

Sugerencia Considera la posibilidad de instalar Microsoft SQL Server Express edición en el escritorio, que admite hasta 10 GB y es una forma gratuita y sencilla de realizar y comprobar la migración. Cuando se conecte, use LocalDB como instancia de base de datos.

Sugerencia Si es posible, use una versión independiente de Access. Si solo puede usar Microsoft 365, use el motor de base de datos de Access 2010 para migrar la base de datos de Access al usar SSMA. Para obtener más información, vea Motor de base de datos de Microsoft Access 2010 Redistributable.

Ejecutar SSMA

Microsoft proporciona Microsoft SQL Server Migration Assistant (SSMA) para facilitar la migración. SSMA migra principalmente tablas y consultas de selección sin parámetros. Los formularios, informes, macros y módulos de VBA no se convierten. El Explorador de metadatos de SQL Server muestra los objetos de base de datos de Access y SQL Server objetos, lo que le permite revisar el contenido actual de ambas bases de datos. Estas dos conexiones se guardan en el archivo de migración si decide transferir otros objetos en el futuro.

Nota El proceso de migración puede tardar algún tiempo en función del tamaño de los objetos de base de datos y la cantidad de datos que se deben transferir.

Para migrar una base de datos con SSMA, primero descargue e instale el software haciendo doble clic en el archivo MSI descargado. Asegúrese de instalar la versión de 32 o 64 bits adecuada para su equipo.
Después de instalar SSMA, ábrala en el escritorio, preferiblemente desde el equipo con el archivo de base de datos de Access.

También puede abrirlo en un equipo que tenga acceso a la base de datos de Access desde la red en una carpeta compartida.
Siga las instrucciones iniciales de SSMA para proporcionar información básica como la ubicación de SQL Server, la base de datos y los objetos de Access para migrar, información de conexión y si desea crear tablas vinculadas.
Si va a migrar a SQL Server 2016 o posterior y desea actualizar una tabla vinculada, agregue una columna rowversion seleccionando Herramientas de revisión > Configuración del proyecto > General.

El campo rowversion ayuda a evitar conflictos de registro. Access usa este campo rowversion en una SQL Server tabla vinculada para determinar cuándo se actualizó por última vez el registro. Además, si agrega el campo rowversion a una consulta, Access lo usará para volver a seleccionar la fila después de una operación de actualización. Esto mejora la eficiencia al ayudar a evitar errores de conflicto de escritura y escenarios de eliminación de registros que pueden ocurrir cuando Access detecta resultados diferentes del envío original, como pueden producirse con tipos de datos de número flotante y desencadenadores que modifican columnas. Sin embargo, evite usar el campo rowversion en formularios, informes o código VBA. Para obtener más información, vea rowversion.

Nota Evita confundir rowversion con marcas de tiempo. Aunque la marca de tiempo de la palabra clave es un sinónimo de rowversion en SQL Server, no puede usar rowversion como una forma de marcar la hora de una entrada de datos.
Para establecer tipos de datos precisos, seleccione Herramientas de revisión > Configuración del proyecto > Asignación de tipos. Por ejemplo, si solo almacena texto en inglés, puede usar el tipo de datos varchar en lugar de nvarchar .

Convertir objetos

SSMA convierte los objetos de Access en objetos SQL Server, pero no los copia inmediatamente. SSMA proporciona una lista de los siguientes objetos para migrar para que pueda decidir si desea moverlos a SQL Server base de datos:

Tablas y columnas
Seleccione Consultas sin parámetros.
Claves principales y externas
Índices y valores predeterminados
Restricciones de comprobación (permitir propiedad de columna de longitud cero, regla de validación de columna, validación de tabla)

Como procedimiento recomendado, use el informe de evaluación de SSMA, que muestra los resultados de conversión, incluidos errores, advertencias, mensajes informativos, estimaciones de tiempo para realizar la migración y pasos individuales de corrección de errores que debe realizar antes de mover realmente los objetos.

La conversión de objetos de base de datos toma las definiciones de objetos de los metadatos de Access, los convierte en una sintaxis equivalente de Transact-SQLTransact-SQL (T-SQL) y, a continuación, carga esta información en el proyecto. A continuación, puede ver los objetos SQL Server o SQL Azure y sus propiedades mediante el Explorador de metadatos de SQL Server o SQL Azure.

Para convertir, cargar y migrar objetos a SQL Server, siga esta guía.

Sugerencia Una vez que haya migrado correctamente la base de datos de Access, guarde el archivo de proyecto para su uso posterior, de modo que pueda migrar los datos de nuevo para pruebas o la migración final.

Vincular tablas

Considere la posibilidad de instalar la versión más reciente de los controladores SQL SERVER OLE DB y ODBC en lugar de usar los controladores nativos de SQL Server que se incluyen en Windows. Los controladores más recientes no solo son más rápidos, sino que también admiten nuevas características en Azure SQL que los controladores anteriores no. Puede instalar los controladores en cada equipo en el que se usa la base de datos convertida. Para obtener más información, vea Microsoft OLE DB Driver 18 for SQL Server and Microsoft ODBC Driver 17 for SQL Server (Microsoft OLE DB Driver 18 for SQL Server y Microsoft ODBC Driver 17 for SQL Server).

Después de migrar las tablas de Access, puede vincularlas en SQL Server que ahora hospeda los datos. Vincular directamente desde Access también le proporciona una forma más sencilla de ver los datos en lugar de usar las herramientas de administración de SQL Server más complejas. Puede consultar y editar datos vinculados según los permisos configurados por el administrador de la base de datos de SQL Server.

Nota Si crea un DSN ODBC al vincular a la base de datos de SQL Server durante el proceso de vinculación, cree el mismo DSN en todos los equipos que usen la nueva aplicación o use mediante programación los cadena de conexión almacenados en el archivo DSN.

Para obtener más información, vea Vincular o importar datos desde una base de datos de Azure SQL Server e Importar o vincular a datos en una base de datos de SQL Server.

Sugerencia No olvide usar el Administrador de tablas vinculadas en Access para actualizar y volver a vincular tablas cómodamente. Para obtener más información, vea Administrar tablas vinculadas.

Probar y revisar

En las siguientes secciones se describen los problemas comunes que puede encontrar durante la migración y cómo tratarlos.

Consultas

Solo se convierten las consultas de selección; otras consultas no lo son, incluidas las consultas de selección que toman parámetros. Es posible que algunas consultas no se conviertan por completo y SSMA informe de errores de consulta durante el proceso de conversión. Puede editar manualmente los objetos que no se convierten mediante la sintaxis de T-SQL. Los errores de sintaxis también pueden requerir convertir manualmente funciones y tipos de datos específicos de Access en SQL Server. Para más información, vea Comparar Access SQL con SQL Server TSQL.

Tipos de datos

Access y SQL Server tienen tipos de datos similares, pero tenga en cuenta los siguientes posibles problemas.

Número grande El tipo de datos Número grande almacena un valor numérico y no monetario y es compatible con el tipo de datos BIGINT de SQL. Puede usar este tipo de datos para calcular de forma eficaz números grandes, pero requiere el formato de archivo de base de datos .accdb de Access 16 (16.0.7812 o posterior) y funciona mejor con la versión de 64 bits de Access. Para obtener más información, vea Usar el tipo de datos Número grande y Elegir entre la versión de 64 bits o la de 32 bits de Office.

Sí/No De forma predeterminada, una columna Sí/No de Access se convierte en un campo de bits de SQL Server. Para evitar el bloqueo de registros, asegúrese de que el campo bit está establecido para no permitir valores NULL. En SSMA, puede seleccionar la columna de bits para establecer la propiedad Permitir nulos en NO. En TSQL, use las instrucciones CREATE TABLE o ALTER TABLE .

Fecha y hora Hay varias consideraciones de fecha y hora:

Si el nivel de compatibilidad de la base de datos es 130 (SQL Server 2016) o superior y una tabla vinculada contiene una o más columnas datetime o datetime2, la tabla puede devolver el mensaje #deleted en los resultados. Para obtener más información, vea La tabla vinculada de Access a SQL-Server base de datos devuelve #deleted.
Use el tipo de datos Fecha y hora de Access para asignarlo al tipo de datos datetime. Use el tipo de datos Fecha y hora extendida de Access para asignar al tipo de datos datetime2 , que tiene un intervalo de fecha y hora mayor. Para obtener más información, vea Usar el tipo de datos de fecha y hora extendida.
Al consultar fechas en SQL Server, tenga en cuenta la hora y la fecha. Por ejemplo:
- FechaPedido Entre 1/1/19 y 31/1/19 puede no incluir todos los pedidos.
- FechaOrdenado Entre 1/1/19 00:00:00 AM y 31/1/19 11:59:59 p.m. sí incluye todos los pedidos.

Datos adjuntos El tipo de datos Datos adjuntos almacena un archivo en la base de datos de Access. En SQL Server, tiene varias opciones que tener en cuenta. Puede extraer los archivos de la base de datos de Access y, a continuación, considerar el almacenamiento de vínculos a los archivos de la base de datos de SQL Server. Como alternativa, puede usar FILESTREAM, FileTables o remote BLOB store (RBS) para mantener los datos adjuntos almacenados en la base de datos SQL Server.

Hipervínculo Las tablas de Access tienen columnas de hipervínculos que SQL Server no admiten. De forma predeterminada, estas columnas se convertirán en columnas nvarchar(max) en SQL Server, pero puede personalizar la asignación para elegir un tipo de datos más pequeño. En la solución de Access, puede seguir usando el comportamiento de hipervínculo en formularios e informes si establece la propiedad Hyperlink del control en true.

Campo multivalor El campo multivalor de Access se convierte en SQL Server como un campo ntext que contiene el conjunto delimitado de valores. SQL Server no admite un tipo de datos multivalor que modele una relación de varios a varios, por lo que podrían hacer falta trabajos de conversión y diseño adicionales.

Para obtener más información sobre cómo asignar tipos de datos de Access y SQL Server, vea Comparar tipos de datos.

Nota Los campos multivalor no se convierten.

Para obtener más información, vea Tipos de fecha y hora, Tipos de cadena y binario y Tipos numéricos.

Visual Basic

Aunque VBA no es compatible con SQL Server, tenga en cuenta los siguientes posibles problemas:

Funciones de VBA en Consultas Las consultas de Access admiten funciones de VBA en los datos de una columna de consulta. Sin embargo, las consultas de Access que usan funciones de VBA no se pueden ejecutar en SQL Server, por lo que todos los datos solicitados se pasan a Microsoft Access para su procesamiento. En la mayoría de los casos, estas consultas deben convertirse en consultas de paso a través.

Funciones definidas por el usuario en consultas Las consultas de Microsoft Access admiten el uso de funciones definidas en módulos de VBA para procesar los datos que se les pasan. Las consultas pueden ser consultas independientes, instrucciones SQL en orígenes de registros de formularios o informes, orígenes de datos de cuadros combinados y cuadros de lista en formularios, informes y campos de tabla, y expresiones de regla de validación o predeterminadas. SQL Server no puede ejecutar estas funciones definidas por el usuario. Es posible que deba volver a diseñar manualmente estas funciones y convertirlas en procedimientos almacenados en SQL Server.

Optimizar el rendimiento

De lejos, la forma más importante de optimizar el rendimiento con su nueva SQL Server back-end es decidir cuándo usar consultas locales o remotas. Al migrar los datos a SQL Server, también se mueve de un servidor de archivos a un modelo de base de datos de cliente-servidor de sistemas. Siga estas directrices generales:

Ejecute pequeñas consultas de solo lectura en el cliente para obtener un acceso más rápido.
Ejecute consultas largas de lectura y escritura en el servidor para aprovechar la mayor potencia de procesamiento.
Minimice el tráfico de red con filtros y agregaciones para transferir solo los datos que necesite.

Optimizar el rendimiento en el modelo de base de datos del servidor cliente

Para obtener más información, vea Crear una consulta de paso a través.

Las siguientes son instrucciones adicionales recomendadas.

Poner lógica en el servidor La aplicación también puede usar vistas, funciones definidas por el usuario, procedimientos almacenados, campos calculados y desencadenadores para centralizar y compartir lógicas de aplicaciones, reglas y directivas empresariales, consultas complejas, validación de datos e integridad referencial en el servidor, en lugar de en el cliente. Pregúntese, ¿se puede realizar mejor y más rápidamente esta consulta o tarea en el servidor? Por último, pruebe cada consulta para garantizar un rendimiento óptimo.

Usar vistas en formularios e informes En Access, haga lo siguiente:

Para los formularios, use una vista SQL para un formulario de solo lectura y una vista indexada de SQL para un formulario de lectura y escritura como origen de registros.
Para los informes, use una vista SQL como origen de registros. Sin embargo, cree una vista independiente para cada informe, de modo que pueda actualizar más fácilmente un informe específico, sin que esto afecte a otros informes.

Minimizar la carga de datos en un formulario o informe No muestre los datos hasta que el usuario los pida. Por ejemplo, mantenga en blanco la propiedad recordsource, haga que los usuarios seleccionen un filtro en el formulario y, después, rellene la propiedad recordsource con el filtro. O bien, use la cláusula where de DoCmd.OpenForm y DoCmd.OpenReport para mostrar los registros exactos necesarios para el usuario. Considere la posibilidad de desactivar la navegación de registros.

Tenga cuidado con consultas heterogéneas Evite ejecutar una consulta que combine una tabla de Access local y SQL Server tabla vinculada, a veces denominada consulta híbrida. Este tipo de consulta aún requiere que Access descargue todos los datos SQL Server en el equipo local y, después, ejecute la consulta, no la ejecuta en SQL Server.

Cuándo usar tablas locales Considere la posibilidad de usar tablas locales para los datos que rara vez cambian, como la lista de estados o provincias de un país o región. Las tablas estáticas se usan a menudo para filtrar y pueden funcionar mejor en el front-end de Access.

Para obtener más información, vea Asistente para la optimización de motor de base de datos , Usar la Analizador de rendimiento para optimizar una base de datos de Access y Optimización de las aplicaciones de Microsoft Office Access vinculadas a SQL Server.