Piecemeal restore com filestream

    Parte do trabalho de consultoria não é só trabalhar para definir as melhores soluções arquiteturais, mas trabalhar dentro dos limites do cliente (ex.: dinheiro, tempo, necessidade e efetividade) e uma vez definido o caminho a ser seguido, pensar em questões delicadas como, desempenho, recuperação de desastres, entre outros.

    O problema: em um cliente vamos utilizar o SQL Server filestream para armazenamento de imagens e espera-se receber para um único evento em torno de 16 milhões de imagens, com tamanho médio de 230 KB. Fazendo o cálculo nós temos aproximadamente 3,5 TB de imagens, fora os dados relacionados.

    Então considerando um eventual desastre com o banco de dados, não queremos esperar X horas com o sistema off-line até o restore finalizar, então foi proposto dividir os arquivos em regiões diferentes, permitindo a recuperação parcial do banco de dados. Para fins didáticos eu montei o script abaixo, que mostra como podemos fazer um piecemeal restore com filestream no SQL Server, e coloco nesse post para seguir como guia.

    Observação importante: não vou entrar no mérito de onde deve ser armazenado as imagens, não estou preocupado com alinhamento dos índices particionados, partition switching ou retirada dos dados desse banco, evitando sua obesidade. Claro que são questões importantes e que devem ser pensadas, mas isso faço junto com o cliente e colocar tudo aqui ia deixar esse artigo muito grande.

Estrutura do banco de dados

    O banco de dados possui o filegroup (FG) primário só com os objetos definidos na model e um filegroup para armazenar o restante dos dados do banco, que não sejam imagens. Até o momento não houve necessidade de particionar outros objetos ou dividir esse arquivo de dados. Além disso temos 5 FGs, uma para cada região que será definida, responsável por armazenar as imagens.

Script 01 – Definição do banco de dados

USE master
GO

exec sp_configure 'filestream access level', 2
RECONFIGURE

-- 2) Cria banco de dados com Filestream e tabela que utiliza recurso
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'NimbusFS')
  DROP DATABASE NimbusFS
GO

CREATE DATABASE NimbusFS ON PRIMARY
  ( NAME = NimbusFS_Primary, 
    FILENAME = N'D:\Temp\SQLData\NimbusFS.mdf',
    SIZE = 15MB,
    MAXSIZE = 50MB, 
    FILEGROWTH = 15MB),
FILEGROUP FGNimbusFSData DEFAULT
  ( NAME = NimbusFS_Data, 
    FILENAME = N'D:\Temp\SQLData\NimbusFS_Data.mdf',
    SIZE = 100MB,
    MAXSIZE = 10GB, 
    FILEGROWTH = 100MB) ,
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG01, 
    FILENAME = N'D:\Temp\SQLData\FSG01'),
FILEGROUP FileStreamGroup2 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG02, 
    FILENAME = N'D:\Temp\SQLData\FSG02'),
FILEGROUP FileStreamGroup3 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG03, 
    FILENAME = N'D:\Temp\SQLData\FSG03'),
FILEGROUP FileStreamGroup4 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG04, 
    FILENAME = N'D:\Temp\SQLData\FSG04'),
FILEGROUP FileStreamGroup5 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG05, 
    FILENAME = N'D:\Temp\SQLData\FSG05')
LOG ON 
  ( NAME = 'NimbusFS_log', 
    FILENAME = N'D:\Temp\SQLLog\NimbusFS.ldf',
    SIZE = 50MB, 
    MAXSIZE = 1GB, 
    FILEGROWTH = 10MB);
GO

USE NimbusFS
GO

ALTER DATABASE NimbusFS
SET RECOVERY FULL
GO

    A tabela região é definida pensando nas 5 regiões do Brasil, mas notem que ela é um termo abstrato e, caso seja necessário, podemos definir nossas “pseudo-regiões”, separando São Paulo ou outros estados com maior volume de dados, ou como o negócio ditar.
    Uma função de particionamento para as regiões é utilizada em 2 esquemas. O primeiro não separa os dados em filegroups diferentes, já o segundo separa os arquivos em diferentes FGs que contém filestream. Depois a tabela Documento é criada, seguindo as definições do filestream e particionando pelo atributo IDRegiao, seguindo os esquemas previamente definidos.
    Talvez você deva estar se perguntando porque não utilizamos o tempo no particionamento, o que é mais comum. Porém neste problema o tempo não é significativo, pois potencialmente um conjunto muito grande de imagens estarão dentro de uma mesma partição, que seria a ativa para o negócio, o que não ajudaria a diminuir o tempo de recuperação.

Script 02 – Definição do banco de dados

IF (OBJECT_ID('dbo.Regiao') IS NOT NULL)

       DROP TABLE Regiao

GO

-- Filegroup default

CREATE TABLE dbo.Regiao

(ID INT IDENTITY NOT NULL PRIMARY KEY

 , Nome VARCHAR(100))

go

INSERT INTO dbo.Regiao (Nome) values ('Norte'), ('Sul'), ('Nordeste'), ('Sudeste'), ('Centro-Oeste')

go

IF (OBJECT_ID('dbo.Documento') IS NOT NULL)

       DROP TABLE Documento

GO

CREATE PARTITION FUNCTION MyPartFunction (INT) AS RANGE LEFT FOR VALUES (1,2,3,4);

CREATE PARTITION SCHEME MyPartScheme AS PARTITION MyPartFunction ALL TO (FGNimbusFSData);

CREATE PARTITION SCHEME MyPartSchemeFS AS PARTITION MyPartFunction TO (FileStreamGroup1, FileStreamGroup2, FileStreamGroup3, FileStreamGroup4, FileStreamGroup5);

CREATE TABLE dbo.Documento

(ID INT IDENTITY NOT NULL

 , ChaveDoc UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() ROWGUIDCOL UNIQUE ON FGNimbusFSData

 , IDFK INT NOT NULL

 , IDRegiao INT NOT NULL

 , Doc VARBINARY(MAX) FILESTREAM

) ON MyPartScheme(IDRegiao)

FILESTREAM_ON MyPartSchemeFS;

go

ALTER TABLE Documento

ADD CONSTRAINT FK_Documento_Regiao_IDRegiao

FOREIGN KEY (IDRegiao)

REFERENCES dbo.Regiao (ID)

go

Inserindo dados e realizando backups

    Uma vez com a estrutura criada, os próximos passos são auto explicativos. Vou inserindo registros no banco de dados, em diferentes regiões, seguidas de um backup full, inserts, backup do filegroup e por fim, um backup do fim do log, que coloquei lá para representar um cenário onde perdemos os dados mas ainda consigo um “tail do log”’.
    Enquanto você estiver executando os passos desse script, recomendo dar uma olhada nos diretórios do filestream. Assim que um registro da região é inserido, o arquivo aparece no filegroup da partição, mostrando claramente o “roteamento” dos dados.

Script 03 – Dados e backups

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (111, 1, CAST('Doc 111 - Regiao 1' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (222, 2, CAST('Doc 222 - Regiao 2' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (333, 3, CAST('Doc 333 - Regiao 3' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (444, 4, CAST('Doc 444 - Regiao 4' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (555, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));

BACKUP DATABASE NimbusFS

TO DISK = 'D:\Temp\SQLBackup\NimbusFSFull.bak'

WITH INIT

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (112, 1, CAST('Doc 112 - Regiao 1' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (223, 2, CAST('Doc 222 - Regiao 2' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (334, 3, CAST('Doc 333 - Regiao 3' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (445, 4, CAST('Doc 444 - Regiao 4' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (556, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));

GO

SELECT * FROM Documento

GO

BACKUP DATABASE NimbusFS

FILEGROUP = 'PRIMARY'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_Primary.bak'

WITH INIT

go

BACKUP DATABASE NimbusFS

FILEGROUP = 'FGNimbusFSData'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSData.bak'

WITH INIT

go

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (113, 1, CAST('Doc 113 - Regiao 1' AS varbinary(MAX)));

BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup1'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG1.bak'

WITH INIT

GO

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (224, 2, CAST('Doc 222 - Regiao 2' AS varbinary(MAX)));

BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup2'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG2.bak'

WITH INIT

GO

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (335, 3, CAST('Doc 333 - Regiao 3' AS varbinary(MAX)));

BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup3'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG3.bak'

WITH INIT

GO

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (446, 4, CAST('Doc 444 - Regiao 4' AS varbinary(MAX)));

BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup4'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG4.bak'

WITH INIT

GO

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (557, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));

BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup5'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG5.bak'

WITH INIT

GO

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (558, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));

SELECT * FROM Documento

GO

-- "Tail do log"

BACKUP LOG NimbusFS

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

WITH INIT;

Recuperando de um desastre

    Supondo que neste momento você perdeu seu banco de dados e tem que partir para o restore. Você não gostaria de esperar todo o restore terminar para indicar ao negócio que todos podem voltar ao trabalho, mas sim permitir que as pessoas voltem a trabalhar aos poucos, minimizando o impacto para as regiões mais críticas. Certo?
    Então se você fez seu dever de casa certinho, pode fazer um piecemeal restore...

Script 04 – Iniciando o piecemeal restore

USE master

GO

-- Simula o desastre... Ou um DBA brilhando... :-)

DROP DATABASE NimbusFS;

GO

-- Início do piecemeal restore

RESTORE DATABASE NimbusFS

FILEGROUP = 'PRIMARY'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_Primary.bak'

WITH PARTIAL, NORECOVERY

RESTORE DATABASE NimbusFS

FILEGROUP = 'FGNimbusFSData'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSData.bak'

WITH NORECOVERY

RESTORE LOG NimbusFSFROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

    Temos restaurado o filegroup primário e o FG com os dados (sem as imagens), colocando-os em um estado consistente. A partir deste momento você já tem acesso ao banco e alguma funcionalidade pode ser utilizada.
    Consultas às tabelas de sistemas, tabelas que não usam o filestream e até as colunas da tabela que não são filestream, podem ser manipuladas. Porém quando você tentar acessar um dado que está em filegroup ainda não restaurado, vai ver a mensagem 670, conforme abaixo.

Script 05 – Utilizando parcialmente o banco

SELECT * FROM NimbusFS.sys.objects WHERE TYPE = 'U';

SELECT * FROM NimbusFS.dbo.Regiao;

SELECT * FROM NimbusFS.dbo.Documento;

/*

Msg 670, Level 16, State 1, Line 177

Large object (LOB) data for table "dbo.Documento" resides on an offline filegroup ("FileStreamGroup1") that cannot be accessed.

*/

SELECT id, ChaveDoc, IDRegiao FROM NimbusFS.dbo.Documento;

    A partir deste momento sua escolha é decidir qual região deve voltar primeiro (em geral isso está definido no plano de DR que você escreveu, mas enfim...). O exemplo abaixo deixa online a região 2 e faz consulta filtrando pela região, que funciona perfeitamente, mas não para as outras regiões.

Script 06 – Restore da região 02

-- Regiao 02

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup2'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG2.bak'

WITH NORECOVERY

RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO

-- Erro

SELECT * FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHERE IDRegiao = 2;

    Continua-se o procedimento de recuperação até que todos os filegroups estejam online. Vale notar também que a última inserção, que estava no backup do transaction log, também é recuperada com sucesso.

Script 07 – Restore das outras regiões

-- Regiao 01

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup1'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG1.bak'

WITH NORECOVERY

RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO

-- Erro

SELECT * FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHERE IDRegiao in (1,2);

-- Regiao 04

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup4'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG4.bak'

WITH NORECOVERY

RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO

-- Erro

SELECT * FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHERE IDRegiao in (1,2,4);

-- Regiao 03

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup3'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG3.bak'

WITH NORECOVERY

RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO

-- Erro

SELECT * FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHERE IDRegiao in (1,2,3,4);

-- Regiao 05

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup5'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG5.bak'

WITH NORECOVERY

RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO

SELECT * FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHERE IDRegiao in (1,2,3,4,5);

    Neste momento o seu banco de dados está todo online! Vale ressaltar que o processo de recuperação como um todo vai levar mais tempo, porém você consegue ir aos poucos permitindo que regiões da sua empresa volte a trabalhar, sem ter que esperar todo o processo de DR.

    Gostou? Espero que sim! E foi bom voltar a postar posts puramente técnicos... Que agora virão com maior frequência, prometo.

Quer o script? Pode fazer o download do OneDrive.

Abraços,

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br