Replique os backups de sua VM SQL Server do Azure para Amazon

Olá pessoal, tudo certo?

No post de hoje vou compartilhar com vocês um caso que atendi em um de nossos clientes.

O nosso cliente tem uma VM com o SQL Server no Azure e gostaria de ter um site de DR (disaster recovery) na Amazon. Até ai tudo bem, você poderia falar “Tiago isso é fácil, o SQL Server te dá tantas possibilidades” ou até mesmo “Poxa ele pode usar a replicação no próprio Azure”. Concordo com tudo isso, mas cliente é cliente e a solicitação era “Quero uma cópia do meu SQL Server na Amazon”. Então a replicação no próprio Azure está descartada.

Pensei em utilizar o AlwaysOn AG, porém, a licença que ele utiliza é a edição Web que não tem suporte. Então AG descartado.

Passamos para próxima possibilidade que é configurar uma Replicação. Só que o custo (esforço) para configurar seria grande, mas seria uma alternativa válida também.

Próximo cenário seria configurar um Database Mirror, porém como os servidores estão em clouds diferentes que não estão integradas, teríamos que configurar o mirror utilizando certificado, mesmo assim não seria possível seguir com esse cenário, pois a edição Web libera você para configurar o servidor apenas como testemunha (witness).

Então chegamos no cenário que melhor atendeu a situação que é configurar um Log Shipping. Contudo, teríamos um esforço grande pelo mesmo motivo do mirror… “clouds diferentes não integradas…”, mas aí que entra uma solução de contorno que é criar um Log Shipping manual, que nada mais é que pegar os backups de um servidor e restaurar no outro.

Mas Tiago como que você copia os backups de um servidor para o outro? Utilizou alguma ferramenta? FTP?

Resposta: Não.

Poderíamos utilizar ferramentas para copiar os backups ou transferir via FTP, só que o nosso serviço foi facilitado pelo cliente já que ele utiliza o serviço de backup direto em um Blob Storage, se você quiser conhecer esse serviço veja o post (Fazendo um backup database On-Premises no Azure). Portanto, no servidor da Amazon eu precisaria apenas fazer o restore sem precisa de copiar os arquivos.

Como que você recuperou os caminhos dos backups?

Novamente aqui o trabalho foi facilitado pelo cliente que fez um form .Net que executa a query abaixo no MSDB e retorna as informações dos backups da instância principal que está no Azure e faz a carga dessas informações na Amazon, ou seja, um backup é realizado no Azure, ele é gravado na MSDB, de 30 em 30 segundos a aplicação valida se tem um backup novo, se tiver ele registra a informação na Amazon. Também daria para fazer um script powershell para recuperar as informações no Blob Storage, porém foi mais simples utilizar a app que o cliente fez.

SELECT A.database_name,
       A.backup_start_date,
       A.backup_finish_date,
       b.physical_device_name AS Diretorio,
       A.type,
       ROW_NUMBER() OVER (PARTITION BY A.database_name ORDER BY A.backup_start_date DESC) AS Ranking
FROM msdb..backupset A
    JOIN msdb..backupmediafamily b
        ON b.media_set_id = A.media_set_id
GROUP BY A.database_name,
         A.backup_start_date,
         b.physical_device_name,
         A.backup_finish_date,
         A.type;

E para fazer o restore como que você fez o controle do que já havia sido restaurado?

Para controlar a sequência lógica dos backups que precisam ser restaurados e até mesmo para controlar a cadeia de log, eu criei uma tabela que chamo de “”, que extrai informações da tabela de histórico de backups que foram realizados no Azure.

CREATE TABLE [dbo].[Controle_BKP](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Nm_Database] [varchar](50) NULL,
	[Dt_Backup] [datetime] NULL,
	[Ds_Diretorio] [varchar](800) NULL,
	[Tipo_BKP] [char](1) NULL,
	[Fl_Restore] [bit] NULL DEFAULT ((0))
) ON [PRIMARY]

Para extrair as informações da tabela de histórico para tabela de controle, fiz uma procedure que pega o último backup full, o último diferencial (se utilizar) depois do full e os últimos backups de log depois do backup full ou do backup diferencial.

/****** Object:  StoredProcedure [dbo].[stpCarga_Historico_Backup]    Script Date: 16/07/2018 20:39:09 ******/

Create PROCEDURE [dbo].[stpCarga_Historico_Backup] @dbname sysname
AS
BEGIN

---BKP FULL
	INSERT INTO Controle_BKP
    (
        Nm_Database,
        Dt_Backup,
        Ds_Diretorio,
        Tipo_BKP,
        Fl_Restore
    )
    SELECT A.database_name,
           A.backup_start_date,
           A.Diretorio,
           A.type,
           0
	FROM
    (
        SELECT A.database_name,
               A.backup_start_date,
               A.diretorio ,
               A.type,
               ROW_NUMBER() OVER (PARTITION BY A.database_name ORDER BY A.backup_start_date DESC) AS Ranking
        FROM historico_backup A
        WHERE A.type = ('D')
             AND A.database_name = @dbname
        GROUP BY A.database_name,
                 A.backup_start_date,
				 A.Diretorio,
                 A.type
    ) A
        LEFT JOIN dbo.Controle_BKP cbd
          ON cbd.Ds_Diretorio = A.Diretorio
    WHERE A.Ranking = 1
       AND cbd.Ds_Diretorio IS NULL;

-- BKP DIF

   INSERT INTO Controle_BKP
    (
        Nm_Database,
        Dt_Backup,
        Ds_Diretorio,
        Tipo_BKP,
        Fl_Restore
    )
    SELECT A.database_name,
           A.backup_start_date,
           Diretorio,
           A.type,
           0
    FROM
    (
        SELECT A.database_name,
               A.backup_start_date,
               A.Diretorio,
               A.type,
               ROW_NUMBER() OVER (PARTITION BY A.database_name ORDER BY A.backup_start_date DESC) AS Ranking
		FROM historico_backup A
        WHERE A.type = ('I')
              AND A.database_name = @dbname
			     AND a.backup_start_date >=
          (
              SELECT MAX(a.backup_finish_date)
              FROM historico_backup A
              WHERE a.type = ('D')
                    AND a.database_name = @dbname
              GROUP BY a.database_name,
                       a.type)
        GROUP BY A.database_name,
                 A.backup_start_date,
				 A.Diretorio,
                 A.type
    ) A
        LEFT JOIN dbo.Controle_BKP cb
            ON cb.Ds_Diretorio = A.Diretorio
    WHERE A.Ranking = 1
          AND cb.Ds_Diretorio IS NULL;

 -- BKP LOG

    INSERT INTO Controle_BKP
    (
        Nm_Database,
        Dt_Backup,
        Ds_Diretorio,
        Tipo_BKP,
        Fl_Restore
    )
    SELECT a.database_name,
           a.backup_start_date,
           A.Diretorio,
           a.type,
           0
     FROM historico_backup A
        LEFT JOIN dbo.Controle_BKP cb
            ON cb.Nm_Database = a.database_name
               AND cb.Dt_Backup = a.backup_start_date
    WHERE a.type = ('L')
          AND a.database_name = @dbname
          AND a.backup_start_date >=
          (
              SELECT MAX(a.backup_finish_date)
              FROM historico_backup A
              WHERE a.type = ('D')
                    AND a.database_name = @dbname
              GROUP BY a.database_name,
                       a.type
          )
          AND cb.Dt_Backup IS NULL
    ORDER BY a.backup_start_date;

end

E agora, as tabelas de controle estão criadas. Como fazer o restore?

O script abaixo é uma procedure que faz o restore. Para facilitar a criação dos bancos eu criei as mesmas estruturas de diretórios do servidor de origem.

Ao final de cada restore, a rotina faz um update na tabela de controle informando que aquele arquivo foi restaurado com sucesso.

Create PROCEDURE [dbo].[stpRestauraBancos]
    @dbname sysname

AS
BEGIN

    DECLARE @caminho VARCHAR(MAX),
            @mdf VARCHAR(MAX),
            @mdf_name sysname,
            @ldf VARCHAR(MAX),
            @ldf_name sysname;

    SELECT @mdf = physical_name,
           @mdf_name = mf.name
    FROM master.sys.master_files mf
        INNER JOIN master.sys.databases db
            ON mf.database_id = db.database_id
    WHERE mf.type = 0 
          AND db.name = @dbname;
 
   SELECT @ldf = physical_name,
           @ldf_name = mf.name
    FROM master.sys.master_files mf
        INNER JOIN master.sys.databases db
            ON mf.database_id = db.database_id
    WHERE mf.type = 1 -- 1 = Log 
          AND db.name = @dbname;

        --Restaurar backup full.
        SET @caminho =
        (
            SELECT Ds_Diretorio
            FROM restore_log.dbo.Controle_BKP
            WHERE Tipo_BKP = 'D'
                  AND Fl_Restore = 0
                  AND Nm_Database = @dbname
        );

        IF @caminho IS NOT NULL     
        BEGIN
            RESTORE DATABASE @dbname
            FROM URL = @caminho
            WITH FILE = 1,
                 MOVE @mdf_name
                 TO @mdf,
                 MOVE @ldf_name
                 TO @ldf,
                 NORECOVERY,
                 NOUNLOAD,
				 STATS = 10,
                 REPLACE;

            IF @@ERROR = 0
            BEGIN
                UPDATE restore_log.dbo.Controle_BKP
                SET Fl_Restore = 1
                WHERE Ds_Diretorio = @caminho;
            END;

            PRINT '>>>Backup Full restaurado: ' + @caminho;

        END;

        --Restarurar backup diferencial.

		DECLARE cursor_BackupDiferencial CURSOR FOR
        SELECT Ds_Diretorio
        FROM restore_log.dbo.Controle_BKP
        WHERE Tipo_BKP = 'I'
              AND Fl_Restore = 0
			  AND Dt_Backup <= (SELECT MIN(Dt_Backup) FROM restore_log.dbo.Controle_BKP WHERE Tipo_BKP = 'I'
																					  AND Nm_Database = @dbname)
              AND Nm_Database = @dbname;

        OPEN cursor_BackupDiferencial;

        FETCH NEXT FROM cursor_BackupDiferencial
        INTO @caminho;

        WHILE @@FETCH_STATUS = 0
        BEGIN

            RESTORE DATABASE @dbname
            FROM URL = @caminho
            WITH FILE = 1,
                 NORECOVERY,
				 NOUNLOAD,
                 STATS = 10,
                 REPLACE;

            IF @@ERROR = 0
            BEGIN
                UPDATE restore_log.dbo.Controle_BKP
                SET Fl_Restore = 1
                WHERE Ds_Diretorio = @caminho;
            END;

            PRINT '>>>Backup Diferencial restaurado: ' + @caminho;

            FETCH NEXT FROM cursor_BackupDiferencial
            INTO @caminho;
        END;

        CLOSE cursor_BackupDiferencial;
        DEALLOCATE cursor_BackupDiferencial;
		end
		
        --Restaurar backup de logs.
        DECLARE cursor_backup_files CURSOR FOR
        SELECT Ds_Diretorio
        FROM restore_log.dbo.Controle_BKP
        WHERE Tipo_BKP = 'L'
              AND Fl_Restore = 0
              AND Nm_Database = @dbname
			  ORDER BY Dt_Backup;

        OPEN cursor_backup_files;

        FETCH NEXT FROM cursor_backup_files
        INTO @caminho;

        WHILE @@FETCH_STATUS = 0
        BEGIN

            RESTORE LOG @dbname
            FROM URL = @caminho
            WITH FILE = 1,
                 NORECOVERY,
				 STATS = 10;

            IF @@ERROR = 0
            BEGIN
                UPDATE restore_log.dbo.Controle_BKP
                SET Fl_Restore = 1
                WHERE Ds_Diretorio = @caminho;
            END;

            PRINT '>>>Backup de Log restaurado: ' + @caminho;

            FETCH NEXT FROM cursor_backup_files
            INTO @caminho;

        END

        CLOSE cursor_backup_files;
        DEALLOCATE cursor_backup_files;

END

Após a criação da procedure, eu criei um job que executa a cada 30 minutos. Isso ficou alinhado com o cliente que assumiu que poderia perder até 30 minutos de dados.

E assim o cliente ficou feliz por que agora tem uma réplica do seu banco de dados SQL Server do Azure na Amazon.

Bom pessoal por hoje é isso.

Um grande abraço,

Tiago Neves