Replicació de bases de dades SQL Server

En un entorn en producció, ens podem trobar amb la necessitat de replicar la nostra base de dades de SQL Server per alliberar una mica de càrrega i poder executar altres processos que necessitem per consultar dades.

En aquest article expliquem com crear i configurar la replicació transaccional de la base de dades en local i partim d'un servidor en producció amb SQL Server 2016 Standard instal·lat i Visio 2016.
Abans de fer cap canvi és recomanable fer un Snapshot.

Es resumeix en els apartats següents:

1. Configuració de permisos
   1. Crear usuari/s
   2. Configurar els permisos d'usuari
      1. Directori on es troba la base de dades que s'ha de replicar
      2. Directori on es troba la base de dades de replicació
      3. Directori on es troben les dades i els registres de la base de dades de replicació
      4. Directori on es troba el recurs compartit per a la instantània de publicació (Snapshot)
      5. Permisos d'accés a la base de dades que s'ha de replicar
      6. Permisos d'accés a la base de dades de distribució
      7. Permisos d'accés a la base de dades de replicació
      8. Permisos d'accés a la llista d'accés de la publicació (PAL)
2. Instal·lar SQL Server Replication
3. Configuració de la distribució
4. Crear i configurar la publicació per a la replicació transaccional
5. Habilitar permisos per a l'agent a la llista d'accés de la publicació (PAL)
6. Crear la subscripció a la publicació transaccional
7. Establir permisos a la base de dades per al subscriptor
8. Veure l'estat de sincronització de la subscripció

1. Configuració de permisos

1. Crear usuari/s

En aquesta secció crearem un únic usuari local amb permisos Full per poder crear la instantània de replicació i el farem servir per a tot el procés, tot i que es poden crear diferents usuaris per delimitar els permisos.

Pas 1/3: Des de panel de control > Herramientas administrativas > Administración de equipo
(Control Panel\All Control Panel Items\Administrative Tools\Computer Management)

Pas 2/3: Despleguem les opcions de "local Users and Groups" i fem clic amb el botó dret damunt de la carpeta "Users" per crear un usuari nou

Pas 3/3: A l'exemple fem servir el nom d'usuari replica, però li podem donar el nom que vulguem. 

A continuació se'ns obrirà una finestra nova on podem emplenar les dades del nou usuari, com ara el nom i la contrasenya. En el nostre exemple el nom de l'usuari és "replica", emplenem els camps de contrasenya i deixem seleccionades les caselles de selecció tal com apareixen a la imatge següent, perquè no sigui possible canviar la contrasenya i perquè no caduqui mai:

Premem el botó "Crear" per crear l'usuari i ja podem tancar la finestra d'administració de l'ordinador.

2. Configurar els permisos d'usuari:

Un cop tinguem l'usuari creat, passem a configurar els permisos. Alguns permisos s'han de  configurar abans i d'altres s'hauran de configurar més endavant quan estiguin creades la base de dades de replicació, la de distribució i la publicació.

A continuació, per veure-ho més clar, llistarem tots els permisos que hem de donar a l'usuari que hem creat. També ens pot servir de referència si volem delimitar els permisos a diferents usuaris:

1. Directori on es troba la base de dades que s'ha de replicar (Vegeu el punt 3).

2. Directori on es troba la base de dades de replicació (Vegeu el punt 3).

3. Directori on es troben les dades i els registres de la base de dades de replicació:

Normalment es desen al mateix directori, però es pot donar el cas que estiguin en ubicacions diferents. En aquest exemple són al mateix directori que es mostra a la imatge següent, i cal afegir permisos d'edició a l'usuari "replica".

Pas1/5: Per assignar permisos: Fem clic amb el botó dret situant el cursor damunt del nom de la carpeta "DATA" i seleccionem "Propiedades":

Pas 2/5 - A la pestanya de seguretat premem el botó "Edit...":

Pas 3/5: Per afegir l'usuari premem el botó "Add...":

Pas 4/5: Se'ns obrirà una finestra nova per seleccionar l'usuari: Escrivim el nom de l'usuari (1) i el verifiquem prement el botó "Check Names" (2), i finalment acceptem els canvis prement el botó OK (3):

Pas 5/5: Un cop assignat l'usuari al pas anterior, es tanca la finestra i ens quedem a la mateixa finestra del "Pas 2". Ens assegurem que estigui seleccionat l'usuari replica i marquem totes les caselles excepte "Full control". Per acabar,  premem el botó "OK" per acceptar els canvis.

4. Directori on es troba el recurs compartit per a la instantània de publicació (Snapshot):

Afegim els permisos totals a l'usuari "replica" per a la carpeta on es crearà el Snapshot que es configura en crear l'editor per a la publicació:

Aquest pas el podem fer més endavant, ja que el directori que crearem manualment a continuació, si no existeix, es crearà en el moment que es configuri la distribució, mostrant el camí on es crea, o bé pot ser un altre camí si cal. En qualsevol cas, caldrà donar permisos a l'usuari que hem creat anteriorment en aquest directori.

• Creem la carpeta amb el nom de repldata al camí: C:\Archivos de programa\Microsoft SQL Server\MSSQL.X\MSSQL\Data
  
  
   
• Assignar permisos: Fem clic amb el botó dret situant el cursor damunt del nom de la carpeta que hem creat anteriorment i accedim a "Propiedades":
  
  Pas 1/4: A la pestanya de seguretat premem el botó "Edit...":
  
  
  Pas 2/4: Per afegir l'usuari premem el botó "Add...":
  
  Pas 3/4: Se'ns obrirà una finestra nova per seleccionar l'usuari: Escrivim el nom de l'usuari (1) i el verifiquem (2), i finalment acceptem els canvis prement el botó OK (3):
  
  
  Pas 4/4: Un cop assignat l'usuari al pas anterior, es tanca la finestra i ens quedem a la mateixa finestra del "Pas 2". Ens assegurem que estigui seleccionat l'usuari replica, marquem la casella "Full control" i podrem observar que es marquen tots els permisos. Per acabar,  premem el botó "OK" per acceptar els canvis:
  

5. Permisos d'accés a la base de dades que s'ha de replicar:

Per afegir els permisos d'accés a la base de dades que s'ha de replicar amb l'usuari que utilitzarem, "replica", seguim aquests 3 passos que veiem a continuació:

Pas 1/3: Obrim l'aplicació Microsoft SQL Server Management Studio:

Pas 2/3: Fem clic amb el botó dret damunt de "Login(1), s'obrirà una finestra nova on podem cercar l'usuari, escrivim el nom d'usuari que hàgim creat al camp "Login name", que en aquest exemple és "replica", i premem el botó de cerca "Search" (2).

Després se'ns obre una finestra petita en què podem prémer el botó "Check Names" (4) per verificar que l'usuari existeix i està creat, i finalment acceptem els canvis prement el botó "OK" (5) i es tancarà la finestra de cerca afegint l'usuari corresponent a la finestra anterior:

Pas 3/3: A continuació, a la mateixa finestra que tenim oberta, seleccionem al menú lateral "User Mappinng" (1), marquem la casella de la base de dades que tenim en producció (2) i a continuació marquem la casella del quadre inferior "db_owner" (3):

6. Permisos d'accés a la base de dades de distribució:

S'ha de fer tan bon punt estigui configurada la distribució (Apartat 3). Consisteix a afegir permisos a l'usuari "replica", que ja ens apareix afegit. Per afegir els permisos, vegeu l'apartat 7 per obtenir més detalls sobre com afegir permisos.

7. Permisos d'accés a la base de dades de replicació:

S'ha de fer tan bon punt estigui configurat l'editor (Apartat 4). Per afegir els permisos, vegeu l'apartat 7 per obtenir més detalls sobre com afegir permisos.

8. Permisos d'accés a la llista d'accés de la publicació (PAL):

Aquest pas el veurem més endavant quan es crea la publicació. S'explica com afegir els permisos a "Apartat 5".

2. Instal·lar SQL Server Replication

Executem el fitxer d'instal·lació de SQL Server com a administrador per afegir aquest nou complement "Replication". S'obrirà una finestra nova per a la instal·lació i, seleccionant al menú lateral "Installation", trobem l'opció per instal·lar SQL o complements:


Al pas següent ens permet instal·lar complements en una instància existent i apareixeran les detectades. Per fer-ho seleccionem l'opció "Añadir complementos a una instancia existente de SQL Server 2016" i en aquest cas només tenim una instància que ja apareix seleccionada:

Si SQL Server Replication està instal·lat, la casella està marcada tal com es mostra a la imatge següent; en cas contrari, no està instal·lat. Marquem la casella per fer la instal·lació i seguim els passos fins a completar-la (No requereix reinici):

3. Configuració de la distribució

Obrim l'aplicació Microsoft SQL Server Management Studio i ens connectem a la instància on tenim la base de dades que s'ha de replicar.
El fet d'haver instal·lat al pas anterior "SQL Server Replication" ens permetrà configurar la distribució. Per fer-ho, ens situem damunt de "Replication" i premem el botó dret del ratolí per desplegar les opcions, i seleccionem "Configure Distribution.." tal com es mostra a la imatge següent:

A continuació, s'obrirà una finestra nova per a la configuració amb l'assistent. Premem el botó "Siguiente" o, el que és el mateix, "Next":

Al pas següent, per defecte ja està seleccionada la primera opció, que és just la que ens interessa.
La primera opció consisteix a crear la distribució a la instància actual i la segona opció ens permet seleccionar una altra instància que ja s'hagi configurat com a distribuïdor. En el nostre cas el distribuïdor és local, així que deixem seleccionada l'opció per defecte i premem el botó "Siguiente":

Ens mostrarà un advertiment que cal tenir en compte, ja que l'agent del servidor SQL ha d'estar en marxa per a la replicació i per fer la instantània. Podem triar si volem posar-lo en marxa automàticament en arrencar el servidor si detecta que està aturat, o bé que no s'iniciï automàticament:

Al pas següent, ens mostrarà el camí per defecte on desarà una còpia de la imatge de la base de dades actual i on replicarà els canvis en temps real. Podem canviar el camí si ens interessa, tenint en compte que cal donar-li permisos totals perquè pugui crear la instantània, tal com s'explica a la secció següent.
Finalment premem "Siguiente":

A la imatge següent, pots canviar el nom de la base de dades de distribució si vols. També es pot definir el camí on es crearà la base de dades de replicació i es desaran els registres, o deixar-lo per defecte. Si es canvien els camins cal tenir en compte els permisos que s'expliquen a la secció següent:

Al pas següent cal marcar els editors de replicació SQL que accedeixen al distribuïdor i prémer següent:

A continuació tenim dues opcions: per defecte deixem només marcada la primera casella per configurar ara la distribució, o bé podem marcar també l'altra casella perquè ens generi un script i ho puguem configurar més endavant. Després premem següent i s'iniciarà la configuració:

Un cop acabi ens mostrarà un resum de tots els punts i premem finalitzar.

Nota: Si no hem marcat l'opció de generar un script per configurar-ho més endavant, és normal que aparegui un error a l'últim pas perquè el servei d'agent SQL no té permisos, cosa que ens falta fer i que s'explica a la secció següent.

4. Crear i configurar la publicació per a la replicació transaccional

El primer pas és configurar l'editor i, per fer-ho, creem una publicació nova:

Se'ns obrirà un assistent per crear-la en una finestra nova, premem següent:

A continuació, seleccionem la base de dades que volem i premem següent:

Aquí és important destacar que seleccionem "Transactional publication" per transmetre les transaccions un cop estigui creada la instantània, ja que la primera opció és per programar quan fer-ho. I premem següent:

Ara, arribats a aquest punt, podem seleccionar els objectes i les taules que vulguem replicar:

Un cop seleccionats els objectes, taules.. al pas anterior, premem següent:

Ens dona el detall de la selecció de la imatge anterior. Premem següent de nou.

A continuació, ens permet definir filtres per aplicar als articles seleccionats. Si premem "Add" podem seleccionar una taula en qüestió i després aplicar-hi un filtre. En aquest cas ho deixem predeterminat, i premem següent:

Al pas següent, per defecte no hi ha cap selecció. En aquest cas seleccionarem la primera opció per crear la instantània immediatament, ja que la segona opció és per crear-la més endavant configurant les planificacions per definir una franja horària:

Al punt següent, hem d'especificar el compte que es farà servir com a agent de les instantànies, que ha de tenir permisos totals. En canvi, per al segon agent "Log Reader Agent" podem seleccionar un altre usuari, ja que no necessita permisos totals, sinó només permisos de lectura. Però en aquest exemple fem servir el mateix usuari, per això no cal desmarcar la casella "use the security settings from the SnapShot Agent". En qualsevol cas,  premem el botó "Security Settings.." que correspongui:

I ens obrirà una segona finestra per poder indicar quin tipus d'usuari; en aquest cas es farà servir un  compte d'usuari creat a Windows i premem el botó "OK":

En tancar-se la segona finestra, tornem a la finestra anterior i ens apareixerà el camp emplenat i el botó "Next" disponible per anar al pas següent:

L'últim pas ja ens permet crear la publicació, tal com veiem a la imatge següent, marcant la casella "Create the publication", o bé marcar la segona opció si us interessa crear-la més endavant mitjançant un script. I premem següent:

A continuació ens faltarà triar un nom per a la publicació, podem fer servir el mateix de la base de dades que tenim i afegir-hi _Pub, per exemple: DatabaseEjemplo_Pub

I premem el botó de finalitzar:

Un cop completat el procés, es pot comprovar que la publicació s'ha creat:

Per obtenir més detalls, fes-hi clic amb el botó dret i al desplegable fes clic a l'opció "View SnapShot Agent Status":

I a continuació, si s'ha creat, apareix un missatge similar:

Si premem el botó del monitor, ens obrirà una finestra nova on podem consultar més detalls del procés. Per continuar, passem a la secció següent, on s'explica com crear el subscriptor.

5. Habilitar permisos per a l'agent a la llista d'accés de la publicació (PAL):

En aquest apartat només hem d'afegir l'usuari amb permisos d'edició, però en aquest exemple s'afegeix l'usuari replica amb permisos totals:

I a continuació el podem afegir en aquest apartat; per fer-ho, seleccionem "Publication Access List" i després el botó d'afegir:

Després de prémer el botó d'afegir ens apareixerà la finestra següent; un cop seleccionat l'usuari, premem OK:

Després tornem a la finestra anterior, on també hem d'acceptar els canvis prement el botó OK:

6. Crear la subscripció a la publicació transaccional

Per crear la subscripció a la publicació transaccional, fem clic amb el botó dret damunt de "Local Subscriptions": 

Se'ns obrirà un assistent nou i seguirem els passos:

Ens mostrarà la PAL (Llista d'accés de la publicació) que ja vam crear en una altra secció anterior. La seleccionem i premem següent:

En aquest exemple acceptem l'opció seleccionada per defecte per executar tots els agents al distribuïdor:

Al pas següent, perquè ens aparegui la casella seleccionada, en aquest cas la instància "DBSQL", hem d'afegir primer un subscriptor nou "Add SQL Server Subscriber.." i després connectar-nos a la instància:

Per fer-ho ens apareixerà la finestra de connexió a la instància del subscriptor, premem connectar i ja està:

Tornem a la finestra anterior, que ens mostrarà la instància on és el subscriptor, i procedim a crear la base de dades seleccionant al desplegable l'opció "New database" i premem següent "Next":

A continuació donem un nom a la nova base de dades de replicació i premem OK:

Ara, a la finestra següent, hem d'afegir l'usuari que fem servir per a la connexió al subscriptor que estem creant; prement damunt de la icona dels punts se'ns obrirà la finestra de configuració:

A la finestra de configuració, configurarem la connexió amb un compte d'usuari; en aquest cas fem servir un usuari local del servidor que ja vam crear al principi d'aquesta guia. Les dades que cal emplenar són les que es marquen a la imatge següent, compte d'usuari i contrasenya, i premem OK:

Automàticament després de prémer OK, tornem a la finestra anterior, que ens permetrà prémer el botó de finalitzar directament o bé prémer següent. Com que deixem la resta de configuracions per defecte, podem prémer finalitzar i començarà a crear la subscripció. Un cop completada, apareixeran els resultats de la manera següent, i només faltarà prémer el botó "Close" si tot és correcte:

Per veure el procés des del monitor de replicació segueix aquests passos:

7. Establir permisos a la base de dades per al subscriptor

Els permisos de base de dades al subscriptor es poden establir ara que està creada la base de dades de replicació. Per fer-ho, despleguem les opcions de Security i Logins, on podem afegir permisos a un usuari ja afegit prement el botó dret damunt de l'usuari en qüestió (En aquest cas l'usuari és DBSQL\replica) i seleccionem "Properties"; en fer-hi clic amb el botó esquerre se'ns obrirà la finestra de propietats de l'usuari SQL:

Marquem les tres bases de dades i, per seleccionar el rol "db_owner", haurem de seleccionar una a una cada base de dades i marcar la casella "db_owner" en cadascuna; en seleccionar la base de dades de distribució també cal marcar la casella replmonitor.
Finalment, per desar els canvis premem OK:

8. Veure l'estat de sincronització de la subscripció

Per veure l'estat de sincronització de la subscripció, fem clic amb el botó dret damunt de la carpeta "Replication" i seleccionem amb el botó esquerre "Launch Replication Monitor":

I se'ns obrirà una finestra nova, on podem prémer el botó dret damunt del treball en curs i veure'n més detalls..:

Ja podem veure que la base de dades està replicada i se sincronitzarà automàticament.