En tant qu’administrateur de base de données, j’ai récemment rencontré un nouveau défi avec SQL Server – la gestion de la réplication de fusion et de la synchronisation pour un groupe d’utilisateurs. Bien que cette technologie ne soit pas nouvelle, c’était la première fois au cours de mes 16 années de carrière que j’ai dû y faire face. Dans cet article, je partagerai le processus automatisé que j’ai développé pour gagner du temps et des efforts dans la gestion des synchronisations de réplication de fusion.

Chacun de mes utilisateurs a SQL Express installé et utilise un package logiciel personnalisé sur la base de données. Ils travaillent à distance et synchronisent leur serveur local avec un éditeur principal ou se connectent via un VPN lorsqu’ils sont à distance. Avec plus de 20 utilisateurs effectuant des modifications de données, leur rappeler manuellement de synchroniser était une corvée. Pour simplifier ce processus, j’ai créé une solution automatisée utilisant des procédures stockées, des tâches et des tables.

Étape 1 : Créer les tables

La première étape consiste à créer les tables nécessaires. La table “ServerList” stockera les noms de serveurs et les adresses e-mail associées. Cette table sera utilisée pour les notifications par e-mail et pour vérifier quels serveurs sont actuellement en ligne. Les noms de serveurs seront remplis par une tâche, tandis que les adresses e-mail devront être ajoutées manuellement. De plus, créez la table “LastSynced” pour suivre l’heure de la dernière synchronisation, le nom de la base de données, l’état de la synchronisation et les détails récapitulatifs.

CREATE TABLE [dbo].[ServerList](
    [Server] [varchar](128) NOT NULL,
    [Server_email] [varchar](50) NULL,
    CONSTRAINT [PK_ServerList] PRIMARY KEY CLUSTERED (
        [Server] ASC
    ) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON,
        FILLFACTOR = 99
    ) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[LastSynced](
    [subscriber_server] [sysname] NULL,
    [db_name] [sysname] NOT NULL,
    [last_sync_date] [datetime] NULL,
    [last_sync_status] [int] NULL,
    [last_sync_summary] [sysname] NULL
) ON [PRIMARY]

Étape 2 : Créer la procédure stockée “CheckLastSync”

La prochaine étape consiste à créer la procédure stockée “CheckLastSync”. Cette procédure utilise xp_cmdshell pour rechercher le réseau des serveurs où SQL Server est installé. Elle crée ensuite une table dans tempdb sur chaque serveur pour stocker les informations de synchronisation. La procédure interroge les données synchronisées pour chaque base de données (à l’exclusion des bases de données système) et les insère dans une table temporaire. Enfin, elle insère les informations de synchronisation dans la table distante créée dans tempdb et dans la table principale centralisée de la base de données.

CREATE PROCEDURE [dbo].[CheckLastSync]
AS
DECLARE @ServerName sysname

DECLARE synccursor CURSOR FORWARD_ONLY FOR
SELECT [Server] FROM [dbo].[ServerList]

OPEN synccursor
FETCH NEXT FROM synccursor INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Créer une table dans tempdb sur chaque serveur distant
    EXEC ('IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE type = ''U'' AND NAME = ''lastsynced'')
        DROP TABLE tempdb.dbo.lastsynced;

        CREATE TABLE tempdb.dbo.lastsynced (
            subscriber_server sysname,
            db_name sysname,
            last_sync_date date,
            last_sync_status INT,
            last_sync_summary varchar(500)
        )')

    -- Interroger les données synchronisées dans la table distante
    EXEC ('EXEC [' + @ServerName + '].master.dbo.sp_msforeachdb
        ''USE [?];
        IF ''''?'''' NOT IN (''''tempdb'''',''''msdb'''',''''master'''',''''distribution'''',''''model'''',''''Northwind5'''',''''pubs'''')
        BEGIN
            INSERT INTO tempdb.dbo.lastsynced
            SELECT [subscriber_server], [db_name], [last_sync_date], [last_sync_status], [last_sync_summary]
            FROM [sysmergesubscriptions]
        END''')

    -- Insérer les détails de la dernière synchronisation dans la table principale de la base de données
    EXEC ('INSERT INTO [DBA_Reports].[dbo].[LastSynced]
        SELECT * FROM [' + @ServerName + '].tempdb.dbo.lastsynced')

    FETCH NEXT FROM synccursor INTO @ServerName
END

CLOSE synccursor
DEALLOCATE synccursor

Étape 3 : Créer la procédure stockée “sendmail_Sync_Needed”

La procédure stockée “sendmail_Sync_Needed” lit la table “LastSynced” et compare la dernière date de synchronisation à il y a 25 jours. Si une base de données n’a pas été synchronisée depuis au moins 25 jours, un e-mail est envoyé au propriétaire du serveur pour lui rappeler de synchroniser. Cette procédure est programmée pour s’exécuter quotidiennement afin d’envoyer des rappels jusqu’à ce que la synchronisation soit terminée.

CREATE PROCEDURE [dbo].[sendmail_Sync_Needed]
AS
DECLARE @ServerName varchar(100)
DECLARE synccursor CURSOR FORWARD_ONLY FOR
SELECT [subscriber_server] FROM [DBA_Reports].[dbo].[LastSynced]
GROUP BY [subscriber_server], last_sync_status
HAVING last_sync_status = 2 AND MAX(last_sync_date) <= GETDATE() - 25

OPEN synccursor
FETCH NEXT FROM synccursor INTO @ServerName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @email varchar(25)
    SELECT email = Server_email
    FROM dbo.ServerList
    WHERE [Server] = @ServerName

    DECLARE @body varchar (1000)
    SET @body = '<html><body><font face=verdana size=2>
        <p><strong>' + @ServerName + '<br><br>
        Vous n''avez pas synchronisé votre base de données depuis au moins 25 jours. Veuillez synchroniser dès que possible.</strong><br></p>
        <p><i>Merci,</i></p>
        <p><strong>
        <AJOUTEZ VOS INFORMATIONS (nom) ICI></strong><br>
        <AJOUTEZ VOS INFORMATIONS (titre) ICI><br>
        <AJOUTEZ VOS INFORMATIONS (entreprise) ICI><br>
        <AJOUTEZ VOS INFORMATIONS (téléphone/e-mail) ICI></p>
        </body></html>'

    -- Envoyer un rappel par e-mail au propriétaire du serveur
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = '<AJOUTEZ VOS INFORMATIONS ICI>',
        @recipients = email,
        @copy_recipients = '<AJOUTEZ VOS INFORMATIONS ICI>',
        @subject = 'ENVOI AUTOMATIQUE : Veuillez synchroniser votre base de données',
        @body = @body,
        @body_format = 'HTML'

    FETCH NEXT FROM synccursor INTO @ServerName
END

CLOSE synccursor
DEALLOCATE synccursor

Étape 4 : Créer la tâche ‘DBA_Reports – Historique de synchronisation et rappel des ordinateurs portables’

La dernière étape consiste à créer une tâche qui exécutera les procédures stockées et enverra des notifications par e-mail. Cette tâche s’exécutera quotidiennement et collectera les informations de synchronisation, enverra un e-mail à l’administrateur de base de données avec les données collectées et enverra des rappels par e-mail aux utilisateurs qui n’ont pas synchronisé depuis au moins 25 jours.

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Non catégorisé (Local)]' AND category_class=1)
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'TÂCHE', @type=N'LOCAL', @name=N'[Non catégorisé (Local)]'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBA_Reports - Historique de synchronisation et rappel des ordinateurs portables', @enabled=1, @notify_level_eventlog=2, @notify_level_email=2, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Rend compte de la dernière fois que les ordinateurs portables ont été synchronisés et de leur état', @category_name=N'[Non catégorisé (Local)]', @owner_login_name=N'sa', @notify_email_operator_name=N'', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Appeler la procédure stockée', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC CheckLastSync', @database_name=N'DBA_Reports', @output_file_name=N'', @flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Envoyer l'e-mail', @step_id=2, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'SET nocount ON;
    DECLARE @Subject VARCHAR (100)
    SET @Subject=''DBA_Reports - Historique de synchronisation des ordinateurs portables''

    DECLARE @Count AS INT
    SELECT @Count=COUNT(*)
    FROM [DBA_Reports].[dbo].[LastSynced]
    WHERE last_sync_status = 2

    PRINT @Count

    IF @Count > 0
    BEGIN
        DECLARE @tableHTML NVARCHAR(MAX) ;
        SET @tableHTML = N''<table border="1">'' + N''<tr>'' + N''<th>Serveur</th>'' + N''<th>Base de données</th>'' + N''<th>Dernière date de synchronisation</th>'' + N''<th>Récapitulatif de la synchronisation</th>'' + N''</tr>'' +
            CAST (
                (
                    SELECT td=[subscriber_server],''''
                        ,td=[db_name],''''
                        ,td=convert(varchar(10), [last_sync_date],110),''''
                        ,td=[last_sync_summary],''''
                    FROM [LastSynced]
                    GROUP BY [subscriber_server], [db_name],[last_sync_date], [last_sync_status], [last_sync_summary]
                    HAVING last_sync_status = 2
                    ORDER BY last_sync_date ASC
                    FOR XML PATH(''tr''), TYPE
                ) AS NVARCHAR(MAX)
            ) + N''</table>'' ;

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = '''',
            @recipients = '''',
            @subject = @Subject,
            @body = @tableHTML,
            @body_format = ''HTML'' ;
    END', @database_name=N'DBA_Reports', @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Envoyer un e-mail d'avertissement aux utilisateurs qui n'ont pas synchronisé depuis 25 jours', @step_id=3, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC sendemail_Sync_Needed', @database_name=N'DBA_Reports', @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Quotidien', @enabled=1, @freq_type=8, @freq_interval=62, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20110603, @active_end_date=99991231, @active_start_time=133000, @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION
GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Une fois la tâche créée, planifiez son exécution une fois par jour. Cela garantira que les utilisateurs reçoivent des rappels quotidiens jusqu’à ce qu’ils synchronisent leurs bases de données.

Avec ce processus automatisé en place, la gestion des synchronisations de réplication de fusion devient beaucoup plus facile et permet de gagner un temps précieux et des efforts. En automatisant les rappels et en suivant l’historique de synchronisation, vous pouvez vous assurer que tous les utilisateurs restent à jour et éviter tout problème ou réinstallation.

N’oubliez pas de personnaliser les scripts avec vos propres informations de serveur et de contact. N’hésitez pas à modifier le processus pour qu’il corresponde à vos besoins et à votre environnement spécifiques.

Bonne synchronisation !