Cómo configurar la replicación de MS SQL Server

Microsoft SQL Server es un software de gestión de bases de datos que se puede instalar en los sistemas operativos Windows Server. Las empresas de todos los sectores utilizan bases de datos, y muchas soluciones de software recurren a ellas, tanto centralizadas como distribuidas. La disponibilidad de las bases de datos y la coherencia de los datos son fundamentales para las empresas, por lo que la copia de seguridad y la replicación de las bases de datos son imprescindibles.
Descubre los tipos de replicación de SQL Server, cómo funciona la replicación en SQL Server y cómo llevarla a cabo.

NAKIVO para backups en Windows

Copia de seguridad rápida de servidores y estaciones de trabajo de Windows en el local, fuera de él y en la nube. Recuperación de equipos completos y objetos en minutos para lograr tiempos de recuperación (RTO) reducidos y un tiempo de actividad máximo.

DESCUBRE LA SOLUCIÓN

¿Qué es la replicación de SQL Server?

La replicación de MS SQL Server es el proceso de copiar datos de una base de datos a otra, incluidos objetos específicos de la base de datos, y mantener una copia sincronizada de estos datos entre la base de datos de origen y la de destino. Con la replicación en SQL Server, puedes crear una copia idéntica de tu base de datos principal y sincronizar los cambios entre ambas bases de datos, al tiempo que se mantiene la coherencia y la integridad de los datos.

Terminología utilizada para la replicación de MS SQL Server

Antes de profundizar en cómo configurar y poner en marcha la replicación de MS SQL Server, repasemos primero brevemente los términos principales y los modelos de replicación.
Los artículos son las unidades básicas que se van a replicar, como tablas, procedimientos, funciones y vistas. Los artículos se pueden ampliar vertical u horizontalmente mediante el uso de filtros. Se pueden crear varios artículos para un mismo objeto.
Una publicación es una colección lógica de artículos. Se trata del conjunto final de entidades de la base de datos designadas para la replicación.
Un filtro es un conjunto de condiciones para un artículo. La replicación de MS SQL Server permite utilizar filtros y seleccionar entidades personalizadas para la replicación, lo que, como resultado, reduce el tráfico, la redundancia y la cantidad de datos almacenados en una réplica de la base de datos. Por ejemplo, puede seleccionar únicamente las tablas y los campos más críticos mediante filtros y, a continuación, replicar solo estos datos.
Los agentes son componentes de MS SQL Server que pueden actuar como servicios en segundo plano para sistemas de gestión de bases de datos relacionales y se utilizan para programar la ejecución automatizada de jobs, como la copia de seguridad y la replicación de bases de datos MS SQL. Existen cinco tipos de agentes: agente de instantáneas, agente de lectura de registros, agente de distribución, agente de fusión y agente de lectura de colas.
Los metadatos son los datos que se utilizan para describir las entidades de la base de datos. Existe un amplio intervalo de funciones de metadatos integradas que permiten obtener información sobre la instancia de MS SQL Server, las instancias de base de datos y las entidades de la base de datos.

Roles en la replicación de bases de datos SQL

Existen tres roles principales en la replicación de bases de datos de MS SQL: Distribuidor, Editor y Suscriptor.

• Un Distribuidor es una instancia de base de datos de MS SQL configurada para recopilar transacciones de las publicaciones y distribuirlas a los suscriptores. Un Distribuidor actúa como la base de datos en la que se almacenan las transacciones replicadas.

  Una base de datos Distribuidora puede considerarse a la vez como Editor y Distribuidor. En el modelo de distribuidor local, una única instancia de MS SQL Server ejecuta tanto el editor como el distribuidor. Se puede utilizar un modelo de distribuidor remoto cuando se desea que los suscriptores estén configurados para utilizar una única instancia de MS SQL Server con el fin de obtener diferentes publicaciones (distribución centralizada). En este modelo, el editor y el distribuidor se ejecutan en servidores diferentes.

• Un editor es la copia principal de la base de datos en la que se configura la publicación, lo que permite que los datos estén disponibles para otros servidores MS SQL Server configurados para utilizarse en el proceso de replicación. El editor puede tener más de una publicación.
• Un suscriptor es una base de datos que recibe los datos replicados de una publicación. Un suscriptor puede recibir datos de más de un editor y de más de una publicación. El modelo de suscriptor único se utiliza cuando hay un solo suscriptor. Se utiliza un modelo de suscriptores múltiples cuando hay varios suscriptores conectados a una misma publicación.

  La suscripción es una solicitud de una copia de una publicación que debe entregarse al suscriptor. La suscripción se utiliza para definir los datos de la publicación que deben recibirse, así como dónde y cuándo se recibirán dichos datos. Existen dos tipos de suscripciones:

  • Suscripción push : Los datos modificados se transmiten de forma forzada desde un distribuidor a la base de datos de un suscriptor. No es necesaria ninguna solicitud por parte del suscriptor.
  • Suscripción de extracción : El suscriptor solicita los datos modificados en el editor. El agente se ejecuta en el lado del suscriptor.

  Una base de datos de suscripción es una base de datos de destino en el modelo de replicación de MS SQL.

  

En el modelo de múltiples editores y múltiples suscriptores , el editor puede actuar como suscriptor en uno de los servidores MS SQL. Asegúrese de evitar cualquier posible conflicto de actualización al utilizar este modelo de replicación de MS SQL Server.

Tipos de replicación de MS SQL Server

La replicación de MS SQL Server es una tecnología que permite copiar y sincronizar datos entre bases de datos de forma continua o periódica, a intervalos programados. En cuanto a la dirección de la replicación, esta puede ser unidireccional, de uno a muchos, bidireccional y de muchos a uno. Existen cuatro tipos de replicación de MS SQL Server: replicación de instantáneas, replicación transaccional, replicación entre pares y replicación de fusión.

La replicación de instantáneas

La replicación de instantáneas se utiliza para replicar los datos tal y como aparecen en el momento en que se crea la instantánea de la base de datos. Este tipo de replicación es adecuado para datos que no cambian con frecuencia, cuando el hecho de que la réplica de la base de datos sea más antigua que la base de datos maestra no supone un problema crítico, o cuando se produce un gran volumen de cambios en un breve periodo de tiempo. El seguimiento de cambios no se utiliza con la replicación de instantáneas.
Por ejemplo, la replicación de instantáneas puede utilizarse cuando los tipos de cambio o las listas de precios se actualizan una vez al día y deben distribuirse desde el servidor principal a los servidores de las sucursales.

Replicación transaccional

La replicación transaccional es una replicación periódica y automatizada en la que los datos se distribuyen desde una base de datos maestra a una réplica de la base de datos en tiempo real (o casi en tiempo real). La replicación transaccional es más compleja que la replicación por instantánea. Se replican todas las transacciones realizadas, así como el estado final de la base de datos, lo que permite la supervisión de todo el historial de transacciones en la réplica.
Al inicio del proceso de replicación transaccional, se aplica una instantánea al suscriptor y, a continuación, los datos se transfieren continuamente desde la base de datos maestra a una réplica de la base de datos a medida que se producen cambios en dichos datos. La replicación transaccional se utiliza ampliamente como replicación unidireccional.

Usos prácticos de la replicación transaccional:

• Creación de un servidor de base de datos con una réplica de la base de datos para utilizarla como herramienta de conmutación por recuperación en caso de que falle el servidor de base de datos principal.
• Recepción de informes sobre las operaciones realizadas en las sucursales mediante el uso de varios editores en las sucursales y un suscriptor en la oficina central.
• Replicación de los cambios tan pronto como se producen.
• Los datos de la base de datos de origen cambian con frecuencia.

Replicación entre pares

Replicación entre pares se utiliza para replicar los datos de la base de datos a varios suscriptores al mismo tiempo. Este tipo de replicación de MS SQL Server puede utilizarse cuando los servidores de bases de datos están distribuidos por todo el mundo. Los cambios pueden realizarse en cualquiera de los servidores de bases de datos. Los cambios se propagan a todos los servidores de bases de datos. La replicación entre pares puede ayudar a realizar la ampliación horizontal de una aplicación que utilice una base de datos. Su principio de funcionamiento se basa principalmente en la replicación transaccional.

A continuación se muestra cómo se puede utilizar la replicación entre pares de MS SQL Server entre servidores de bases de datos distribuidos por todo el mundo.

Replicación de fusión

Replicación de fusión es un tipo de replicación bidireccional que se suele utilizar en entornos de servidor a cliente para sincronizar datos entre servidores de bases de datos cuando estos no pueden estar conectados de forma continua. Cuando se establece la conexión de red entre ambos servidores de bases de datos, los agentes de replicación por fusión detectan los cambios realizados en ambas bases de datos y modifican estas para sincronizar y actualizar su estado. La replicación por fusión es similar a la replicación transaccional, pero los datos se replican desde el editor al suscriptor y viceversa.

Este tipo de replicación de bases de datos es el más complejo de todos los tipos de replicación de MS SQL Server y rara vez se utiliza. Por ejemplo, la replicación de fusión puede ser utilizada por varias tiendas homólogas que trabajan con un almacén compartido. Cada tienda está autorizada a modificar la información de la base de datos del almacén y, al mismo tiempo, todas las tiendas deben disponer del estado actualizado de sus bases de datos después de que se realicen los envíos de mercancías o la entrega de suministros al almacén. La replicación de fusión puede utilizarse en casos en los que la información actualizada deba estar disponible simultáneamente para la base de datos principal (o central) y las bases de datos de las sucursales.

Requisitos para la replicación de MS SQL Server

Deben abrirse los siguientes puertos para el tráfico entrante:

• TCP 1433, 1434, 2383, 2382, 135, 80, 443
• UDP 1434

Asegúrese de configurar el cortafuegos de Windows y habilitar los puertos adecuados para el tráfico entrante en cada host antes de instalar MS SQL Server. Los hosts que participan en la replicación de MS SQL deben resolverse entre sí mediante un nombre de host.
Antes de configurar la replicación de MS SQL Server, debe instalarse el siguiente software para MS SQL Server:

• .NET Framework: un conjunto de bibliotecas
• MS SQL Server: el software del servidor de bases de datos
• MS SQL Server Management Studio (SSMS): software para gestionar bases de datos MS SQL mediante la GUI (interfaz gráfica de usuario).

NOTA: En este artículo se utiliza MS SQL Server 2016 para la configuración. Puedes aplicar el mismo principio para configurar la replicación en versiones más recientes de SQL Server.
Ten en cuenta que, si instalas MS SQL Server 2016 en el primer equipo donde se encuentra la base de datos de origen, deberás tener instalado MS SQL Server 2016 en el segundo equipo para que la base de datos funcione correctamente. Por ejemplo, si deseas configurar la replicación transaccional de MS SQL, puedes utilizar el segundo servidor de bases de datos (en el que está configurado el suscriptor) de una versión que se encuentre dentro de dos versiones del servidor de bases de datos de origen en el que está configurado el editor. Si la versión del editor en MS SQL Server es la 2016, el distribuidor se puede configurar en las versiones 2016, 2017, 2019 y 2022, y el suscriptor se puede configurar en MS SQL Server 2012, 2014, 2016, 2017 y 2019. La versión del distribuidor no puede ser inferior a la del editor. La replicación no funcionará si, por ejemplo, se instala MS SQL Server 2008 en el segundo equipo.

Recomendaciones básicas para la replicación de bases de datos de MS SQL

Antes de configurar el entorno para MS SQL Server, hay que tener en cuenta algunos factores:

• Existen limitaciones en cuanto a los campos de identidad y los desencadenadores.
• Las publicaciones solo pueden contener tablas con clave primaria.
• Se recomienda no programar la creación de instantáneas para bases de datos de gran tamaño, a fin de evitar el consumo excesivo de recursos informáticos.
• Hay que tener cuidado al modificar datos en la réplica de la base de datos que reside en el suscriptor. Cuando se produce una transacción que modifica datos y dichos datos han sido editados o eliminados, la replicación puede detenerse hasta que se resuelva este problema.

Configuración del entorno

Al configurar la replicación de MS SQL por primera vez, se recomienda hacerlo primero en un entorno de prueba. Por ejemplo, configuramos la replicación en servidores SQL que se ejecutan en máquinas virtuales. En este tutorial se utilizan dos hosts que ejecutan Windows Server 2016 y MS SQL Server 2016 para explicar la replicación de MS SQL Server.
Echemos un vistazo a la configuración del entorno de prueba utilizado para redactar esta entrada del blog, con el fin de comprender mejor la configuración de la replicación de MS SQL Server.
Host 1

• Dirección IP: 192.168.101.101
• Nombre de host: MSSQL01
• ID de instancia de MS SQL Server: MSSQLSERVER1

Host 2

• Dirección IP: 192.168.101.102
• Nombre de host: MSSQL02
• ID de instancia de MS SQL Server: MSSQLSERVER2

Ambos equipos tienen el disco C: y el disco D: en su configuración de discos.
Puede desactivar temporalmente el cortafuegos de Windows al instalar MS SQL Server para practicar la configuración de la replicación de MS SQL Server. Esta entrada del blog no aborda cómo instalar MS SQL Server, ya que este tutorial se centra en la configuración de la replicación de MS SQL Server. En este ejemplo, ambos servidores MS SQL Server están instalados sin PolyBase.
Comprueba que has instalado las funciones necesarias para la replicación de MS SQL Server una vez finalizada la instalación de MS SQL Server. Tenga en cuenta que los servicios del motor de base de datos, como la replicación de SQL Server y R-Services, deben seleccionarse durante la instalación de MS SQL Server. En este ejemplo se utiliza la ruta de instalación predeterminada (C:Archivos de programaMicrosoft SQL Server).

Otros ajustes:

• Modo de autenticación mixto (autenticación de Windows y autenticación de MS SQL Server)
• Directorio raíz de datos: D:MSSQL_Server
• Directorio de la base de datos del sistema: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directorio de la base de datos de usuarios: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directorio de registros de la base de datos de usuarios: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directorio de backup: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

Una vez instalados MS SQL Server 2016 y SQL Server Management Studio en los equipos, puede preparar sus servidores MS SQL para la replicación de bases de datos.

Preparación para la replicación de MS SQL Server

Debe configurar los servidores antes de poder iniciar la replicación de bases de datos. En nuestro ejemplo, se utilizará una cuenta de Windows para los agentes de replicación de MS SQL Server.

1. Crea el mssql usuario en ambos servidores y establece la misma contraseña.
2. El usuario mssql es miembro de los siguientes grupos en este ejemplo:
   • Administradores (administradores locales en equipos locales, no administradores de dominio)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. Puede editar usuarios y grupos pulsando Win+R , abriendo CMD y ejecutando el comando lusrmgr.msc .

Los dos equipos con Windows Server utilizados en este ejemplo no están en Active Directory. Si utilizas Active Directory, puedes crear el usuario mssql en el controlador de dominio.

Conexión a MS SQL Server

1. Ejecuta SQL Server Management Studio.
2. Inicia sesión (véase la captura de pantalla) como sa utilizando la autenticación de SQL Server.
   • MSSQL01MSSQLSERVER1 es el nombre de host y el nombre de la instancia de MS SQL en el primer servidor.
   • MSSQL02MSSQLSERVER2 es el nombre de host y el nombre de la instancia de MS SQL en el segundo servidor.

   

Del mismo modo, puedes conectarte desde el segundo servidor (MSSQL02) a la segunda instancia de MS SQL Server (MSSQLSERVER2). También puede conectarse a la segunda instancia de MS SQL Server (MSSQLSERVER2) desde el primer servidor MS SQL Server (MSSQL01) introduciendo las credenciales en SQL Server Management Studio. Puede conectarse a ambas instancias de MS SQL Server (MSSQL01 y MSSQL02) en una única instancia de SQL Server Management Studio.
Para ello, en el Explorador de objetos, haga clic en Conectar > Motor de base de datos . En este tutorial, nos conectaremos a MSSQLSERVER1 desde MSSQL01 y a MSSQLSERVER2 desde MSSQL02 utilizando SQL Server Management Studio para configurar los servidores MS SQL.

Inicio del agente

Una vez que haya iniciado sesión en la instancia de MS SQL Server, verá que el agente no se está ejecutando. De forma predeterminada, el agente de SQL Server no se inicia automáticamente. Puede iniciar este servicio manualmente, pero es mejor configurarlo para que se inicie automáticamente después del inicio de Windows.

Para configurar el servicio del agente para que se inicie automáticamente:

1. Pulse Win+R , ejecute cmd, y ejecute el services.msc comando.
2. Abra las propiedades del servicio del agente de SQL Server y establezca el tipo de inicio en Automático .

   

Configuración de usuarios para MS SQL Server

Después de conectarnos a la instancia MSSQLSERVER1 en SQL Server Management Studio, debemos configurar los usuarios:

1. Vaya a Explorador de objetos y abra Seguridad > Inicios de sesión .
2. Haga clic con el botón derecho en Inicios de sesión y seleccione Nuevo inicio de sesión . Seleccione Autenticación de Windows .
3. Introduzca el nombre de usuario mssql en la sección General .
4. Haga clic en Buscar , a continuación pulse Comprobar nombres para confirmar y haga clic en Aceptar dos veces para guardar los ajustes.

   

5. Ahora, el usuario de Windows MSSQL01mssql se ha añadido a la lista de usuarios que pueden iniciar sesión en la base de datos (del mismo modo, añade el usuario mssql a los inicios de sesión en el segundo equipo, MSSQL02, en SQL Server Management Studio).
6. Añade el usuario mssql a los roles de servidor sysadmins en la configuración de seguridad de la base de datos en SQL Server Management Studio.
7. Vaya a MSSQL01MSSQLSERVER1 > Roles del servidor , haga clic con el botón derecho en sysadmin y abra Propiedades .
8. En la página Miembros , haga clic en Añadir , introduzca el nombre de su usuario mssql, y haga clic en Comprobar nombres .
9. Marque la casilla del nombre de usuario MSSQL01mssql y haga clic en Aceptar .

   

10. Realice la misma configuración en su segundo equipo (MSSQL02 en este caso).
11. Reinicia ambos equipos.

    Ahora puedes iniciar sesión utilizando la autenticación de Windows en ambos servidores.

    

Importación de una base de datos desde una copia de backup

Importemos una base de datos de ejemplo desde una copia de backup y, a continuación, repliquemos la base de datos del primer equipo al segundo. La AdventureWorks2016 base de datos se utiliza como base de datos de ejemplo en este caso.

1. Copia el AdventureWorks2016.bak archivo de backup de la base de datos en tu directorio de backups de MSSQL. En nuestro caso, este directorio en el primer servidor es D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Importe una base de datos de ejemplo. En el primer equipo, en SQL Server Management Studio, vaya a MSSQL01MSSQLSERVER1 , haga clic con el botón derecho en Bases de datos, y seleccione Restaurar base de datos en el menú contextual.

   

3. En la ventana Restaurar base de datos , seleccione los parámetros necesarios:
   • Origen: Dispositivo .
   • Haga clic en los tres puntos para explorar el archivo de copia de seguridad de la base de datos.
     • En la ventana Seleccionar dispositivos de backup , seleccione el tipo de soporte de backup: archivo .
     • Haga clic en Añadir .
   • Seleccione el archivo .bak necesario: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Pulse Aceptar y, a continuación, pulse Aceptar una vez más.
4. La base de datos AdventureWorks2016 se ha restaurado correctamente.

   

Puedes importar la base de datos de un backup en la segunda máquina, donde la réplica de la base de datos se ejecutará. Este enfoque permite reducir el tráfico de red porque la replicación empezará copiando los cambios desde la creación del backup sin necesidad de copiar todos los datos de la base de datos a una base de datos vacía.
Restaura la base de datos desde un backup en el segundo servidor y cambia el nombre de la base de datos a AdventureWorks2016r , donde “r” significa “réplica”.
Finalmente, tenemos:

Después de importar la base de datos, debes realizar algunos ajustes para preparar tus servidores MS SQL

1. En la máquina MSSQL01 , ve a MSSQL01MSSQLSERVER1 > Seguridad > Logins , selecciona MSSQL01mssql . Haz clic derecho (o doble clic) en el usuario mssql y selecciona Propiedades .
2. En Roles de servidor , marca la casilla junto al rol dbcreator .

   

3. En la página Mapeo de usuario , selecciona los usuarios mapeados a este login y marca la casilla de la base de datos AdventureWorks2016 (selecciona AdventureWorks2016r en el segundo servidor, según corresponda).
4. En la sección membresía de rol de base de datos , marca la casilla de db_owner .

   

5. Haz clic en OK para guardar los ajustes.

Realiza la misma configuración en la máquina MSSQL02. Luego, podrás configurar los componentes de MS SQL Server necesarios para la replicación de la base de datos.

Encuentra el plan adecuado para tu entorno

Descubre las ediciones y los modelos de licencia flexibles de NAKIVO, diseñados para ofrecer una protección de datos de nivel empresarial a un precio que se adapte a tu presupuesto.

VER PRECIOS

Configuración de la replicación de bases de datos

La configuración de la replicación en modo gráfico es el método más cómodo. La siguiente configuración se lleva a cabo en SQL Server Management Studio. En este ejemplo se explica la replicación transaccional de bases de datos, ya que es uno de los tipos de replicación más utilizados en MS SQL Server.
En la siguiente captura de pantalla se muestran la vista del servidor de base de datos principal (MSSQL01MSSQLSERVER1) y la vista del segundo servidor (MSSQL02MSSQLSERVER2) en SQL Server Management Studio.

Configuración de la distribución

La distribución se puede utilizar para varios editores y suscriptores. En este ejemplo, la distribución se configura en el servidor principal en el que se almacena la base de datos de origen. En el servidor principal (MSSQL01MSSQLSERVER1), haz clic con el botón derecho del ratón en Replicación y, en el menú contextual, selecciona Configurar distribución .

Se abrirá el Asistente para configurar la distribución .

1. Distribuidor . Selecciona la instancia de base de datos actual que se ejecuta en el servidor principal (MSSQL01MSSQLSERVER1) para que actúe como distribuidor en este ejemplo. Haga clic en Siguiente cada vez para pasar al siguiente paso del asistente.
2. Inicio de SQL Server Agent . Si no ha configurado SQL Server Agent para que se inicie automáticamente, tal y como se ha explicado anteriormente, aparecerá el siguiente mensaje. Seleccione Sí, configurar el servicio SQL Server Agent para que se inicie automáticamente .

   

3. Carpeta de instantáneas . Puede dejar la ruta predeterminada aquí. Se necesita una instantánea para inicializar la replicación. Asegúrese de que haya suficiente espacio libre en el disco en la ubicación donde se encuentra el directorio de instantáneas. La cantidad de espacio libre debe corresponder, como mínimo, al tamaño de la base de datos replicada.
4. Base de datos de distribución . Introduzca el nombre de la base de datos de distribución. Puede dejar el nombre predeterminado ( distribution ) y las carpetas para el archivo de la base de datos de distribución y el archivo de registro.

   

5. Editores . Defina los editores de replicación de MS SQL Server que pueden acceder al distribuidor. Marque la casilla situada junto al nombre de la base de datos de distribución en la instancia principal de MS SQL Server (que aloja una base de datos de origen que se va a replicar). En este ejemplo, se trata de la instancia MSSQL01MSSQLSERVER1, y el nombre de la base de datos de distribución es distribution .
6. Acciones del asistente . Seleccione Configurar distribución para configurar la distribución durante el paso final del asistente. En este ejemplo, no generaremos un archivo de script para ejecutarse después.

   

7. Complete el Asistente . Verifique el resumen de configuración de la distribución y haga clic en Finalizar para crear el Distribuidor.

   

8. El estado de Éxito debería aparecer si el Distribuidor se ha creado y configurado correctamente.

   

Si ve que ocurrió un error al configurar SQL Server Agent para iniciarse automáticamente, vaya a la configuración de servicios y verifique el modo de inicio de SQL Server Agent (vea cómo configurar el Inicio del Agente arriba en esta publicación de blog).
También puede abrir las propiedades de SQL Server Agent en SQL Server Management Studio y verificar el estado del servicio y las opciones de reinicio. Haga clic derecho en SQL Server Agent al final de la lista en Explorador de Objetos y haga clic en Propiedades para ver o editar las propiedades del agente.

Configurando el Publicador

Una vez que la Distribución esté configurada, puede configurar el Publicador. El Publicador debe configurarse en el servidor principal (MSSQL01MSSQLSERVER1) donde se almacena la base de datos maestra a replicar. Seleccione Replicación , haga clic derecho en Publicaciones Locales , y en el menú de contexto, seleccione Nueva Publicación .

El Asistente de Nueva Publicación se abre.

1. Base de Datos de Publicación . Seleccione la base de datos que desea replicar ( AdventureWorks2016 en este caso). Haga clic en Siguiente en cada paso del asistente para continuar.

   

2. Tipo de Publicación . Para este paso, puede seleccionar tipos de replicación de MS SQL Server para una base de datos. Vamos a seleccionar una publicación transaccional, que es un tipo de replicación ampliamente utilizado.
3. Artículos . Seleccione los objetos necesarios, como tablas, procedimientos, vistas, vistas indexadas y funciones definidas por el usuario para publicar como artículos. Es posible seleccionar la replicación de los campos personalizados en las tablas y seleccionar propiedades de artículos si es necesario. En este ejemplo, se seleccionan algunas tablas.

   

4. Filtrar Filas de Tabla . No se agregan filtros en este ejemplo (esta es la configuración predeterminada de filtros). Puede agregar filtros si es necesario.
5. Agente de Instantánea . Especifique cuándo ejecutar el Agente de Instantánea. Vamos a configurar el Agente para ejecutarse inmediatamente. Selecciona Crear una instantánea inmediatamente y mantenga la instantánea disponible para inicializar suscripciones .

   

6. Seguridad del agente . Selecciona Usar los ajustes de seguridad del Agente de Instantáneas . Haz clic en el botón Ajustes de Seguridad para seleccionar la cuenta bajo la cual el Agente se ejecutará.

   En la ventana Seguridad del Agente de Instantáneas que se abre, introduce las credenciales del usuario de Windows mssql que hayas creado antes. Selecciona conectar al Publicador Mediante la personificación de la cuenta del proceso . Haz clic en OK para guardar los ajustes y volver al asistente.

   

   Después de definir el usuario necesario, puedes ver este usuario en las secciones Agente de Instantáneas y Agente Lector de Registros .

   

7. Acciones del Asistente . Selecciona la casilla superior para crear la publicación durante el paso final del asistente.
8. Completar el Asistente . Revisa la configuración de tu publicación y haz clic en Finalizar para crear una nueva publicación.

   

En la ventana Creando Publicación , puedes supervisar el progreso de crear una nueva publicación. Espera un momento y deberías ver el estado de éxito si todo se ha realizado correctamente.

La publicación ahora está creada y puedes ver la publicación en el Explorador de Objetos yendo a Replicación > Publicaciones Locales .

Configurando el Suscriptor

Como recordarás, la replicación de MS SQL Server puede ser replicación push o pull. Si configuras replicación push, debes configurar el Suscriptor para ejecutar agentes en el servidor principal de bases de datos (MSSQL01 en este caso). Si configuras replicación pull, el Suscriptor debe configurarse para ejecutar agentes en la segunda máquina (MSSQL02), es decir, la máquina en la que se creará la réplica de la base de datos.
Vamos a configurar la replicación push y crear una nueva suscripción en el primer Servidor MS SQL (MSSQL01MSSQLSERVER1) donde reside la base de datos maestra.
En el Explorador de Objetos, ve a Replicación , haz clic derecho en Suscripciones Locales y, en el menú contextual, selecciona Nuevas Suscripciones .

Se abre el Asistente de Nueva Suscripción .

1. Publicación . Selecciona la publicación para crear una nueva suscripción. En nuestro ejemplo, el nombre del Editor es MSSQL01MSSQLSERVER1 y el nombre de la publicación (creada anteriormente) es AdvWorks_Pub . Haga clic en Siguiente en cada paso del asistente para continuar.
2. Ubicación del Agente de Distribución . Seleccione el tipo de replicación eligiendo entre suscripción push o pull. En nuestro ejemplo, queremos que todos los agentes se ejecuten en el lado del servidor de origen, por lo que se selecciona la primera opción para crear una suscripción push. Esto le permite gestionar la replicación de MS SQL Server de forma centralizada.

   

3. Suscriptores . Por defecto, el servidor en el que se ejecuta el asistente (en este caso MSSQL01MSSQLSERVER1) se muestra como el Suscriptor, y la base de datos de la suscripción no está definida. Vamos a añadir un nuevo Suscriptor y seleccionar una base de datos de suscripción ubicada en el segundo servidor de bases de datos (MSSQL01MSSQLSERVER2). Haga clic en Agregar Suscriptor y, en el menú contextual, seleccione Agregar Suscriptor de SQL Server .
   • En la ventana emergente, ingrese las credenciales para la segunda instancia de MSSQL Server (en nuestro caso MSSQL01MSSQLSERVER2) y haga clic en Conectar .

     

   • Seleccione la casilla de verificación de su segundo servidor donde se almacenará su réplica de base de datos (MSSQL02MSSQLSERVER2) y, en el menú desplegable Base de Datos de Suscripción , seleccione una nueva base de datos o una base de datos existente restaurada desde un backup para ser usada como réplica de base de datos.

     En nuestro ejemplo, AdventureWorks2016r fue creada en el segundo servidor restaurando la base de datos principal (origen) AdventureWorks2016 desde un backup para iniciar la replicación. La replicación se inicia replicando solo los nuevos datos pero no copiando toda la base de datos después de iniciar el proceso de replicación. Por lo tanto, AdventureWorks2016r se selecciona como base de datos de suscripción en el ejemplo actual.

     

4. Seguridad del Agente de Distribución . Haga clic en el botón de tres puntos (…), y seleccione el usuario y otras opciones de seguridad para el Agente de Distribución.

   En la ventana Seguridad del Agente de Distribución que se abre, configure el Agente de Distribución para que se ejecute en el host MSSQL01 bajo la cuenta de usuario mssql . Ingrese la contraseña para el usuario de Windows mssql . Selecciona Conectarse al distribuidor suplantando la cuenta de proceso y selecciona Conectarse al suscriptor suplantando la cuenta de proceso . Pulsa Aceptar para guardar los ajustes.

   

   Ahora ya tienes configuradas las propiedades de la suscripción.

   

5. Programación de sincronización . Selecciona el agente que se encuentra en el distribuidor para Ejecutar de forma continua para el suscriptor actual.
6. Inicializar suscripciones . Marque la casilla de verificación « » (Inicializar) y, en el menú desplegable, seleccione « » (Inmediatamente) como momento para inicializar la suscripción. También puede seleccionar la opción « » (Optimizada para memoria) si es necesario.

   

7. Acciones del asistente . Marque la casilla de verificación superior para crear la(s) suscripción(es) al final del asistente.
8. Completar el asistente . Puede comprobar los ajustes de la suscripción y hacer clic en « » (Finalizar) para crear la suscripción.

   

9. Espere hasta que se haya creado la suscripción. Si ve el Éxito estado, significa que la suscripción se ha creado correctamente.

   

10. Después de configurar la replicación en SQL Server, se muestran tres jobs en el Explorador de objetos, y puede verlos accediendo a Agente de SQL Server > Jobs .

    

Finalización de la configuración de la replicación

Una vez que haya configurado el distribuidor, el editor y el suscriptor, puede comprobar el estado de la replicación de MS SQL Server.

1. En el primer servidor (MSSQL01MSSQLSERVER1), inicie el monitor de replicación para ver el estado de la replicación de MS SQL Server. En SQL Server Management Studio, seleccione su instancia de MS SQL Server (MSSQLSERVER1), vaya a Replicación , haga clic con el botón derecho en Publicaciones locales y, en el menú contextual, seleccione Iniciar el monitor de replicación .

   

2. En nuestro caso, hay un error del Agente de lectura de registros . Para ver los detalles del error, selecciona la base de datos de origen (el editor) en el panel izquierdo, selecciona la pestaña Agentes en el panel derecho y haz doble clic en el nombre del error.

   

3. En la ventana que se abre, podrás ver el historial del agente y los mensajes de error. Los mensajes de error son:
   • El proceso no pudo ejecutar sp_replcmds en MSSQL01MSSQLSERVER1. Origen: MSSQL_REPL. Número de error: MSSQL_REPL20011).
   • No se puede ejecutar como el principal de la base de datos porque el principal “dbo” no existe, este tipo de principal no se puede suplantar, o no tiene permiso. (Fuente: MSSQLServer, Número de error: 15517).

   

   El segundo mensaje de error sugiere que falta algún tipo de permiso. Vamos a corregir este error.

4. Cree una nueva consulta en MS SQL Management Studio y ejecute esta consulta. En la ventana principal, haga clic en el botón Nueva Consulta .
5. En la sección de consulta SQL de la ventana principal, ingrese la siguiente consulta:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Haga clic en el botón Ejecutar .

   

   Comando(s) completado(s) con éxito.

6. A continuación, vaya a MSSQL01MSSQLSERVER1 > Replicación > Publicaciones Locales > [AdventureWorks2016]: AdvWorks_Pub . Haga clic derecho en el nombre de la publicación y, en el menú contextual, seleccione Ver Estado del Agente de Instantáneas . Puede hacer clic en Acción > Actualizar para actualizar el estado y Reinicializar Todas las Suscripciones para aplicar una instantánea a cada Suscriptor.

   Ahora todo está resuelto, no se muestran errores, y la replicación de MS SQL Server debería funcionar.

   

Comprobando Cómo Funciona la Replicación

Veamos la replicación de MS SQL Server en acción. Vea el contenido de una tabla de la base de datos AdventureWorks2016 almacenada en el primer servidor MS SQL ( MSSQL01MSQLSERVER1 ). En nuestro ejemplo, vamos a seleccionar todos los datos de la tabla Person.AddressType . Para hacerlo, ejecute la consulta:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
El resultado de la ejecución de la consulta se muestra en la captura de pantalla a continuación:

Ejecute una consulta similar en el segundo servidor para mostrar todos los datos de Person.AddressType de la base de datos AdventureWorks2016r almacenada en MSSQL02MSSQLSERVER2.
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Si compara las capturas de pantalla anteriores y siguientes, el contenido de Person.AddressType es idéntico en ambas bases de datos (una base de datos de origen en el primer servidor y la base de datos de destino que es una réplica de base de datos en el segundo servidor).

Vamos a eliminar una fila en la tabla PersonAddressType de la base de datos AdventureWorks2016 (origen) en el primer servidor (MSSQL01MSSQLSERVER1). Ejecute la consulta para eliminar una fila que contenga ‘Nombre’ en el nombre y para mostrar el contenido de la tabla después de eso:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

Como puede ver, se eliminó la primera fila con el DirecciónTipoID 1 y el nombre ‘Nombre’ de la tabla de Persona.DirecciónTipo en la base de datos AdventureWorks2016 en la máquina MSSQL01 .
La replicación transaccional está en ejecución. Verifiquemos el contenido de la tabla de Persona.DirecciónTipo en la base de datos AdventureWorks2016r en la máquina MSSQL02 . Ejecute una consulta similar como la anterior una vez más para ver el contenido de la tabla:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Como resultado de la replicación, la primera línea también se eliminó de la tabla de Persona.DirecciónTipo en la base de datos secundaria que actúa como la réplica de base de datos ( AdventureWorks2016r ). Puede ver los resultados en la captura de pantalla a continuación.

La replicación de base de datos en el servidor SQL funciona correctamente.

Conclusión

Existen cuatro tipos de replicación de MS SQL Server — instantánea, transaccional, entre pares y replicación de combinación. Dado que la replicación transaccional es ampliamente utilizada, hemos configurado este tipo de replicación de MS SQL Server en esta publicación de blog. Deben configurarse el Distribuidor, el Publicador y el Suscriptor para que la replicación de base de datos funcione. Se puede configurar el Suscriptor en un servidor de origen (replicación push) y servidor de destino (replicación pull).
Sin embargo, debe considerar utilizar tanto la replicación como backup de bases de datos MS SQL para aumentar las posibilidades de recuperación de datos de bases de datos exitosas.

Prueba NAKIVO Backup & Replication

Solicita una versión de prueba gratuita para descubrir todas las funciones de protección de datos de la solución. 15 días gratis. Sin limitaciones de funciones ni de capacidad. No es necesario facilitar ningún dato de tarjeta de crédito.

PROBAR GRATIS