Olá, pessoal! Tudo certo?

No artigo 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é aí 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.

O 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).

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?”

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, confira o artigo “Fazendo um backup database On-Premises no Azure“. Portanto, no servidor da Amazon eu precisaria apenas fazer o restore sem precisar 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 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 que 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 porque 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!