Compartilhar via

FILESTREAM (SQL Server)

  • Artigo

Aplica-se a: SQL Server – Somente Windows

FILESTREAM permite que aplicativos baseados no SQL Server armazenem dados não estruturados, como documentos e imagens, no sistema de arquivos. Os aplicativos podem utilizar as APIs de streaming avançado e o desempenho do sistema de arquivos e, ao mesmo tempo, manter consistência transacional entre os dados não estruturados e os dados estruturados correspondentes.

O FILESTREAM integra o Mecanismo de Banco de Dados do SQL Server a sistemas de arquivos NTFS ou ReFS, armazenando dados BLOB (objeto binário grande) varbinary(max) como arquivos no sistema de arquivos. As instruções do Transact-SQL podem inserir, atualizar, consultar, pesquisar e fazer backup dos dados do FILESTREAM. As interfaces do sistema de arquivos do Win32 fornecem acesso de streaming aos dados.

O FILESTREAM usa o cache do sistema NT para armazenar dados de arquivos. O armazenamento em cache de arquivos no cache do sistema ajuda a reduzir qualquer impacto que os dados FILESTREAM possam ter no desempenho do Mecanismo de Banco de Dados. O pool de buffers do SQL Server não é usado. Portanto essa memória está disponível para processamento de consulta.

FILESTREAM não é habilitado automaticamente quando você instala ou atualiza o SQL Server. Você deve habilitar o FILESTREAM usando o SQL Server Configuration Manager e o SQL Server Management Studio. Para usar o FILESTREAM, você deve criar ou modificar um banco de dados para conter um tipo especial de grupo de arquivos. Em seguida, crie ou modifique uma tabela de modo que ela contenha uma coluna varbinary(max) com o atributo FILESTREAM. Depois que você concluir essas tarefas, poderá usar o Transact-SQL e o Win32 para gerenciar os dados de FILESTREAM.

Quando usar FILESTREAM

No SQL Server, os BLOBs podem ser dados varbinary(max) padrão que armazenam os dados em tabelas ou objetos varbinary(max) FILESTREAM que armazenam os dados no sistema de arquivos. O tamanho e o uso dos dados determinam se você deve usar armazenamento de banco de dados ou armazenamento de sistema de arquivos. Se as condições a seguir forem verdadeiras, você deve considerar o uso de FILESTREAM:

  • Os objetos que estão sendo armazenados têm, em média, mais de 1 MB.
  • O acesso rápido para leitura é importante.
  • Você está desenvolvendo aplicativos que usam uma camada intermediária para a lógica de aplicativo.

Para objetos menores, o armazenamento de BLOBs varbinary(max) no banco de dados, normalmente, fornece melhor desempenho de streaming.

Armazenamento de FILESTREAM

O armazenamento de FILESTREAM é implementado como uma coluna varbinary(max), na qual os dados são armazenados como BLOBs no sistema de arquivos. Os tamanhos dos BLOBs são limitados apenas pelo tamanho do volume do sistema de arquivos. A limitação padrão de varbinary(max) de tamanhos de arquivos de 2 GB não se aplica a BLOBs armazenados no sistema de arquivos.

Para determinar que uma coluna deve armazenar dados no sistema de arquivos, especifique o atributo FILESTREAM em uma coluna varbinary(max). Isso faz com que o Mecanismo de Banco de Dados armazene todos os dados dessa coluna no sistema de arquivos, mas não no arquivo do banco de dados.

Os dados FILESTREAM devem ser armazenados em grupos de arquivos FILESTREAM. Um grupo de arquivos FILESTREAM é um grupo de arquivos especial que contém diretórios do sistema de arquivos em vez dos próprios arquivos. Esses diretórios do sistema de arquivos são chamados de contêineres de dados. Contêineres de dados são a interface entre o armazenamento do mecanismo de banco de dados e o armazenamento do sistema de arquivos.

Ao usar armazenamento de FILESTREAM, considere o seguinte:

  • Quando uma tabela contém uma coluna FILESTREAM, cada linha deve ter uma ID de linha exclusiva não nula.
  • Vários contêineres de dados podem ser adicionados a um grupo de arquivos FILESTREAM.
  • Contêineres de dados FILESTREAM não podem ser aninhados.
  • Quando você estiver usando clustering de failover, os grupos de arquivos FILESTREAM devem estar em recursos de disco compartilhados.
  • Grupos de arquivos FILESTREAM podem estar em volumes compactados.

Gerenciamento integrado

Como FILESTREAM é implementado como uma coluna varbinary(max) e integrado diretamente ao Mecanismo de Banco de Dados, a maioria das funções e ferramentas de gerenciamento do SQL Server funciona sem nenhuma modificação para dados FILESTREAM. Por exemplo, é possível usar todos os modelos de backup e recuperação com dados FILESTREAM e o backup dos dados FILESTREAM pode ser feito com os dados estruturados no banco de dados. Se você não desejar fazer backup de dados FILESTREAM com dados relacionais, poderá usar um backup parcial para excluir grupos de arquivos FILESTREAM.

Segurança Integrada

No SQL Server, os dados FILESTREAM são protegidos exatamente como outros dados, com a concessão de permissões em níveis de tabela ou coluna. Se um usuário tiver permissão para a coluna FILESTREAM em uma tabela, ele poderá abrir os arquivos associados.

Observação

Não há suporte para criptografia em dados FILESTREAM.

Apenas a conta na qual a conta de serviço do SQL Server é executada recebe permissões para o contêiner FILESTREAM. É recomendável não conceder a nenhuma outra conta permissões no contêiner de dados.

Observação

Os logons do SQL não funcionarão com contêineres FILESTREAM. Somente uma autenticação NTFS ou ReFS funcionará com contêineres FILESTREAM.

Acessar dados BLOB com o Transact-SQL e acesso a streaming do sistema de arquivos

Depois de armazenar dados em uma coluna FILESTREAM, você pode acessar os arquivos usando transações Transact-SQL ou usando APIs do Win32.

Acesso ao Transact-SQL

Usando o Transact-SQL, é possível inserir, atualizar e excluir dados FILESTREAM:

  • Você pode usar uma operação de inserção para pré-popular um campo FILESTREAM com um valor nulo, vazio ou com dados embutidos relativamente curtos. No entanto uma quantidade grande de dados é transmitida de maneira mais eficiente em um arquivo que usa interfaces Win32.
  • Ao atualizar um campo FILESTREAM, você modifica os dados BLOB subjacentes no sistema de arquivos. Quando um campo FILESTREAM é definido como NULL, os dados BLOB associados ao campo são excluídos. Não é possível usar uma atualização de Transact-SQL em bloco, implementada como UPDATE.Write(), para executar atualizações parciais nos dados.
  • Ao excluir uma linha ou ao excluir ou truncar uma tabela que contém dados FILESTREAM, você também exclui os dados BLOB subjacentes do sistema de arquivos.

Acesso a streaming do sistema de arquivos

O suporte a streaming do Win32 funciona no contexto de uma transação do SQL Server. Dentro de uma transação, é possível usar funções FILESTREAM para obter um caminho do sistema de arquivos UNC lógico de um arquivo. Em seguida, use a API OpenSqlFilestream para obter um identificador de arquivo. Esse identificador pode então ser usado por interfaces de streaming de arquivo do Win32, como ReadFile() e WriteFile(), para acessar e atualizar o arquivo por meio do sistema de arquivos.

Como as operações de arquivo são transacionais, não é possível excluir ou renomear arquivos FILESTREAM por meio do sistema de arquivos.

Aviso

O contêiner FILESTREAM é uma pasta gerenciada pelo SQL Server. Não adicione ou remova arquivos da pasta FILESTREAM manualmente ou por meio de outros aplicativos. Se você fizer isso, resultará em erros de backup e inconsistência. Para obter mais informações, consulte MSSQLSERVER_3056, MSSQLSERVER_7908 e MSSQLSERVER_7906.

Modelo de instrução

O acesso ao sistema de arquivos FILESTREAM modela uma instrução Transact-SQL usando abertura e fechamento de arquivo. A instrução inicia quando um identificador de arquivo é aberto e termina quando o identificador é fechado. Por exemplo, quando uma alça de gravação é fechada, qualquer possível acionador AFTER registrado na tabela é disparado como se uma instrução UPDATE fosse concluída.

Namespace de armazenamento

No FILESTREAM, o mecanismo de banco de dados controla o namespace do sistema de arquivos físicos BLOB. Uma nova função intrínseca, PathName, fornece o caminho UNC lógico do BLOB que corresponde a cada célula FILESTREAM na tabela. O aplicativo usa esse caminho lógico para obter o identificador do Win32 e operar nos dados BLOB usando interfaces normais de sistema de arquivos do Win32. A função retornará NULL se o valor da coluna FILESTREAM for NULL.

Acesso a sistema de arquivos transacionado

Uma nova função intrínseca, GET_FILESTREAM_TRANSACTION_CONTEXT(), fornece o token que representa a transação atual à qual a sessão está associada. A transação deve ter sido iniciada e ainda não anulada ou confirmada. Obtendo um token, o aplicativo associa as operações de streaming do sistema de arquivos FILESTREAM a uma transação iniciada. A função retorna NULL no caso de nenhuma transação explicitamente iniciada.

Todos os identificadores de arquivo devem ser fechados antes de a transação ser confirmada ou anulada. Se um identificador for deixado aberto além do escopo da transação, leituras adicionais no identificador causarão uma falha; gravações adicionais no identificador terão êxito, mas os dados reais não serão gravados no disco. Da mesma forma, se o banco de dados ou a instância do mecanismo de banco de dados for encerrado, todos os identificadores abertos serão invalidados.

Durabilidade transacional

Com FILESTREAM, após a confirmação da transação, o mecanismo de banco de dados garante a durabilidade da transação para os dados do FILESTREAM BLOB que são modificados a partir do acesso de streaming do sistema de arquivos.

Semântica de isolamento

A semântica de isolamento é regida pelos níveis de isolamento de transações do Mecanismo de Banco de Dados. O nível de isolamento confirmado por leitura tem suporte para Transact-SQL e acesso ao sistema de arquivos. Operações de leitura repetíveis, serializáveis e níveis de isolamento dos instantâneos são suportados. Não há suporte para leitura suja.

As operações de abertura de acesso ao sistema de arquivos não aguardam nenhum bloqueio. Em vez disso, as operações abertas falharão imediatamente se não puderem acessar os dados devido ao isolamento da transação. As chamadas da API de streaming falharão com ERROR_SHARING_VIOLATION se a operação de abertura não puder continuar por causa de violação de isolamento.

Para permitir a realização de atualizações parciais, o aplicativo pode emitir um controle de FS de dispositivo (FSCTL_SQL_FILESTREAM_FETCH_OLD_CONTENT) para buscar o conteúdo antigo no arquivo ao qual o identificador aberto faz referência. Isso aciona uma cópia de conteúdo antigo no lado do servidor. Para obter melhor desempenho do aplicativo e evitar a ocorrência de tempos limites potenciais ao trabalhar com arquivos muito grandes, recomendamos usar E/S assíncrona.

Se o FSCTL for emitido após o identificador ter sido gravado, a última operação de gravação persistirá e as gravações anteriores feitas no identificador serão perdidas.

APIs do sistema de arquivos e níveis de isolamento suportados

Quando uma API do sistema de arquivos não pode abrir um arquivo devido a uma violação de isolamento, uma exceção ERROR_SHARING_VIOLATION é retornada. Essa violação de isolamento ocorre quando duas transações tentam acessar o mesmo arquivo. O resultado da operação de acesso depende do modo no qual o arquivo foi aberto e da versão do SQL Server na qual a transação está sendo executada. A tabela a seguir descreve os possíveis resultados de duas transações que estão acessando o mesmo arquivo.

Transação 1 Transação 2 Resultado no SQL Server 2008 (10.0.x) Resultado no SQL Server 2008 R2 (10.50.x) e em versões posteriores
Abrir para leitura. Abrir para leitura. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abrir para leitura. Abrir para gravação. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1.
Abrir para gravação. Abrir para leitura. A abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. Ambas realizadas com êxito.
Abrir para gravação. Abrir para gravação. A abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. A abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION.
Abrir para leitura. Abrir para SELECT. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abrir para leitura. Abrir para UPDATE ou DELETE. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam as operações de leitura executadas na transação 1.
Abrir para gravação. Abrir para SELECT. A transação 2 é bloqueada até a transação 1 confirmar ou finalizar a transação, ou o bloqueio da transação atingir o tempo limite. Ambas realizadas com êxito.
Abrir para gravação. Abrir para UPDATE ou DELETE. A transação 2 é bloqueada até a transação 1 confirmar ou finalizar a transação, ou o bloqueio da transação atingir o tempo limite. A transação 2 é bloqueada até a transação 1 confirmar ou finalizar a transação, ou o bloqueio da transação atingir o tempo limite.
Abrir para SELECT. Abrir para leitura. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abrir para SELECT. Abrir para gravação. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam a transação 1. Ambas realizadas com êxito. As operações de gravação na transação 2 não afetam a transação 1.
Abrir para UPDATE ou DELETE. Abrir para leitura. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. Ambas realizadas com êxito.
Abrir para UPDATE ou DELETE. Abrir para gravação. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION.
Abra para SELECT com leitura repetida. Abrir para leitura. Ambas realizadas com êxito. Ambas realizadas com êxito.
Abra para SELECT com leitura repetida. Abrir para gravação. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION. A operação de abertura da transação 2 falhará com uma exceção ERROR_SHARING_VIOLATION.

Write-through de clientes remotos

O acesso do sistema de arquivos remoto a dados FILESTREAM é habilitado pelo protocolo SMB. Se o cliente for remoto, nenhuma operação de gravação será armazenada em cache pelo lado do cliente. Os operações de gravação sempre serão enviados ao servidor. Os dados podem ser armazenados em cache no lado de servidor. Recomendamos que os aplicativos que estão sendo executados em clientes remotos consolidem pequenas operações de gravação em operações de tamanho maior. O objetivo é realizar menos gravações.

Não há suporte para a criação de exibições de memória mapeadas (E/S mapeada da memória) usando um identificador FILESTREAM. Se o mapeamento de memória for usado para dados FILESTREAM, o Mecanismo de Banco de Dados não poderá garantir a consistência e a durabilidade dos dados nem a integridade do banco de dados.

Recomendações e diretrizes para aprimorar o desempenho de FILESTREAM

O recurso FILESTREAM do SQL Server permite armazenar dados de objetos binários grandes varbinary(max) como arquivos no sistema de arquivos. Quando você tem um grande número de linhas em contêineres FILESTREAM, que são o armazenamento subjacente para colunas FILESTREAM e FileTables, você pode acabar com um volume do sistema de arquivos que contém um grande número de arquivos. Para obter o melhor desempenho ao processar os dados integrados do banco de dados e do sistema de arquivos, é importante garantir que o sistema de arquivos seja ajustado de forma otimizada. Apresentamos as seguintes opções de ajuste disponíveis com base na perspectiva do sistema de arquivos:

  • Verificação de altitude para o driver do filtro FILESTREAM do SQL Server (por exemplo, rsfx0100.sys). Avalie todos os drivers de filtro carregados para a pilha de armazenamento associada a um volume em que o recurso FILESTREAM armazena arquivos e verifique se o driver rsfx está localizado na parte inferior da pilha. Você pode usar o programa de controle FLTMC.EXE para enumerar os drivers de filtro para um volume específico. Aqui está um exemplo de saída do utilitário FLTMC: filtros C:\Windows\System32>fltMC.exe

    Nome do filtro Instâncias num Altitude Frame
    Sftredir 1 406000 0
    MpFilter 9 328000 0
    luafv 1 135000 0
    FileInfo 9 45000 0
    RsFx0103 1 41001.03 0

  • Verifique se o servidor está com a propriedade "horário do último acesso" desabilitada para os arquivos. Esse atributo do sistema de arquivos está mantido no Registro: Nome da Chave: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Nome: NtfsDisableLastAccessUpdate
    Tipo: REG_DWORD
    Valor: 1

  • Verifique se o servidor está com a nomenclatura 8.3 desabilitada. Esse atributo do sistema de arquivos está mantido no Registro: Nome da Chave: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem
    Nome: NtfsDisable8dot3NameCreation
    Tipo: REG_DWORD
    Valor: 1

  • Verifique se os contêineres do diretório FILESTREAM não têm criptografia do sistema de arquivos ou compactação do sistema de arquivos habilitada, pois isso pode introduzir um nível de sobrecarga ao acessar esses arquivos.

  • Em um prompt de comandos com privilégios elevados, execute instâncias fltmc e verifique se nenhum driver de filtro está conectado ao volume que você está tentando restaurar.

  • Verifique se os contêineres do diretório FILESTREAM não têm mais de 300.000 arquivos. Você pode usar as informações da exibição do catálogo sys.database_files para descobrir quais diretórios no sistema de arquivos armazenam arquivos FILESTREAM-related. Isso pode ser evitado ao ter vários contêineres. (Confira o próximo item do marcador para mais informações.)

  • Com apenas um grupo de arquivos FILESTREAM, todos os arquivos de dados são criados na mesma pasta. A criação de um grande número de arquivos pode ser afetada por índices de NTFS grandes, que também podem ficar fragmentados.

    • O fato de ter vários grupos de arquivos geralmente ajuda (o aplicativo usa particionamento ou tem várias tabelas, cada uma indo para o próprio grupo de arquivos).

    • Com o SQL Server 2012 (11.x) e versões posteriores, você pode ter vários contêineres ou arquivos em um grupo de arquivos FILESTREAM e um esquema de alocação equilibrada será aplicado. Consequentemente, o número de arquivos do NTFS por diretório fica menor.

  • O backup e a restauração poderão se tornar mais rápidos com vários contêineres FILESTREAM se vários volumes de contêineres de armazenamento forem usados.

    O SQL Server 2012 (11.x) dá suporte a vários contêineres por grupo de arquivos e pode facilitar tudo ainda mais. Nenhum esquema de partição complicado poderá ser necessário para gerenciar um grande número de arquivos.

  • Quando há um número muito grande de contêineres FILESTREAM em uma instância SQL, iniciar os bancos de dados com muitos contêineres FILESTREAM pode levar muito tempo para registrá-los no driver de filtro FILESTREAM. Distribuí-los em vários volumes diferentes ajuda a melhorar o tempo de inicialização do banco de dados.

  • O MFT NTFS pode ficar fragmentado e isso pode causar problemas de desempenho. O tamanho reservado do MFT depende do tamanho do volume, de modo que isso pode ser encontrado ou não.

    • Você pode verificar a fragmentação do MFT com defrag /A /V C: (altere C: para o nome do volume real).

    • Você pode reservar mais espaço do MFT usando fsutil behavior set mftzone 2.

    • Os arquivos de dados FILESTREAM devem ser excluídos da verificação de software antivírus.

      Observação

      O Windows Server 2016 habilita automaticamente o Windows Defender. Verifique se o Windows Defender está configurado para excluir arquivos Filestream. Caso contrário, isso pode resultar na redução do desempenho para operações de backup e de restauração.

      Para obter mais informações, confira Configurar e validar exclusões para verificações do Windows Defender Antivírus.

Conteúdo relacionado