En un entorno en producción, podemos encontrarnos con la necesidad de replicar nuestra BBDD de SQL Server para liberar algo de carga y poder ejecutar otros procesos que necesitamos para consultar datos.
En este artículo explicamos cómo crear y configurar la replicación transaccional de la BBDD en local y partimos de un servidor en producción con SQL Server 2016 Standard instalado y Visio 2016.
Antes de hacer ningún cambio es recomendable hacer un Snapshot.
Se resume en los siguientes apartados:
- Configuración de permisos
- Crear usuario/s
- Configurar los permisos de usuario
- Directorio donde se encuentra la BBDD a replicar
- Directorio donde se encuentra la BBDD de replicación
- Directorio donde se encuentran los datos y registros de la BBDD de replicación
- Directorio donde se encuentra el recurso compartido para la instantánea de publicación (Snapshot)
- Permisos de acceso a la BBDD a replicar
- Permisos de acceso a la BBDD de Distribución
- Permisos de acceso a la BBDD de Replicación
- Permisos de acceso a la lista de acceso de la publicación (PAL)
- Instalar SQL Server Replicación
- Configuración de la distribución
- Crear y configurar la publicación para la replicación transaccional
- Habilitar permisos para el agente en la lista de acceso de la publicación (PAL)
- Crear la suscripción a la publicación transaccional
- Establecer permisos en la Base de Datos para el suscriptor
- Ver el estado de sincronización de la suscripción
1. Configuración de permisos
1. Crear usuario/s
En esta sección vamos a crear un único usuario local con permisos Full para poder crear la instantánea de replicación y lo usaremos para todo el proceso, aunque se pueden crear diferentes usuarios para delimitar los permisos.
Paso 1/3: Desde el panel de control > Herramientas administrativas > Administración de equipo
(Control Panel\All Control Panel Items\Administrative Tools\Computer Management)
Paso 2/3: Desplegamos las opciones de "local Users and Groups" y botón derecho encima de la carpeta "Users" para crear un nuevo usuario
Paso 3/3: En el ejemplo usamos el nombre de usuario replica, pero podemos darle el nombre que queramos. que queramos y
A continuación se nos abrirá una nueva ventana donde podemos cumplimentar los datos del nuevo usuario como el nombre y su contraseña, en nuestro ejemplo el nombre del usuario es "replica", cumplimentamos los campos de contraseña, y dejamos seleccionadas las casillas de verificación como aparecen en la siguiente imagen, para que no sea posible cambiar la contraseña y que nunca expire:
Pulsamos el botón "Crear" para crear el usuario y ya podemos cerrar la ventana de administración de equipo.
2. Configurar los permisos de usuario:
Una vez tengamos el usuario creado, pasamos a configurar los permisos. Algunos permisos se necesitan configurar antes y otros se tendrán que configurar más adelante cuando esté creada la BBDD de replicación, distribución y la publicación.
A continuación, para verlo con más claridad, vamos a listar todos los permisos que necesitamos darle al usuario que hemos creado. También nos puede servir de referencia si queremos delimitar los permisos a diferentes usuarios:
1. Directorio donde se encuentra la BBDD a replicar (Ver punto 3).
2. Directorio donde se encuentra la BBDD de replicación (Ver punto 3).
3. Directorio donde se encuentran los datos y registros de la BBDD de replicación:
Normalmente se guardan en el mismo directorio pero se puede dar el caso de que estén en ubicaciones diferentes. En este ejemplo están en el mismo directorio que se muestra en la siguiente imagen, y hay que añadir permisos de edición al usuario "replica".
Paso1/5: Para asignar permisos: Botón derecho situando el cursor encima del nombre de la carpeta "DATA" y seleccionamos las "Propiedades":
Paso 2/5 - En la pestaña seguridad pulsamos el botón de "Edit...":
Paso 3/5: Para añadir al usuario pulsamos el botón "Add...":
Paso 4/5: Se nos abrirá una nueva ventana para seleccionar el usuario: Escribimos el nombre del usuario (1) y lo verificamos pulsando el botón "Check Names" (2), y finalmente aceptamos los cambios pulsando el botón de OK (3):
Paso 5/5: Una vez asignado el usuario en el paso anterior se cierra la ventana y nos quedamos en la misma ventana del "Paso 2". Nos aseguramos que esté seleccionado el usuario replica, marcamos todas las casillas a excepción "Full control". Para terminar, pulsamos el botón "OK" para aceptar los cambios.
4. Directorio donde se encuentra el recurso compartido para la instantánea de publicación (Snapshot):
Añadimos los permisos totales al usuario "replica" para la carpeta donde se creará el Snapshot que se configura al crear editor para la publicación:
Este paso lo podemos hacer más adelante, ya que el directorio que vamos a crear manualmente a continuación, si no existe se creará en el momento que se configure la distribución mostrando la ruta dónde se crea, o bien puede ser otra ruta si se requiere. En cualquier caso, será necesario dar permisos al usuario que hemos creado anteriormente en este directorio.
- Creamos la carpeta con el nombre de repldata en la ruta: C:\Archivos de programa\Microsoft SQL Server\MSSQL.X\MSSQL\Data
- Asignar permisos: Botón derecho situando el cursor encima del nombre de la carpeta que hemos creado anteriormente, y accedernos a las "Propiedades":
Paso 1/4: En la pestaña seguridad pulsamos el botón de "Edit...":
Paso 2/4: Para añadir al usuario pulsamos el botón "Add...":
Paso 3/4: Se nos abrirá una nueva ventana para seleccionar el usuario: Escribimos el nombre del usuario (1) y lo verificamos (2), y finalmente aceptamos los cambios pulsando el botón de OK (3):
Paso 4/4: Una vez asignado el usuario en el paso anterior se cierra la ventana y nos quedamos en la misma ventana del "Paso 2". Nos aseguramos que esté seleccionado el usuario replica, marcamos la casilla de "Full control" y podremos observar que se marcan todos los permisos. Para terminar, pulsamos el botón "OK" para aceptar los cambios:
5. Permisos de acceso a la BBDD a replicar:
Para añadir los permisos de acceso a la BBDD a replicar con el usuario que vamos a utilizar "replica", seguimos estos 3 pasos que vemos a continuación:
Paso 1/3: Abrimos la aplicación de Microsoft SQL Server Management Studio:
Paso 2/3: Botón derecho encima de "Login(1), se abrirá una nueva ventana dónde podemos buscar el usuario, escribimos el nombre de usuario que hayamos creado en el campo de "Login name" que en este ejemplo es "replica" y pulsamos el botón buscar "Search" (2).
Luego se nos abre una pequeña ventana en la que podemos pulsar el botón "Check Names" (4) para verificar que el usuario existe y está creado y finalmente aceptamos los cambios pulsando el botón "OK" (5) y se cerrará la ventana de búsqueda agregando el usuario correspondiente en la ventana anterior:
Paso 3/3: A continuación, en la misma ventana que tenemos abierta, seleccionamos en el menú lateral "User Mappinng" (1), marcamos la casilla de la Base de Datos que tenemos en producción (2) y a continuación marcamos la casilla del cuadro inferior "db_owner" (3):
6. Permisos de acceso a la BBDD de distribución:
Se debe hacer en cuanto esté configurada la distribución (Apartado 3). Consiste en añadir permisos al usuario "replica" que ya nos aparece añadido. Para añadir los permisos, ver apartado 7 para más detalle de cómo añadir permisos.
7. Permisos de acceso a la BBDD de Replicación:
Se debe hacer en cuanto esté configurado el editor (Apartado 4). Para añadir los permisos, ver apartado 7 para más detalle de cómo añadir permisos.
8. Permisos de acceso a la lista de acceso de la publicación (PAL):
Este paso lo veremos más adelante cuando se crea la publicación. Se explica cómo añadir los permisos en el "Apartado 5".
2. Instalar SQL Server Replication
Ejecutar el archivo de instalación de SQL Server como administrador para añadir este nuevo complemento "Replication". Se abrirá una nueva ventana para la instalación, y seleccionando en el menú lateral "Installlation" encontramos la opción para instalar SQL o complementos:
En el siguiente paso nos permite instalar complementos en una instancia existente y aparecerán las detectadas, para ello seleccionamos la opción de "Añadir complementos a una instancia existente de SQL Server 2016" y en este caso solo tenemos una instancia que ya aparece seleccionada:
Si está instalado el SQL Server Replication, la casilla está marcada como se muestra en la siguiente imagen, en caso contrario no está instalado. Marcar la casilla para realizar la instalación y seguir los pasos hasta completar la instalación (No requiere reinicio):
3. Configuración de la distribución
Abrimos la aplicación de Microsoft SQL Server Management Studio y nos conectamos a la instancia donde tenemos nuestra BBDD a replicar.
Al haber instalado en el paso anterior el "SQL Server Replication"; nos permitirá configurar la distribución. Para ello, nos situamos encima de "Replication" y pulsamos botón derecho del mouse para desplegar las opciones, y seleccionamos "Configure Distribution.." como se muestra en la siguiente imagen:
A continuación, se abrirá una nueva ventana para la configuración con el asistente. Pulsamos el botón "Siguiente" o que es lo mismo "Next":
En el siguiente paso, por defecto ya está seleccionada la primera opción que es justo la que nos interesa.
La primera opción consiste en crear en la instancia actual la distribución y la segunda opción nos permite seleccionar otra instancia que ya se haya configurado como distribuidor. En nuestro caso el distribuidor es local, así que dejamos seleccionada la opción por defecto y pulsamos el botón "Siguiente":
Nos mostrará una advertencia a tener en cuenta ya que el agente del servidor SQL tiene que estar en marcha para la replicación y hacer la instantánea. Podemos elegir si ponerlo en marcha si detecta que está parado de forma automática al arrancar el servidor o bien, que no se inicie de forma automática:
En el siguiente paso, nos mostrará la ruta por defecto donde guardará una copia de la imagen de la BBDD actual y donde replicará los cambios en tiempo real. Podemos cambiar la ruta si nos interesa teniendo en cuenta darle premisos totales para que pueda crear la instantánea, que se explica en la sección siguiente.
Finalmente pulsamos "Siguiente":
En la imagen siguiente, puedes cambiar el nombre si quieres de la BBDD de distribución. También se puede definir la ruta donde se creará la BBDD de replicación y guardará los log, o dejarlo por defecto. Si se cambian las rutas hay que tener en cuenta los permisos que se explica en la siguiente sección:
En el siguiente paso hay que marcar los editores de replicación SQL que acceden al distribuidor y pulsar siguiente:
A continuación tenemos dos opciones, por defecto dejamos solo marcada la primera casilla para configurar ahora la distribución, o bien podemos marcar también la otra casilla para que nos genere un script y lo podamos configurar más adelante. Después pulsamos siguiente e iniciará la configuración:
Una vez termine nos mostrará un resumen de todos los puntos y pulsamos finalizar.
Nota: Si no hemos marcado la opción de generar un script para configurar más adelante, es normal si aparece error en el último paso al no tener permisos el servicio de agente SQL que nos falta por hacer y que se explica en la siguiente sección.
4. Crear y configurar la publicación para la replicación transaccional
El primer paso es configurar el editor que para ello creamos una nueva publicación:
Se nos abrirá un asistente para crearlo en una nueva ventana, pulsamos siguiente:
A continuación, seleccionamos la BBDD que queremos y pulsamos siguiente:
Aquí es importante destacar que seleccionamos "Transactional publication" para transmitir las transacciones una vez esté creada la instantánea, ya que la primera opción es para programar cuando hacerlo. Y pulsamos siguiente:
Ahora, llegados a este punto podemos seleccionar los objetos y tablas que queramos replicar:
Una vez seleccionados los objetos, tablas.. en el paso anterior, pulsamos siguiente:
Nos da el detalle de la selección en la imagen anterior. Pulsamos siguiente de nuevo.
A continuación, nos permite definir filtros para aplicar a los artículos seleccionados si pulsamos "Add" podemos seleccionar una tabla en cuestión y luego aplicar un filtro. En este caso lo dejamos predeterminado, y siguiente:
En el siguiente paso, por defecto no hay ninguna selección. En este caso vamos a seleccionar la primera opción para crear la instantánea inmediatamente ya que la segunda opción es para crearlo más adelante configurando las schedule para definir una ventana horaria:
En el siguiente punto, tenemos que especificar la cuenta que se usará como agente de las instantáneas que tiene que tener permisos totales en cambio, para el segundo agente "Log Reader Agent" podemos seleccionar otros usuario ya que no necesita permisos totales y solo tenga permisos de lectura. Pero en este ejemplo usamos el mismo usuario por eso no hace falta desmarcar la casilla de "use the security settings from the SnapShot Agent". En cualquier caso, para pulsamos el botón "Security Settings.." que corresponda:
Y nos abrirá una segunda ventana para poder indicar qué tipo de usuario, en este caso se usará una cuenta de usuario creada en Windows y pulsamos el botón "OK":
Al cerrarse la segunda ventana, volvemos a la ventana anterior y nos aparecerá cumplimentado el campo y el botón de "Next" disponible para ir al siguiente paso:
El último paso ya nos permite crear la publicación como vemos en la siguiente imagen marcando la casilla "Create the publication" o bien, marcar la segunda opción si os interesa crearla más adelante por un script. Y pulsamos siguiente:
A continuación nos faltará elegir un nombre para la publicación, podemos usar el mismo de la DDBB que tenemos y añadir _Pub por ejemplo: DatabaseEjemplo_Pub
Y pulsamos el botón de finalizar:
Una vez completado el proceso, se puede comprobar que la publicación se ha creado:
Para más detalles, pulsa encima botón derecho y en el desplegable haz clic en la opción de "View SnapShot Agent Status":
Y a continuación, si se ha creado aparece un mensaje similar:
Si pulsamos el botón monitor, nos abrirá una nueva ventana donde podemos consultar más detalles del proceso. Para continuar, pasamos a la siguiente sección donde se explica cómo crear el suscriptor.
5. Habilitar permisos para el agente en la lista de acceso de la publicación (PAL):
En este apartado solo tenemos que añadir el usuario con permisos de edición pero en este ejemplo, se añade el usuario replica con permisos totales:
Y a continuación lo podemos añadir en este apartado, para ello, seleccionar "Publication Access List" y luego botón añadir:
Después de pulsar el botón añadir nos aparecerá la siguiente ventana, seleccionado el usuario pulsamos OK:
Luego volvemos a la ventana anterior que también tenemos que aceptar los cambios pulsando el botón OK:
6. Crear la suscripción a la publicación transaccional
Para crear la suscripción a la publicación transaccional, botón derecho encima de "Local Subscriptions":
Se nos abrirá un nuevo asistente y seguiremos los pasos:
Nos mostrará la PAL (Lista de acceso de la publicación) que ya creamos en otra sección anterior. La seleccionamos y pulsamos siguiente:
En este ejemplo aceptamos la opción seleccionada por defecto para ejecutar todos los agentes en el distribuidor:
En el siguiente paso, para que nos aparezca el check seleccionado en este caso la instancia "DBSQL", tenemos que añadir primero el un nuevo suscriptor "Add SQL Server Subscriber.." y luego conectar a la instancia:
Para ello nos aparecerá la ventana de conexión a la instancia del suscriptor, pulsamos conectar y listo:
Volvemos a la ventana anterior, y nos mostrará la instancia donde está el suscriptor y procedemos a crear la BBDD seleccionando en el desplegable la opción "New database" y pulsamos siguiente "Next":
A continuación le damos un nombre a la nueva BBDD de replicación y pulsamos OK:
Ahora en la siguiente ventana tenemos que añadir el usuario que usamos para la conexión al suscriptor que estamos creando, pulsando encima del icono de los puntos se nos abrirá la ventana de configuración:
En la ventana de configuración, vamos a configurar la conexión con una cuenta de usuario, en este caso usamos un usuario local del servidor que ya creamos al principio de esta guía. Los datos a cumplimentar son los que se marcan en la siguiente imagen, cuenta de usuario y contraseña, y pulsamos OK:
Automáticamente después de pulsar OK, volvemos a la ventana anterior que nos permitirá pulsar el botón de finalizar directamente o bien pulsar siguiente. Ya que el resto de configuraciones las dejamos por defecto, podemos pulsar finalizar y empezará a crear la suscripción. Una vez completada, aparecerán los resultados de la siguiente forma, y solo faltará pulsar el botón de "Close" si todo está correcto:
Para ver el proceso desde el monitor de replicación sigue estos pasos:
7. Establecer permisos en la Base de Datos para el suscriptor
Establecer permisos de base de datos en el suscriptor se pueden establecer ahora que está creada la BBDD de replicación. Para hacerlo, desplegamos las opciones de Security y Logins dónde podemos añadir permisos a un usuario ya añadido pulsando botón derecho encima del usuario en cuestión (En este caso el usuario es DBSQL\replica) y seleccionamos "Properties" que pulsando encima con el botón izquierdo se nos abrirá la ventana de propiedades del usuario SQL:
Marcamos las tres BBDD y para seleccionar el role de "db_owner" tendremos que seleccionar una a una, cada BBDD, y marcar el check de "db_owner" en cada una, y al seleccionar la DDBB de distribución también marcar el check de replmonitor.
Finalmente para guardar los cambios pulsamos OK:
8. Ver el estado de sincronización de la suscripción
Para ver el estado de sincronización de la suscripción hacer click con el botón derecho encima de la carpeta "Replication" y seleccionar con el botón izquierdo "Launch Replication Monitor":
Y se nos abrirá una nueva ventana, donde podemos pulsar con el botón derecho encima del trabajo en curso y ver más detalles..:
Ya podemos ver que la BBDD está replicada y se sincronizará de forma automática.
Recuerda, si tienes consultas sobre esta u otra cuestión relacionada con tus servidores en Clouding, no dudes en escribir a soporte@clouding.io ¡Estamos a tu lado para lo que necesites!