Blog
Réplication – Initialisation d’un abonné par sauvegarde/restauration
Dans cet article, nous allons créer une publication transactionnelle et lancer l’initialisation d’un abonné par sauvegarde/restauration à partir d’un autre abonné utilisé comme référence. Cette méthode est appropriée dans les cas suivants :
- N bases abonnées, strictement identiques au niveau structure
- Personnalisations importantes des bases abonnées (ex : index)
Attention : cette méthode implique de suspendre toutes les synchronisations au même moment pendant toute la durée de l’initialisation d’un abonné. L’objectif étant de partir d’une base abonnée susceptible de contenir des personnalisations importantes (ex : index, vues indexées) et d’industrialiser la création/initialisation d’autres abonnés à partir de ce modèle.
NB : Dans cet exemple, nous partons du principe que le distributeur est déjà configuré. Dans tous les scripts, nous avons recours à des paramètres de template pour faciliter le remplacement de valeurs telles que le nom de la base publiée, etc.
1) Création d’une BDD à répliquer
En premier lieu, sur l’instance A, nous allons créer une BDD lambda avec une table contenant un jeu de données générées aléatoirement :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
CREATE DATABASE [<PublishedDatabase, SYSNAME, value>] GO USE [<PublishedDatabase, SYSNAME, value>] GO CREATE TABLE [dbo].[RandomData]( [RowId] [int] IDENTITY(1,1) NOT NULL, [SomeInt] [int] NULL, [SomeBit] [bit] NULL, [SomeVarchar] [varchar](10) NULL, [SomeDateTime] [datetime] NULL, [SomeNumeric] [numeric](16, 2) NULL, [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_RandomData1_CreateDate] DEFAULT (getdate()), [UpdateDate] [datetime] NOT NULL CONSTRAINT [DF_RandomData1_UpdateDate] DEFAULT (getdate()), CONSTRAINT [PK_RandomData] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO dbo.RandomData (SomeInt, SomeBit, SomeVarchar, SomeDateTime, SomeNumeric) SELECT TOP 10000 ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, CASE WHEN DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END [SomeBit], CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar], DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime], (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric] FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 GO |
2) Activer la publication sur la base à publier
Activer la publication de la BDD créée précédemment, comme suit :
|
1 2 3 4 5 6 7 8 |
USE [<PublishedDatabase, SYSNAME, value>] GO EXEC sp_replicationdboption @dbname = N'<PublishedDatabase, SYSNAME, value>', @optname = N'publish', @value = N'true' GO |
3) Créer une publication transactionnelle
Créer une réplication transactionnelle sur la BDD lambda, en spécifiant le nom de la publication.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
USE [<PublishedDatabase, SYSNAME, value>] GO EXEC sp_addpublication @publication = N'<PublicationName, SYSNAME, value>', @description = N'Transactional publication of database ''<PublicationName, SYSNAME, value>'' from Publisher ''<PublicationInstance, SYSNAME, value>''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', @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 |
4) Création de l’agent de capture de la publication
Création d’un agent de snapshot sur la publication créée à l’étape d’avant :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE [<PublishedDatabase, SYSNAME, value>] GO EXEC sp_addpublication_snapshot @publication = N'<PublicationName, SYSNAME, value>', @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 = null, @job_password = null, @publisher_security_mode = 1 GO |
5) Ajout d’un article
Ajout d’un article dans la publication, en l’occurrence la table créée précédemment :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE [<PublishedDatabase, SYSNAME, value>] GO EXEC sp_addarticle @publication = N'<PublicationName, SYSNAME, value>', @article = N'RandomData', @source_owner = N'dbo', @source_object = N'RandomData', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'RandomData1', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboRandomData', @del_cmd = N'CALL sp_MSdel_dboRandomData', @upd_cmd = N'SCALL sp_MSupd_dboRandomData' GO |
6) Création de la base de données, futur abonné
On se connecte sur l’instance qui hébergera une copie de la base de données publiée qui deviendra abonnée par la suite.
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO CREATE DATABASE [<SubscribtionDatabase1, SYSNAME, value>] CONTAINMENT = NONE ON PRIMARY ( NAME = N'<SubscribtionDatabase1_DataName, SYSNAME, value>', FILENAME = N'<SubscribtionDatabase1_DataPath, VARCHAR(500), value>' , SIZE = 1024MB, MAXSIZE = 5120MB, FILEGROWTH = 1024MB ) LOG ON ( NAME = N'<SubscribtionDatabase1_LogName, SYSNAME, value>', FILENAME = N'<SubscribtionDatabase1_LogPath, VARCHAR(500), value>' , SIZE = 1024MB, MAXSIZE = 5120MB , FILEGROWTH = 1024MB) GO |
7) Création de l’abonné 1
Une fois la base de données secondaire créée, nous passons à la création de l’abonnement avec les options par défaut.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
USE [<PublishedDatabase, SYSNAME, value>] GO exec sp_addsubscription @publication = N'<PublicationName, SYSNAME, value>', @subscriber = N'<SubscribtionInstance1, SYSNAME, value>', @destination_db = N'<SubscribtionDatabase1, SYSNAME, value>', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 exec sp_addpushsubscription_agent @publication = N'<PublicationName, SYSNAME, value>', @subscriber = N'<SubscribtionInstance1, SYSNAME, value>', @subscriber_db = N'<SubscribtionDatabase1, SYSNAME, value>', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @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 = 20151002, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' GO |
8) Initialisation de l’abonné 1
|
1 2 3 4 5 6 7 8 9 |
USE [<PublishedDatabase, SYSNAME, value>] EXEC sp_reinitsubscription @subscriber = '<SubscribtionInstance1, SYSNAME, value>', @destination_db = '<SubscribtionDatabase1, SYSNAME, value>', @publication = N'<PublicationName, SYSNAME, value>'; USE [<PublishedDatabase, SYSNAME, value>] EXEC sp_startpublication_snapshot @publication = N'<PublicationName, SYSNAME, value>'; GO |
8) Vérification de la synchronisation de l’abonné 1
Juste pour la forme, on vérifie que la base abonnée a bien été synchronisée en comparant le nombre de lignes présente de chaque côté, respectivement sur la base de données publiée et la abse de données abonnée :
|
1 2 3 4 5 |
USE [<PublishedDatabase, SYSNAME, value>] EXEC sp_spaceused 'dbo.RandomData' USE [<SubscribtionInstance1, SYSNAME, value>] EXEC sp_spaceused 'dbo.RandomData' |
8) Arrêt de l’agent de lecture
On se connecte sur l’instance qui héberge la distribution et on stoppe purement et simplement l’agent de lecture, qui correspond à un job dont le nom est récupéré dynamiquement.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @job_name SYSNAME USE distribution; SELECT TOP 1 @job_name = [name] FROM [distribution].[dbo].[MSlogreader_agents] WHERE publisher_db = '<PublishedDatabase, SYSNAME, value>' USE msdb; EXEC dbo.sp_stop_job @job_name = @job_name ; |
9) Vérification de la synchronisation de l’abonné 1
A intervalle régulier, on vérifie que la liste des transactions en attente de réplication, ce qui correspond à la colonne Undelivcmdsindistdb. Il faut qu’elle soit à 0 pour tous les abonnés de la publication concernée (1 abonné par ligne). Dans l’exemple suivant, il n’y a qu’un abonné mais dans l’absolu, il pourrait y en avoir plusieurs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
USE distribution GO SELECT ( CASE WHEN Mdh.Runstatus = '1' THEN 'Start' WHEN Mdh.Runstatus = '2' THEN 'Succeed' WHEN Mdh.Runstatus = '3' THEN 'InProgress' WHEN Mdh.Runstatus = '4' THEN 'Idle' WHEN Mdh.Runstatus = '5' THEN 'Retry' WHEN Mdh.Runstatus = '6' THEN 'Fail' END ) [Run status], Mda.Subscriber_db [Subscriber db], Mda.Publication [Pub name], Mdh.[Time] [Lastsynchronized], Und.Undelivcmdsindistdb [Undistcom], Mdh.Comments [Comments], Subscriber = sub.name, DATEDIFF(SECOND, Mdh.[Time], GETDATE()) Lastrefresh FROM DBO.Msdistribution_agents Mda LEFT JOIN DBO.Msdistribution_history Mdh ON Mdh.Agent_id = Mda.Id INNER JOIN master.sys.servers sub ON Mda.subscriber_id = sub.server_id INNER JOIN (SELECT S.AGENT_ID, Maxagentvalue.[Time], SUM(CASE WHEN Xact_seqno > Maxagentvalue.Maxseq THEN 1 ELSE 0 END) AS Undelivcmdsindistdb FROM DBO.Msrepl_commands T (NOLOCK) JOIN DBO.Mssubscriptions AS S (NOLOCK) ON ( T.ARTICLE_ID = S.ARTICLE_ID AND T.PUBLISHER_DATABASE_ID = S.PUBLISHER_DATABASE_ID ) JOIN (SELECT Hist.AGENT_ID, MAX(Hist.[TIME]) AS [Time], H.Maxseq FROM DBO.Msdistribution_history Hist ( NOLOCK) JOIN (SELECT Agent_id, ISNULL(MAX(Xact_seqno), 0x0) AS Maxseq FROM DBO.Msdistribution_history ( NOLOCK) GROUP BY Agent_id) AS H ON ( Hist.AGENT_ID = H.Agent_id AND H.Maxseq = Hist.XACT_SEQNO ) GROUP BY Hist.AGENT_ID, H.Maxseq) AS Maxagentvalue ON Maxagentvalue.Agent_id = S.AGENT_ID GROUP BY S.AGENT_ID, Maxagentvalue.[Time]) Und ON Mda.Id = Und.Agent_id AND Und.[Time] = Mdh.[Time] WHERE Mda.Publication = '<PublicationName, SYSNAME, value>' --AND sub.name = '<SubscribtionInstance1, SYSNAME, value>' --AND Mda.Subscriber_db = '<SubscribtionDatabase1, SYSNAME, value>' ORDER BY Mdh.[Time] DESC |
10) Sauvegarde de l’abonné 1
Il n’y a plus aucune transaction en attente de réplication, tout abonnés confondu s’il y en a plusieurs. Nous sauvegardons l’abonné pour la création et l’initialisation d’une autre base abonnée.
|
1 2 3 |
USE [master]; BACKUP DATABASE [<SubscribtionDatabase1, SYSNAME, value>] TO DISK = N'<BackupFullPath, VARCHAR(500), value>' WITH NOFORMAT, INIT, NAME = N'<SubscribtionDatabase1, SYSNAME, value>-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
11) Restauration sur une future base abonné 2
Une fois la sauvegarde effectuée, nous procédons à la création/restauration d’une futur base de données abonnée, en spécifiant l’option KEEP_REPLICATION. Elle permet de conserver les objets tels que les PS liées à la réplication lors d’une restauration, typiquement :
- sp_MSins_dboRandomData
- sp_MSdel_dboRandomData
- sp_MSupd_dboRandomData
|
1 2 3 4 5 6 |
USE [master] RESTORE DATABASE [<SubscribtionDatabase2, SYSNAME, value>] FROM DISK = N'<BackupFullPath, VARCHAR(500), value>' WITH FILE = 1, MOVE N'<SubscribtionDatabase1_DataName, SYSNAME, value>' TO N'<SubscribtionDatabase2_DataPath, VARCHAR(500), value>', MOVE N'<SubscribtionDatabase1_LogName, SYSNAME, value>' TO N'<SubscribtionDatabase2_LogPath, VARCHAR(500), value>', NOUNLOAD, REPLACE, KEEP_REPLICATION, STATS = 5 GO |
12) Création de l’abonné 2
Une fois la base de données 3 restaurée, nous passons à la création de l’abonnement en spécifiant que nous ne souhaitons pas de synchronisation avec l’option @sync_type = ‘none’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
USE [<PublishedDatabase, SYSNAME, value>] EXEC sp_addsubscription @publication = N'<PublicationName, SYSNAME, value>', @subscriber = N'<SubscribtionInstance2, SYSNAME, value>', @destination_db = N'<SubscribtionDatabase2, SYSNAME, value>', @subscription_type = N'Push', @sync_type = N'none', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 EXEC sp_addpushsubscription_agent @publication = N'<PublicationName, SYSNAME, value>', @subscriber = N'<SubscribtionInstance2, SYSNAME, value>', @subscriber_db = N'<SubscribtionDatabase2, SYSNAME, value>', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @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 = 20151002, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor' GO |
Les étapes 11 et 12 seront renouvellées autant de fois que d’abonnés supplémentaires à créer. Pour rappel, cela implique de suspendre la réplication pour la totalité des abonnés déjà présents. Si la réplication a été réactivée entre temps et qu’on souhaite ajouter d’autres abonnés par la suite, dans ce cas, il faudra repartir de l’étape 8 puis répéter les étapes 11 & 12 à hauteur du nombres d’abonnés à ajouter.
13) Injection de nouvelles données sur la base de données publiée
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [<PublishedDatabase, SYSNAME, value>] INSERT INTO dbo.RandomData (SomeInt, SomeBit, SomeVarchar, SomeDateTime, SomeNumeric) SELECT TOP 10000 ABS(CHECKSUM(NEWID()))%2500+1 AS SomeId, CASE WHEN DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END [SomeBit], CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar], DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime], (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric] FROM Master.dbo.SysColumns t1, Master.dbo.SysColumns t2 |
14) Démarrage de l’agent de lecture
On se connecte sur l’instance qui héberge la distribution et on démarre l’agent de lecture stoppé précédemment.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @job_name SYSNAME USE distribution; SELECT TOP 1 @job_name = [name] FROM [distribution].[dbo].[MSlogreader_agents] WHERE publisher_db = '<PublishedDatabase, SYSNAME, value>' USE msdb; EXEC dbo.sp_start_job @job_name = @job_name; |
15) Vérification de la synchronisation des abonnés 1 & 2
A intervalle régulier, on vérifie que toutes les bases de données abonnées ont bien été synchronisées en comparant le nombre de lignes présentes de chaque côté :
|
1 2 3 4 5 6 7 8 |
USE [<PublishedDatabase, SYSNAME, value>] EXEC sp_spaceused 'dbo.RandomData' USE [<SubscribtionDatabase1, SYSNAME, value>] EXEC sp_spaceused 'dbo.RandomData' USE [<SubscribtionDatabase2, SYSNAME, value>] EXEC sp_spaceused 'dbo.RandomData' |
Auteur
- Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
Derniers articles
- 2019.12.02BI & Big DataEtude des contextes en DAX (4/4) : Transition de contexte
- 2019.11.25BI & Big DataEtude des contextes en DAX (3/4) : Contexte de requête
- 2019.11.17BI & Big DataEtude des contextes en DAX (2/4) : Contexte de filtre
- 2019.11.11BI & Big DataEtude des contextes en DAX (1/4) : Contexte de ligne