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 sistemas operativos Windows Server. Las bases de datos son utilizadas por empresas de todos los sectores, y muchas soluciones de software utilizan bases de datos, 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 las copias de seguridad y la replicación de las bases de datos son una necesidad.

Conozca los tipos de replicación de SQL Server, cómo funciona la replicación en SQL Server y cómo realizar la replicación de SQL Server.

NAKIVO for Windows Backup

Fast backup of Windows servers and workstations to onsite, offiste and cloud. Recovery of full machines and objects in minutes for low RTOs and maximum uptime.

DISCOVER SOLUTION

¿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 en la base de datos de origen y de destino. Con la replicación en SQL Server, puede crear una copia idéntica de su base de datos principal y sincronizar los cambios entre las dos bases de datos manteniendo la coherencia y la integridad de los datos.

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

Antes de entrar de lleno en cómo configurar e instalar la replicación de MS SQL Server, repasemos brevemente los términos principales y los modelos de replicación.

Los artículos son las unidades básicas que deben reproducirse, como tablas, procedimientos, funciones y vistas. Los artículos pueden ampliarse vertical u horizontalmente mediante 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 base de datos. Por ejemplo, puede seleccionar sólo las tablas y campos más críticos utilizando filtros y, a continuación, replicar sólo 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 el backup 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 utilizados para describir las entidades de la base de datos. Existe una amplia gama de funciones de metadatos integradas que permiten devolver información sobre la instancia de MS SQL Server, las instancias de base de datos y las entidades de 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 MS SQL: Distribuidor, Editor y Suscriptor.

• Un distribuidor es una instancia de base de datos MS SQL configurada para recopilar transacciones de publicaciones y distribuirlas a los suscriptores. Un Distribuidor actúa como base de datos para almacenar las transacciones replicadas.

  Una base de datos de Distribuidores puede considerarse a la vez 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 configurar a los suscriptores para que utilicen una única instancia de MS SQL Server para obtener diferentes publicaciones (distribución centralizada). En este modelo, el Editor y el Distribuidor funcionan en servidores diferentes.

• Un Publisher es la copia de base de datos principal en la que se configura la publicación, que pone los datos a disposición de otros servidores MS SQL configurados para ser utilizados 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 publicación. El modelo de abonado único se utiliza cuando hay un solo abonado. Un modelo multi-suscriptor se utiliza cuando varios suscriptores están conectados a una única publicación.

  La suscripción es una solicitud de un ejemplar de una publicación que debe entregarse al suscriptor. La suscripción se utiliza para definir los datos de publicación que deben recibirse y dónde y cuándo se recibirán estos datos. Hay dos tipos de suscripción:

  • Suscripción push: Los datos modificados se transmiten forzosamente desde un Distribuidor a una base de datos de Abonados. No es necesario que el abonado lo solicite.
  • Tirar de la suscripción: Los datos modificados realizados en el Publicador son solicitados por un Abonado. El Agente funciona en el lado del Abonado.

  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-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 cuando utilice 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 para 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, la replicación de MS SQL Server puede ser unidireccional, uno-a-muchos, bidireccional y muchos-a-uno. Existen cuatro tipos de replicación en MS SQL Server: replicación instantánea, replicación transaccional, replicación peer-to-peer y replicación merge.

Replicación de instantáneas

La replicación de instantáneas se utiliza para replicar los datos exactamente 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 tener una réplica de base de datos más antigua que la base de datos maestra no es un problema crítico, o cuando se realiza un gran volumen de cambios en un corto periodo de tiempo. El seguimiento de cambios no se utiliza con la replicación instantánea.

Por ejemplo, la replicación instantánea 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 automatizada en la que los datos se distribuyen desde una base de datos maestra a una réplica de base de datos en tiempo real (o casi). La replicación transaccional es más compleja que la instantánea. Todas las transacciones realizadas, así como el estado final de la base de datos, se replican, lo que permite supervisar todo el historial de transacciones en la réplica.

Al principio del proceso de replicación transaccional, se aplica una instantánea al suscriptor y, a continuación, los datos se transfieren continuamente de la base de datos maestra a una réplica de base de datos a medida que se realizan cambios en estos datos. La replicación transaccional se utiliza ampliamente como replicación unidireccional.

Casos prácticos de replicación transaccional:

• Creación de un servidor de base de datos con una réplica de base de datos para utilizarla como conmutación por error si falla el servidor de base de datos principal.
• Recepción de informes sobre las operaciones realizadas en las sucursales utilizando varios Editores en las sucursales y un Suscriptor en la oficina principal.
• Replicar los cambios en cuanto se producen.
• Los datos de la base de origen cambian con frecuencia.

Replicación entre iguales

La replicación peer-to-peer se utiliza para replicar datos de bases de datos a múltiples 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 peer-to-peer puede ayudar a la ampliación de una aplicación que utiliza una base de datos. El principio de funcionamiento principal se basa en la replicación transaccional.

A continuación puede ver cómo puede utilizarse la replicación peer-to-peer de MS SQL Server entre servidores de bases de datos distribuidos por todo el mundo.

Fusionar la replicación

La replicación combinada es un tipo de replicación bidireccional que suele utilizarse en entornos de servidor a cliente para sincronizar datos entre servidores de bases de datos cuando no pueden estar conectados continuamente. Cuando se establece la conexión de red entre ambos servidores de bases de datos, los agentes de replicación merge detectan los cambios realizados en ambas bases de datos y los modifican para sincronizar y actualizar su estado. La replicación merge es similar a la replicación transaccional, pero los datos se replican del Publisher al Subscriber 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 merge puede ser utilizada por varios almacenes pares que trabajan con un almacén compartido. Cada tienda puede modificar la información de la base de datos del almacén y, al mismo tiempo, todas las tiendas deben tener el estado actualizado de sus bases de datos tras el envío de mercancías o la entrega de suministros al almacén. La replicación combinada puede utilizarse en los casos en los que la información actualizada debe estar disponible para la base de datos principal (o central) y las bases de datos de las sucursales simultáneamente.

Requisitos para la replicación de MS SQL Server

Los siguientes puertos deben estar abiertos 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 atendidos en la replicación MS SQL deben resolverse entre sí por 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 de servidor de bases de datos
• MS SQL Server Management Studio (SSMS): software para gestionar bases de datos MS SQL con la GUI (interfaz gráfica de usuario).

NOTA: MS SQL Server 2016 se utiliza para la configuración en este post. Puede utilizar el mismo principio para configurar la replicación en SQL Server de versiones más recientes.

Tenga en cuenta que si instala MS SQL Server 2016 en el primer equipo donde se encuentra la base de datos de origen, deberá tener instalado MS SQL Server 2016 en el segundo equipo para que la base de datos funcione correctamente.

Por ejemplo, si desea configurar la replicación transaccional MS SQL, puede utilizar el segundo servidor de base de datos (en el que está configurado el Suscriptor) de una versión comprendida entre dos versiones del servidor de base de datos de origen en el que está configurado el Editor. Si la versión de Publisher en MS SQL Server es 2016, el Distribuidor puede configurarse en las versiones 2016, 2017, 2019 y 2022, y el Suscriptor puede configurarse en MS SQL Server 2012, 2014, 2016, 2017 y 2019. La versión del Distribuidor no puede ser inferior a la versión del Editor. La replicación no funcionará si instala MS SQL Server 2008 en la segunda máquina, por ejemplo.

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

Antes de configurar el entorno para MS SQL Server, he aquí algunos factores a tener en cuenta:

• Existen limitaciones para los campos de identidad y los activadores.
• Las publicaciones sólo pueden contener tablas con la clave primaria.
• Se recomienda no utilizar la programación de creación de instantáneas para bases de datos de gran tamaño para evitar el uso de una gran cantidad de recursos informáticos.
• Tenga cuidado al cambiar los datos en la réplica de la base de datos que reside en el Suscriptor. Cuando llega una transacción que modifica datos y éstos han sido editados o borrados, la replicación puede detenerse hasta que se resuelva este problema.

Configurar el 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 escribir esta entrada del blog para entender mejor la configuración de la replicación de MS SQL Server.

Anfitrión 1

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

Anfitrión 2

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

Ambas máquinas tienen el disco C: y el disco D: en su configuración de disco.

Puede desactivar temporalmente el firewall de Windows cuando instale MS SQL Server para practicar la configuración de la replicación de MS SQL Server.

Esta entrada del blog no entra en cómo instalar MS SQL Server porque este tutorial se centra en la configuración de la replicación de MS SQL Server. En este ejemplo, ambos servidores MS SQL están instalados sin PolyBase.

Compruebe que ha 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:³ de programa³ Microsoft SQL Server).

Otros ajustes:

• Modo de autenticación mixto (autenticación Windows y autenticación MS SQL Server)
• Directorio raíz de datos: D:MSSQL_Server
• Directorio de base de datos del sistema: D:\MSSQL_Server\MSSQL13.MSSQLSERVER1\MSSQL\Data
• Directorio de base de datos de usuario: D:\MSSQL_Server\MSSQL13.MSSQLSERVER1\MSSQL\Data
• Directorio de registro de la base de datos del usuario: D:\MSSQL_Server\MSSQL13.MSSQLSERVER1\MSSQL\Data
• Directorio de backups: D:\MSSQL_Server\MSSQL13.MSSQLSERVER1\MSSQL\Backup

Una vez que MS SQL Server 2016 y SQL Server Management Studio están instalados 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

Hay que configurar los servidores antes de 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. Cree el usuario mssql en ambos servidores y establezca la misma contraseña.
2. El usuario mssql es miembro de los siguientes grupos en este ejemplo:
   • Administradores (administradores locales en máquinas 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 comando.

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

Conexión a MS SQL Server

1. Ejecute SQL Server Management Studio.
2. Inicie sesión (ver captura de pantalla) como sa utilizando la autenticación de SQL Server.
   • MSSQL01\MSSQLSERVER1 es el nombre del host y de la instancia MS SQL en el primer servidor.
   • MSSQL02\MSSQLSERVER2 es el nombre del host y el nombre de la instancia de MS SQL en el segundo servidor.

   

Del mismo modo, puede conectarse en 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 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.

Iniciar el Agente

Una vez que inicie sesión en la instancia de MS SQL Server, verá que el Agente no se está ejecutando. Por defecto, 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 tras el arranque de Windows.

Para configurar el servicio de Agente para que se inicie automáticamente:

1. Pulsa Win+R, ejecuta cmd, y ejecuta el services.msc comando.
2. Abra las propiedades del servicio Agente de SQL Server y establezcaTipo de inicio en Automático.

   

Configuración de usuarios para MS SQL Server

Después de conectarse a la instancia MSSQLSERVER1 en SQL Server Management Studio, tenemos que configurar los usuarios:

1. Vaya al Explorador de Objetos y abra Seguridad > Logins.
2. Haga clic con el botón derecho del ratón en Inicio 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, luego en Comprobar nombres para confirmar y pulse Aceptar dos veces para guardar los ajustes.

   

5. Ahora el usuario de Windows MSSQL01\mssql se añade a la lista de usuarios que pueden iniciar sesión en la base de datos (de forma similar, añada el usuario mssql a los inicios de sesión en la segunda máquina MSSQL02 en el SQL Server Management Studio).
6. Añada 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 MSSQL01\MSSQLSERVER1 > Roles de servidor, haga clic con el botón derecho en sysadmin y abra Propiedades.
8. En la página Miembros , haz clic en Añadir, introduce el nombre de tu usuario mssql y haz clic en Comprobar nombres.
9. Seleccione la casilla de verificación del nombre de usuario MSSQL01\mssql y haga clic en Aceptar.

   

10. Realice la misma configuración en su segunda máquina (MSSQL02 en este caso).
11. Reinicie ambas máquinas.

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

    

Importar una base de datos a partir de un backups

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

1. Copie el archivo de backup de la base de datos AdventureWorks2016.bak en el directorio de backups de MSSQL. En nuestro caso, este directorio en el primer servidor es D:\MSSQL_Server\MSSQL13.MSSQLSERVER1\MSSQL\Backup\
2. Importar una base de datos de ejemplo. En el primer equipo, en SQL Server Management Studio, vaya a MSSQL01\MSSQLSERVER1, 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:
   • Fuente: Dispositivo.
   • Haga clic en los tres puntos para examinar el archivo de backups de la base de datos.
     • En la ventana Seleccionar dispositivos de backup, seleccione el tipo de medio de backup: archivo.
     • Haz clic en Añadir.
   • Seleccione el archivo .bak necesario – D:\MSSQL_Server\MSSQL13.MSSQLSERVER1\MSSQL\Backup\AdventureWorks2016.bak
   • Pulsa OK y, a continuación, pulsa OK una vez más.
4. La base de datos AdventureWorks2016 se ha restaurado correctamente.

   

Puede importar la base de datos desde un backup en la segunda máquina, donde se ejecutará la réplica de la base de datos. Este enfoque permite reducir el tráfico de red porque la replicación se iniciará copiando los cambios desde que se creó la copia de seguridad sin copiar todos los datos de la base de datos en una base de datos vacía.

Restaure la base de datos a partir de un backup en el segundo servidor y cambie el nombre de la base de datos a AdventureWorks2016r, donde «r» significa «réplica».

Por último, tenemos:

Tras importar la base de datos, deberá realizar algunos ajustes para preparar sus servidores MS SQL.

1. En la máquina MSSQL01 , vaya a MSSQL01\MSSQLSERVER1 > Security > Logins, seleccione MSSQL01\mssql. Haga clic con el botón derecho del ratón (o doble clic) en el usuario mssql y seleccione Propiedades.
2. En Roles de servidor, seleccione la casilla situada junto al rol dbcreator .

   

3. En la página Asignación de usuarios, seleccione los usuarios asignados a este inicio de sesión y marque la casilla de verificación Base de datos AdventureWorks2016 (seleccione AdventureWorks2016r en el segundo servidor según corresponda).
4. En la sección de pertenencia al rol de base de datos, marque la casilla db_owner.

   

5. Pulse OK para guardar los ajustes.

Realice la misma configuración en la máquina MSSQL02. A continuación, puede configurar los componentes de MS SQL Server necesarios para la replicación de bases de datos.

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

Configurar la replicación en modo gráfico es el método más cómodo. La siguiente configuración se realiza en SQL Server Management Studio. La replicación de bases de datos transaccionales se explica en este ejemplo porque es uno de los tipos de replicación de MS SQL Server más utilizados.

La vista en el servidor de base de datos principal (MSSQL01\MSSQLSERVER1) y la vista en el segundo servidor (MSSQL02\MSSQLSERVER2) en SQL Server Management Studio se muestran en la siguiente captura de pantalla.

Configuración de la distribución

La distribución puede utilizarse para varios editores y abonados. 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 (MSSQL01\MSSQLSERVER1), haga clic con el botón derecho del ratón en Replicación y, en el menú contextual, seleccione Configurar distribución.

Se abre el Asistente para configurar la distribución.

1. Distribuidor. Seleccione la instancia de base de datos actual que se ejecuta en el servidor principal (MSSQL01\MSSQLSERVER1) para que actúe como Distribuidor en este ejemplo. Haga clic en Siguiente cada vez para continuar con el siguiente paso del asistente.
2. Inicio del agente de SQL Server. Si no ha configurado MS SQL Server Agent para que se inicie automáticamente, como se ha explicado anteriormente, aparecerá el siguiente mensaje. Seleccione Sí y configure el servicio Agente de SQL Server para que se inicie automáticamente.

   

3. Carpeta de instantáneas. Puede dejar aquí la ruta por defecto. Se necesita una instantánea para inicializar la replicación. Asegúrese de que hay suficiente espacio libre en el disco donde se encuentra el directorio de instantáneas. La cantidad de espacio libre debe corresponder al menos 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(distribución) y las carpetas para el archivo de 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. Seleccione la casilla de verificación situada junto al nombre de la base de datos de distribución en la instancia primaria de MS SQL Server (que aloja una base de datos de origen que se replicará). En este ejemplo, se trata de la instancia MSSQL01\MSSQLSERVER1, y el nombre de la base de datos de distribución es distribución.
6. Acciones del Mago. Seleccione Configurar distribución para configurar la distribución durante el último paso del asistente. En este ejemplo, no generaremos un archivo de script para ejecutarlo posteriormente.

   

7. Completa el Asistente. Compruebe el resumen de configuración de la Distribución y haga clic en Finalizar para crear el Distribuidor.

   

8. El estado Éxito debería aparecer si el Distribuidor ha sido creado y configurado con éxito.

   

Si ve que se ha producido un error al configurar SQL Server Agent para que se inicie automáticamente, vaya a la configuración de servicios y compruebe el modo de inicio de SQL Server Agent (consulte cómo configurar el inicio del agente más arriba en esta entrada del blog).

También puede abrir las propiedades de SQL Server Agent en SQL Server Management Studio y comprobar el estado del servicio y las opciones de reinicio. Haga clic con el botón derecho en SQL Server Agent al final de la lista en el Explorador de objetos y pulse Propiedades para ver o editar las propiedades del agente.

Configuración del editor

Una vez configurada la Distribución, puede configurar el Editor. El Publisher debe configurarse en el servidor principal (MSSQL01\MSSQLSERVER1) donde se almacena la base de datos maestra a replicar. Seleccione Replicación, haga clic con el botón derecho del ratón en Publicaciones locales y, en el menú contextual, seleccione Nueva publicación.

Se abre el Asistente para nueva publicación.

1. Base de datos de publicaciones. Seleccione la base de datos que desea replicar(AdventureWorks2016 en este caso). Pulse Siguiente en cada paso del asistente para continuar.

   

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

   

4. Filtrar filas de tabla. En este ejemplo no se añade ningún filtro (ésta es la configuración por defecto de los filtros). Puedes añadir filtros si es necesario.
5. Agente de instantáneas. Especifique cuándo ejecutar el agente de instantáneas. Configuremos el Agente para que se ejecute inmediatamente. Seleccione Crear una instantánea inmediatamente y mantener la instantánea disponible para inicializar las suscripciones.

   

6. Agente de seguridad. Seleccione Utilizar los ajustes de seguridad del Agente de instantáneas. Haga clic en el botón Ajustes de seguridad para seleccionar la cuenta con la que se ejecutará el Agente.

   En la ventana Seguridad del agente de instantáneas que se abre, introduzca las credenciales del usuario de Windows mssql que ha creado anteriormente. Seleccione conectar con el Editor Suplantando la cuenta del proceso. Haga clic en Aceptar para guardar los ajustes y volver al asistente.

   

   Una vez definido el usuario necesario, podrá verlo en las secciones Agente de instantáneas y Agente de lectura de registros.

   

7. Acciones del Mago. Seleccione la casilla superior para crear la publicación durante el último paso del asistente.
8. Completa el Asistente. Compruebe la configuración de la publicación y haga clic en Finalizar para crear una nueva publicación.

   

En la ventana Crear publicación, puede supervisar el progreso de la creación de una nueva publicación. Espere un momento y debería ver el estado de éxito si todo se ha hecho correctamente.

La publicación ya está creada y puede verla en el Explorador de Objetos accediendo a Replicación > Publicaciones locales.

Configuración del abonado

Como recordará, la replicación de MS SQL Server puede ser pull o push. Si configura la replicación push, debe configurar el Suscriptor para que ejecute agentes en el servidor de base de datos principal (MSSQL01 en este caso). Si se configura la replicación pull, el Suscriptor debe estar configurado 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.

Configuremos la replicación push y creemos una nueva suscripción en el primer MS SQL Server (MSSQL01\MSSQLSERVER1) donde reside la base de datos maestra.

En el Explorador de objetos, vaya a Replicación, haga clic con el botón derecho en Suscripciones locales y, en el menú contextual, seleccione Nuevas suscripciones.

Se abre el Asistente para nueva suscripción.

1. Publicación. Seleccione la publicación para la que desea crear una nueva suscripción. En nuestro ejemplo, el nombre del Editor es MSSQL01\MSSQLSERVER1 y el nombre de la publicación (que se creó anteriormente) es AdvWorks_Pub. Haga clic en Siguiente en cada paso del asistente para continuar.
2. Agente de distribución Ubicación. Seleccione el tipo de replicación seleccionando suscripción push o suscripción pull. En nuestro ejemplo, queremos que todos los agentes se ejecuten en el lado del servidor de origen, por lo tanto, 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. Abonados. Por defecto, el servidor en el que se ejecuta el asistente (MSSQL01\MSSQLSERVER1 en este caso) se muestra como el Suscriptor, y la base de datos de suscripción no está definida. Añadamos un nuevo Suscriptor y seleccionemos una base de datos de suscripción ubicada en el segundo servidor de bases de datos (MSSQL01\MSSQLSERVER2). Haga clic en Añadir suscriptor y, en el menú contextual, seleccione Añadir suscriptor de SQL Server.
   • En la ventana emergente, introduzca las credenciales para la segunda instancia de MSSQL Server (MSSQL01\MSSQLSERVER2 en nuestro caso) y haga clic en Conectar.

     

   • Seleccione la casilla del segundo servidor en el que se almacenará la réplica de la base de datos (MSSQL02\MSSQLSERVER2) y, en el menú desplegable Base de datos de suscripción, seleccione una base de datos nueva o una base de datos existente restaurada a partir de una copia de seguridad para utilizarla como réplica de la base de datos.

     En nuestro ejemplo, AdventureWorks2016r se creó en el segundo servidor restaurando la base de datos principal (de origen) de AdventureWorks2016 a partir de una copia de seguridad para iniciar la replicación. La replicación se inicia replicando sólo los datos nuevos, pero no copiando toda la base de datos tras iniciar el proceso de replicación. Por lo tanto, se selecciona AdventureWorks2016r como base de datos de suscripción en el ejemplo actual.

     

4. Seguridad del agente de distribución. Haga clic en el botón con 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 con la cuenta de usuario mssql . Introduzca la contraseña del usuario Windows mssql . Seleccione Conectar con el distribuidor suplantando la cuenta del proceso y seleccione Conectar con el abonado suplantando la cuenta del proceso. Pulsa OK para guardar los ajustes.

   

   Ahora sus propiedades de suscripción están configuradas.

   

5. Horario de sincronización. Seleccione el Agente que se encuentra en el Distribuidor para Ejecutar continuamente para el Abonado actual.
6. Inicializar suscripciones. Seleccione la casilla de verificación Inicializar y, en el menú desplegable, seleccione Inmediatamente para saber cuándo inicializar la suscripción. También puede seleccionar la opción Memoria optimizada si es necesario.

   

7. Acciones del Mago. Seleccione la casilla superior para crear la(s) suscripción(es) al final del asistente.
8. Completa el Asistente. Puede comprobar sus ajustes de suscripción y hacer clic en Finalizar para crear la suscripción.

   

9. Espere hasta que se cree la suscripción. Si ve el estado Éxito , 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 yendo a SQL Server Agent > Jobs.

    

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

Una vez configurados el Distribuidor, el Publicador y el Suscriptor, puede comprobar el estado de replicación de MS SQL Server.

1. En el primer servidor (MSSQL01\MSSQLSERVER1), inicie el monitor de replicación para ver el estado de 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 supervisión de replicación.

   

2. En nuestro caso se produce un error del agente Log Reader. Para ver los detalles del error, seleccione la base de datos de origen (el Editor) en el panel izquierdo, seleccione la pestaña Agentes en el panel derecho y haga doble clic en el nombre del error.

   

3. En la ventana que se abre, puede ver el historial del agente y los mensajes de error. Los mensajes de error son:
   • El proceso no pudo ejecutar sp_replcmds en MSSQL01\MSSQLSERVER1. Fuente: MSSQl_REPL. Número de error: MSSQL_REPL20011).
   • No se puede ejecutar como la entidad de seguridad de la base de datos porque la entidad de seguridad «dbo» no existe, no se puede suplantar este tipo de entidad de seguridad o no se tienen permisos. (Fuente: MSSQLServer, Número de error: 15517).

   

   El segundo mensaje de error sugiere que falta algún tipo de permiso. Arreglemos este error.

4. Cree una nueva consulta en MS SQL Management Studio y ejecútela. En la ventana principal, haga clic en el botón Nueva consulta.
5. En la sección de consulta SQL de la ventana principal, introduzca la siguiente consulta:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Pulse el botón Ejecutar .

   

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

6. A continuación, vaya a MSSQL01\MSSQLSERVER1 > Replication > Local Publications > [AdventureWorks2016]: AdvWorks_Pub. Haga clic con el botón derecho del ratón 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.

   

Comprobar cómo funciona la replicación

Veamos la replicación de MS SQL Server en acción. Ver el contenido de una tabla de la base de datos AdventureWorks2016 almacenada en el primer servidor MS SQL(MSSQL01\MSQLSERVER1). En nuestro ejemplo, vamos a seleccionar todos los datos de la tabla Person.AddressType. Para ello, ejecute la consulta:

USE AdventureWorks2016;

GO

SELECT *

FROM Person.AddressType

;

El resultado de la ejecución de la consulta se muestra en la siguiente captura de pantalla:

Ejecute una consulta similar en el segundo servidor para mostrar todos los datos de Person.AddressType de la base de datos AdventureWorks2016r almacenada en MSSQL02\MSSQLSERVER2.

USE AdventureWorks2016r;

GO

SELECT *

FROM Person.AddressType

;

Si compara las capturas de pantalla de arriba y abajo, 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 de la tabla PersonAddressType de la base de datos AdventureWorks2016 (origen) en el primer servidor (MSSQL01\MSSQLSERVER1). Ejecute la consulta para eliminar una fila que contenga ‘Fac turación’ 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 ha eliminado la primera fila con el AddressTypeID 1 y el nombre ‘Billing ‘ de la tabla Person.AddressType de la base de datos AdventureWorks2016 en el equipo MSSQL01 .

Se está ejecutando la replicación transaccional. Comprobemos el contenido de la tabla Person.AddressType en la base de datos AdventureWorks2016r de la máquina MSSQL02 . Ejecute de nuevo una consulta similar a la anterior 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 Person.AddressType de la base de datos secundaria que actúa como réplica de la base de datos(AdventureWorks2016r). Puede ver los resultados en la siguiente captura de pantalla.

La replicación de bases de datos en SQL Server funciona correctamente.

Conclusión

Existen cuatro tipos de replicación de MS SQL Server: instantánea, transaccional, peer-to-peer y merge. Como la replicación transaccional es ampliamente utilizada, hemos configurado este tipo de replicación de MS SQL Server en esta entrada del blog. El Distribuidor, el Publicador y el Suscriptor deben estar configurados para que funcione la replicación de bases de datos. El Suscriptor puede configurarse en un servidor de origen (replicación push) y en un servidor de destino (replicación pull).

Sin embargo, debería considerar la posibilidad de utilizar tanto la replicación como el backup de bases de datos MS SQL para aumentar las posibilidades de éxito en la recuperación de datos de bases de datos.

1 Year of Free Data Protection: NAKIVO Backup & Replication

Deploy in 2 minutes and protect virtual, cloud, physical and SaaS data. Backup, replication, instant recovery options.

Get the Free Edition