Introduction :

L’architecture de la réplication SQL Server repose sur les différentes techniques qui permettent de copier et de distribuer les données des différents objets d’une base de données vers une autre.

SQL Server propose trois types de réplication :

• La réplication transactionnelle (Transactional Replication).
• La réplication de capture instantanée (Snapshot Replication).
• La réplication par fusion (Merge Replication).

La réplication transactionnelle est généralement utilisée dans un environnement serveur à serveur pour répondre aux besoins suivants :

• Redondance des données sur un ou plusieurs serveurs qui se trouvent sur le même site ou sur des sites différents.
• Utilisation du serveur abonné comme un serveur de reporting et de lecture seule.
• Consolidation des données sur un serveur central en provenance de plusieurs sites distants.
• Besoin d’avoir un serveur abonné mis à jour en quasi temps réel avec le serveur de publication.
• La volumétrie et l’activité de la base est très importante, dans ce cas la réplication par capture instantanée peut prendre longtemps et verrouille à chaque fois l’accès aux données sur la base de publication.
• Réplication vers un serveur non-SQL Server, comme Oracle ou Sybase.

Principe de fonctionnement :

• La réplication transactionnelle commence en général par une capture instantanée des objets et des données de la base à publier.
• Une fois la première capture effectuée, toutes les modifications effectuées sur les schémas et les données de la base de publication sont transmis au fur et à mesure (presque en temps réel) aux différents abonnés.
• Les changements et les transactions produites sur le serveur de publication sont appliqués dans le même ordre sur les abonnés, donc la cohérence des données est garantie par le mécanisme de la réplication.

Schéma d’architecture d’une réplication transactionnelle :

 URL : https://technet.microsoft.com/fr-fr/library/ms151706(v=sql.105).aspx

La réplication transactionnelle est effectuée par différents agents :

• Agent de capture instantanée (snapshot)
• Agent de lecture du journal de transaction
• Agent de distribution.

L’agent de capture instantanée (snapshot) :

• Génère un snapshot (ensemble de fichiers) contenant les schémas et les données des objets de la base à publier (tables, vues, fonctions, procédures, …).
• Les fichiers générés sont déposés dans un répertoire partagé (répertoire snapshot) accessible par tous les serveurs qui participent à la réplication (serveur de publication, serveur de distribution et les différents abonnés).
• Enregistre les informations de synchronisation dans la base de distribution qui se trouve sur le serveur de distribution ou sur le serveur de publication si celui-ci joue les deux rôles à la fois.
• Bref, c’est lui qui génère la copie des données qui sera utilisée pour initialiser la base à mettre en réplication sur les différents abonnés.
• Le job de l’agent est planifié en « one shot » car il est utilisé uniquement pour l’initialisation ou pour la réinitialisation des données.

L’agent de lecture du journal :

• Surveille le journal des transactions de la base publiée (l’activité de la base sur le serveur de publication), et copie les transactions qui doivent être répliquées depuis le journal des transactions dans la base de distribution.
• La base de distribution joue donc le rôle d’une file d’attente (stockage et distribution des transactions).
• Le job de l’agent de lecture du journal tourne sans arrêt sur le serveur de publication. Si le job est arrêté, la réplication des données s’arrête.

L’agent de distribution :

• Se charge de la copie des données de la capture instantanée (le snapshot d’initialisation) et les différentes transactions stockées au fur et à mesure dans la base de distribution vers les abonnés.
• Le job de cet agent tourne sans arrêt comme l’agent de lecture du journal. Si le job est arrêté, la réplication s’arrête aussi.
• Le job de l’agent peut être configuré sur le serveur de distribution (publication/distribution si le serveur de publication joue les deux rôles) ou sur l’abonné selon le mode de configuration de la réplication (push ou pull).

Mise en place d’une réplication transactionnelle :
Pré-requis :

• Le module de réplication doit être installé sur tous les serveurs qui participent à la réplication (publication, distribution et les abonnés). SQL Server dispose d’une procédure qui permet de le vérifier facilement (sp_MS_replication_installed).
• Deux comptes de service (Active Directory) : un compte pour le service SQL Server Agent et l’autre pour le fonctionnement de la réplication (il est déconseillé d’utiliser le même compte).
• Création d’un partage pour la capture instantanée (snapshot).
• Droits de lecture/écriture sur le partage et les droits NFS sur le dossier à partager pour le compte de service SQL Agent.
• Le rôle serveur « sysadmin » pour le compte de service.

Création de la réplication avec T-SQL :
Déclaration des variables :

DECLARE @distributor as sysname
DECLARE @distributionDB as sysname
DECLARE @publisher as sysname
DECLARE @publication as sysname
DECLARE @publicationDB as sysname
DECLARE @data_folder as  sysname
DECLARE @log_folder as sysname
DECLARE @replicationPath as varchar(255)
DECLARE @loginJob as sysname
DECLARE @password as sysname

Initialisation des variables :

SET @publisher = 'PUBLICATIONSRV'                -- le serveur de publication
SET @distributor = 'PUBLICATIONSRV'              -- Le serveur de distribution (c'est le même que le serveur de publication dans notre cas)
SET @distributionDB = 'distribution'             -- Le nom de la base de distribution
SET @publication = 'REPDB_PUBLICATIONSRV_TO_ABONNEESRV' -- le nom de la publication
SET @publicationDB = 'DBREP'          -- la base à mettre en réplication (base source)
SET @data_folder = 'D:\MSSQL\Data\'   -- répertoire data pour la base distribution
SET @log_folder = 'E:\MSSQL\Data\'    -- répertoire log pour la base distribution
SET @replicationPath = '\\PUBLICATIONSRV\REPLICATION_TRANS'   -- Partage de réplication (à utiliser pour les snapshots de la base)
SET @loginJob = 'DOMAINNAME\svc_ReplDB'               -- Compte AD à utiliser pour les jobs de réplication
SET @password = ''         -- mot de passe du compte AD de réplication

Installation et configuration de la distribution :

-- installation du serveur de distribution
use master
exec sp_adddistributor @distributor = @distributor, @password = N''
GO
-- Création de la base de distribution :
exec sp_adddistributiondb @database = @distributionDB , @data_folder = @data_folder,
@log_folder = @log_folder, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1;
 GO
-- Configuration de la base de distribution
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
	create table UIProperties(id int) ;
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
	EXEC sp_updateextendedproperty N'SnapshotFolder', @replicationPath, 'user', dbo, 'table', 'UIProperties' ;
else
	EXEC sp_addextendedproperty N'SnapshotFolder', @replicationPath, 'user', dbo, 'table', 'UIProperties';

Configuration de la publication :

-- configuration de la publication
exec sp_adddistpublisher @publisher = @publisher, @distribution_db = @distributionDB ,
@security_mode = 1, @working_directory = @replicationPath, @trusted = N'false',
@thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER' ;
GO
use [DBREP]  -- @publicationDB
exec sp_replicationdboption
@dbname = @publicationDB,
@optname = N'publish',
@value = N'true';
exec [DBREP].[sys].[sp_addlogreader_agent]
@job_login = @loginJob,
@job_password = @password,
@publisher_security_mode = 1,
@job_name = null;
GO
-- ajout de la publication transactionnelle
exec sp_addpublication @publication = @publication,
@description = N'Transactional publication of database @publicationDB',
@sync_method = N'concurrent',
@retention = 0,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active',
@independent_agent = N'true',
@immediate_sync = N'true',
@allow_sync_tran = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'false',
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false';
GO
-- Configuration du snapshot
exec sp_addpublication_snapshot
@publication = @publication,
@frequency_type = 1,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = @loginJob ,
@job_password = @password,
@publisher_security_mode = 1
;
GO

Configuration des objets de la base à répliquer :

• Ajout de toutes les tables utilisateurs de la base. Vous pouvez utiliser le script suivant pour générer le script de toutes les tables utilisateurs de la base. Il suffit ensuite d’exécuter le script généré pour ajouter toutes les tables.

-- générer le script pour tous les articles "TABLES" :
USE [REPDB] -- @publicationDB
GO
DECLARE @publication as sysname
SET @publication=@publication
SELECT
' exec sp_addarticle
@publication = '''+@publication+''',
@article = '''+name+''',
@source_owner ='''+schema_name(schema_id)+''',
@source_object ='''+name+''',
@type = ''logbased'',
@description = null,
@creation_script = null,
@pre_creation_cmd = ''drop'',
@schema_option = 0x000000004D235FDF,
@identityrangemanagementoption = ''manual'',
@destination_table ='''+name+''',
@destination_owner = '''+schema_name(schema_id)+''',
@vertical_partition = ''false'',
@ins_cmd = ''CALL sp_MSins_'+schema_name(schema_id)+name+''',
@del_cmd = ''CALL sp_MSdel_'+schema_name(schema_id)+name+''',
@upd_cmd = ''SCALL sp_MSupd_'+schema_name(schema_id)+name+'''
;
'
FROM sys.all_objects
where type ='U' and is_ms_shipped=0

Remarque : Toutes les tables à répliquer doivent posséder une clé primaire (la réplication transactionnelle ne fonctionne pas avec les heap tables).

• Ajout de toutes les vues utilisateurs. Même principe que le script pour les tables :

-- Générer le script pour toutes les vues
USE [REPDB] -- @publicationDB
GO
DECLARE @publication as sysname
SET @publication=@publication
SELECT
' exec sp_addarticle
@publication = '''+@publication+''',
@article = '''+name+''',
@source_owner ='''+schema_name(schema_id)+''',
@source_object ='''+name+''',
@type = ''view schema only'',
@description = null,
@creation_script = null,
@pre_creation_cmd = ''drop'',
@schema_option = 0x0000000008000001,
@identityrangemanagementoption = ''manual'',
@destination_table ='''+name+''',
@destination_owner = '''+schema_name(schema_id)+''',
@vertical_partition = ''false'',
@ins_cmd = ''CALL sp_MSins_'+schema_name(schema_id)+name+''',
@del_cmd = ''CALL sp_MSdel_'+schema_name(schema_id)+name+''',
@upd_cmd = ''SCALL sp_MSupd_'+schema_name(schema_id)+name+'''
;
'
FROM sys.all_objects
where type ='V' and is_ms_shipped=0

• Ajout de toutes les procédures utilisateurs :

-- Génrer le script pour tous les articles procédures :
use [REPDB]  -- @publicationDB
GO
DECLARE @replication as sysname
SET @replication = @replication
SELECT '
exec sp_addarticle
@publication = '''+@replication+''',
@article = '''+name+''',
@source_owner = '''+schema_name(schema_id)+''',
@source_object = '''+name+''',
@type = ''proc schema only'',
@description = null,
@creation_script = null,
@pre_creation_cmd = ''drop'',
@schema_option = 0x0000000048000001,
@destination_table = '''+name+''',
@destination_owner = '''+schema_name(schema_id)+''';'
FROM sys.all_objects
where type ='P'
and is_ms_shipped = 0
;

• Ajout de toutes les fonctions :

-- Génrer le script pour tous les articles fonctions :
use [REPDB]  -- @publicationDB
GO
DECLARE @replication as sysname
SET @replication = @replication
SELECT '
exec sp_addarticle
@publication = '''+@replication+''',
@article = '''+name+''',
@source_owner = '''+schema_name(schema_id)+''',
@source_object = '''+name+''',
@type = ''func schema only'',
@description = null,
@creation_script = null,
@pre_creation_cmd = ''drop'',
@schema_option = 0x0000000048000001,
@destination_table = '''+name+''',
@destination_owner = '''+schema_name(schema_id)+''';'
FROM sys.all_objects
where type ='FN'
and is_ms_shipped = 0
;

Création de la subscription (l’abonné) en mode « pull » :

• Déclaration de la subscription au niveau du serveur de publication (publisher) :

-- Déclaration des variables
DECLARE @destinationDB as sysname
-- DECLARE @publication as sysname
-- DECLARE @subscriber as sysname
SET @destinationDB = 'REPDB'  -- on garde le même nom de la base
SET @subscriber = 'ABONNEESRV'
-- SET @publication = 'REPDB_PUBLICATIONSRV_TO_ABONNEESRV'
use [REPDB] -- @publicationDB
exec sp_addsubscription @publication = @publication, @subscriber = @subscriber,
@destination_db = @destinationDB, @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO

• Création de la subscription sur le serveur abonné (subscriber) : Script à exécuter sur l’abonné.

use [REPDB] --@destinationDB
exec sp_addpullsubscription @publisher = @publisher , @publication =@publication,
@publisher_db = @publicationDB , @independent_agent = N'True', @subscription_type = N'pull',
@description = N'', @update_mode = N'read only', @immediate_sync = 1
exec sp_addpullsubscription_agent
@publisher = @publisher,
@publisher_db = @publicationDB,
@publication = @publication,
@distributor = @distrubitor,
@distributor_security_mode = 1,
@distributor_login = N'',
@distributor_password = null,
@enabled_for_syncmgr = N'False',
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20170105,
@active_end_date = 99991231,
@alt_snapshot_folder = N'',
@working_directory = N'',
@use_ftp = N'False',
@job_login = @loginJob,
@job_password = @password,
@publication_type = 0
GO

La mise en place dans notre réplication est terminée.
==> La suite dans le prochain article.